2

I am designing a database where i have an Orders table. (obviously this is an online shop)

The Orders table holds an addressId as well, which points to an Saved Addresses table(the saved addresses of a customer). However the customer can edit his Saved Addresses, which means that if he does that the address in the Order will change as well, which is wrong since the Order was shipped to the old address(before editing).

I was wondering what is the norm in the community to handle such things?

Do i hard-record? (not point to an address but instead plainly write the address details to an order)

Do i prohibit the customer from editing/deleting Saved Addresses? (this looks like bad UX and i have the feeling that the customer is allowed by law to delete personal information from my database)

What is the ''best practise'' here?

nicholaswmin
  • 21,686
  • 15
  • 91
  • 167
  • When a customer edits an address that has already been used, create a new row instead of updating the existing one. – nvogel Oct 27 '13 at 22:11
  • What if the customer wishes to delete an address? Wouldn't it be wrong if i restrict the user from doing that? – nicholaswmin Oct 27 '13 at 22:13
  • 2
    Would you also allow the customer to delete a past order? IANAL but if you are going to keep a copy of the order it seems reasonable to keep the delivery address for a period of time. Potentially important for resolving future queries/disputes I would have thought. – nvogel Oct 27 '13 at 22:23
  • possible duplicate of [Database optimization orders](http://stackoverflow.com/questions/11927084/database-optimization-orders) – Branko Dimitrijevic Oct 28 '13 at 00:30

2 Answers2

1

It is a good practice to take a snapshot of any financial transaction, including its related reference data once the transaction is finalized. Auditors like to be shown that your data represents an accurate picture of your business activity.

For practical purposes, this usually means having rules like: "Thou shalt not update or delete transactional data (e.g. sales) once they are confirmed."

Better yet, use double-entry style accounting for financial transaction data. This allows you to keep a trustworthy record of financial transactions which nevertheless allows you to record changes to history (like issuing a credit note or cancelling an order) without destroying the history in your database.

For dimensional data, like addresses, it's a good idea to keep a history table of all recorded addresses, not just the current ones, and then relate your order to the appropriate address version in your address history table.

Joel Brown
  • 14,123
  • 4
  • 52
  • 64
1

I've solved a similar problem by changing what happens when the Customer edits their address. Instead of updating the existing record in your Saved Addresses table, create a new record with the new address and link that to the Customer record. The old address remains in the database linked to the Order but is not linked to the Customer anymore.

John Cullen
  • 83
  • 7
  • I opeed for both. Taking a ''snapshot'' of the order details and also pointing to an addressId. We plan to use the way you suggested in your answer in regards to deleted/edited addresses. This way we are definetely safe. I understand that we are duplicating some data but orders is a critical part of the system and i want to be sure that i have the address that was used during the making of order stays intact within an order. Thanks for the answer anyway – nicholaswmin Oct 28 '13 at 18:42