Im designing my first eCommerce schema. I've been reading around the subject for a little while, and am a bit confused about the relationship between an order_line_item and a product

A product can been purchased. It has various details, but the most important is unit_price.

An order_line_item has a foreign key to the product_id purchased, the quantity purchased and the unit_price at the point in time the customer purchased the product.

Most of what I have read says that the unit_price on the order_line_item should be explicitly added (i.e. not referenced through the product_id). Makes sense, as the store could change the price in the future which would mess up order reports, tracking, integrity etc.

The thing I don't understand, is why directly save the unit_price value to the order_line_item?

Would it not be better to create an audit/history table that documents the unit_price change of a product?

When an order_line_item is created, the foreign key of the product_audit table is added and the price can be retrieved (by reference) from there.

There seem to me to be a lot of positives to using this approach (less duplication of data, price change history etc), so why isn't it more frequently used? I haven't come across an example of an eCommerce schema that uses this approach, am i missing something?

UDPATE: It seems like my question relates to Slowly Changing Dimension. I'm still confused though as Slowly Changing Dimension relates to data warehouse and OLAPs. So can Slowy Changing Dimension types be applied to my main business transaction process database (OLTP)? I wondering if I'm mixing a lot of concepts up, Would greatly appreciate some guidance.

  • I would actually do both: store the sales price in the order_line and store a history of product prices. Because both serve different purposes. Storing the sales price will make queries on orders much easier and faster. On the other hand you might want to retrieve old prices even for products that have not been sold. – a_horse_with_no_name Feb 1 '14 at 11:58
  • Surely making order queries easier can not be the only driver behind saving the final price every time. Its a relational database after all - queries wouldn't be that much harder. – Gaz_Edge Feb 1 '14 at 12:16
  • 3
    Storing the sales price in the order line isn't "non-relational" (and I do consider it normalized as well as the sales prices is a direct attribute of the order line in my opinion). The art of using a relational database is about knowing the limits. If you are running a shop with 100 products and 5 orders a day, then storing and retrieving old prices isn't a problem. If you are running a marketplace with millions of products, thousands of dealers and hundres of orders per minute, then querying that history will be a problem. – a_horse_with_no_name Feb 1 '14 at 12:22
  • Where would you store the history prices? From what I'm reading I'd need two databases. One for OLTP one for OLAP. The history goes in the OLAP? – Gaz_Edge Feb 2 '14 at 22:55
up vote 10 down vote accepted
+50

As you've identified, storing the price on the order makes the technical implementation easier. There are a number of business reasons why this may be beneficial though.

In addition to web transactions, many businesses support sales through other channels, e.g.:

  • Over the phone
  • Sales agents "on the road"
  • At a physical location (e.g. shop, office)

In these cases the order may be entered into the system at some time after the transaction took place. In these circumstances it can be difficult to impossible to correctly identify which historical price record should be used - storing the unit price directly on the order is the only feasible option.

Multiple channels often bring another challenge - different prices for the same product. Surcharges for phone orders are common - and some customers may negotiate themselves a discount. You may be able to represent all possible prices for all channels in your product schema, but incorporating this into your order tables can become (very) complex.

Anywhere that negotiation is allowed it becomes very difficult to link price history to the order price agreed (unlesss agents have very narrow negotiation limits). You need to store the price on the order itself.

Even if you only support web transactions and having a relatively simple pricing structure, there's still an interesting problem to overcome - how should price increases be handled for in flight transactions? Does the business insist that the customer must pay increases or do they honour the original price (when the product was added to the basket)? If it's the latter it the technical implementation is complicated - you need to find a way to ensure you're maintaining the price version in the session correctly.

Finally, many businesses are starting to use highly dynamic pricing. There may not be one fixed price for a given product - it is always calculated at runtime based on factors such as time of day, demand for the product and so on. In these cases the price may not be stored against the product in the first place!

I will add some practical points that I have seen.

  1. Products are transient.

    What they may signify today, may not be the same as what they used to signify an year back. The same sku code (and hence the product_id), might refer to different variant/kind of the product at different stages.

    Not everyone understands all the concerns at hand; hence a user may change the atrributes of the original product instead of creating a fresh one out of his own ignorance. A lot of times, this could happen because of the plan a user is on (Hey! I can have 100 sku's only, so why not keep changing the older ones instead of upgrading the plan) So, you see, in a lot of carts, a product will never signify the same thing forever.

  2. Different prices based on ordering and shipping conditions

    As user @Chris has mentioned, different prices may be applicable in different scenarios.

    In most carts, you will find at least 3 different fields being stored - the unit price, the discount amount and the discounted price. In more advanced ones, you will find 2 more - unit price with tax, discounted price with tax. You may find a couple more fields to describe the shipping method charges, and additional payment method charges. The tax percents may vary depending on the state, the product, the country, shipping method, and so on, and so do the other cost heads. Similarly the discounts can vary depending on geography, promotions, time of sale and so on. Hence, there is information which can be obtained at order level only, and this combined information cannot be generated from data in products table alone.

  3. Separation of concerns

    A lot of cart are implemented in a way, so that different teams can have control over different parts of data. Some one managing the order system doesn't always need to know what all products are in stock, what were there prices at different point of time, what are alternatives for a given sku, and so on. Keeping product related data alongwith the order data helps achieve a separation of concern. This could also be true at development stages, if different teams manage different parts of the system.

  4. Easier scalability across multiple systems

    A lot of the times, the Order Management System, the Rule Engine, the Catalog Engine, Content Management System are all built/maintained as separate systems of their own. This helps optimize for various load conditions and generate specialized intelligence for each of the system. One system, then, cannot be held to ransom because of non-availabilty of information from another system.

  5. Faster development and running time

    I have used the term "development time" here, though using "debugging time" would be more apt. Whenever any new development is happening, it will be faster if data needed is available without adding complexity of its own, because then, there will be comparatively smaller debugging cycles.

    Imagine you were asked to generate on-demand reports for discounts offered on a day to day basis for a given month half a year back. If you have the original price, the discounted price in 1-2 tables along with the order, order item details, this is pretty much straight forward. If however, you have to go and fetch prices from another table, and then the applicable discounts from another table, and then figure out the details, both the development and running time will be higher.

A good design should try to optimize as much for the future, as it should for the present.

It may end up costing more in storage, but I prefer to house all relevant details of the sale with the transaction itself, so that if for whatever reason our audit trail gets broken, or an administrator overrides the safeties in place, the details of the sale like: currency used, unit-price, qty, taxes applied and what value they came to, etc. are all available. I generally store that as XML so it can be flexible from sale to sale.


EDIT: To expand on what I was briefly saying above, in my follow up comment below, and what @a_horse_with_no_name touched on above, redundancy in transaction data is not only important, but it's also necessary at scale.

I'm assuming that you're building out using OOP and so you should likely have a transaction object and either an all encompassing product object and/or a price object. In my own personal experience, I prefer to be verbose in my history, storage is relatively cheep.

What we've done is create an object history which you can facilitate using a your existing RDBMS or some flavour of NOSQL key value store ( or even better a RDBMS that allows NoSQL like connections like handlersocket or memcache ), and we store the object history that way, with every detail and price change in one place easily and quickly available. If you're serious you could even use DIFF's to save on storage and only store the changes forward, although it has it's own caveats. That should take care of your history, and the advantage of serialized objects is that your system will/should be able to bring them back up as the objects they were stored. That takes care of history.

With regard to my suggestion, storing the transaction details like taxes, currency, etc. with the transaction itself means no need to look elsewhere for those details, your transaction object will be aware of its properties and you're views can take care of presenting the varying data as you see fit. You get speedy access to the snapshot and have the added benefit of redundant and verifiable records.

It's worth it, trust me!

  • that doesn't really make sense to say that you don't use it because it could be deleted. You could override any data. Any strategy should keep backups – Gaz_Edge Jan 31 '14 at 20:01
  • @Gaz_Edge They're not mutually exclusive, you can still utilize an audit trail, and store the details with transactions, the redundancy in this case is justified. Never leave yourself with data this important subject to a single point of failure. In our case, I use a centralized object store as the history mechanism rather than trying to build a history per object type (like a transaction or product in this case). I'll have both the entire transaction object in history but all the most important details with the record itself. Thats entirely aside of the product objects in history. – oucil Feb 2 '14 at 21:56
  • an what if I wanted to run reports on orders? Like how many product x has been purchased? Or how many orders over y amount? Saving as XML means you lose the ability to query the data, unless i'm mistaken – Gaz_Edge Feb 3 '14 at 13:53
  • @Gaz_Edge Not true, if you're talking MySQL, you've got SELECT ExtractValue(field_name, '/x/path/'); so you could filter for things like, all transactions in a specific currency, or all transactions with a certain minimum tax value, or whatever. Larger scale reports can be done from the object history. For larger scale reports you can set up an elasticsearch server/instance which has BigData style reporting and it easily scales into the many millions of docs+. – oucil Feb 3 '14 at 14:07
  • @Gaz_Edge I should also mention, the reports you're talking about (purchases over value, sales of product, etc.) are common reports and those should have values stored as columnar values with the transaction record for faster processing. Anything that is important but not necessarily reported on often can go into the XML. The snapshot data is really only for two things, 1. the Slowly Changing Dimension issue, and 2. Validation and comparison when a customer complains, and you need to quickly see who's right. It's not for every day use. – oucil Feb 3 '14 at 15:40

My vote would be to store the unit price on your line item and track the pricing history of your products in a separate table. My justification for this is for added flexibility.

Even if your pricing structure is rigid and well defined and doesn't allow for the variations that @Chris Saxon mentioned above, are you comfortable that it will always be that way? Even if you are confident, why paint yourself in a corner? I think it would be a good idea to store this on the line item detail because I can't think of a compelling reason to keep it separate.

As for storing your pricing history, there is definite value in storing that separately as there could be changes in a item's price and nobody bought it. That would definitely be useful info to know if a price change was ineffective. As you mentioned, this is a classical use case of a Type 2 Slowly Changing Dimension in a data warehouse scenario. Typically any price change in your product table would be captured and a new row would be added to the dimension table with the updated price and a time stamp to indicate when this change took place. The previous row would have it's end date updated to indicate that it is no longer the effective price. So one approach would be to track this type of change in a data warehouse.

However, if you do not want to concern yourself with designing a data warehouse schema and ETL process at the same time as designing your OLTP ecommerce database, then this history could certainly be captured in our ecommerce database. This could be done as you described with creating a separate product_audit table that hangs off of the product table and contains start and end dates for when that version of a product was in effect. It could also be done in the product table itself by adding start and end dates to the table to indicate which product is currently active. However, depending on the number of products and number or pricing changes that your company undergoes, this could make your product table much larger than intended and could cause query performance issues later on.

Lastly, separating out your pricing history from the actual unit price on the line item could definitely give some other analytic opportunities to see when a product was sold at a price that was above or below the listed price at the time.

  • thanks for the answer. I think the strategy I am going with is going to be to design the OLTP per the book. I actually do like the idea of having a data warehouse for reporting. Although it adds some additional work (creating a new schema) the schema can be tailored to OLAP. Sort of avoiding a scenario where I'm trying to fit a square peg in a round hole. – Gaz_Edge Feb 5 '14 at 19:05
  • @Gaz_Edge: Am in a similar situation in terms of making a decision for my new project. Can you please care to share your thoughts about what design approach you took a year back? Did it work well? – Coder Absolute Oct 24 '15 at 6:45
  • @CoderAbsolute my original solution was very 'database' orientated. My application is now centred around a Service Orientated Architecture. I now have many small decoupled database schemas rather than one tightly coupled one. The need to 3N across one massive schema has now gone. So I now just add the unit price directly to the order. Keeping historic product price changes has now vanished as it was not a business requirement. Hope that helps. – Gaz_Edge Oct 24 '15 at 8:36

I totally agree with the primary idea of keeping order related information (context) together. Just a minor side note that such a situation will arise only when you are designing your application very much database centric and everything revolves around the big fat db. If you switch your point of view by looking at the problem domain from a different angle you will clearly observe that order is a captured snapshot of a very special event in the lifecycle of your application. When you handle problems based on the context then database issues will become secondary and complexity that everyone is afraid of about querying and making reports will be handled seamlessly in the domain model.

  • Some small examples would make your answer much easier to understand. Especially sentences like 'order is a captured snapshot of a very special event in the lifecycle of your application'. – dezso Dec 6 '16 at 10:40

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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