foreign key table update impact

Question

Lets say there is orders table with few foreign keys. One of them is addressId.

Orders
-- Id
-- orderNum
-- customerId
-- addressId (foreign)

CustomerAddress
-- Id
-- customerId
-- street
-- cityId
-- areaId
-- postal
-- geo

Customer
-- Id
-- Name
-- more columns

now when the customer updates the address in profile. the updated address will show up even for past orders that are linked to that record. which is not good.

My thought solution is :-

1. create a new table (OrderAddress).
2. when an order is placed, insert from CustomerAddress to OrderAddress.
3. get addressId of the newly inserted record from OrderAddress and save it in the Order record.

this way I make sure the order address would not get modified in case the original address has been. But it's kind of expensive.

Is this good practice ?

Answer 1

I would maintain two address tables. Not an address per order. These would be...

1. CustomerAddress - would always be their home address.
2. ShippingAddress - This could be anywhere they would like something shipped to, even if it's somewhere other than their home address.

If they ever update their shipping address, simply insert a new record in shipping address and mark it as active or primary. This way all previous orders still link back to shipping address 1, and all future orders link to shipping address 2.

Answer 2

I would simply duplicate the street, cidtyId and postal columns in the Orders table and keep the addressId column. This allows you to update these columns from the CustomerAddress table in case only a typo was corrected in the address and the address was not changed completely.

But fundamental questions are:

• Why does Orders have a customerId as well as an addressId?
• How is the address linked to the customer? Does the customer have an addressId column or is there a link table between the two?

In case a customer can have several addresses, there should be a customerId foreign key column in CustomerAddress. It would then be possible to only have a customerAddressId in the orders and this would determine the customer as well. Not only the address columns should be duplicated, but also the customer name. Sometimes companies change their name.