normalization confus.. ization.

I'm struggling to decide which way to go. or if I could overnormalize my database.

I'm working with plants. I started to build tables on the topic, as I was learning (both about plants and about databases). For example, I started with one field for color... then, when I realized there could be more than one color per plant, I separated them out into a column of boolean T/F for each individual color. That quickly got unwieldy, as I discovered more terms. For example, there is a standard terms for two colors at once, "variegated"... if this kept up, I was going to make wayyyy too many columns. And that's just colors. I've also realized there are multiple leaf shapes on a single plant, as well as several other multiplistic parts.

I'm thinking i need to build a new table for each of these characteristics. But my head is swimming. I'm assuming it's a many-to-many scenario? or is it many-to-one. And how would I structure the table? or is it overdoing it, when there are easily 5 different descriptors that could be pulled into a secondary table to join.

Having a column for each of those attributes like you realized is not the correct approach. What you would do is have a lookup table of colors. Then another table that links your plant table with the color table. That table would then just need two columns, the plant ID and the color ID. You can use the same methodology for your leaf shape table as well.

Here is an example of that type of structure:

Plant Table:
PlantID, Plant Name, etc.

Color Table:
ColorID, Color Name

Plant_Colors:
PlantID, ColorID


I would use a composite primary key on the Plant_Colors table.