MySQL database design - trouble figuring out table relationships

Question

I'm trying to figure out the best way to design these tables for a website I'm making for a school club. I want to set it up so each user can have multiple emails, phone numbers, and addresses tied to their account. To do this I tried to tie all these things to a contacts table and store the contacts id in the users table as a foreign key. The contacts id is also a foreign key in the emails, phone numbers, and addresses table. Is this a feasible way of relating these tables or should I just cut out the middle man (contacts table) and store the user id in the emails, phone numbers, and addresses tables?

Just in case my description of the relationships weren't enough, here is an ERD for the tables:

Sorry for such a "noob" question, it's been a while since I had to build a database with more complexity than 2 tables. Any general tips for database design are very much welcomed as well.

Answer 1

All you need to do is remove the Contacts table and store the user_id in the tables on the right, rather than contact_id.

Remove contact_id from Users as well.

Answer 2

I have dealt with this very question in the past. We did it wrong and we were sorry.

The determining factors should be these:

1. Will you have any other category of person that isn't a user, for whom you need to store contact information?

2. Will those kinds of persons somehow be "fungible" with users?

If you answer both these questions "yes," keep your contact table. Otherwise get rid of it.

The mistake made by a team I worked on was our answer to the second question. We had medical patients and doctors/nurses/etc as our categories of people. We stored their contact information together. But we shouldn't have done that because patients' contact information is very sensitive and confidential, but health care provider information is much less so. We were always wishing we didn't have the two kinds of data in just one set of tables after the system became successful.

Unless you can convince yourself you need your contact table, get rid of it, I say!