Alex Rivera | Logout

Creating Taxonomy Table in MySQL

Asked 2010-11-21T21:21:34.727
11

I am creating a botanical database where the plants will be organized by their taxonomy:

Life Domain Kingdom Phylum Class Order Family Genus Species

I was considering using the example put forth by the article Managing Hierarchical Data in MySQL, however it is adding the above list as records inside the table....and I'm not sure if that is the best thing to do since I will be having multiple species per genus and multiple genus per family and so on. What would you suggest is the best way to approach this problem. Thanks in advance.

sql mysql database taxonomy hierarchy
Edit
Report

1 Answer

4

I'm not sure I really buy into that article. Graph structures would be needed when the categories itself are mutable. Such as, all the sudden taxonomists decided to add three new levels between genus and species, and so on.

From the article:

... the management of hierarchical data is not what a relational database is intended for.

Actually, its exactly what it is intended for:

http://en.wikipedia.org/wiki/Hierarchical_database_model

The hierarchical data model lost traction as Codd's relational model became the de facto standard used by virtually all mainstream database management systems.

I would first write a view that joined all of your tables so that you would have these as your columns:

Life Domain Kingdom Phylum Class Order Family Genus Species

Now you can query that view any way you like and not have to worry about any joins. Easy :)

answered 2010-11-21T22:25:16.883

Your Answer