3

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.

  • 1
    Check out Datomic by Rich Hickey! It's a database system that completely revolves around the idea of immutable records. Interesting, but I think it's overkill! – Vérace Apr 13 at 7:51
  • 1
    You might find of interest Q & A no. 1 and Q & A no. 2. – MDCCL Apr 14 at 15:34
  • Look into the versioning capabilities of MariaDB. In most respects MariaDB is the same as MySQL, but this is an added feature. – Rick James Apr 23 at 4:47
  • @rick-james, yes that would be the best solution. Problem is we have crufty old SQL that relies on MySQL quirks, like inserting default values for null in non-nullable columns. This is being fixed, and when done, we will make the switch. I thought I read, though, that versioning is on the table but not each row...maybe I need to read that again. – Jeff Lowery Apr 26 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.