Heritage

[Sebastian Davancens]

Hi everyone. Im having a problem with heritage. 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...
Thanks in advance
Sebastian Davancens

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Oliver Elphick]

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 ol**@lfix.co.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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Mark Gibson]

Sebastian Davancens wrote:

Hi everyone. Im having a problem with heritage. 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...

Have you tried deferred constraints, eg:

BEGIN;
SET CONSTRAINTS ALL DEFERRED;
DELETE ...;
INSERT ...;
END;

I've haven't had chance to test this, but I think this could
be what you're looking for.

--
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster