CODEWITHSUNDEEP
mysqldatabase-designdata-modeling
xralf
xralf

Reputation: 3752

Storing multiple attributes in MySQL

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?

Upvotes: 0

Views: 736

Answers (1)

Frazz
Frazz

Reputation: 3043

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.

Upvotes: 3

Related Questions