self referential database table

[aakbar]

Hello every body,

I need some clarification of concept regarding self referential
tables.

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.

thanks

Ali

[Ed Murphy]

aa****@gmail.co m wrote:

I need some clarification of concept regarding self referential
tables.

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.

Make sure you restrict who has delete permission, and/or add a delete
trigger that rolls back delete attempts with an appropriate complaint.

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.

I would go with a composite key of (emp_id + date_inserted). Preferably
in a separate history table, but if I had to do it in the main table,
then I would create a view of current data:

create view vPersons as
select *
from Persons p
where date_inserted = (
select max(p2.date_ins erted)
from Persons p2
where p2.emp_id = p.emp_id
)

[aakbar]

thanks Ed,
is there a way without adding a column or what i am thinking to just
reply on relationship between emp_id with itself is not sufficient.

[Erland Sommarskog]

(aa****@gmail.c om) 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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Ed Murphy]

aa****@gmail.co m wrote:

is there a way without adding a column or what i am thinking to just
reply on relationship between emp_id with itself is not sufficient.

You need some column that determines which row contains the current
data for a given emp_id - e.g. 'date_inserted' , or 'is_active', or
an IDENTITY column. Rows are not inherently sorted in order of
insertion (Celko: "rows are not records") - you have to impose a
sort order based on one or more columns, which can match order of
insertion if you configure things to that end.

I agree (as I think I mentioned previously) with Erland's suggestion
of keeping current data in one table and history in another; failing
that, creating a current_data view and using it consistently.