Taxonomy database setup advise

[Alex8]

Hi,

I would like some advise on how to setup a database containing a (biology) taxonomy. 

I'll explain the idea. I will have a main table with information about a species and the taxonomy linked to this information. It's the taxonomy part that I'm not sure about what is the best way op setting it up. It contain species, genus, family and so on. (About 10 rank levels)

After searching on the web I have the following ideas. 

1. I make one table containing: 

ID, name, rank, parentID

Then creating 10 (for each taxon rank) table occurrences relating parentID to ID. 

2. Create 10 tables, one for each taxonomy rank with:

ID, name, parentID

and relate these also by parentID > ID. 

3. I haven't figured out yet how this would workand what the advantage is, but I read about a join table used for a taxonomy ? So one table with name and rank and one with id and parentid ? 

One thing is that somethimes I will have a level in the taxonomy missing, for example, there will be a species name, and the next one will be a family name, so genus will not be used. This isn't a problem with option 1 as the parentID will be there. For option 2 that means I'll have to make an empty record (but with ID and parentID) to not break the chain. 

What option would you advise me to use ? Or suggesting another better option. ;-)

I also want to make a single form to add a new species with the connecting taxon as easy as possible. Will these different options have a big influence on this or does it require a fair ammount of scripting anyway ?

I hope you can help me selecting the best setup for my database. 

Edited February 9, 2016 by Alex8
change font

[comment]

4 minutes ago, Alex8 said:

1. I make one table containing: 

ID, name, rank, parentID

Then creating 10 (for each taxon rank) table occurrences relating parentID to ID. 

Actually, you only need three occurrences at most: ParentTaxon, Taxon, and ChildTaxons. This structure works recursively, and can accommodate any number of levels.

This is a good arrangement to have when the hierarchy of ranks is not rigid (e.g. some levels may not exist within certain branches) or when the items being classified can belong to any level, not only to the lowest one in hierarchy. Otherwise you may prefer to have a table for each rank - esp. if each rank may have some unique attributes.

--
P.S. Please use the default font when posting.

 

 

[Alex8]

Thanks for the reply.

Could you explain me how this would work ?

I'll explain how I was thinking so you have an idea what I'm not understanding.
On a layout I want to display the complete taxonomy, so in my case all 10 levels. I was thinking of more occurrences because in a layout I can make a field for each occurrence, so each next field displays the next taxonomy level.

P.S. Changed the font, I typed the first message on my phone, must have messed it up a bit.

[comment]

1 hour ago, Alex8 said:

On a layout I want to display the complete taxonomy

If you mean to display the entire taxonomy as a tree, you would do that in a list view of the Taxons table (or in a portal to same) - probably with indents calculated as shown here: http://fmforums.com/topic/68142-hierarchy-help/?do=findComment&comment=323413.

I don't see how having 10 fields would help you here. You need more than one field to show a descendant level. To take a simplified example, if a country has 15 cities, you won't be able to show them using one field.

 

[Alex8]

On 2/9/2016 at 10:03 PM, comment said:

I don't see how having 10 fields would help you here. You need more than one field to show a descendant level.

Sorry, this doesn't make sense, first you say 10 fields isn't necessary and then that I do need more than one field...?

Just to be clear, I do want to see the whole taxonomy in one layout, not just the parent or child values.
To take your example...I want to select a city and see in which country, on which continent and which planet ;-) this city is located.

 

[comment]

1 hour ago, Alex8 said:

Sorry, this doesn't make sense, first you say 10 fields isn't necessary and then that I do need more than one field...?

I meant you would need more than one field to show a descendant level, if you wanted to use individual fields to show them.

 

1 hour ago, Alex8 said:

I want to select a city and see in which country, on which continent and which planet ;-) this city is located.

Have you looked at the post I linked to earlier?

[Aurigo]

Hi Alex8

If I have understood you correct, you always want to display the full parental tree of a record.

Both, the displaying and caluclation of the tree can be achieved with 3 fields and 1 relationship and some formating. For the calculation of the FullList field you can also use some other values, titles or whatever you want to display from your taxonomy.

Relationship: Parent ID => ID

Fields

- ID (text field with a serial incremented value or whatever you like)

- Parent ID (text)

- FullList (calculated, = Table__Parent:: Full List & Separator & Title)

With this setup you can calculate any depth of level that you are creating. Depending on how you format the FullList field you can enter tabs, returns or whatever for the separator so you can show the tree either vertically or with a " > " in between or whatever.

For example if you want to get a tree display, you use a return as separator and add before the title a calculation:

Table__Parent:: Full List & Return &

Left ( "                                                                           "; ValueCount ( Table__Parent:: Full List ) * 3) & Title

Considerations

- For level 0 (say: if you have an empty Parent ID) you might want to calculate the FullList a bit different (no separator etc)

- Be aware that this list is calculated recursively and hence you will not be able to use it as a key field for a relationship.

I just did something like this for a scientific stufff (1, 1.1., 1.1.1, 1.1.2 etc.) and it works flawlessly.

 

Edited February 16, 2016 by Aurigo

[Alex8]

Quote

Have you looked at the post I linked to earlier?

Yes, a bit difficult to get it because of the changing taxonomy examples in every reply and the links to the examples are unfortunately not working anymore, but it's useful for the display options of a taxonomy.

Thanks Aurigo for your reply. This clears things up a little for me, I think I could make it work how I want it.

Thank you both for the replies, I made up my plan and a little test file, I got the basics working. I start building and hopefully can make it all work out well. If not I'll open an new topic to the specific problem I might encounter.