eCommerce data modeling - is it common to duplicate product properties in line items?

Question

Suppose I have the following entities in a commerce data model:

BaseProduct (PK id, price, name)

LineItem (PK id, FK product_id, price, name)

Order (PK id, line_items)

An Order includes one or many LineItems. A LineItem includes a reference to a BaseProduct, but, and here lies the crux of the question, we don't use the price and name attributes of the referenced product. Rather, we propose to duplicate these properties and store a copy of each one.

This anticipates the problem that BaseProduct attributes may be edited over time (for example, their price). But if we examine an order in the future, we would want to see what each line item cost at the time of the order.

I believe another alternative would be to make base products immutable, and model variations of it, so that if a product is edited by an administrator we would store it as a variation, thus keeping a history of changes.

Perhaps for my use case the latter would be overkill, but I want to know if these solutions are common in commerce modeling, or if there are yet other (better, simpler) possibilities which I'm not contemplating?

Answer 1

Yes its standard practice.

You essentially have two concepts. The "base price" of the item and the price you sold that particular item at.

The base or catalogue price is fairly fixed, If it changes over time you may choose to represent that as a second instance of an immutable object. It may help your reporting to distinguish between the items sold and sold at sale for example.

However the line item price is likely to be affected by any number of offers, discounts and other factors. It would be impractical to create new immutable instances simply because a single customer has a 10% off voucher.