3

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?

user3077796
  • 192
  • 1
  • 19

1 Answers1

0

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.

marcothesane
  • 6,192
  • 1
  • 11
  • 21
  • Ok this seems to work. But then my ID for shelf is always changing when the name is changing. Is this a normal approach. I find having a stable ID very useful. Or am i missing something. I am not completely following the hash approach. And I have read the the table grows really fast if you have everything in one table. What about that? – user3077796 Jun 28 '21 at 08:43
  • The user always selects for the Business ID and the time when you want to see the data: Either `valid_to`= `9999-12-31` for the current version of the truth, or with `WHERE DATE '2020-04-01' BETWEEN valid_from AND valid_to` to get the historical version. Only the joins between the tables go via the surrogate ID – marcothesane Jun 28 '21 at 10:36
  • And how to you create the Business ID? Currently I am working with Postgres and I have a serial key which the database automatically counts up. Do I have to calculate the Business ID my self? What to do if two users at the same the create an item? – user3077796 Jun 28 '21 at 13:38
  • The business ID is usually: a product SKU; for a person, for example, a Social Security Number in the US; for a vehicle, the Vehicle Identification Number (VIN). Anything that uniquely identifies a concept that is represented as an entity type / a table – marcothesane Jun 28 '21 at 13:43
  • Yes in that case if i have some unique ID it would work. But how when there is nothing like that? Currently I just let the database count up an integer automatically. This would not work anymore, right? – user3077796 Jun 28 '21 at 14:54
  • Then, it can get tricky. Imagine you had a new row with `Arthur` as first name, `Dent` as last name and `Guildford` as his city on Saturday. Today, Monday, you get an `Arthur`, `Dent`, again with `Islington` as his city. Has Arthur Dent moved to Islington from Guildford over the weekend? Or is there a second Arthur Dent who has always lived in Guildford? The Social Security Number would make that clear. Automatically using auto-increment here would simply add to the confusion ... – marcothesane Jun 28 '21 at 15:00
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/234294/discussion-between-marcothesane-and-user3077796). – marcothesane Jun 28 '21 at 15:41