0

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. test@mail.com,09222229291, http://facebook.com/test,432-2233)

contact_id

ContactTypes

id

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

0

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).

  • You could give me a +1 so, as helpful :-) – Vérace Jul 5 '16 at 4:05
0

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.

  • I think the point of the question was about not using .csv datatypes - the line "val (eg. test@mail.com,09222229291, facebook.com/test,432-2233)" meant email OR URL OR...... In fact, the OP wanted to know about storing multiple emails, URLs, phones &c. per user. – Vérace Jul 5 '16 at 4:03
  • There is very little advantage in 'normalizing' urls, emails, addresses. Phone numbers are 1:many, so a table of them (home, work, fax, cell, second cell, girl friend, probation officer, etc) can be useful because of open-endedness. – Rick James Jul 10 '16 at 22:20
  • @RickJames If there is only one URL, eMail, or address, then I would leave them in the main table. However, I am increasing seeing multiples of each on systems. – BillThor Jul 11 '16 at 0:22
  • @BillThor - I agree with what you are saying, but I think the word "normalize" is not appropriate. Wikipedia talks about "minimizing data redundancy"; moving repeating groups out needs a 1:N mapping, which does not provide for reduced redundancy. – Rick James Jul 11 '16 at 1:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.