Database Design – Tracking Correct Order Details When Modifying Products

databasedatabase-designdatabase-developmentrelational-databaseschema

I am implementing an ecommerce database. This is slightly different than most as the 'products' that are for sale are for services provided. For example a user (vendor) of the system may define a service to 'wash your car'.

Table: Services
--------------------------------------------------
| ID (pk) |      Title       |    Description    |
--------------------------------------------------
     1       Home Services      wash your car

I have omitted a number of columns, but should still be able to make my point.

A client can then purchase the Home Services. So I have an orders table with a FK to the the Services table. Standard ecommerce stuff.

The issue I have is what happens if the vendor decides to slightly update the description or even the title? This may have the affect of changing the entire service. For a normal commerce site, you'd add a discrete product. If this product changed, you would create a new product and discontinue the old one. With the packages, we need to allow updates as the description is the product and the sales pitch (maybe thats my problem though – maybe separate the two?) so I have to allow changes.

For example they could change the description to 'wash your car and clean your house'.

The obvious issue with this is that existing orders will still reference the same PK on the services table but the service is now different.

I'm wondering what the best solution is to allow changes to be added, but maintain the correct data for existing orders. One solution I thought of is to not update the row, but add a new one and a version number

Table: Services
------------------------------------------------------------------------------
| ID (pk) |      Title       |            Description             |  Version |
------------------------------------------------------------------------------
     1       Home Services     wash your car                           1
     2       Home Services     wash your car and clear your house      2

We can now access the correct information for previous orders, and existing customers can purchase the new home services option.

This feels like a sensible way to do it, but want to see if anyone knew of any other solutions?

One other solution is to allow updates on the services table, but then for each order, save the description and title (similar to how sometimes in commerce schemas the final price is saved directly to the order). Issue I have with this is a lot of duplicate data being saved.

Best Answer

What you want to do is "freeze" the information at the time of an order.

When a client makes an order you just make a copy of the description, price, product etc. in the order record.

For instance, the price of a product can change a lot. You don't want a new product record for every price change. So you "freeze" the price in the order record.

Once an order is made, a lot of the information references to the order and not to the products anymore. Things like price, description, vendor, vat, but also shipping adress get stored with the order. It now has become historical data. That way, even when things change, like price, decription or adress you can always see what the actual order was. A change of shipping adress for a client for instance, doesn't change where an order was shipped originally.

See it as a contract: you take your product catalog, get the product details and client details and put them in the contract, the actual product and the name and adress of the client. You don't put in the contract: client 12345 has ordered 3 of product 7 on page 25 of the catalogue version B from may 2013 with 10% sunday sales discount.

Related Topic