Take snapshot of DB table row?

I want to take a snap shot of a row in a MySQL table.

The reason being, if someone buys a product. I want to take a snapshot of that product to store for the order.

It needs to be a snapshot to maintain data integrity. If I just assign the product to the order, if the product changes in the future the order will show those changes. For example if the price changes, the order will now load the new data and say that it sold the product at its new price rather than what the price was when the order was placed. So a snapshot needs to be assigned to the order instead.

The way I did this in the past was having 2 tables, one for products, and one for snapshots of products. The snapshot had every column as the regular table plus extra colums like order_id

I had a script to take a snapshot that automatically looked at the fields in the regular table and tried to do an insert into the same fields into the snapshot table.

The biggest problem with that approach is that, if I added a column to the regular table and forgot to add开发者_高级运维 the same column to the snapshot table; the script would try to insert data into a non existent field and fail.

I also disliked the idea of having 2 tables that were nearly identical. I think maybe figuring out a way to use one table for both purposes might be better.

---

So I am wondering if there is a known method I am unaware of to solve this issue?

My previous project used no framework but my next one will be using CakePHP if that matters.

---

I think the best way of handling this would be to roll the "snapshot" information into an orders_products table. So if you have an order, store the total price, tax, etc. information in a single row on the orders table and reference that order_id on your orders_products table. On your orders_products table, you can have order_id, product_id, price, quantity, discount and whatever else you need.

---

Seems like your previous is fine. But that you just need to do more testing to ensure that you don't forget to add the new fields to the snapshot table. Seems like a basic test that would be easy to do. The other alternative is to just use a big text field, and store the snapshot as XML. This will let you store the snapshot regardless of if the schema changes. Depending on how much you want to query this data, it may or may not work for you.

Also you may not want to store every field, as it may just take up extra space. For instance, if you have the location of the image file of the item, you may not want to store that, as it may not be important at a later date. You could try querying information_schema to query which fields are in the snapshot table, and only copy the available fields.