Versioning Rows for Point-in-time
-
When a row in a particular table is modified, I'd like to preserve the original version and insert a new row with a new modified date/time and user id. I'd define a view to pull the most recent version of each record and stored procedures to manage updates.
My question is how to manage the primary key. If different versions of a particular record are differentiated by the date/time modified, it seems that it should be part of a compound primary key. The current version of the table simply has an integer identity column as the primary key. They key has several foreign key relationships to other tables.
How can I define the primary key to support the foreign key relationships while also including the date/time for versioning. Should I move previous versions to a separate table with a different primary key (id + date/time)? Any other suggestions? The key requirement is that I need to be able to fetch a prior version of a specific record for a specified date/time.
Thanks.
-
You're better off moving the history data into an history table. The primary key there would the the primary key of the main table + DateModified
-
Thanks, Remi. I figured that was probably the case, but I was curious if anyone had come up with a more elegant solution to the problem.
-
That's not unelegant. It's the way it should be.
-
I agree with Celco... er, I mean Remi.
You may want to consider making this history table in a seperate database depending upon how large it may get over time and purging or archiving of data issues...
I wasn't born stupid - I had to study.
-
I'm not that rude, am I??
-
I didn't take it as rudeness. It's actually nice to hear someone comment on this topic in an authoritative tone. I would have thought that this would be discussed somewhere as a best practice, but I couldn't find any specific discussion on the topic.
Thanks for your help.
- Jim
-
That's not even a best pratice, that's just basic database design.
It's like if you have a db with cars and their owners over time.
You'd have a table car, a table owners and a table ownerscars with start date and end date. Here it's the same principle but with a self reference. So you don't need the 3rd table in this case... But you can't have just one either.
-
I often see a DateModified/ModifiedBy column pair on a table but all that tells you is who touched it last and when. Sometimes you'll also see a generic 'history' table stuffed with textual 'change' information, but not in a way that facilitates structured queries or allows the entity's state to be recalled for a specific point in time.
I completely agree that the approach you prescribed in your original post makes good common sense as basic database design. Thanks again.
-
The idea of that design is to able to go to the last person who touched the record... not to be able to see the whole thing version by version. It just depends on your needs.
-
You're not at all that rude - I was just needling you...
I wasn't born stupid - I had to study.
-
Tx... it's true that I try to drive the point home, hard, at times
. -
Oh and btw I do believe that Celco IS rude, specially with people that come to these forums asking for help and knowledge and happen to mention something that may seem stupid but it is really just lack of practice.
* Noel -
Agreed! (And Remi you are nothing like that...). It is too bad, especially for new members who encounter that...
I wasn't born stupid - I had to study.
-
Glad to hear that
.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply