Posted by Erland Sommarskog on 05/21/07 21:29

(aakbar@gmail.com) writes:
> consider we have a "Person" table that stores data about employees of
> an organisation.
> in case a person change his name or any details we dont want to update
> our database by loosing old information and adding new one. what we
> need is to hold previous details as well as the new ones.
> theoratically for me its easy i will just add another row and link
> that row with one of the existing row. to do so i created a relation
> between the "Emp_id" column of "Person" table to itself. so one
> "Emp_id" could be related to another "Emp_id" in the same table.
> after doing so i dont know how can i indicate while inserting a record
> that this new record in linked with one of the previous records. means
> in insert statement how the relation ship would be added.
> here i am not sure if i am thinking in the right direction or not as
> we may need to add another table or another column to indicate the
> relation between old an new row. but if we have to add that new column
> say "old_Emp_id" in "Person" table then what does the relationship
> between "Emp_id" with itself serves.

There are probably several solutions. Which is the best may depend on what
the business requirements at hand. Since I don't know what they are, I
can only voice my preference. And that is that you have your Persons
table to hold current values only. Most of the time users will work with
current value, and if you mix current values with historic, you can cause
mess and confusion.

Instead, I would recommend that you have a table PersonHistory that holds
pervious version of the rows. If Emp_id is the key in Person, the
key in PersonHistory would be (Emp_id, Change_no) where Change_no is
a running number. The PersonHistory would hold columns you are required
to track, and and also columns who report and when this version was
created. You would add a row to this table whenever a row is inserted
or updated. That is, the would be a row for the current version of the
record as well.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]