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
Unsolved 
