Plants Database w/Varieties

[mae]

I've started a gardening database. The main table is tblPlants, which has
fields such as GenusName, SpeciesName (those will make up the primary key),
CommonName, Lifecycle, Hardiness, Shape, BloomColor, MoisturePreference,
SoilPreference, LightPreference, etc.

Some plants have varieties that are different than the "plain" species.
Most lilacs are purple, for instance, but some varieties are white. Maybe a
particular variety of a species likes it wetter than all the others, or maybe
one is fragrant whereas the rest are odorless.

I had thought about making each variety it's own record in tblPlants, but
that means there would be a lot of repeated data (and I'd need a different
PK) because usually it's only one or two things that are different. I could
have 20 records for roses, all identical but for a few factors.

That didn't seem right, so I thought maybe have tblPlants and tblVarieties,
to link each variety to it's "main" species plant. But since there are so
many factors that could vary, tblVarieties would have almost all the same
fields as tblPlants: Hardiness, Shape, BloomColor, MoisturePreference, etc,
with most of the fields being empty. I KNOW this isn't right.

My spreadsheet-geared mind is having a hard time thinking up alternatives.
Any thoughts or suggestions to share?

Thank you.

 

[John Vinson]

I've started a gardening database. The main table is tblPlants, which has
fields such as GenusName, SpeciesName (those will make up the primary key),
CommonName, Lifecycle, Hardiness, Shape, BloomColor, MoisturePreference,
SoilPreference, LightPreference, etc.

Some plants have varieties that are different than the "plain" species.

Not to mention polymorphic species like _Brassica oloracea_

Most lilacs are purple, for instance, but some varieties are white. Maybe a
particular variety of a species likes it wetter than all the others, or maybe
one is fragrant whereas the rest are odorless.

I had thought about making each variety it's own record in tblPlants, but
that means there would be a lot of repeated data (and I'd need a different
PK) because usually it's only one or two things that are different. I could
have 20 records for roses, all identical but for a few factors.

That didn't seem right, so I thought maybe have tblPlants and tblVarieties,
to link each variety to it's "main" species plant. But since there are so
many factors that could vary, tblVarieties would have almost all the same
fields as tblPlants: Hardiness, Shape, BloomColor, MoisturePreference, etc,
with most of the fields being empty. I KNOW this isn't right.

Well... it's a toughie, since in practice it's hard to predict which
Entity (the Species or the Variety) actually has a particular
MoisturePreference as an attribute.

My spreadsheet-geared mind is having a hard time thinking up alternatives.
Any thoughts or suggestions to share?

I'd really be inclined to use one table with a three field (Genus,
Species, Variety) primary key. But you can make a very good case for
the two table solution as well.

Do note that fields with NULL value take up no room in the database,
so (aesthetics aside) they're not all THAT bad.

John W. Vinson[MVP]

 

[mnature]

To keep it simple, let's start with two tables for the plants:

tbl_Plants
PlantID (PK)
PlantName

tbl_Varieties
VarietyID (PK)
PlantID (FK)
VarietyName

Different varieties of plants are related back to the primary plant through
the foreign key of PlantID. Then you need a table for the different factors
that can apply to plants, or to the variety of the plant.

tbl_Factors
FactorID
FactorName
FactorInfo

You will then need a way to apply different factors to their respective
plants or varieties. This will be done through two linking tables. There is
probably a better way to do this than through two tables, but perhaps someone
else will jump in here and correct this slightly unnormalized way of doing it.

tbl_PlantFactors
PlantFactorID (PK)
PlantID (FK)
FactorID (FK)
PlantFactorText

tbl_VarietyFactors
VarietyFactorID (PK)
VarietyID (FK)
FactorID (FK)
VarietyFactorText

This give you a way of distinguishing between a factor that is only applied
to a variety, or a factor that is applied to the entire plant species. You
could then have no factors, or any number of factors, applied to anything,
whether species or variety.