how to deal with schema/constraint changes in insert-only Relational DBs?

Question

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! :)

Answer 1

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.

Answer 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.