So I have this concept in my head of "immutable" records, which I am sure is not novel, but can't seem to find the right google keywords.
Basically, historical row values need to be preserved as 'obsolete', but remain referencable as FKs from older records. These types of modifications are infrequent. I'm using MySQL, which limits things a bit. The four approaches I see are:
Naive
- PK is an auto-generated ID
- obsolete_stamp is a timestamp indicating that the row values are obsolete
Drawback: historical data is preserved, but there is no way to track history of changes
Better:
- PK same as above
- old_id is the id of the row that this row modifies (if exists)
- obsolete_stamp (null if row data is current)
consequence: new rows are modifications of old rows, but the new rows have to update the old_id (if there is one)
audit:
- an on update trigger creates a new row in an audit table
drawback: queries will need to check audit table for obsoleted rows
compound key:
- id + obsolete_stamp
drawback: id can't be generated anew, only if the row is 'novel' (no history); not clear how to do that automatically.
I'm one of those full-stack developers, so I know just enough about everything to be dangerous.
