Problem solve Get help with specific problems with your technologies, process and projects.

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?

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 last published in January 2004

Dig Deeper on Oracle and SQL



Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.