Storing multiple attributes in MySQL

Question

I have a database (for a PHP web application) with a table client having various attributes. Two such attributes are phone_numbers and emails.

One client can have zero or more phone-numbers and zero or more e-mails.

I'd like to store the client table as:

create table client (
  id int not null auto_increment primary key,
  ...
  text phone_numbers,
  text e-mails) default character set utf8

The phone-numbers would have a format "pn1:type1,pn2:type2,...,pnN:typeN" and e-mails would have a format "e1,e2,...,eN".

Are there some important problems with this design I could encounter later?

Is there a better design for these issues and why?

base64encoded serialised arrays would typically be used in this situation. But IMO, the best solution is to have a separate table with multiple phones or emails. — Kohjah Breese
– Kohjah Breese, Commented Aug 23, 2014 at 19:54
AS you want to have multiple phone and email. This is not a normalized design. It is better to have two separate tables for phone and email. You can have two column one for foreign key as client_id and other will be the phone or email value. — sugunan
– sugunan, Commented Aug 23, 2014 at 20:00

Frazz · Accepted Answer · 2014-08-23 20:08:12Z

3

The structure you are thinking of is not normalized and will give you problems... when you search, when you display data, and in many other situations.

It is better to have either 3 tables:

client      (id, ...)
phonenumber (id, client_id, phone_type, phone_number)
email       (id, client_id, email)

... or just 2 tables:

client  (id, ...)
contact (id, client_id, contact_type, contact_text)

I prefer the latter, as it provides more flexibility (it can handle social network accounts, webpages and many other ways to contact people). It is easier to search on than the 3 table structure, and it does not require to be altered when you want to manage a new contact type.

answered Aug 23, 2014 at 20:08

Frazz

3,0432 gold badges22 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

John Ruddell Over a year ago

so what happens when you have more than one phone and only one email? the second design is flawed.

Frazz Over a year ago

Why do you say that? You just have more than one row with the same client_id and contact_type. PRIMARY KEY is (id). UNIQUE key is (client_id, contact_type, contact_text).

xralf Over a year ago

@Frazz Thank you for answer. I let it open for a while yet, if somebody would like to tell something important to this design issue yet.

John Ruddell Over a year ago

@xralf go with option 1. Following normalization you should have a table for phone numbers and a table for email.. There can be unforseen obstacles when using the second option. Both are there in the answer so you should accept the answer still :)

Collectives™ on Stack Overflow

Storing multiple attributes in MySQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related