If data exists in the field print, else don't

[eluser]zebake[/eluser]
I have to admit, I really wasn't expecting to learn so much about database design and best practices via this forum. Most forums tend to snub their noses when questions venture out of the realm of the software/framework the community supports. But, even this single thread alone has offered me a wealth of knowledge on a variety of topics.

Jedd, the wikipedia article on database normalization wasn't real friendly to my brain. I searched for more on the topic, and found this "MySQL Database Design" chapter from Peachpit to be much easier to follow. They covered First, Second and Third Normal Forms, MySQL Data Types, NULL and Default Values, Indexes and it ends with a little database design best practices. I think it'd be beneficial to have a MySQL category in Fuzzy's list-o-resources. I'd recommend this article if there was such a category...

With my new found knowledge, I have decided I did need to rethink my database and restructure it (yet again).

Here is the new structure I have come up with:

Code:

student
==============================
student_id    //primary key
student_fname
student_lname
student_phone

parent
==============================
parent_id    //primary key
parent_fname
parent_lname
parent_cell

address
==============================
address_id    //primary key
street
city
state
zip

email
==============================
email_id    //primary key
email

student_parent
==============================
student_id    //index
parent_id    //index

student_address
==============================
student_id    //index
address_id    //index

parent_address
==============================
parent_id    //index
address_id    //index

parent_email
==============================
parent_id    //index
email_id    //index

I attempted to use foreign keys but, due to the nature of this project, I don't think they apply here. Most students (thankfully) have two parents, so adding a foreign key (parent_id) to the student table would not work. And adding a foreign key (student_id) to the parent table would not work as many parents have multiple students. Again, the address table can not have either a foreign key of student_id or parent_id as a single address can be related to multiple parents and multiple students. Now the email table nearly got a foreign key, however, flipping through last years paper directory I notice that many parents share the same email address. Sorry email table...no foreign key for you! </normalization nazi>

One other noted change that I made to the database structure is that I moved the phone field to the student table. This seems a bit odd, but there needs to be one main phone number for each student. This told me that the phone field is related to the student, so why not put it in the student table?

With the database structured in this manner I'm thinking it will reduce much redundancy and allow for me to do more with queries later down the road. Yes?