Relational Databases and Version Control: Revision Intervals
I was thinking about how to apply versioning to a relatively simple database, and I figured I should do something like mentioning in the Jim T post where global #s revisions exist (like in Subversion or Mercurial, for example), and each database record has a valid interval.
Example:
Create a person.
|Name|D.O.B |Telephone|From|To | |Fred|1 april|555-29384|1 |NULL|
Update phone number
|Name|D.O.B |Telephone|From|To | |Fred|1 april|555-29384|1 |1 | |Fred|1 april|555-43534|2 |NULL|
Remove fred:
|Name|D.O.B |Telephone|From|To | |Fred|1 april|555-29384|1 |1 | |Fred|1 april|555-43534|2 |2 |
Are there any disadvantages to this approach? It doesn't seem complicated.
The only thing I can think of is that it seems like it will have subtle effects on primary keys other than the auto-incrementing record number, which is independent and not data bound. For example, if you have data like this:
Person: (primary key = PersonID which is an autoincrementing integer)
|PersonID|Name|Telephone|
|1 |Fred|555-2938|
|2 |Lois|555-2939|
|3 |Jim |555-1000|
Home: (primary key = HomeID which is an autoincrementing integer)
|HomeID|Address |
|1 |123 Elm St. |
|2 |456 Maple Ave.|
PersonHome: (primary key = person ID and home ID)
|PersonID|HomeID|
|1 |1 |
|2 |1 |
|3 |2 |
then you cannot just add the From and To fields above as you destroy the uniqueness of the primary keys. Instead, I'll probably have to do something like this (with the addition of the appropriate indices to replace the function of the previous primary keys):
Person: (primary key = K which is an autoincrementing integer)
|K|PersonID|Name |Telephone|From|To |
|1|1 |Fred |555-2938 |1 |NULL|
|2|2 |Lois |555-2939 |1 |1 |
|3|3 |Jim |555-1000 |1 |NULL|
|4|4 |Sunshine |555-2000 |1 |2 |
|5|2 |Lois |555-1000 |2 |NULL|
|6|4 |Daisy May|555-2000 |3 |NULL|
|7|5 |Connor | |5 |NULL|
Home: (primary key = K which is an autoincrementing integer)
|K|HomeID|Address |From|To |
|1|1 |123 Elm St. |1 |NULL|
|2|2 |456 Maple Ave.|1 |NULL|
|3|3 |789 Vista Dr. |1 |3 |
|4|3 |104 Vista Dr. |4 |NULL|
PersonHome: (primary key = K which is an autoincrementing integer)
|K|PersonID|HomeID|From|To |
|1|1 |1 |1 |NULL|
|2|2 |1 |1 |1 |
|3|3 |2 |1 |NULL|
|4|4 |3 |1 |NULL|
|5|2 |2 |2 |NULL|
|6|5 |2 |5 |NULL|
Revisions: (comments here for illustration)
|Revision|Comments |
|1 |Initial dataset |
|2 |Lois divorced Fred and moved in with Jim |
|3 |Sunshine changed her name to Daisy May |
|4 |Daisy May house was renumbered by the fire dept for 911 rules|
|5 |Lois and Jim had a baby named Connor |
a source to share
Let's think about the same problem!
from | has a design flaw that requires updating and inserting when you want to add a new version of a record. I'm also concerned that this construct has a twofold meaning, it tells you the version, for example. x | null means the most recent and it tells you the status of the entry, eg. x | x means delete.
Historical wisdom suggests using dates http://www.dbforums.com/database-concepts-design/1641734-data-record-versioning-how-implement.html
Working with dates is not the hardest thing when you retrieve your data into your object model.
Recently, two tabular approaches have been preferred: Database - Data Versions
I am not too keen on this either because of the need to maintain repeating tables.
If you changed your mind to have a version column and the status column will work? The highest version number is the most recent, the recording status is the most recent version.
Still pondering ...
a source to share