Multiple id's in a single field?

[wabash]

I'm looking for the best practice to assign multiple blooming colors to a plant.  For example a Yarrow plant blooms white and yellow flowers.  I have a plant table and plant color table (among others) which look like:

 

plantTable

id, plant, color

1, Yarrow, ??

 

plantColorTable

id, color

1, yellow

2, white

3, red

etc.

 

?? Can I put multiple color Id's in a single field for color in the plantTable or is there a better practice for something like this?

 

Thanks so much for your help.

 

Bill....

 

 

[kickstart]

Hi

 

Putting multiple ids in one field makes it very difficult to do a JOIN (it is possible, but is very nasty code and just about unreadable). It is a common mistake people make when starting with SQL.

 

Use a link table. Don't have the colour in the plant table. In the link table you have multiple rows per flower, one for each colour.

 

All the best

 

Keith

[wabash]

Thanks Keith,

 

So would this link table look like the following?:

 

flowerColorTable

 

flower id, red, whtie, green yellow, purple,...

23,0,1,0,1,0

 

I include the flower id and put a #1 if the flower has that blooming color?  Something like this or are you thinking of different?

 

Thanks

 

Bill...

 

[PFMaBiSmAd]

You would have a row for each entry -

 

plant_id, color_id
23          1
23          2

 

If you need to add a new color (i.e. lavender, Indigo, ...) you would just add that color to the color table and then use that color_id where needed in the above table. The scheme you proposed would require that you alter the table and add a column in order to add a new color. You should never find yourself needing to dynamically add a column to a table just because a new value is being used (that is referred to as spread-sheet thinking and it results in bad database designs.)

 

[kickstart]

Hi

 

As above. Having multiple columns also makes it long winded to get the real colour. Using a column per colour would mean doing a JOIN to the colours table for every colour if you wanted to know the name rather than the id.

 

All the best

 

Keith

[wabash]

Ah, that makes a lot of sense.  Thanks guys I really appreciate it.

 

Bill...