0

Is there any issue in storing the values in SQL Server DB as comma separated values?

I have 100 categories and for each category I have 500 products. My initial thinking is store the product codes in a comma separated values so that only 100 records will come in table:

  catagcd1 - prod1,prod2,prod3......
  catagcd2 - prod2,prod3,prod5......

But when I ask my friend: don't store it as a comma separated, store as a normal structure. That means:

   catagcd1 - prod1
   catagcd1 - prod2
   catagcd1 - prod3
   catagcd2 - prod2
   ----------------
   ----------------

What is the difference between my way and his? And what is the difference from a performance point of view also? Can you please explain?

Rodia
  • 1,407
  • 8
  • 22
  • 29
Tony
  • 141
  • 4
  • 21
  • 1
    See http://stackoverflow.com/questions/6631851/many-database-rows-vs-one-comma-separated-values-row or http://stackoverflow.com/questions/738133/comma-separated-values-in-a-database-field for example... – eis Jul 31 '12 at 05:55
  • 3
    Comma-separated values are violating even the **first normal form** and are a bad idea generally. SQL Server **has no problem at all** with 500 rows - heck, not even with 500'000 rows! But trying to parse those comma-separated values back into separate values is a mess and takes a lot of (unnecessary) code. Advice: **JUST DON'T DO IT!** The "savings" on number of rows is totally irrelevant and doesn't help at all - the messiness in design and in your queries **IS VERY RELEVANT** and will cause you tons of grief in the future. – marc_s Jul 31 '12 at 06:02
  • Why even store those 100 records? Why not store the 100 records also as a comma separated list, and then you'll have a single record! If 100 is better than 50000, then 1 must be even so much better. – Remus Rusanu Jul 31 '12 at 07:26

1 Answers1

2

Your friend is probably referring to database normalisation, whereby you either have a 1 to many relationship between category and product, or a many : many (category, product and categoryproduct) if a product can appear in more than one category.

The problem with storing the data in comma separated values is that you need to decode the product string every time you need to work with products, which is likely to be slow and cumbersome.

As an alternative, if you never need to reference products directly, you could also store the product / category hierarchy in an Xml column (approaching the problem similar to a document or multivalued database)

StuartLC
  • 104,537
  • 17
  • 209
  • 285