CODEWITHSUNDEEP
databasedatabase-design
yolo
yolo

Reputation: 2805

Foreign key for different entities

Say if a real life operation like loaning a library book is to be performed on two entities Teacher and Student. And the operation details are present in a table like Loans or Transaction. How do I define a foreign key in the Loans table for both Teacher and Student which have different schemes for primary keys? (student has 6digit key but teacher has 4 digit)

A similar question arises when a hospital database system Appointment table attempts to link to both an Employee table and EmployeeRelatives table using a foreign key, where both Employees of the hospital and their relatives are to be given free treatment. How does one specify format of foreign key in Appointments table?

Upvotes: 0

Views: 58

Answers (1)

mucio
mucio

Reputation: 7137

A foreign key can reference only one table.

My solution in these cases is to introduce an additional table (People or whatever name you prefer) which contains all the IDs of Teacher and Student. Teacher and Student can also have the People table key as foreign key.

You can find other possible solutions here: Foreign Key to multiple tables

Upvotes: 1

Related Questions