Storing a Shipping Address Best Practice

Question

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?

Answer 1

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.

Answer 2

Are you interested in auditing information? Solution 2 would allow you to easily track changes to customer addresses and allow you to determine who did it (and optionally why, if you include a reason field.)

I am presently working with a system that uses approach 1 and the database is filled with duplicate data. In my particular case the address table is huge and requires quite a bit of processing to get through.

In my mind the ideal solution would be option 3: A mechanism that uses existing records if the data matches, spawns new records if the data changes, and deletes old records when nothing references them anymore. This way you'd get the full benefits of normalizing the data.

Answer 3

Option 3: The address row's foreign key should be the ID of the customer that the address belongs to.

Option 4: Store only the shipping address and provide a form for the billing address only if it differs from the shipping address.

For both options you'd only ever store 2 addresses. If the customer's shipping address changes you'd simply edit it; an enum/flag comes to mind to differentiate between the two. Personally, I'd go with Option 3 and run edits on the existing record any time it changed.