# Data versioning in a relational database

I've been working on an industrial project where my job was to design a data model in-memory and in a relational database in such a way, that can capture the time-varying nature of the production planning of a factory. In other terms, there exist input data and whenever a planning occurs, I have to take a snapshot of the current state of the world and store it.

My solution was to add a header object/table for all relevant input data and output data and have them reference each other's header by its unique auto-incrementing id. Such headers are then considered final and immutable and I do a deep-copy only if something needs to change.

I believe this approach isn't new but I can't recall any (relational-) data modelling courses explicitly mentioning this. My PhD advisor (who is not into general CS) wants me to have a thesis point about this and I'm unable to convince him that either this isn't new or at most a trivial extension to some existing methodology. I've tried searching Google Scholar, but didn't find any articles, perhaps due to wrong keywords.

Can someone point me into the right direction?

• – reinierpost Jul 3 '15 at 8:57
• Certainly but the system has already been built and the purpose wasn't just historical because the user can ask the system to use a previous version as a basis for a new plan. I'm interested in whether there is an academic paper about the issue. – akarnokd Jul 3 '15 at 9:07
• I understand ... I've created a placeholder answer, but I'll gladly delete it when a better answer is provided. – reinierpost Jul 3 '15 at 9:17
• Welcome to Computer Science Stack Exchange. I do not really understand your problem, but that is only me. I am not sure this is the best site for this question. You might consider datascience.stackexchange.com. But avoid cross-posting. – babou Jul 3 '15 at 13:49
• Are you looking for persistent data structures (wiki) or copy-on-write techniques (wiki)? – hengxin Jul 4 '15 at 4:54

I've seen a thesis on this subject, but that was long ago.

One thing to search for a combination of relational databases and versioning, which will produce things such as tuple versioning; another is temporal databases.

As far as I know, there really is no standard way to address your problem. How best to model it depends on what information you want to be in the snapshot and on how you want to use the information later.

For instance, if you never ever want to pose queries that combine information from different snapshots, you can save each snapshot to a different database. That allows you to use the same constraints and queries on your snapshots that you use on the live data.

If you never need to pose queries that combine information from your live operational state with information from your snapshots, you probably want to save the snapshots to a different database than the live information. That way, you can at least continue to use constraints and queries on the live data that do not take the extra snapshot information into account. On the snapshots, you will need to rewrite the constraints and queries. But it will be easier to pose queries that combine information from different snapshots.

To be honest, I don't understand what you mean by "add a header object/table for all relevant input data". If all of the snapshots are put into the same database, without creating separate tables for each snapshot, I expect to see one or two columns for every table whose tuples should be in the snapshot. The extra column(s) would contain a snapshot identifier: a timestamp or a foreign key into a new table with more information about snapshots. In case of two columns, they would indicate the first and last snapshot in which the given tuple is present. This can save space if you have many snapshots, but it can also make querying harder.

• To clarify, the data model looks like this: X_Header { id, other_fields}; X {id, headerId, other_fields}; X_child { id, xId, other_fields }, X_to_Y { xId, yId }. For example, there is a header table for a plan which references a header of quantities, a header of demand and a header of shipments. – akarnokd Jul 3 '15 at 9:24
• I don't see the purpose, sorry ... – reinierpost Jul 3 '15 at 14:53