Database design question regarding multiple one-to-many relationships and foreign keys?
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.
Add a join table for each base table. TeacherAddresses, StudentAddresses, ParentAddresses.
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.
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...
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).
I would have a people table, then a student table for student specific things, a teacher table for teacher specific things and a parnet table for parent specific things and then put the PKFK on the people/address combo. Also a PKFK between people and students, people and teachers, etc. – HLGEM Sep 29 '09 at 22:08.
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?
I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.
Related Questions
Is it alright to create foreign keys that are not really foreign keys in a database?
Should I prefer generic names or strongly typed names for the primary keys and foreign keys in a database?
Database Design and how Foreign keys work?
Database Design column naming convention; Primary and Foreign Keys?
Database Design - NULL Foreign Keys?
Database design w/Entity Framework 4 - where should I put my foreign keys?