Store the integrity of a table's records

Question

I had a question that I couldn't solve/answer, to exemplify I'll give a use case. I have an ORDER_ITEMS table that is related to the ORDER table and a PRODUCT(ID, PRODUCT_NAME) table that is related to ORDER_ITENS. As the sales system is fed, I have a history of orders and their products sold, let's say I change the PRODUCT_NAME, all products sold prior to the change will have their name changed as a result of the reference between the ORDER_ITEMS table with PRODUCT. Is there a pattern where I could store the integrity of the PRODUCT_NAME without affecting the orders placed before the product name change?

Answer 1

Such a join in a relational database looks up the current value.

If you need a history of that foreign field at the time of the child’s creation, make a copy. Add a column on the child table into which you copy the value of interest from the parent.

You likely will also want to capture the moment of this creation-and-copying by adding yet another column on the child table, of a type akin to the SQL standard type TIMESTAMP WITH TIME ZONE.

An alternative is to add an intermediate table, a child table to the parent with a pair of TIMESTAMP WITH TIME ZONE columns that track when the sibling columns‘ values are in effect. Each time you modify the product’s name, you create a new row in this “in-effect” table. Each order item would link to a row in this “in-effect” table, with the more general product table being a grandparent to the order item.