0

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

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

I am confused whether to use the format shown above, or if I should use comma-separated values like

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

If I go with option 1 I the number of rows keeps increasing and empid and mangerid can repeat. But if I go with option 2 then I it's not in a normalized form.

Which is better solution and why?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Pratima
  • 95
  • 2
  • 9
  • 2
    what's wrong with the number of rows increasing? It's literally the databases job – Farkie Sep 16 '16 at 10:17
  • It really depends how you want to analyse the data - maybe consider 2 joined tables - I would like more information and some sample code and data – kerry Sep 16 '16 at 10:20
  • @kerry : Thanks for so quick reply. As i am re structuring the db, the issue is we are allocating employees some managers , and these managers are allocated on different pages , hence on which page they are allocated need to be stored too. The pages are not stored in any different table rather its been added dynamically. I hope u got the situation. I cannot give any code as its not in my hand. So gave the example above. – Pratima Sep 16 '16 at 10:26
  • @user3933674 I always prefer you to go with option1. because in that you can use `indexing`. you can find particular page of particular manager easily. as compared to choosing option2. In option2, indexing will not work. – Manish Sep 16 '16 at 10:28
  • Actually... You _could_ if you use JSON and use something like Flexviews or secondary indexes, but it's probably out of scope ;) – Farkie Sep 16 '16 at 13:50
  • 1
    Option 1 isn't quite right, but option 2 isn't even an option. See option 3: Normalisation. – Strawberry Sep 17 '16 at 09:44
  • Does this answer your question? [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – philipxy Nov 05 '22 at 04:59

2 Answers2

0

YMMV depending on the exact usecase, but as a rule of thumb - keep your database normalized (or at the very least, don't violate 1NF, as the second option suggests!) so you can query/update it easily. Databases are built to store rows and query them effectively, and unless you have a redonculous (read: facebook-like) amount of rows, don't try to reinvent the wheel.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
0

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
Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72