Definition
Historisation (Relational)
In relational data models, historisation is a concept of how to model a database such that the historical changes of a value can be tracked.
Types of Normalisation
Full Normalisation
In full-normalised historisation, there is a entity table, which might contain additional properties, and a history table, which contains the start_date and the historised column of the entity table. The history table is a weak entity w.r.t. the entities table.
Example: Here, the price is historised and is therefore not contained in the products table.
Partial Normalisation
Partial-normalised historisation is similar to Full Normalisation, but the most recent value of the historised column is also included in the entity table.
Example: Again, the price is historised and is contained in the products table and the product_history table, and is therefore partial-normalised.
Benefits of Partial Normalisation
When historisation is implemented in a partial-normalised fashion, no lookup is necessary to get the current price.
This is normally done by creating a trigger which is executed on insert on the history table and updates the current_value in the entity table when the inserted row is the newest.
Precision
The datatype of the start_date column of the depends on the context. But it should be as minimal as constrained as possible for the given context.
For example, the start_date column must not be of type datetime when there can only be one change per day. Hence, a more fitting datatype is date.
Example: The price can only change in the morning, noon or afternoon
Here, time_of_day can either be morning, noon or afternoon and day is of type date, meaning that, for example, the primary key (1, 28.05.2024, "noon") can only occur once.
Example: The price can only change four times a day, but the time when it changes during that they does not matter.
The change_nr is a running index of a product (key) during a day, where day is of type date. A change_nr, which is increment for each change, can only occur once during a day for a product. To constraint the maximum number of changes, it is constrained with CHECK change_nr <= 3, meaning that there can only be a maximum of 3 changes since change_nr starts at 0.