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.