Database design question regarding multiple one-to-many relationships and foreign keys

Question

In my database there are four databases: Teachers, Students, Parents and Addresses.

Teachers, students, parents can have any number of addresses (zero or more).

I would like to use foreign keys to link between teachers and their addresses (and between students and their addresses etc).

Since records in Addresses can originate in either a teacher, a student or a parent, I cannot use a (single) foreign key in Addresses. And since Teachers etc can have any number of addresses, I cannot use an unlimited number of foreign keys in a Teacher record.

What would be a good strategy here?

I was thinking of using intermediate tables but that seems a bit cumbersome. Also, is it a good idea to have each Address record include 3 (NULLable) foreign keys, and only use one?

Answer 1

Add a join table for each base table. TeacherAddresses, StudentAddresses, ParentAddresses.

Answer 2

Using the table to represent the relationship is probably the way to go here. You will need a Parent_Address, Student_Address and Teacher_Address table. It will make the querying that much simpler later on.

Answer 3

Something that has worked for me in the past is having a context and context_id field. Context would be the parent record (e.g. Teacher), and context_id would be its id. So, you'd have something like

teacher = find single teacher by id
addresses_for_teacher = find all addresses with context = 'teacher' and context_id = teacher.id

With appropriately indexed tables and a healthy serving of memcached we're seeing great results on Cork'd.

Answer 4

What you have is a many to many relationship. Therefore you need a join tables.

Where you define Students to Address table that has Student ID and Address ID, a Teacher to Address table that has Teacher ID and a Address ID, etc...

Answer 5

I would redesign this (depending on the structure of the 4 tables you mentioned) into either 1 table (People) which has a type column (Teacher/Student/Parent) making it much simpler to then add a person_id as foreign key to Addresses

If you need to keep the current 3 tables for people because of very different requirements I would advise having a 3 field unique key on Addresses:

address_id: sequence (either table wide or per person_type/persond_id) person_id: unique id from each person_type table person_type: values T,S,P (varies depending on table of origin Teacher/Student/Parent)