Search code examples
mysqlcode-organization

MySQL column organization

My web application allows a user to define from 1 up to 30 emails (could be anything else). Which of these options is best?

1) ...store the data inside only one column using a separator, like this:

  • [COLUMN emails] peter@example.com,mary@example.com,john@example.com

Structure: 

emails VARCHAR(1829)

 

2) ...or save the data using distinct columns, like this:

  • [COLUMN email1] peter@example.com
  • [COLUMN email2] mary@example.com
  • [COLUMN email3] john@example.com
  • [...]

Structure:

email1 VARCHAR(60)
email2 VARCHAR(60)
email3 VARCHAR(60)
[...]
email30 VARCHAR(60)

Thank you in advance.

Solution

  • Depends on how you are going to use the data and how fixed the amount of 30 is. If it is an advantage to quickly query for the 3rd address or filter using WHERE clauses and such: use distinct fields; otherwise it might not be worth the effort of creating the columns.

    Having the data in a database still has the advantage of concurrent access by several users.