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. enter image description here 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?


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 :

 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.