-1

I'm creating a MySQL database and have a table for "shirts" with over 30 shirts and a table for "colors" with about 20 colors. Since each shirt can have multiple colors, and multiple shirts can have the same color should I use a comma separated list string column in my shirts table with the ID's of the colors from the colors table or just list the colors in a CSL? Also, a lot of people advise against using CSL's but how else would I create this if I don't use a CSL? For future use I will be making a form that will allow user to search for a shirt that has certain colors.

Robert
  • 35
  • 5
  • 1
    One shirt can have many colors and a color can apply to many shirts is just another way of saying that there is a many-to-many relationship between shirts and colors. A many-to-many relationship is established with a linking table. – Honeyboy Wilson Feb 14 '18 at 22:00
  • Time to read a book on information modeling, the relational model & database design. Also this is a faq. – philipxy Feb 15 '18 at 02:53
  • Possible duplicate of [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – philipxy Feb 15 '18 at 02:54

2 Answers2

1

I would go for a many to many relationship.

Tables

Shirt
id
...

Color
id
name

Shirt_Color
id
shirt_id
color_id

For future use I will be making a form that will allow user to search for a shirt that has certain colors.

Query to select the shirts by colors 'red' and 'blue' if you use many to many relations
Harder to query because of the joins but because the color is only stored once you can't make typing errors.
But this is the way how anny database should be used.
Also makes COUNT and grouping the data much eazier.

Query

SELECT
 Shirt.*
FROM 
 Shirt
INNER JOIN 
 Shirt_Color 
ON
 Shirt.id = Shirt_Color.shirt_id
INNER JOIN 
 Color 
ON
 Shirt_Color.color_id = Color.id
WHERE
 Color.name IN ('red', 'blue')

comma separated values approach looks more like this.

Table

Shirt
id
colors

Query to select the shirts by colors 'red' and 'blue' if you use comma separated values
Looks eazier to query but is more error prone because you need to reinsert every color within the colors field.
Makes COUNT and grouping the data much harder because you need to build a dynamic string splitter with a number generator thats explained here Restructuring a bad database with PHP loops or MySQL (also a post off mine)

Query

SELECT
 *
FROM 
 Shirt
WHERE
   FIND_IN_SET('red', Shirt.colors)
 AND
   FIND_IN_SET('blue', Shirt.colors)
Raymond Nijland
  • 11,488
  • 2
  • 22
  • 34
  • Thanks for the suggestions, i'll try this out later tonight but it looks like that should work. – Robert Feb 15 '18 at 17:36
0

This is a very subjective question, however one better solution would be to create another table that contains columns for entry-ID, shirt-ID and color-ID. You can then populate this table with shirt-color associations. Anytime you need to search for particular colors, you can perform a join with this table.

Sanchit Anand
  • 195
  • 2
  • 11