Table structure where name changes to be retained

Question

Example Situation - order table with client list and products. In below example table , when client name/company changes (company name may change) when updating records need to retain old company name for past order records.

(or Employee name change, where old name to be retained in record) Please suggest able structure for achieving this, example will be helpful.

Table 1 - Clients:
UID: Auto generate
Client ID: Not Null
Client Company: Not Null
Client Name: Not Null


Table 2 -Products:
UID: Auto Generate
Product ID: Not Null
Product Name: Not Null
Product Model: Not Null

Table 3 - Oders:
UID: Auto Generate
Client UID: Not Null
Product UID: Not Null
Oder Date: Auto generate

Answer 1

There are a couple of common approaches to this:

---

1: Version the source data, so that for instance when a product changes name your structure stores the old name (or the whole record).

To avoid bugs where new code updates properties but fails to update the history, triggers are commonly used to maintain the history instead of expecting other code to do so. I.e. with a before-update trigger, copy the current data to a history table (that is likely identical, except for the addition of a date time column as a timestamp if the base table doesn't have such a last-modified column, and with indexes optimised for point-in-time searches).

With this stored history you can lookup the product name at the point in time the order was made, if you want that instead of the current name.

You may wish to keep the current data in the "history" too, so any trigger became after-insert/update. This simplifies history lookups (only using one table for each entity) but for large data that hardly ever changes has the disadvantage of nearly doubling storage.

Maintaining history like this has built-in support in many databases, or is available as an add-in, often referred to as "system versioned" or "temporal" tables. For instance SQL Server 2016+. Though if you chosen DB doesn't, implementation by hand isn't rocket science.

---

2: Capture the time sensitive data:

Full versioning like the above may be overkill for your needs, so the second option is to simply store the time sensitive property in each record. So in your order-line table as well as storing the product ID also store the current name. Make it obvious that this is essentially a cached value by naming the column something like "CapturedProductName" or "ProductNameAtTime".

This is far simpler to use for read queries as the data is just there in the row you already have, no need to go lookup in the referred entity's history.

The disadvantages are extra storage, every order line having a copy of the product name even if it never changes in the life of the product and extra difficulty correcting errors in child data if you want the update to reflect everywhere.

---

A notable problem with any technique like this is that you may need to consider what to do if a change to the referred record is a correction (fixing a typo in product name for instance) that you want reflected on related records. As a general rule it is most correct to keep the original value, but users sometimes disagree with their typos being remembered for eternity!