Posted by aakbar on 05/21/07 12:28

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

[Back to original message]