I admit to dropping database design in college when I hated the prof....thankfully you all probably know much more than she did.

My goal is to create a database that allows me to track information about plants. This includes the many varieties in many families, along with trading and purchasing info, and growing info.

I used only one family - gesneriads - for the following. I'm not sure what will happen when I branch out and include additional families, as they have completely different characteristics. I'll be asking more about that later, I imagine. Having them all in one database seems daunting, so I thought I'd see if I'm even on the right track with this one family first.

I thought about as many characteristics as I could about the plants and identified them in logical areas. I think all of these would be tables, but am looking for guidance. I haven't identified primary keys, data types, etc. I just want to see if I'm thinking about this logically.

Thanks for looking and offering any input.

General Plant Info
Plant Information
Plant ID
Plant Name
Year Hybridized
Plant Description
Plant Personal Rating
Plant Family
Plant subfamily
Plant Genera
Species or Hybrid

Plant Status
Classic
Vintage
AVSA Most Wanted

Plant Registration
Registration Y/N
Registration Number
Registering Body
Registration Date

Plant Hybridizer
Lastname
Firstname
Company

Plant Size
Mini
Semimini
Small standard
Standard
Large

Plant Type
Regular
Trailer
Chimera

Flower
Flower lobes
Single
Single/semi
Semidouble
Semidouble-double
Double

Flower shape
Star
Pansy
Wasp
Bell
Tubular
Other

Flower drop
Drops
Sticktite
Unspecified

Flower edge
Geneva
Thin
Wide
Glitter
None

Flower color
Main petal
Top petal
Eye
Edge
Band
Tip
Overlay
Fantasy
Shading
Rays
Splotches/patches
Thumbprint

Flower color characteristics
Two-tone
multicolor
Bi-color
Shaded
Sparkle/glitter
Pinwheel
Reverse

Flower petal characteristics
Ruffled
Frilled
Fringed
Fluted
Wavy

Leaves
Leaf main color
Light green
Medium green
Dark green
Black green
Olive green

Leaf variegation type
Crown
Mosaic
Tommie Lou
Unspecified
None

Leaf variegation colors
White
Red
Pink
Cream
Yellow
Light green

Leaf back color
Pink
Red
Silver

Leaf shape
Heart
Round
Ovate
Holly
Longifolia
Girl
Clackamus
Bustle-back

Leaf characteristics
Serrated
Scalloped
Round
Glossy
Pointed
Plain / tailored
Pebbled
Hairy
Wavy
Bustle
Quilted
Supreme
Cupped
Ruffled
Strawberry

Growing (including purchasing and trading)
Purchase Info
Purchase Date
Shipping Date
Received Date
Vendor ID
Item type ordered
item type received
Number received
Cost per item
Condition item received
Purchase Notes

Trading Info
Trading Partner ID
Send or Receive
Item sent
Date item sent
Shipping method
Item type sent
Item size
Number sent
Item type received
Item size received
Date item shipped
Date item received
Condition item received
Number received
Items owed
Trading Notes

Propagation
Propagation ID
Date Started
Propagation Method
Dated potted
Visible Plantlet (date)
Number offspring
Offspring removed (date)
Potting Media
Fertilizer used
Other (hormone, keiki grow, etc.)
Propagation Notes

Growing Details
Currently Growing Y/N
Reason Growing
Reason not growing
Bloom stalks identified (data)
Pot size
Potting Media
Fully Open Bloom (date)
Lighting received
Plant Location
Growing Notes

Care
Date last care
Care performed (repot, crown, trim leaves, prune, suckers removed, etc)
Root rating
Fertilizer used
Overall plant status

Disease/Pest/Issues
Routine treatment date
Routine treatment method
Symptoms
Issue (pest, disease, other)
Date identified
Date resolved
Treatment method
Treatment date
Treatment notes

Taxonomies are a common and perplexing problem in database design.
There are several methods to handle this. I'd recommend storing consistent data in standard relational database form, and storing type-specific data in an XML field.
But that is just one solution.

Thank you for the reply, blindman. I am not familiar with XML fields, having dropped that database class in 1998, lol. I looked them up and seems SQL Server added them in 2005.

What is the benefit of storing some data as an XML field? And, as a novice, is this something that I can do without too much pulling of hair and gnashing of teeth?

Help me understand what you mean by consistent and type-specific data. For example, what would you classify as type-specific data here - things like growing data, personal ratings of plants, trading information, etc?

If I did that, how would it influence how the tables were set up? I broke out the data in ways that seemed logical to me in that they were only about that topic, but do they make sense as tables if you break them out as you've suggested?

No, much hair-pulling and teeth-gnashing is virtually guaranteed.

Consistent data fields would be information that applies to all of your data records. For example, all plants have a name. All plants are toxic or non-toxic, annual or perennial, etc.

You can create fields for storing all these data elements of which you have prior knowledge, and even leave the values null for plants to which they do not apply.

Some characteristics may be specific to single plants and may not be anticipated in your design. If you encounter one, you either have to modify your schema to add that attribute, or you'll need to set up an XML data column.

The best approach, if possible, is still to anticipate and plan for all the elements you need to capture. I'd suggest you set up a table for Plants, and a table for Hybridizers, and see how many of the attributes you've listed fit logically in that two-table schema.