Storing email,phone,fax and url in same table

Question

Is it ok to store email,phone,fax and url in same table? I want user to be able to add multiple email,phone,fax or website url on their profile.

Here's my current table

Contacts

id

user_id

val (eg. [email protected],09222229291, http://facebook.com/test,432-2233)

contact_id

ContactTypes

id

name (eg. mobile,tel,email,url,fax)

Answer 1

You should do something like the following:

CREATE TABLE client
(
  client_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
  client_name VARCHAR(100)
  .. other
  .. client
  ., fields
);

and

CREATE TABLE contact
(
  contact_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  client_id INT,
  contact_type VARCHAR(3),  -- (e.g. 'URL', 'PH', 'FAX'  &c.)
  contact_value VARCHAR(256),
  FOREIGN KEY (fk_contact_client) REFERENCES client (client_id)
);

A few things to note:

• No need for a contact_type table unless there are many (rule of thumb, more than ~7 and/or if your codes are not intuitive).

• Of course, you can add a contact_type child table to contact if you wish, in the same manner as contact is a child to client. Whether you decide you want to do this will depend on your use case and the number of contact_types.

• It's a pity that MySQL doesn't have CHECK constraints, which would be ideal for a small number of contact types. Whatever you do, don't use an ENUM type, they are evil 😀

• If restricting/controlling contact_type is important to you, use a lookup table. This is not "clutter" - many small reference tables are a "good thing" and MySQL can cope with 1000's of tables. Take a look at the first reference in my answer here - avoid the OTLT (One True Lookup Table) trap. Also, see here and here for further discussion ( Joe Celko is a big hitter in the world of SQL).

Answer 2

If you want to normalize your data, you will need separate tables for phone/fax numbers, addresses, and websites. When normalizing data repeating groups require a child table.