Re: Heritage

On Wed, 2004-09-08 at 14:27, Sebastian Davancens wrote:
> Hi everyone. Im having a problem with heritage.

... whatever that might be...

> The situation is the following:
> I have two tables, tbl_everyone and tbl_employees. tbl_employees
> inherits from tbl_everyone.
> In tbl_everyone, i store some information about everyone who is
> related with the place where i work: Name, ID (PK), Birth Date,
> Adress...
> Then, in tbl_employees i have aditional information, like area, position, etc.
> The problem appears when i have someone in tbl_everyone that becomes
> an employee. What do i have to do ? Consider that ID in tbl_everyone
> is used in auxiliar tables, so i cannot easily delete the person in
> tbl_everyone and insert it again in tbl_employees...

Rather than inheriting, tbl_employees could be a separate table that
holds only the additional information and it should have a foreign key
reference to tbl_everyone. When someone becomes an employee, create a
row in tbl_employees with the extra information.

Create a View that combines tbl_employees with tbl_everyone to return
the information that your inheriting table would have given you.

Alternatively, create a separate index of keys to the hierarchy and use
it as the target for foreign key references. Use triggers to keep it up
to date.

--
Oliver Elphick olly(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"Put on the whole armor of God, that ye may be able to
stand against the wiles of the devil."
Ephesians 6:11