Multiple Billing/Shipping addresses

[Scoop]

I am working with a database for Customers/Addresses/Orders/etc. and am
trying to figure out the best table design to handle what I need to do.
I know there is no *universal* way of doing these things and that
table design for multiple address situations is very situational but I
thought I'd try to gather some input...

I have theses tables:

tblCustomers
--------------------
CustomerID (PK)
<otherCustomerInfo>

tblAddresses
-------------------
AddressID (PK)
<addressInfo>

tlnkCustomerAddresses
------------------
CustomerID (PK) - Linked to tblCustomers.CustomerID
AddressID (PK) - Linked to tblAddresses.AddressID

tblOrders
-------------------
OrderID (PK)
CustomerID - Linked to tblCustomers
<otherOrderInfo>

tblOrderDetails
------------------
OrderDetailID (PK)
OrderID - Linked to tblOrders.OrderID
<individualOrderLineItems>

The Many-to-Many relationship handles how I want Addresses related to
Customers but I'm having trouble with the relationship(s) between
Addresses and orders. I want to be able to ship different line items
on an order to different locations but I don't think it's necessary to
be able to bill individual line items to a different address. So,
should I add a "BillToID" field to "tblOrders" and a "ShipToID" field
to "tblOrderDetails" and link each to tblAddresses.AddressID? Are
these relationships even necessary?

One problem I have is I want to retain history of where orders are
shipped so I don't want users to delete or even update an address and
have that affect what shipping address an order (line item) is
associated with. So if a customer moves, what is the best way to
update/change there address(es)?

I don't know if I'm doing a very good job of describing my dilemma(s)
but if anyone understands what I'm trying to do and can offer some
ideas, I would appreciate it.

 

[Designing-Systems.com]

hi,
one way to handle this is to have the <b>AddressId</b> added to the Orders
table. Then enforce referential integrity and Cascade Update (without cascade
delete) so that related records cannot be delelted. This referenctial
integrity will enforce that if an address is involved in the Orders table, it
cannot be deleted.

Regards,