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 can been purchased. It has various details, but the most important is
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
Would it not be better to create an audit/history table that documents the
unit_price change of a
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.