4

We are working on a project that has some product attribute design. I don't think saving attributes as comma separated would be a good practise . But my friend told me that comma separated list will save space.

I read the article in stackoverlow Is storing a delimited list in a database column really that bad?

But nobody there mentioned about memory problem, all of them praised about keeping separate records. Anybody can tell me the real story?

Community
  • 1
  • 1
Duke
  • 35,420
  • 13
  • 53
  • 70

3 Answers3

4

There is no justification based on space. The size of the data could actually be smaller by normalizing since you won't need to put extra characters in the data (commas or whatever you are using for separation).

Your friend is probably just afraid of the prospect of having to write "complicated" queries involving joining tables and is trying to find justification for it (and not succeeding).

Beyond that there is already enough written about why you normalize data.

Mark Fraser
  • 3,160
  • 2
  • 29
  • 48
  • 1
    Yaep, normalisation is your friend. What happens when your DB is useful, is still in use 5 years later and another use is identified for the data. But not in the comma delimited format you have constrained it with? Of course if you DB is not going to be around long then good design both wont matter and will help ensure its not arround long :) – Karl Mar 22 '12 at 08:30
2

Bill's answer in the linked question covers the issues rather nicely. There are few, if any, realterm benefits in storing data in such an unnormalised form. Using multi-value fields WILL cost you in the long term and probably short term too.

If you are 100% sure that your application will NEVER need to process (filter, order by, group by, etc) the contents of your serialised data (comma separated list) then it will have no impact on your application. In most cases where people make this assumption they "get their fingers burnt" as the requirements for the application change with time.

Processing multi-value fields will need more memory and more CPU cycles due to the full table scans required to do anything other than simply read the contents of the field. Googling for "mysql multivalued fields performance problems" returns many results.

user1191247
  • 10,808
  • 2
  • 22
  • 32
-1

Yes, it will definitely save space as comparing to creating a new table. However it is not a good practice to keep the data in such way. As far as creating a new table is concerned, i don't think it may affect a lot. You can focus over other area like creating proper index on table etc.

Neeraj
  • 8,625
  • 18
  • 60
  • 89