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

[Dax_Trajero]

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 ?

[ilssac]

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

[Dax_Trajero]

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]

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.

[Dax_Trajero]

Agreed

As ever you make a lot of sense and your advice is invaluable.

Many thanks Ian.

[-__cfSearching__-]

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?