How to let user change his address without affecting the same one in the order?

I want every order to have one shipping address. But if I added shipping_addresss_id in order table, then if the user ordered something that is shipped and delivered, and after months the user changed his/her shipping address into something else, then the orders already processed months before will also change into the new address, leading to falsified results. If he/she deleted the address completely, it also would cause problems.

I thought about this solution: whenever the user orders I will provide him/her with all his/her addresses from shipping_address table and the user will chose one , but instead of pointing into shipping_address_id , I will get the hard string of address and save it in the order. Is it a good solution?

Here the relevant parts in my current ERD:

This is the whole database

Indeed, there is a difference between the curent list of possible shipping addresses that a customer can chose for new orders, and the historical addresses used for shipping that cannot change once the parcel left the warehouse.

There are three main approaches to address this problem:

1. Denormalization: have the required address fields in the order and don't change it anymore once the order is in a shipment status (e.g. "ready to be collected", or "picked by carrier"). The easiest way to populate this shipped order address seems to be to copy the valid shipping address at the moment of the status change.

   Pros:

   • easy to implement and use, doesn't affect the management of (desired) shipping addresses.

   Cons:

   • the order would contain information that corresponds to a different concern, which makes maintenance difficult: if address format would change, you would have to change also the order entity.
   • the order would contain lots of redundant addresses.
   • Moreover, you may have duplicate code, since displaying the effectively shipped address would be performed differently than desired shipping address.

2. Add a shipped_address entity for the effective order shipping address: you'd just store there the address used at the moment of the shipping, with a relation to the relevant order.

   Pros&Cons: It reduces somewhat the cons of the first option, by isolating the address in a separate table, but is very similar.

3. Smart shipping address management: rethink the way the shipping address is used. Your order should refer as well to the shipping address. In this case, you would have to make sure that shipping addresses used in shipped orders are not changed anymore (e.g. using a flag). If the address of a customer would change and the current address is such a flagged address, you'd then create a new address instead of the existing one. Since customer can have several desired shipping addresses, you'd then also need to take care of whether a customer still wants to use the historical address or not.

   Pros:

   • denormalization is ensured and redundant addresses are prevented
   • updates of the shipping address can be done as long as no order was effectively shipped to that address, and in a single place
   • the shipping address of orders already shipped is preserved
   • deploying shipping address is always done the same way, whether it's an historical address or a real one.

   Cons:

   • requires a more careful management of the shipping addresses.

Conclusion: I'd advise to go for option 3. It requires you to think a little bit more about shipping addresses and address state, but ultimately would lead to a more powerful solution.