CODEWITHSUNDEEP
sql-serverasp.net-mvcdatabase-designinventory-management
lee_mcmullen
lee_mcmullen

Reputation: 3177

Order, OrderItem, Product. Write Product details to OrderItem?

This is not a question about a particular technical issue, more a general question about database design. Nonetheless, the tech stack is: ASP.NET MVC, SQL DB.

I've inherited a system recently which has an Order-->Order Item-->Product concept i.e. an order has many order items and each order item is associated with one product.

The Order Item stores the following when saved to the DB:

  • OrderID
  • ProductID
  • Qty
  • Unit Cost (written from Product)
  • Total NET (calculated based on Qty * Unit Cost)
  • VAT
  • Total GROSS

The Order Item viewed through the UI looks like this:

Qty | ProdCode | ProdDescription | Unit Cost | Total NET | VAT | Total Gross

Qty, Unit Cost and Totals are all pulled into the UI from the Order Item and the Product Code and Product Description are pulled from the associated Product record.

All seems sensible enough.

So my question revolves around which data should be written from the Product vs which data should be references from the Product. Specifically, the Unit Cost is written from the Product when when the Order Item is created. I assume this is because Product prices change and you don't want this change to apply to old orders. Fine, makes sense.

My question is: should the same logic should also apply to Product Code and Product Description? And if not, why not?

To my mind, it seems like the Product Code and Description should also be getting written to the Order Item i.e. the Product Code and Description are as liable to change as the Product Unit Cost. In which case if you were to go back and look at an old order, the Prod Code and Description on the order would appear to be different to what was originally ordered, which seems wrong to me.

The developer who built the system is no longer available to discuss his thinking when he designed it.

The system is working fine and there have been no complaints. However that is mainly because there have never been any updates to Prod Codes/Descriptions even though they are available to edit for various users.

I'd be interested to hear people's thoughts on it before I go making wholesale changes, is this a common scenario and am I worrying about nothing?

Upvotes: 0

Views: 1924

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82484

There are several aspects to take into consideration in such cases. Let's start with the fact that the orders MUST be immutable.

Since the product code and description are not immutable, it would seem to make sense to keep them in the orders table at a first glance.
However, this may cause a massive amount of duplicated data, for each product in each order.

Another approach is to never keep the product code and description immutable.

One more approach is to enable the administrators edit the product code and description, but instead of updating the row in the products table you simply mark it as history (you will need to add a status column for that, of course) and add a new row with for that product, with the new code and description.
This solution will allow you to keep the integrity of the orders while allowing your administrator users to edit whatever they want, and keep the bare minimum of data, especially if the changes to the product code or descriptions are as sparse as you wrote.

Upvotes: 1

Related Questions