I have a question regarding my db design.

Right now, i have an Address Table and a Orders Table. Each Order record has a billingAddressId and a shippingAddressId which points to a record in the Address table. And they wont point to the same record even if the address is the same. I also have a customers table which each customer can have multiple addresses through a intermediate table (CustomerAddress).

However, when a customers uses one of their addresses to place an order, a new address record is inserted with a copy of the data that way if a customer updates an address associated with their account, it doesnt affect the order. That way if i bring up an order a year down the road, it still had the address the order was actually shipped to and not the newly updated address.

Should be using the Address table with the Order Table, or should have put the address fields right into the order table?

ie. Order.BillingAddress1, Order.BillingCity, Order.ShippingCity, etc...

Thanks

Answer,

If the address is actually part of the order, then I would see no reason to have them in separate tables. Separating them doesn't save you anything design-wise. It might save performance in certain situations, like when you want to grab the order without the address, but that might be splitting hairs.

It sounds like you could have a business rule that addresses can't be changed, just added/inactivated, so if a customer "changes" their address they are actually adding a new one and deactivating the old one. That way orders can share a reference to the same address with no risk of losing historical information when it changes.

I don't see anything wrong with your solution, though.

Phil

Answer I'd have done it the same way as you did.

From a normalization point of view and from O.O. point of view, an address seems to me like an entity, and the table is already there, so why would you repeat those columns twice in the oreder table.

I'd say all addresses go into the Address table.