Database snapshots
Below you can download the full database as a MySQL script, automatically created at monthly intervals. The script can be imported into any local MySQL database and will automatically create all relevant tables and their data.
Database documentation
Author: Sarah Faulwetter
Date: 2013-10-09
Below you find a description of the database tables contained in the regular database snapshots which can be downloaded above. The snapshots only contain tables that contain relevant biological traits data - “helper” tables are not included. Likewise, some fields in the download are only used for certain functions of the web interface, these are described below as “internally used only” and greyed out.
This documentation can also be downloaded in pdf format.
Click on the image to enlarge it.
|
Taxonomic backbone of the database, contains all taxonomic names their rank and their synonymy. The hierarchy is stored both as a parent-child model as well as a nested set model.
|
| id_taxon |
primary key of the table |
|
taxon
|
the taxon name without authority |
| author |
the authority and year of the taxon's description |
| fk_valid |
foreign key, links to id_taxon in the same table and points to the name/taxon that is currently considered as the accepted one. In case of no synonymy, fk_valid and id_taxon have the same value |
| fk_unacc_reason |
foreign key, links to table taxa_unacc_reason, describes the reason for synonymy (e.g. objective synonym, subjective synonym, ...) |
| fk_parent |
foreign key, links to id_taxon in the same table and points to the direct parent of the current taxon in the taxonomic classification |
| lft |
left value of a nested set hierarchy model |
| rgt |
right value of a nested set hierarchy model |
| fk_rank |
foreign key, links to table ranks |
| fk_publication_synonym |
foreign key, links to table publications and points to the publication which contains information on the synonymy of the taxon |
| datemodify |
date of last modification of this entry |
| creator |
user who created this entry |
|
Contains a list of the biological ranks (e.g. species, genus...) and their hierarchy
|
| id_rank |
primary key of the table |
| rank |
name of the rank (e.g. species, genus) |
| ranklevel |
internally used only |
| fk_parentrank |
foreign key, links to id_rank in table rank,stores the next higher rank in the hierarchy |
|
parent_rankgroup
|
internally used only |
| rankpath |
internally used only |
| datemodify |
date of last modification of this entry
|
| creator |
user who created this entry |
|
Stores a list of reasons why a taxonomic name is not accepted (e.g. synonymy, misspelling)
|
| id_unacc_reason |
primary key of the table |
| unaccept_reason |
list of reasons for synonymy (e.g. objective synonym, subjective synonym, ...) |
|
Stores all modalities (=trait categories) used in the database |
| id_modality |
primary key of the table |
|
modality
|
name of the modality |
| modality_shortname
|
abbreviated name of the modality
|
| fk_trait |
foreign key, links to table traits
|
| rule_present |
internally used only
|
| definition |
definition of the modality. Can contain literature references in the form "[123]" which correspond to id_publication in table publications. |
| term_identifier |
unique identifier of the term in form of a URL, pointing to the term's definition |
| related_terms |
related, similar or synonymous terms which are often found in the literature |
| description |
any additional explanations to clarify the scope of the term. Can contain literature references in the form "[123]" which correspond to id_publication in table publications. |
| sorting_weight |
internally used only |
| temp_trait |
internally used only |
| temp_reference |
internally used only |
| datemodify |
date of last modification of this entry |
| creator |
user who created this entry |
|
Stores all traits used in the database
|
| id_trait |
primary key of the table |
| trait |
name of the trait |
| fk_mode |
foreign key, links to table modes |
| trait_shortname |
abbreviated name of the trait |
| definition |
definition of the trait. Can contain literature references in the form "[123]" which correspond to id_publication in table publications. |
| related_terms |
related, similar or synonymous terms which are often found in the literature |
| term_identifier |
unique identifier of the term in form of a URL, pointing to the term's definition |
| description |
any additional explanations to clarify the scope of the term. Can contain literature references in the form “[123]” which correspond to id_publication in table publications. |
| datemodify |
date of last modification of this entry
|
| creator |
user who created this entry |
|
Stores a list that groups the traits into adult traits, reproductive traits and larval traits |
| id_mode |
primary key of the table |
| mode |
name of the mode |
| datemodify |
date of last modification of this entry
|
| creator |
user who created this entry |
|
Stores all publications used in the database. |
| id_publication |
primary key of the table |
| pub_year |
the journal's title |
| title |
title of the publication |
| volume |
volume of the publication (where applicable, e.g. journals) |
| pages |
page range or number of pages of the publication |
| issue |
issue of the publication (where applicable, e.g. journals) |
| other |
any description of a publication not classifiable into a publication type such as journal article, book, etc. |
| label |
internally used only |
| DOI |
Digital Object identifier of the publication |
| fk_journal |
foreign key, links to table journals |
| booktitle |
title of the publication if it is a monograph-type of publication (e.g. book, report) |
| publisher |
publisher of the publication |
| city |
city of the publisher |
| institution |
name of the institution that published the publication (mainly applied to theses or reports) |
| seriestitle |
title of the book or report series |
| thesistype |
if publication is a thesis, this stores the type of thesis (e.g. BSc, MSc, PhD) |
| referencetype |
type of the publication (e.g. journal article, book, book chapter, thesis, report, URL...) |
| abstract |
abstract of the publication |
| url |
Unified Resource Locator - internet address linking to the publication |
| fullref |
automatically assembled full citation of the reference |
| shortref |
short version of the reference (e.g. Smith et al. 1999) |
| datemodify |
date of last modification of this entry |
| creator |
user who created this entry |
|
Contains a list of keywords with which publications can be annotated. |
| id_keyword |
primary key of the table |
| keyword |
the term / keyword |
| datemodify |
date of last modification of this entry |
| creator |
user who created this entry |
|
Links table “publication” to table “keywords” |
| fk_keyword |
foreign key, linking to table keywords |
| fk_pub |
foreign key, linking to table publications |
| datemodify |
date of last modification of this entry |
| creator |
user who created this entry |
|
Stores a list of all authors and editors of the publications contained in the database. |
| id_person |
primary key of the table |
| last_name |
last name of the person |
| first_name |
first name of the person |
| datemodify |
date of last modification of this entry |
| creator |
user who created this entry |
|
Links table “publications” to table “persons”. |
| fk_pers |
foreign key, linking to table persons |
| fk_pub |
foreign key, linking to table publications |
| order_appear |
stores the order of the authors in a given publication |
| flag_editor |
flags a person to be an editor of the given publication |
| datemodify |
date of last modification of this entry |
| creator |
user who created this entry |
|
Stores a list of all journal names of publications contained in the database. |
| id_journal |
primary key of the table |
| journal |
the journal's title |
| datemodify |
date of last modification of this entry |
| creator |
user who created this entry |
|
Stores the exact text passage from a literature source that supports the assignment of a modality to a taxon. |
| id_sourcedata |
primary key of the table |
| sourcedata |
contains the exact text passage supporting the assignment of a modality to a taxon |
| date_modify |
date of last modification of this entry |
| creator |
user who created this entry |
|
Links tables “taxon” and “modalities” and assigns a present/absent value to this relationship, indicating whether a modality is present or absent in a taxon |
| id_relation |
primary key of the table |
| fk_taxon |
foreign key, links to table taxa |
| fk_modality |
foreign key, links to table modalities |
| traitvalue |
present/absent value, describes whether a modality is present in a taxon or not |
| datemodify |
date of last modification of this entry |
| creator |
user who created this entry |
|
links table “relations” to table “publications” to support each taxon-modality-traitvalue entity by a publication |
| fk_relation |
foreign key, links to table relations; primary key in combination with fk_publication |
| fk_publication |
foreign key, links to table publications; primary key in combination with fk_relation |
| fk_original |
foreign key, links to table sourcedata |
| datemodify |
date of last modification of this entry |
| creator |
user who created this entry |
| lists all users of the database |
| user |
foreign key, links to field “creator” in all other tables |
| firstname |
first name of the user |
| lastname |
last name of the user |
