Is it better to save a string/array in a column rather than having multiple rows in a table ? Which has better performance

Question

This is my table with a small sample data:

    orbId  orbPosition  layoutName
    1      6.7         FirstLayout
    2      7.3         FirstLayout
    3      8.4         FirstLayout
    1      5.5         SecondLayout
    2      6.8         SecondLayout

I am using SELECT query to fetch everything with WHERE clause on layoutName. Is it better if I save multiple orbs in one rows and have less rows instead for example:

  orbId          orbPosition         layoutName
 ["1","2","3"]  ["6.7","7.3","8.4"]  FirstLayout
 ["1","2"]      ["5.5","6.8"]        SecondLayout

Let me know pros and cons of both methods and which method is better suitable.

http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574 — e4c5, Nov 13 '16 at 23:32

Vishal Kumar Sahu · Answer 1 · 2016-11-13T21:07:55.580

0

If you want to update/modify this data you should go with first approach else if the data is permanent and needs no change and not needed to be queried individually, then second way is better.

Considering that you have fixed setting for a layout and you need not to modify at any time* and you need not to fetch it individually then you should go with second approach.

Update

For better performance first approach is better as the rows grow, index thing will take care of it.

edited Nov 13 '16 at 21:07

answered Nov 13 '16 at 20:35

Vishal Kumar Sahu

1,232
3
15
27

Well I will need to update it occasionally but I do it by sending a whole string through a webservice in JSON that updates the whole column. for example I can change orbPosition ["6.7","7.3","8.4"] to ["1.6","1.3","4.4"] by sending the latter string as "post" data. You still think I should stick with first approach ? – Sameer Hussain Nov 13 '16 at 20:47
Bottom line can you explain why should I stick to first approach if I need to update/modify and vice versa ? – Sameer Hussain Nov 13 '16 at 20:49
If you are doing it for storing purpose then go with this approach else this approach is not adviced. – Vishal Kumar Sahu Nov 13 '16 at 20:50
If you want to update the data individually like `["6.7","7.3","8.4"] ["1.6","1.3","4.4"] ` to (e.g.) `["1.8","7.3","8.4"] ["0.6","1.3","4.4"] `, mean first value, you will have to update whole the string. Also if you want to have first value you will need to get whole the string. – Vishal Kumar Sahu Nov 13 '16 at 20:52
First comment from @ scaisEdge, clean appraoch would be first one. – Vishal Kumar Sahu Nov 13 '16 at 20:54
I guess you are doing it for Android application. – Vishal Kumar Sahu Nov 13 '16 at 20:54
Close. It's actually for a game. – Sameer Hussain Nov 13 '16 at 20:59
Select the better approach based on data quantity, quality of the code and ease for you. – Vishal Kumar Sahu Nov 13 '16 at 21:05

Is it better to save a string/array in a column rather than having multiple rows in a table ? Which has better performance

1 Answers1