Simple Problem Relating 3 Small Tables

[joeyb12]

Dear Forum,

Apologies if this seems like a trivial question, but I'm a noob and am having a tough time cracking this problem. I have 3 tables: 1 for a table of plant species, 1 for a table of cultivars for each species, and 1 table for organizations that have seeds for either species or cultivars or both. Here is my attempt at relating the 3 tables:

The relationship between species and organizations is many-to-many, the relationship between species and cultivars is one/none-to-many, and the relationship between cultivars and organizations is many-to-many.

Basically, organizations can have seeds for a species and/or its cultivars. That is to say, if an organization has a cultivar's seeds, by biological definition, the organization automatically has seeds for the species; however, if it has seeds for the species, it doesn't necessarily mean it has seeds for a particular cultivar--it could be for a different cultivar or no cultivar (i.e., its just seeds for the plain/original species and not a fancy/altered cultivar).

Is this design appropriate? It seems like it has a circular logic...isn't that a bit of a design faux pas?

Any comments/improvements in this design would be greatly appreciated!

[rich_m]

Does that mean a cultivator can have only 1 species, or have no spices????

Dear Forum,

Apologies if this seems like a trivial question, but I'm a noob and am having a tough time cracking this problem. I have 3 tables: 1 for a table of plant species, 1 for a table of cultivars for each species, and 1 table for organizations that have seeds for either species or cultivars or both. Here is my attempt at relating the 3 tables:

[ATTACH]6831[/ATTACH]

The relationship between species and organizations is many-to-many, the relationship between species and cultivars is one/none-to-many, and the relationship between cultivars and organizations is many-to-many.

Basically, organizations can have seeds for a species and/or its cultivars. That is to say, if an organization has a cultivar's seeds, by biological definition, the organization automatically has seeds for the species; however, if it has seeds for the species, it doesn't necessarily mean it has seeds for a particular cultivar--it could be for a different cultivar or no cultivar (i.e., its just seeds for the plain/original species and not a fancy/altered cultivar).

Is this design appropriate? It seems like it has a circular logic...isn't that a bit of a design faux pas?

Any comments/improvements in this design would be greatly appreciated!

[joeyb12]

Does that mean a cultivator can have only 1 species, or have no spices????

Hi Rich,

A cultivar can only belong to ONE species. Basically a 'cultivar' is a specific type of the species bred specifically for a given reason. For instance, pink roses are a cultivar of the rose. People breed pink roses specifically because they are pink. A pink rose can only belong to the rose species, it can't belong to the apple or oranges species. Some plant species don't have cultivars (you don't see people breeding pink maple trees, for instance). I hope that makes sense?

So, given the table relationships in the image (please see original post), I'm having problems generating reports that give me meaningful summaries of the data in all the 3 tables. For instance, I want a report showing me all the holdings of an organization (i.e., the seeds of the species and its cultivars, if any. But because species and cultivars are in 2 separate tables, and because I'm a noob, I'm not really sure what I'm doing. Is there a simpler way to relate all 3 tables?

Thanks for your time!