Thread: Heritage
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
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@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
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.