|
|
Posted by Ed Murphy on 05/21/07 15:24
aakbar@gmail.com 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_inserted)
from Persons p2
where p2.emp_id = p.emp_id
)
Navigation:
[Reply to this message]
|