A data structure problem !

[kicken]

The only reason to split out the address would be if you want to allow the user to add multiple addresses. If they can only have one address then you'd just leave it in the original table.

 

The same applies to the contacts. Technically you could separate out email and phone but it's probably not worth it. If you did want to separate it out, then you'd want a table more like this to allow any number of contact methods.

 

create table user_contact (
   id int auto_increment primary key,
   method varchar(20) not null,
   details varchar(200)
);

method would be a string such as 'email', 'phone', 'facebook', etc. details would be whatever that methods value is.

 

When deciding whether or not to split out a set of data into another table, I generally consider two things.

 

1) Can the user have multiples of this data (multiple addresses, multiple emails, etc).

 

2) Will I want to use this data independently of the other data occasionally.

 

Regarding the second point, this applies sometimes to login information, particularly if I have multiple user types each with different details.

 

For example, I might have three tables:

   login (id, username, password, type, other_shared_fields)
   faculty (id, bio, experience, etc)
   student (id, degree, enrollment_date, etc)

Both faculty and student have logins and some common info such as name, email, etc. In some areas I also need to refer to a particular user regardless of their type (ie for change tracking). Have a separate login table allows for this.

[ajoo]

Hi Kicken, 

 

Thanks for the reply.

 

I guess I need more practice before I would be able to figure out which tables require to be split and which not. Database normalization and the desire to do things right have me all confused. 

 

I will keep in mind the two points that you have enlisted above before I decide on splitting a table next time.

 

Regarding the other example, it is similar & so reinforces what was proposed by Guru Jacques.  I am also using a common uid (as in the three of your tables) from the user_detail table for the different types of users in my tables.

 

Thank you very much.

Edited July 7, 2016 by ajoo