Supertypes and subtypes
I have three tables: two master tables and a transaction table. Say TeachingStaff is a master and NonTeachingStaff is another master, and SalarySlip is my transaction. The EmployeeID of SalarySlip can take values either from TeachingStaff master or from NonTeachingStaff master. Now I want to have a foreign key defined on EmployeeID of SalarySlip. How can I address this situation?

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Each person in your database will be found in either the TeachingStaff or the NonTeachingStaff table. If these tables have separate, unrelated EmployeeID primary keys, for example if each primary key is a separate sequentially assigned number, then you will already probably have encountered the problem of what to do when a person needs to "transfer" from one table to another; such a transfer will require a delete/insert with a completely new key. This of course also has cascading effects on all foreign keys in the SalarySlip table. In fact, when you think about it, TeachingStaff and NonTeachingStaff cannot have unrelated primary keys, because then the foreign keys in SalarySlip wouldn't know which table the values came from. That would only work if they had mutually exclusive keys, and then they couldn't very well be sequentially assigned.

Your TeachingStaff and NonTeachingStaff tables are actually examples of a subtype. The supertype entity in this scenario would be called something like People. In your case, you haven't defined it, but it is a good idea to do so, if you can, because it will conveniently hold all the fields that TeachingStaff and NonTeachingStaff have in common.

Here's the design you should have:

create table People
       ( EmployeeID  integer not null primary key auto_increment
       , FirstName   varchar(30)
       , LastName    varchar(30)
       , DateHired   datetime  not null
       )

       
create table TeachingStaff
       ( EmployeeID  integer not null primary key
       , constraint TS_People 
            foreign key (EmployeeID)
            references People (EmployeeID)
       , HomeRoom    varchar(10)
       )       
       
create table NonTeachingStaff
       ( EmployeeID  integer not null primary key
       , constraint NTS_People 
            foreign key (EmployeeID)
            references People (EmployeeID)
       , MainSubject    varchar(10)
       )       
       
create table SalarySlip
       ( EmployeeID  integer not null primary key
       , constraint SS_People 
            foreign key (EmployeeID)
            references People (EmployeeID)
       , DatePaid     datetime    not null
       , AmountPaid   number(9,2) not null
       )

The People table has an auto_increment (MySQL syntax) primary key, but the other tables don't. The other tables all use the same EmployeeID, defined as a foreign key which references the People table. The TeachingStaff and NonTeachingStaff now hold only those specific columns which are unique to that type. Finally, the SalarySlip table references the People table, and there is no difficulty with the foreign key.


This was first published in January 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top