MySQL - Best Practices for Storing a Shipping Address

MySQL

I'm building a checkout system which needs to store the customer's shipping address in the database and am unsure the best way to go about it.

Currently I have an addresses table which stores each address with a unique ID:

id | address         | city     | division   | country | postcode
-----------------------------------------------------------------
1  | 23 Example Road | York     | Yorkshire  | GB      | PE21 5QR
2  | 7 Lala Land     | Stockton | California | US      | 96341

I then have a many to many relationship table called account_addresses which links addresses to user accounts:

account_id | address_id
-----------------------
23         | 1
48         | 2

For storing the shipping address in the database I was going to allow the user to select one of their existing addresses or let them add a new one to their account and store that address ID in the orders table. However, I realised that if I just store the address ID, if the user edits that address it would change the shipping address thus making it wrong.

I only see two solutions:

  1. Store the shipping address as a new row in the addresses table
    although this will duplicate the data that already exist.
  2. When a user edits their address it inserts a new address and leaves
    the old one in the database instead of updating the data.

What's the best solution and why?

Best Answer

The Shipping Address is a property of each and every order, not of the customer. Even if it was a property of the Customer, it would be necessary to know where past orders were shipped to if a customer relocated.

Therefore all that can be stored as a Customer property are the Default Shipping Address (and Default Billing Address), for pre-populating each order and invoice as they are created. This is not a denormalization, because it directly reflects and captures the required business rules.

Now that the historical accounting records are taken care of, there should be a CustomerLocation table with FK's to three Address records in an Address table: Physical Address; Default Billing Address; and Default Shipping Address. The first of these records should be associated with an EffectiveDate, so that again a historical record exists as changes occur.

Related Question