DataModel for Implementing row versioning in OLTP system

Question

I have following table

Person(PID, Name, Dob, AddressId) PID being surrogate key(auto incremented)
Address(AddressId, Line1, Line2, City, State) AddressId being surrogate key(auto incremented)

PersonID (PID) is used in my lot of transaction tables. Since address of a person may change over the period of time, I need to store record version in database so that I can refer correct version according to time slice. How should I handle it.

I am thinking of having solution something like this

Using same table to store history e.g.
Person(PID, Name, Dob, AddressId)
Address(AddressId, Line1, Line2, City, State, StartDate, EndDate, LastUpdatedBy, LastUpdatedDate)

Let's assume initial record of person as below

Person(100, X, Y, 110)
Address(110, A, B, C, D, 2011-01-01, 9999-12-31, Y, 2011-01-01)

Now address was changed in 2012 for PID 100. I am thinking to do

Address(320, A, B, C, D, 2011-01-01, 2012-06-01, Y, 2011-01-01) - INSERT
Address(110, A, B, Q, D, 2012-06-01, 9999-12-31, Z, 2012-06-01) - UPDATE

if address changes again in 2014 for PID 100. It would look like

Address(740, A, B, Q, D, 2012-06-01, 2014-06-01, Y, 2012-01-01) - INSERT
Address(320, A, B, C, D, 2011-01-01, 2012-06-01, Y, 2011-01-01) - NO CHANGE
Address(110, A, B, N, D, 2014-06-01, 9999-12-31, R, 2012-06-01) - UPDATE

Now I can query historical data as well as current data from same table without changing surrogate key. I won't have to refer historical tables hence no bombarding of logic and tables.

This approach won't let me enforce unique key constraint on tables assuming I have Address Code being unique. How shall I handle this?

Please suggest different approach and feedback on this.

The search term for this is "temporal tables". [Snodgrass's seminal book on temporal databases](http://www.cs.arizona.edu/~rts/publications.html) is free on his web site. The subject is more complex than you might think. — Mike Sherrill 'Cat Recall', Dec 31 '14 at 06:14

miljanm · Answer 1 · 2014-12-31T10:21:40.023

What I would do is to make a relation table that contains dates:

Person_At_Address(PID,AddressId,StartDate,EndDate,LastUpdatedBy,LastUpdatedDate)

And remove the corresponding columns from Person and Address yielding:

Person(PID, Name, Dob)
Address(AddressId, Line1, Line2, City, State)

Now whenever a person changes the address you just insert a new row into the Person_At_Address table. When querying, you have got all the info you need about historic and current data.

As for your case with NO-CHANGE row, you have number of options to pull it out:

You can have a scheduled job that inserts rows at given checkpoints in time if there is no row present for that time slice. Then select query gives you exactly the above output.
You can also create a display like that after you run the query by filling in the missing rows in your application. That is fairly simple also.

DataModel for Implementing row versioning in OLTP system

1 Answers1