Database Design for storing versions
 

I want to create a database design where I want to keep all changes made to a database and the user could revert back to earlier versions. Let say there is a table user which has following values initially

Userid(Primary Key)  FirstName   LastUpdated

111                           abc          17/05/'07

Now if the user(abc) changes his first name to say 'def' I still want to be able to show him all the values that his firstName Column had in history.

Userid(Primary Key)  FirstName   LastUpdated

111                           abc          17/05/'07  

 (key ??)                    def           18/05/'07

In a normal case where I do not need to keep the records I would have just updated the first row. But here I need to keep all the versions for the record. One solution could be that I add a baseUserId column which would be same for all the edits made to one record. Another issue is that the primary key (userid) is also the foreign key for some other table and I am not really sure how to maintain the links with other table in case of edit by a user.

TIA
Gaurav

Re: Database Design for storing versions
 

Why not leave the UserId field alone and just update the name?
Then you can have another table, say History which will store all the edits a user has made.

Re: Database Design for storing versions
 

Hi,
Thanx for the reply. I am now not storing any version information in the user table. I have added a separate User_Archive table which is something like:

hist_id(Primary Key) Userid FirstName LastUpdated
321 111 abc 17/05/'07
234 111 def 18/05/'07Thanx.
Gaurav