How to handle immutable table referencing mutable tables?
While building a fairly standard online store in .NET, I ran into some architectural conundrum regarding my database. I have an "Orders" table that is referenced by the "OrderItems" table. The latter refers to the "Products" table.
Now the tables of orders and orderitems are immutable in most aspects, that is, the created order and its orderitems should look the same no matter when you look at the tables (for example, print a receipt for an order for accounting every year should provide the same receipt that received by the customer at the time of order).
I can think of two ways to achieve this behavior, one of which is in use today:
1. Denormalization, where values such as product price are copied into the orderitem table.
2. Make references to tables unchanged. Code that handles products can create a new product whenever a value such as price changes. Table variables referencing products will update their links, whereas immutable ones will be good and dandy with their old link
What's your preferred way of doing this? Is there a better, smarter way to do this?
source to share
It depends. I am writing in rather complex corporate software that includes a kind of document management and auditing and is used in a pharmacy.
Usually, primitive values are denormalized. For example, if you just want the current state of the customer when the order was created, I saved it in order.
There is always more complex data that must be available almost at any given time. There are two approaches: you create their history, or you implement a version control system that is pretty much the same.
History means that every state that has ever existed is stored as a separate record in the same or a different table.
I have implemented a version control system in which I split the records into two tables, one for the actual item, lets say the product and the other for its versions. This way I can refer to the product as a whole, or any specific version of it, because both have their own primary key.
This system is used for many facilities. I can safely reference a revised object from an audit trail, for example, or other immutable records. In the beginning it seems more difficult to have such a system, but in the end it is very straightforward and simultaneously solves many problems.
source to share
I think Denormalization is the way to go.
Also, the product should not have a price (when it changes from time to time and when the price means different value to different people -> retailers, customers, in bulk, etc.).
You can also have a price history table that contains ProductID, FromDate, ToDate, Price, IsActive - to maintain the price history of a product.
source to share
Storing the price in both the Product table and the OrderItem table will NOT be denormalized if the price can change over time. The normalization rules say that each "fact" should only be recorded once in the database. But in this case, just because both numbers are called "prices" do not make them the same. One is the current price, the other is the price at the date of sale. These are two different things. Just like "customer zip code" and "store zip code" are completely different fields; the fact that both can be called "zip" for short does not make them the same. Personally, I have a strong aversion to providing fields that contain different data with the same name because it creates confusion. I would not call them "Price": I would call one "Current_Price "and another" Sale_Price "or something like that.
Failure to comply with the price at the time of sale is clearly not true. If we need to know this, which we almost certainly do, we need to save it.
Duplicate the entire product record for every sale or every time the price changes are also wrong. You almost certainly have constant product data such as description and supplier that doesn't change with every price change. If you duplicate a product record, you will duplicate all of this data, which is definitely a denormalization. This creates many potential problems. For example, if someone corrects a spelling mistake in a product description, we might now have a new entry labeled "4-point toaster" and an old entry labeled "4-slice taster". If we create a report and sort it by description, they will be separated and look like different products. Etc.
If the only data that changes about a product and that you care is the price, I would just post the price in the OrderItem record.
If there is a lot of data that changes, you want to split the Product table into two tables: one for data that is persistent or you don't need history, and the other for data where you need to track history. For example, have a ProductBase table with description, salesperson, stock number, shipping weight, etc .; and the ProductMutable table with our cost, selling price and everything else that usually changes. You probably also want the date of the date, or at least an indication that it is up to date. The first ProductMutable key could be Product_id plus As_of_date, or if you prefer simple sequential keys for all tables, the order at least has a reference to the product_id. The OrderItem table references ProductMutable, NOT ProductBase. We find ProductBase via ProductMutable.
source to share