Design using multiple tables for 1 booking table

Question

Forgive if the answer to this is obvious, but I have no clue what keywords to google with.

I'm designing a database for a school that has a variety of IT resources it can lend out (such as iPads, laptops, projectors)

My database keeps track of the resources by using a booking log. The problem I have is the resources can be booked by either a student, teacher, or a school associate member.

This is my design. Student, staff and associate members each have their own table, and their own attributes.

How do I design the tables and relationships so the resource hire log keeps track of who's booked the resource?

Answer 1

Common table will be the best approach in my opinion. For example,

Person (person_id (PK), first_name, last_name, birthday);

Student (person_id(PK,FK to Person),...);
Staff (person_id(PK,FK to Person),job_title);
Association_member( person_id(PK,FK to Person), reg_date) ;
Resource_hire_log(resource_id, person_id (FK to Person), ....) ;

You may also think about Student, Staff, and Association member as roles which person can play . Then you don't need Student, Staff, etc tables, but Role and Person_Role.

Alternatively, you can use exclusive arcs approach :

Resource_hire_log(resource_id, 
 student_id (FK to Student), staff_id (FK to Staff),   
 association_member_id (FK to Association_member), .... )

Then you will need to add check constraint to ensure only one of (student_id, staff_id, association_member_id ) is not null.