I'm developing a botanical database in which the plants is going to be organized by their taxonomy:
Existence Domain Kingdom Phylum Class Order Family Genus Species
I had been thinking about while using example help with through the article Managing Hierarchical Data in MySQL, yet it's adding the above mentioned list as records within the table....and I am unsure if that's the very best factor to complete since I'll be getting multiple species per genus and multiple genus per family and so forth. An amount you suggest is the easiest method to approach this issue. Thanks ahead of time.
I am unsure I truly subscribe to it. Graph structures could be needed once the groups itself are mutable. For example, all of the sudden taxonomists made the decision to include three new levels between genus and species, and so forth.
In the article:
... the control over hierarchical information is not exactly what a relational database is supposed for.
Really, its exactly what it's meant for:
The hierarchical data model lost traction as Codd's relational model grew to become the p facto standard utilized by almost all mainstream database management systems.
I'd first write a view that became a member of all your tables to ensure that you'd have these as the posts:
Life Domain Kingdom Phylum Class Order Family Genus Species
You can now query that view in fashion without having to be worried about any joins. Easy
I labored concentrating on the same data, and that i managed to get by 50 percent parts. In PostgreSQL syntax.
First is taxonomy structure (Family, Genus, Species, ...):
CREATE TABLE taxonomic_units ( id serial PRIMARY KEY, name varchar(20) NOT NULL, parent_id integer NOT NULL REFERENCES taxonomic_units(id) ); 1 | Life | NULL 2 | Domain | 1 ... 7 | Family | 6 8 | Genus | 7 9 | Species | 8
Second is describtion and storing of botanical data:
CREATE TABLE taxons ( id serial PRIMARY KEY, suptaxon_id integer REFERENCES taxons(id), taxonomic_unit_id integer NOT NULL REFERENCES taxonomic_units(id), name varchar(50) NOT NULL, authority varchar(50) ); 100 | NULL | 8 | Ocimum | L. 101 | 100 | 9 | basilicum | L. 102 | 100 | 9 | gratissim | L.
It may sound a lot more like a graph. I'd question if NEO4J will be a better option.
You will find a number of ways of representing hierarchical data inside a relational database, although a NoSQL solution may be simpler to utilize as @duffymo pointed out. So presuming an RDBMS, see my question on the topic for an enumeration of a half dozen possibilities. For the situation, I'd lead having a materialized road to make seeing the household tree easy. When the hierarchy changes regularly I'd most likely also model being an adjacency list increase the materialized path utilizing a trigger.