How to keep copies of ordered items in a web shop?

Question

People buy stuff in a web shop.

I take their orders and save them to a database. For each ordered item, I need the quantity and the information about the item. At the very least, I'm going to need to save the current price and name of each item but I would really prefer to save everything.

And here comes the question... What's the best way to keep all the information about each ordered item as it was at the time of purchase?

1. Copy into a separate table with the same columns as the product table
2. Make copies of products in the same table, marked as non-editable, non-viewable copies
3. Some sort of copy on write schema that saves some space until the "live" product information is actually changed?
4. ????

For simplicity, let's assume all product information is kept in a single table.

Answer 1

Generally an ecom store has the following tables:

accounts
products
order
orderdetails

There usually is a lot more to this. But this is the base.

Account = the user shopping in the store
Product = the product description, price, etc.
Order = the high level order details such as purchase date, shipping/billing address
OrderDetails = the low level order details such as price at time of purchase, quantity, etc.

Take a look at an open source shopping cart such as DashCommerce to get a good idea of how a working cart system is set up. I like this particular one as it implements many of the required and more complex of ecommerce!

Answer 2

If the products in your shop are going to change frequently, then it sounds like what you need is a product version history.

Basically, you should try to identify all the properties/fields of your shop items that are going to frequently change, and put them into a separate product_properties (or something) table, with a timestamp for that particular version of those properties. Perhaps all your properties will change all the time, in which case you might get away with a single table. In any event, you can assume that the newest timestamp is the latest version of the product.

When the shop owner updates a product, you create a new record in product_properties with all the changed properties, and update the main product table record to point to this new record (or simply just pull out the newest timestamp at display time).

When someone makes a purchase, you record the product id and the history/version id of the product in the purchase table.

With this type of history setup, you've normalized the product data, so it's not stored over and over again with each purchase record, and you can still get the specific details of each purchase.

Answer 3

It is a good idea when storing the "at the time of purchase info" to only link to a sku --- but actually copy in all the information of the product table (including pricing, description, sku, etc). In case in the future those product description and pricing changes, the orders you have won't break.

I normally link the product in by product ID and then also copy in all the product data into the order detail.

Likewise, you will want to do the same between Accounts <--> Orders. Link an order to account ID, but store all of the actual customer information (name, address, etc) so it can't be disputed later if it was changed, etc.

You won't have to worry about which version of this sku it was setup as -- whatever it was at the time, it was, and that's what was billed. That way all of a client's past history stays in effect.

If you want another layer you might want to implement tracking changes to the account and product tables so you know what was changed when.

The above is what i have seen in systems like SAP B1 and the design pattern makes sense.. For storing some extra information you get rid of the complexity elsewhere.

Answer 4

Jas Panesar's idea of a SKU is quite a good one. If however, you're looking for a simpler approach, you could store the information in an an array, serialize it, and then store the serialized result in a single field in your database.

While this isn't as robust as a solution, it does give you the ability to quickly and easily grab information without redesigning your whole database structure.