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.
Best Answer
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.:
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!