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.

Dig Deeper on Oracle and SQL