Coding a cart: When the user checks out, should I copy every piece of product data into an "order product" table to save its state?

Question

Should I clone the entire product along with all related data into another table for posterity, effectively logging the current state of the product, as it was when the user purchased it?

Pros:

• The product (as the user saw it when it was purchased) is held for posterity and never changed. If the product data changes over time, it won't change the original order information.

Cons:

• A lot of added complexity:
  • Several more tables required in the database.
  • Similar schema to keep track of.
• More disk space used.

Does anyone have a clever solution to this conundrum?

Answer 1

I would suggest adding a 'version number' to the product data, and keeping old versions. This way, you maintain the product data, don't add more tables, and have the option of culling old products and orders later if you are using up too much space.

Answer 2

I would suggest cloning the attributes of the product that are relevant to the order. For example, the unit price. Most of the attributes of the product are not part of the order. Which attributes are relevant to the order depends on how the enterprise does business.

If it's just a few attributes, you can clone them directly into the order detail record (row) and not create another table to hold that data. The added complexity is minimal.

That's my suggestion for transaction processing. If, on the other hand you are building up a data warehouse for long term analysis, then keeping a version on the product dimension table is the right way to go.

Then, you would never do an update to the product dimension. Instead, when a product attribute changes, you would insert a new row into the product dimension to hold the new values. The new row would have the same product key, but a different version number.