Re: self referential database table
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Mon, 21 May 2007 21:29:42 +0000 (UTC)
(aakbar@xxxxxxxxx) 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@xxxxxxxxxxxxx
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
.
- References:
- self referential database table
- From: aakbar
- self referential database table
- Prev by Date: [Announce] Stylus Studio 2007 Release 2 Now Available
- Next by Date: Re: Bulk insert, skip rows with duplicate key error?
- Previous by thread: Re: self referential database table
- Next by thread: SQL Query to insert data to a table
- Index(es):
Relevant Pages
|
