Mutable/immutable data with foreign keys - a database design issue -

Let's say we have a webshop, where people can save their shipping / billing addresses for later use. We store our data in a relational database, so obviously the addresses get their table when the customer purchases, we want to record what address they chose for their purchase, so that In its purchase history, it can be reviewed. We can make a foreign key in the purchase table on the address table, so that the DB is somewhat normalized, but in this way, we have to ensure that the addresses are irreversible, so that the shipping / billing address in the purchase history remains the same. The second way is to replicate the addresses in the purchase table so that users can edit / delete freely from the table of addresses, and purchase history remains the same. This replication scheme works well for small amounts of data, but we ask that we want to attach a picture to each shipping / billing address, and now it is becoming very expensive, especially with resources like smartphones On devices. Which approach is better? Are there other ways to deal with this method?

To allow users to manage their addresses, you know what to buy to know There is a need to store information for (by checking a different flag in the purchase table or address table). The main part of the solution is this flag or check it in the database, indicating that an address has been used in a purchase, as noted by @SkuelWogel.

This single piece of information will allow the following scenarios:

1. To enter a new address to the user - there will be no current reference in the purchase history, hence the purchase It can be edited even before.
2. The user has an existing address, which can be used in any previous purchase or not.
3. To change the address for a purchase user, either by editing a new and unused address or by selecting one of the previously used addresses. / Li>
4. If a user edits the existing and used address, save it as a new address instead of the original (and a good idea to find them), which adds a new address has gone.

   You can provide such a pseudo-extinction feature, which shows that this is a historical record (which you should know by your users).

   Shipping versus billing address ID: You can do this already, but these two need to have different columns in the purchase table. Therefore history is kept right with the above control.