CODEWITHSUNDEEP
mysqlsqldatabase-designrelational-databaseinnodb
KylePlusPlus
KylePlusPlus

Reputation: 259

MySQL database design - trouble figuring out table relationships

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:

ERD

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.

Upvotes: 3

Views: 1180

Answers (4)

Michael Durrant
Michael Durrant

Reputation: 96454

Yes I would cut out the midle man:

Although I was tempted to go the 'contact_type' route, I have found that there are usually validations and different data types which become more complicated when the contact is generic. For instance a table that has address fields is not the same as a phone number and having both presents more complexity and less readability.

This model focuses on simplicity, e.g. a user has many emails and an email belongs ot a user.

enter image description here

Upvotes: 2

Kaushal
Kaushal

Reputation: 471

According to me you can design DB accordingly

Table 1 : Users
UserID //PK
Name

Table 2 : Contacts
ContactID  //PK
UserID  //FK to Users
ContactTypeID   // FK to ContactType
Value

Table 3 : ContactType
ContactTypeID   //PK
ContactTypeName
Description

Table 1 is pretty clear stores user information Table 3 holds information about contacttype i.e email, home phone, mobile, home address, shipping address, etc Table 2 holds information about user, contact type and its value like cinatacttypeid corresponds to mobile than value is , etc.

Upvotes: 1

O. Jones
O. Jones

Reputation: 108641

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!

Upvotes: 3

Gigi
Gigi

Reputation: 29421

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.

Upvotes: 3

Related Questions