Skip to main content
D
Dax_Trajero
Inspiring
May 5, 2010
Question

drop-down list - get the data from csv in field or separate table ?

  • May 5, 2010
  • 2 replies
  • 699 views

As a rule of thumb is it always good design to never use a comma separated list in a field ?

eg.

a merchandise table contains necklaces, bracelets and rings

the rings come in 10 different sizes

Option 1

For each ring (row) in the merchandise table, I could have a "size" field with comma separated values.

alternatively

Option 2

For each ring (row) in the merchandise table, I could join to another table called "sizes"

which is the better approach ?

With option 1, it'll save running a query every time a particular ring is viewed on the site ?

This topic has been closed for replies.

2 replies

-
-__cfSearching__-
Inspiring
May 5, 2010
As a rule of thumb is it always good design to never use a comma separated list in a field ?

There are exceptions to every rule, but it is almost never a good idea to store comma separated lists.

Option 1

For each ring (row) in the merchandise table, I could have a "size" field with comma separated values.


With option 1, it'll save running a query every time a particular ring is viewed on the site ?

... and if you needed to find out how many items came in size 6. How would you write that query?

ilssac
ilssac
Inspiring
May 5, 2010

Well option 2 can also be done with a single query using a simple inner join syntax.

D
Dax_TrajeroAuthor
Inspiring
May 5, 2010

Ah, there's me thinking I'd only get the ring sizes in the query results , and no fields from the merchandise table for that item, but thinking about what you said, I could specifiy the query results list not only ring sizes, but also the fields from the matching row in the merchandise table ?

Kill two birds with the one stone so to speak ?

ilssac
ilssac
Inspiring
May 5, 2010

Dax Trajero wrote:

Kill two birds with the one stone so to speak ?

Many more then two, once you start accounting for how difficult it is to update a single size value in a comma list stored in a single field.  How limiting it is to do other types of queries such as "What products are size 6?" example listed below and others.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices