3

as per request, I'll reduce this whole set of questions to 1-2 core points (thanks for the feedback ;)).

I'm considering using insert-only tables for keeping the history of my data. while the productive usage is pretty clear to me by now, I'm still struggling to find a solution for some problems from a continued development perspective:

The Problem I have is, how do I deal with a changing Schema without having to manipulate/delete/transfer old records elsewhere? cases that I'm currently considering:

  • I get a new constraint, all current live data will be changed to fullfil that constraint according to some business rule. constraint gets added. however my old historical records may break this constraint, which forces me to do something with those entries (delete and copy elsewhere, also modify these) which I can't do as I need this data as they are for compliance reasons
  • some datatypes change: I now only allow numbers in a field where previously Strings were allowed (its just an example, I know that I could also fix this specific example by some custom constraints instead of column datatype)

as I preferably want to deny any deletes and updates on the database level, keeping an admin access which could allow deleting old data via copying it elsewhere seems to break the idea behind a insert only table, no?

how would one deal with those situations in insert-only tables? create new table, rename old table to something else with a version number and now only use the new table? ... doesn't sound right, but idk what the best way is to deal with it.

so.. thanks for any help! :)

2
  • You have a lot of good information, but can you edit your question to focus on a single problem only? Commented Nov 16, 2023 at 13:20
  • @GregBurghardt okay, done. thanks for the feedback. Commented Nov 16, 2023 at 14:59

2 Answers 2

3

The historical insert only table should only have 1 constraint, and that is to the primary key of the record that it is keeping tracking of. No other constraints/FKs should exist, even though they may exist in the source table.

As far as changing columns data types, if the source table is moving from the string to a number, then update the source table schema with something like this:

  1. Read data out string column
  2. Rename string column
  3. Add new number column with same string column name
  4. Insert data into number column, handing cases where current values are not a number.
  5. Delete string column

Now for the insert only table, just keep it a string as all numbers can be represented strings. Consider keeping all columns that you are tracking historical data in the insert only history table as string types as pretty much any other data type can be converted to a string. Number-> String, Date-> String, etc. Not all strings can be numbers or dates, so use the least restrictive type of data in your historical insert only table as the column type. That way, if you change data types in the source table, no big deal. The only caveat is that the reference to the PK of the source table should be the same data type in the historical table. Then your insert only historical table is resilient to change.

4
  • Unless I'm misunderstanding terms here, typically "history" tables shouldn't have a foreign key constraint to the record it tracks if records can be deleted from the main table. Data retention policies or laws might dictate that old data should be deleted. This isn't so much a critique of you answer as it is just additional info. Even the FK in the history table to the main table might be unnecessary. Commented Nov 17, 2023 at 0:39
  • Great point @GregBurghardt. Originally my thought was to have the constraint to cascade the deletes to the history table, but if you didn't want to do that, having no constraints decouples the history table completely. Thanks for pointing that out.
    – Jon Raynor
    Commented Nov 17, 2023 at 15:00
  • Your suggestion about keeping the PKs will work when the main tables uses, for example, GUIDs as keys (so keys which will never ever be repeated for new records). But when records in the main table are allowed to be deleted and PKs might be reused at a later point in time, you will get a PK collision in the history table. In this case, one may need to add a timestamp or version number as a second PK column to the history tables,
    – Doc Brown
    Commented Nov 18, 2023 at 12:38
  • @DocBrown - Great point, if you are deleting history records as source table records are being deleted, that would not be an issue, however, if you kept the history records and the database re-used the same PK guid on the source table, then erroneous history. Would need an additional identifier because those new history records would be intermingling with the prior history records with the same PK GUID. Should always strive for natural keys as Guid and number sequences (artificial keys) always have their little nuances.
    – Jon Raynor
    Commented Nov 20, 2023 at 2:44
2

The purpose of a schema in an RBDMS is to ensure data structure integrity. If you want to have a changing-over-time structure for your data you either need a technology that allows loose data structure/format or do some schema versioning.

Loose structure persistence store

Loose data structure means you can insert data respecting different schemas in the same store. This can be achieved using a non-relational database management system, such as document based databases, or key-value stores. Your program must however handle the fact that the data structure can vary at the entry level and must be flexible enough to work when the data is structured using different models.

Of course there is still the option to use your relational database as a non-relational database, id est having a plain text column which contains a serializaed value of your object. This, however, defeats the benefit of using a relational database (data integrity) with all the downside of such technology (cost, slow).

Schema versioning

Another option is to use a versionned data schema. Imagine you have an entity persisted in a database table using a v1 schema and you want to change the schema to v2:

  • If the change consists of removing a mandatory field, then you can simply set the associated column as nullable and stop valuing the column;
  • If the change consists of adding a field, then you can add a new nullable column that stores v2 value and null for v1 rows. If you want the column to be non-nullable, then you can create a new table with v2-only columns, and a foreign key to the v1 table instead;
  • If the change consists of changing the column type, handle as an "add and delete" operation: create a new column (and cast existing data) and make the older one nullable.

It is good practice to add a version number column, that allows the program to tell which schema version the entity was created with, and how the data should be interracted with. This technique can also be used in non-relational stores.

Beware that having a flexible schema is probably going to hit your performance. Also you will lower the ability of your database to ensure integrity; this responsibility will shift towards the application. For instance, you won't be able to handle at the database level that a specific column must be null if version is >= 3.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.