Lets say there is
orders table with few foreign keys. One of them is
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 :-
- create a new table (OrderAddress).
- when an order is placed, insert from CustomerAddress to OrderAddress.
- 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 ?