Tracking correct order details in commerce database when modifying 'products'

Question

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.

Answer 1

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.

Answer 2

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.

The moment you assign a new ID (which is the PK) to the updated row ('wash your car and clear your house') you are logically creating a new product and all your existing FK references will break.

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

This represents two unique services since their IDs are different and your version column has no use. You cannot track that these two rows are actually the same service that has been modified by the provider.

What you need to do is make the ID and the version a composite key and reference them from your other tables as FK. This way each order will point to a specific version of the service that is offered and will not cause any conflicts.

So your ORDERS table can have the foreign key defined as (ID,VERSION) which point to the composite (ID,VERSION) in SERVICES table.

e.g.

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

In this case, although your FKs will still have different FKs for (1,1) and (1,2) you can do a query where you search for ID=1 and you get the entire history of modification for service whose Id is 1. And your orders will still point to the correct version depending on when they were placed.

This may or may not be supported in your database so check this beforehand.

Some useful links:
MySQL: https://stackoverflow.com/questions/10565846/use-composite-primary-key-as-foreign-key
PostgreSQL: https://stackoverflow.com/questions/8575046/foreign-key-to-composite-key

Answer 3

I think you are missing something fundamental about the nature of products -- they are hierarchical. Typically you would have something like:

Brand
   Product
      Size
        Variant
           Production Batch

So uubder the "Dove" brand you have a "Soap" product in "Large" size with the "pretty wrapper" manufactured as the zillionth batch at the Springfield plant.

The board of directors would be interested in the Brands performance, quality control would be interested in the production batch, sales in "Dove Soap", marketing in the "pretty wrapper".

So you need a hierarchical product structure:

Home Services
     Yard Work
          Mow Lawn
          Clear Snow
     House Work
          Spring Clean
          Put Up Curtains
Educational Services
     Tuition
          Maths Coaching
          Piano Lessons
Personal Services
     Childcare 
          Babysit
          School Run

When sell a new service say "Trim Hedge" you do not modify an existing product but add a new product in the "Yard Work" category. You can inherit all sorts of properties from the parent category -- but you have a unique product definition which should never change.

All the stuff that changes --> appointed time, agreed price and any other special conditions belong in the "order item".