Storing Ecommerce Order Information Correctly

Yesterday I was doing a tiny bit of web realted freelancing for a friend – simply involving doing a bulk, conditional update of his entire stock for his ecommerce website. The site itself was simple in design and used PayPal with a sprinkle of IPN. So, after a bit of MySQL magic, I updated all of his prices, for all ~700 products in one fell swoop – all good so far, easy money.

However, after an hour or so of emailing the store owner I got a very worried phone call. In short, the bulk price update to had caused all archived order invoices to become incorrect. Why – due to bad database design by the original developer, let me explain.

The web developer had stored the order as you would expect – an order header table, coupled with a order lines table (one record for each order item). All good so far. Where the previous web developer had fallen short is not storing the price at the time of the order, instead simply storing the product id within the order lines table and looking order details via the way of an INNER JOIN – bad move unfortunately. Obviously, in any sort of product price update (as above) all of his historical order information would become null and void. The latter was especially bad in this case, as the store owner pretty much used the list of store orders as base for financial and year end reporting, as the business was pretty small (no Sage then). Explaining the latter to a none technical client is even harder, believe me 🙂

To avoid such nightmares never, ever store such information in this manner – always store product details at the time of ordering. Storing products details at this stage will ensure that an order made in 2008 will be correct when viewed even 10 years later when prices will have been updated. So, in the order lines table, as an absolute minimum store the product name, price and any attributes that will affect price – meaning an INNER JOIN with the order header table is not required.

Depending upon your business rules and frequency by which your product information changes, you could also store the product at time of ording, within a product archive table – that stores basic product info. This way, there is the benefit of saving the description the customer saw at time of ordering.

What is descriobed above is basically bog standard when making any ecommerce solution, or even using something bigger like Opencart, but when it is not present, the amount of issues it causes is huge.

Luckily for me (and the store owner) I created a quick backup of the entire store’s database schema and data before making the bulk update, so was able to restore this for the time being. All I have to do now is amened the database design accordingly and alter the way the site stores order information.

So, never ever rely upon relational data, to view and store data that needs to be achived.