Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 3 down vote accepted
+50

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.

share|improve this answer
    
but if you are going to store the information of a service in EVERY order, is it not better to maintain a history of the changes, and then allow the orders to reference to them? The assumption is that you have more orders than you have price changes so you would store less information if a change table is created? –  Gaz_Edge Feb 3 at 10:50
    
You want to have a history of changes, but with orders, you can choose to store the vital information with the order and not as a reference. That way you can't retroactively edit a 1000 and 1 orders. –  Pieter B Feb 3 at 10:52
    
ok, but say i have a product, its price is changed one a year, but it has 50 orders at one price and 50 orders at the other. If we freeze the details in the order, we are saving the same information 50 times, but if we save the changes we only save twice. Thats why i can't understand why the order takes a snap shot. It just seems wasteful as you store the same information over and over again when you could just reference to it? –  Gaz_Edge Feb 3 at 10:55
    
Because references can break your history. Once a price is set in an order it won't change so it isn't actually refering to anything anymore. –  Pieter B Feb 3 at 11:00
    
ok, makes sense. Can you expand on you answer a little then? Need a few more details to satisfy a 'conical answer' for the bounty –  Gaz_Edge Feb 3 at 11:30

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: http://stackoverflow.com/questions/10565846/use-composite-primary-key-as-foreign-key
PostgreSQL: http://stackoverflow.com/questions/8575046/foreign-key-to-composite-key

share|improve this answer
    
hmm im not a fan of composite keys. I could have a services table, and a service_descriptions table. The latter has FK to the former? –  Gaz_Edge Jan 30 at 21:18
    
Yes, that would also be a good solution. In that case your services table could be immutable and all the changes to a service would be tracked in the service_descriptions table using a one-to-many relation. However each object population would need to look up two tables which would be slightly slow (but the difference is not much considering you are saving disk space) –  Jit B Jan 31 at 5:22

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".

share|improve this answer
    
-1 no even close to attempting to answer the question –  Gaz_Edge Feb 4 at 15:51
    
It is really -- he needs a hieracrachical product structure -- Having one product "Home Services" then constantly changing the description from "Oven Cleaning" to "Paint Fence" is just plain silly. Edited post to make this clearer –  James Anderson Feb 5 at 1:24
    
at no point have you mentioned anything about 'Tracking correct order details in commerce database when modifying 'products''. All you've said is 'create a product hierarchy' not very helpful really –  Gaz_Edge Feb 5 at 8:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.