I have two tables, student and teacher. I have an email table because a student can have more than one email. But, so can a teacher. Should I include a foreign key of both student and teacher in my email table or create two separate tables, that is, a student_email table and a teacher_email table? Is there any difference which of these I do? Is there a more elegant solution?
dubiousandrew 0 Newbie Poster
timothybard 12 Posting Whiz
The main goal in designing a database is designing a database that works for your purpose. There are many situations where it is possible to make a database more properly normalized, but it may take too much time, money and effort to justify. For example, in a properly normalized database, to record an address, you would have an "address" table that has, as an example, the following fields:
House Number, Street ID
You would then have the following tables:
Street: ID, Zip ID
Zip: ID, City ID
City: ID, State ID
Even though that would be considered properly normalized, most people don't break out a database to that level because they don't have the time and resources to maintain all of the City, State and Zip code tables.
Having said that, I would say the properly normalized design for your situation would contain the following tables:
People: ID, Last Name, First Name, etc
Teachers: ID, People ID (FK)
Students: ID, People ID (FK)
E-mail: ID, People ID (FK), E-mail Address
With this design, to determine if a person is a teacher, student or both, you need to join the People table to the Teachers and Students table and look for non NULL records.
However, if it is not practical to redesign the database to have that structure, then you can use a student_email table and teacher_email table or just one email table with two FKs. If you have people who are both teachers and students, then I would use only the one e-mail table so that if someone changes their e-mail address, you only have to record the change in one location. If teachers can't also be students, then I would go with the two e-mail tables because it is easier to create queries and program the rest of the database with that structure.
dubiousandrew 0 Newbie Poster
Thanks for replying. I like the idea of a people table. But ultimately I think you are right about it being easier to program the rest and create quieries without a people table. Usually, I think people have an object for each table in the database. I could have an abstract person object and extend it with a teacher or a student. I plan on doing this all in php which I have not used before and I think extending a person object may be overly complicated. I am not sure where I am going with all this. Anyway, thanks for your reply.
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.