user3933674 user3933674 - 1 year ago 40
MySQL Question

Multiple rows vs comma seperated column design in MySQL

I have a table where in i need to add rows as follows:

id | empid | manager | page
1 | emp001 | mg001 | page1
2 | emp001 | mg001 | page2
3 | emp001 | mg002 | page1

So I am under confusion whether to use as shown above , or should I use comma separated like,

id | empid | manager | page
1 | emp001 | mg001 | page1,page2,page3,page4.....
2 | emp001 | mg002 | page2,page10,page5,.....

If i go with option1 i feel that number of rows keep increasing and
can repeat. But if i go with option2 then i feel its not normalized form.

Can any one suggest which is better solution and why ?

Answer Source

There is nothing wrong with increasing number of rows. Relational databases work best with normalized data and you can perform effective JOIN operation between tables using indexes provided that you have created them.

Hence the approach presented in option 1 which is actually getting your data to 1NF (first normal form) is way better and will not come to bite you in the future just as the option 2 certainly would.

If in the future you might come up with an idea of analyzing employees and their managers per pages this is where the option 2 bites you.

As an additional note I think you could lower the storage required for your columns and stop repeating yourself by adding additional tables to store employee and manager strings and reference them only by an integer column. As for column pages I find it redundant to append the part "page" making the column values look like "pageX". Column name already tells you that it consist of page values so an integer column X would also suffice in this case.

I find the following schema to be a good start:

  • Employees table storing their ids as integers and names as text
  • Managers table managed analogical to the Employees table
  • Employees_Managers junction table storing its id, foreign key to Employees.ID and Managers.ID and pages of type integer