Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.