Versioning concept for records with relationships (foreign key) [Database]

Question

With the versioning concept of having a extra history/revision table for each table, where you want to keep track of the changes, I am wondering how to handle the relationships (foreign keys)?

Example:

Table: T_Shelf
ID, Name

Table: T_Inventory
ID, Item, FK_T_Shelf_ID

Table: T_Shelf_Rev
ID, ID_T_Shelf, Name, Date_Modified

Table: T_Inventory_Rev
ID, ID_T_Inventory, Item, (FK_T_Shelf_ID or FK_T_Shelf_Rev_ID), Date_Modified

Do I link the foreign key to the corresponding history table? Or does the approach does not work at all with relation databases?

Answer 1

Model them like so:

CREATE TABLE t_shelf (
  shf_surrogate_id INTEGER NOT NULL 
    DEFAULT HASH(shf_business_id,shf_valid_from) NOT NULL PRIMARY KEY 
, shf_business_id  INTEGER NOT NULL
, shf_valid_from  DATE     NOT NULL
, shf_valid_to    DATE     NOT NULL
, shf_name        VARCHAR(32)
)

CREATE TABLE inventory (
  inv_surrogate_id INTEGER NOT NULL 
    DEFAULT HASH(inv_business_id,inv_valid_from) NOT NULL PRIMARY KEY 
, shf_surrogate_id INTEGER NOT NULL FOREIGN KEY REFERENCES t_shelf
, inv_business_id  INTEGER NOT NULL
, inv_valid_from   DATE    NOT NULL
, inv_valid_to     DATE    NOT NULL
, inv_item         VARCHAR(32)
);

Do not separate stem and version as you are suggesting. The valid_to date value of the currently active record is at 9999-12-31. If you need details on how to handle this type of model - just answer here.

Some databases offer a HASH() function that returns an integer based on one or more input expressions, comma separated. I'm using that here. Otherwise, you'd be stuck to using a database sequence object.