Tree Database Data Dictionary

TableColumnTypeKeyDescription
Census CensusID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify a census.
Census PlotID int(10) unsigned MUL Foreign Key to Site table.
Census PlotCensusNumber char(16) Integer census number for an individual plot, 1=first census, 2=second census, etc. If there are more than one plot in the database, each one has a census 1.
Census StartDate date Date on which the first measurement of the census was taken.
Census EndDate date Date on which the last measurement of the census was taken.
Census Description varchar(128) Notes pertinent to the census or general description of the conditions prevailing at the time.
CensusQuadrat CensusID int(10) unsigned MUL Foreign Key to Census table.
CensusQuadrat QuadratID int(10) unsigned MUL Foreign Key to Quadrat table.
CensusQuadrat CensusQuadratID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify combination of census and quadrat.
Coordinates CoorID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify each record.
Coordinates FeatureID int(10) unsigned MUL Foreign key to Feature table. This is only filled if the coordinate is a vertex of a feature.
Coordinates PlotID int(10) unsigned MUL Foreign key to Site table. This is only filled if the coordinate is a vertex of a plot.
Coordinates QuadratID int(10) unsigned MUL Foreign key to Quadrat table. This is only filled if the coordinate is a vertex of a quadrat.
Coordinates GX float Global coordinate on the X-axis (longitude or UTM easting)
Coordinates GY float Global coordinate on the Y-axis (latitude or UTM northing)
Coordinates GZ float Global elevation (ie from sea level) of the point
Coordinates PX float Distance from the plot origin (lower left corner) in meters on the X axis.
Coordinates PY float Distance from the plot origin (lower left corner) in meters on the Y axis.
Coordinates PZ float Elevation of the point in the plot (relative to plot origin's elevation)
Coordinates QX float Distance from the lower left corner of the quadrat in meters on the X axis.
Coordinates QY float Distance from the lower left corner of the quadrat in meters on the Y axis.
Coordinates QZ float Elevation of the point in the quadrat (relative to quadrat origin's elevation)
Coordinates CoordinateNo int(10) unsigned Uniquely identifies the coordinate in the case that more than one point was measured in the plot, feature, or quadrat, and gives order needed for drawing a map of the feature
Country CountryID smallint(5) unsigned PRI Primary key, an integer automatically generated to uniquely identify a country.
Country CountryName varchar(64) Country name
CurrentObsolete SpeciesID int(10) unsigned PRI Foreign key to Species table, indicating the new and correct species name.
CurrentObsolete ObsoleteSpeciesID int(10) unsigned PRI Foreign key to Species table, indicating the incorrect and thus obsolete species name.
CurrentObsolete ChangeDate datetime PRI Date on which data change was made.
CurrentObsolete ChangeCodeID int(10) unsigned MUL Foreign key to TreeTaxChange table, referring to a tree taxonomic change code.
CurrentObsolete ChangeNote varchar(128) Descriptive reason for the change of species.
DataCollection CensusID int(10) unsigned MUL Foreign Key to Census table.
DataCollection StartDate date Date on which the first measurement was taken by collector in a quadrat.
DataCollection EndDate date Date on which last measurement was taken by collector in a quadrat (format is yyyy-mm-dd).
DataCollection DataCollectionID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify each data collection record.
DataCollection PersonnelRoleID int(10) unsigned MUL Foreign key to PersonnelRole table indicating the data collector and his/her role
DataCollection QuadratID int(10) unsigned MUL Foreign key to Quadrat table identifying the quadrat.
DBH CensusID int(10) unsigned MUL Foreign Key to Census table.
DBH StemID int(10) unsigned MUL Foreign Key to Stem table.
DBH DBH float Stem diameter in user-defined units, consistent within the database.
DBH HOM char(16) Height along the stem from the ground at which the diameter was measured; usually in meters, and usually =1.3 meters.
DBH PrimaryStem varchar(20) A text code for the stem (Primary, Secondary, or Branch, but other values could be defined).
DBH ExactDate date Date on which the measurement was taken.
DBH DBHID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify a single DBH measurement.
DBH Comments varchar(128) Descriptive text regarding the measurement: any comment, note, or problem entered on the field sheet.
DBHAttributes CensusID int(10) unsigned MUL Foreign Key to Census table.
DBHAttributes TSMID int(10) unsigned MUL Foreign Key to TSMAttributes (Tree,Stem and dbh Measurement codes) table.
DBHAttributes DBHID int(10) unsigned MUL Foreign Key to DBH table.
DBHAttributes DBHAttID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify the attribute.
Family FamilyID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify a family.
Family Family char(32) Taxonomic family name (from the Angiosperm Phylogeny Group - APG - system).
Family ReferenceID smallint(5) unsigned MUL Foreign key to Reference table, indicating a reference for the taxonomic family
Features FeatureID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify a landscape feature such as a hill or stream.
Features FeatureTypeID int(10) unsigned MUL Foreign Key to FeatureTypes table.
Features Name varchar(32) Feature name.
Features ShortDescrip varchar(32) Short description of feature.
Features LongDescrip varchar(128) Longer (128 chars) description of feature.
FeatureTypes FeatureTypeID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify a Feature type.
FeatureTypes Type varchar(32) User defined feature types, eg, rock outcrop, stream, etc.
Genus GenusID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify a plant genus.
Genus Genus char(32) Taxonomic genus of the plant, according to the APG system.
Genus ReferenceID smallint(5) unsigned MUL Foreign key to Reference table indicating a citation for taxonomic work on the genus.
Genus Authority char(32) Taxonomic authority for the classification of the genus.
Genus FamilyID int(10) unsigned MUL Foreign Key to Family table, indicating which family it belongs to.
Log LogID bigint(20) unsigned PRI Primary key, an integer automatically generated to uniquely identify a Log record.
Log PersonnelID smallint(5) unsigned MUL Foreign Key to Personnel table - indicating the person making the change.
Log ChangedTable varchar(32) Table name whose data has been modified.
Log PrimaryKey varchar(32) Text giving the columns and values required to uniquely identify the changed row in the ChangedTable. Eg. "MeasureID=4820,CensusID=1".
Log ChangedColumn varchar(32) Column name if a single column has been changed. Null when a row delete is being logged.
Log ChangeDate date Date on which data change has been done (format is yyyy-mm-dd).
Log ChangeTime timestamp Time at which data change has been done (format is yyyy-mm-dd hh:mm:ss).
Log Description varchar(256) Descriptive text explaining change in data.
Log Action enum('I','D','U') Code indicating whether the change was an insertion, deletion or an update to the data. Possible values are 'I','D','U'.
Log Old varchar(512) Value in the changed column prior to change. Deletions require a concatenated list of columns and value for the whole row. Eg "StemID=2,TreeID=4256,DBH=12.5,HOM=1.3".
Log New varchar(512) New value in the changed column. Insertions require a concatenated list of columns and value for the whole row. Eg "StemID=2,TreeID=4256,DBH=12.5,HOM=1.3".
Measurement MeasureID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify a Measurement.
Measurement CensusID int(10) unsigned MUL Foreign Key to Census table.
Measurement TreeID int(10) unsigned MUL Foreign Key to Tree table.
Measurement StemID int(10) unsigned MUL Foreign Key to Stem table.
Measurement MeasurementTypeID int(10) unsigned MUL Foreign Key to MeasurementType table, indicating the type of measurement.
Measurement Measure varchar(256) The measurement, which may be a continuous numeric or categorical variable.
Measurement ExactDate date Date on which measurement has been done (format is yyyy-mm-dd).
Measurement Comments varchar(128) Descriptive text entered on the field sheet: comments, notes, or problem regarding the measurement
MeasurementAttributes MAttID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify a particular combination of a TSM code with a measurement.
MeasurementAttributes MeasureID int(10) unsigned MUL Foreign Key to Measurement table.
MeasurementAttributes CensusID int(10) unsigned MUL Foreign Key to Census table.
MeasurementAttributes TSMID int(10) unsigned MUL Foreign Key to TSMAttributes table.
MeasurementType MeasurementTypeID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify a user-defined measurement type.
MeasurementType UOM varchar(32) Unit of Measure.
MeasurementType Type varchar(256) Name of a user defined measurement type. Eg. Tree height, leaf area index etc.
Personnel PersonnelID smallint(5) unsigned PRI Primary key, an integer automatically generated to uniquely identify a person.
Personnel FirstName varchar(32) First name of person.
Personnel LastName varchar(32) Last name of person.
PersonnelRole PersonnelRoleID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify the allocation of a person to a role.
PersonnelRole PersonnelID smallint(5) unsigned MUL Foreign Key to Personnel table.
PersonnelRole RoleID smallint(5) unsigned MUL Foreign Key to RoleReference table.
Quadrat PlotID int(10) unsigned MUL Foreign Key to Site table.
Quadrat QuadratName char(8) MUL The character name for the quadrat, usually the name used in the field; may be the row and column. eg. "0322"
Quadrat Area float unsigned Area of quadrat in square meters.
Quadrat IsStandardShape enum('Y','N') Y if quadrat is a square, otherwise N.
Quadrat QuadratID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify a quadrat.
Reference ReferenceID smallint(5) unsigned PRI Primary key, an integer automatically generated to uniquely identify a reference or citation.
Reference PublicationTitle varchar(64) Title of journal, book or other publication.
Reference FullReference varchar(256) Complete reference or citation ideally in format required for publication.
Reference DateofPublication date Date of publication of journal etc. (format is yyyy-mm-dd).
RemeasAttribs CensusID int(10) unsigned MUL Foreign Key to Census table.
RemeasAttribs TSMID int(10) unsigned MUL Foreign Key to TSM Attributes table.
RemeasAttribs RemeasureID int(10) unsigned MUL Foreign Key to Reameasurement table.
RemeasAttribs RmAttID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify a particular combination of a TSM code with the remeasurement.
Remeasurement CensusID int(10) unsigned MUL Foreign Key to Census table.
Remeasurement StemID int(10) unsigned MUL Foreign Key to Stem table.
Remeasurement DBH float DBH measurement when remeasured. Units of measure must match those in DBH table.
Remeasurement HOM float Height on stem at which diameter was measured; must match definition of HOM in DBH table.
Remeasurement ExactDate date Date of remeasurement. (format is yyyy-mm-dd).
Remeasurement RemeasureID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify a remeasurement.
RoleReference RoleID smallint(5) unsigned PRI Primary key, an integer automatically generated to uniquely identify a person's role in the project.
RoleReference Description varchar(128) Description of a role, e.g. field worker, field supervisor, data entry technician, principal investigator, etc.
Site PlotID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify a plot site.
Site PlotName char(64) Name of the plot eg. BCI, Sinharaja.
Site LocationName varchar(128) Geographical location. Eg 'Barro Colorado Island', 'Central Province'.
Site CountryID smallint(5) unsigned MUL Foreign Key to Country table.
Site ShapeOfSite char(32) A character description of the plot's shape (user-defined; might be dimensions, e.g. 1000x500m or 500x500m)).
Site DescriptionOfSite varchar(128) A free text description of the site.
Site Area float unsigned Area of the plot in square meters.
Site QDimX float unsigned Length of quadrat in meters along the X axis.
Site QDimY float unsigned Length of quadrat in meters along the Y axis.
Site GUOM varchar(32) Unit of Measure for global coordinates.
Site GZUOM varchar(32) Unit of Measure for global elevation coordinates.
Site PUOM varchar(32) Unit of Measure for plot coordinates.
Site QUOM varchar(32) Unit of Measure for quadrat coordinates.
Site GCoorCollected varchar(32) Were global coordinates collected: Y or N?
Site PCoorCollected varchar(32) Were plot coordinates collected: Y or N?
Site QCoorCollected varchar(32) Were quadrat coordinates collected: Y or N?
Site IsStandardSize enum('Y','N') Y if plot is rectangular, N if circular or irregularly shaped.
Species SpeciesID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify a taxonomic species.
Species CurrentTaxonFlag smallint(6) 1 if name is current, 0 if not current.
Species ObsoleteTaxonFlag smallint(6) 1 if name is obsolete, 0 if not obsolete. A name can be both current and obsolete in different context eg. if a taxon has been split.
Species GenusID int(10) unsigned MUL Foreign Key to Genus table. GenusID=9999 when genus is unknown.
Species ReferenceID smallint(5) unsigned MUL Foreign Key to Reference table giving the citation for the taxonomic work.
Species SpeciesName char(64) Species part of Latin name; (or may be a morphospecies name).
Species Mnemonic char(10) MUL Code used in the field for designating the species, usually 6 letters (4 for the genus and 2 for the species).
Species Authority varchar(128) Taxonomic authority for the classification of the species.
Species IDLevel char(8) The deepest taxonomic level for which full identification is known. Limited to values species, genus, family, none, or multiple. None is used when family is not known. Multiple is used when the name may include a mixture of more than one species.
Species FieldFamily char(32) The family determination in the field. May be an obsolete family name no longer in the Family table. Generally used when it is different from the Family table or to indicate family when the genus is unknown, and should be NULL otherwise.
Species Description varchar(128) A free text description of the species, as relevant for the plot (especially, who identified and how).
SpeciesInventory SpeciesInvID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify a species inventory record.
SpeciesInventory CensusID int(10) unsigned MUL Foreign Key to Census table.
SpeciesInventory PlotID int(10) unsigned MUL Foreign Key to Site table.
SpeciesInventory SpeciesID int(10) unsigned MUL Foreign Key to Species table.
SpeciesInventory SubSpeciesID int(10) unsigned MUL Foreign Key to SubSpecies table.
Specimen SpecimenID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify a specimen.
Specimen TreeID int(10) unsigned MUL Foreign Key to Tree table identifying the tree from which the specimen was taken.
Specimen Collector char(64) Foreign Key to Personnel table, indicating who collected the specimen
Specimen SpecimenNumber int(10) unsigned Index number of specimen.
Specimen SpeciesID int(10) unsigned MUL Foreign Key to Species table.
Specimen SubSpeciesID int(10) unsigned MUL Foreign Key to SubSpecies table.
Specimen Herbarium char(32) Name of herbarium in which the specimen is lodged.
Specimen Voucher smallint(5) unsigned Whether the specimen number is a voucher in a herbarium. True or False.
Specimen CollectionDate date Date specimen was collected. (format is yyyy-mm-dd).
Specimen DeterminedBy char(64) Name of person who determined the species name of the specimen.
Specimen Description varchar(128) Free text description of the specimen and its collection circumstances.
Stem StemID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify a stem.
Stem TreeID int(10) unsigned MUL Foreign Key to Tree table, indicating which tree the stem belongs to
Stem StemTag varchar(32) The stem tag used in the field to identify the different stems of a tree in the case of multiple-stemmed trees. Most sites give the main stem a value of 0 and additional stems consecutive values 1,2 etc. Some sites have given multiple stems tags in the same series as trees.
Stem StemDescription varchar(128) Free text description of the stem.
Stem QuadratID int(10) unsigned Foreign Key to Quadrat table, indicating which quadrat the stem is found in
Stem StemNumber int(10) unsigned Included for backward compatability with a previous version of the database and contains the former stemid. May be used to determine the unique stems a tree has when the site did not tag stems.
Stem Moved enum('Y','N') Default is N. If the stem has moved from a previous location (due to landslide etc .) then this column should be Y.
Stem GX float GPS coordinate on the X axis.
Stem GY float GPS coordinate on the Y axis.
Stem GZ float Global elevation (using GPS)
Stem PX float Distance from the plot origin (lower left corner) in meters on the X axis.
Stem PY float Distance from the plot origin (lower left corner) in meters on the Y axis.
Stem PZ float Elevation of the point where stem is found (elevation may be a relative number).
Stem QX float Distance from the lower left corner of the quadrat in meters on the X axis.
Stem QY float Distance from the lower left corner of the quadrat in meters on the Y axis.
Stem QZ float Elevation of the point where stem is found (elevation may be a relative number).
SubSpecies SubSpeciesID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify a subspecies.
SubSpecies SpeciesID int(10) unsigned MUL Foreign Key to Species table.
SubSpecies CurrentTaxonFlag smallint(6) 1 if subspecies name is current, 0 if not current
SubSpecies ObsoleteTaxonFlag smallint(6) 1 if subspecies name is obsolete, 0 if not obsolete (a name can be both current and obsolete in different context eg. if a taxon has been split)
SubSpecies SubSpeciesName char(64) Subspecies portion of the Latin name, may be a subspecies or variety.
SubSpecies Mnemonic char(10) Code used in the field for designating the subspecies.
SubSpecies Authority varchar(128) Taxonomic authority for the classification of the subspecies.
SubSpecies InfraSpecificLevel char(32) Indicates whether the name refers to a subspecies, a variety, a subvariety, a form, etc.
Tree TreeID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify a Tree.
Tree Tag char(10) MUL Tag number on the tree in the field, should be unique within each plot.
Tree SpeciesID int(10) unsigned MUL Foreign Key to Species table, indicating the species identification of the tree
Tree SubSpeciesID int(10) unsigned MUL Foreign Key to SubSpecies table, indicating the subspecies if there is one
TreeAttributes CensusID int(10) unsigned MUL Foreign Key to Census table.
TreeAttributes TreeID int(10) unsigned MUL Foreign Key to Tree table.
TreeAttributes TSMID int(10) unsigned MUL Foreign Key to TSMAttributes table.
TreeAttributes TAttID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify a particular combination of a tree with a TSM code.
TreeTaxChange ChangeCodeID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify a taxonomic change code.
TreeTaxChange Description varchar(128) Free text description of the taxonomic change giving the type of change and/or the reason for change. Eg 'Species A split into Species A and B', 'Misspelled Species name now corrected'.
TSMAttributes TSMID int(10) unsigned PRI Primary key, an integer automatically generated to uniquely identify a Tree, Stem or Measurement Code.
TSMAttributes TSMCode char(10) Code describing or explaining tree, stem or measurements. Eg "Dead", "Lost", "Leaning" etc.
TSMAttributes Description varchar(128) Free text description of the code above..
ViewFullTable DBHID int(11) PRI Foreign Key to DBH table.
ViewFullTable PlotName int(11) Descriptive name of the site also referred to as the plot.
ViewFullTable PlotID varchar(35) MUL Foreign Key to Site table.
ViewFullTable Family char(32) Taxonomic family name (from the Angiosperm Phylogeny Group - APG - system).
ViewFullTable GenusSpecies char(64) Scientific Latin name that includes the genus and species.
ViewFullTable Genus char(32) MUL Genus of the plant, according to the APG system.
ViewFullTable SpeciesName char(64) MUL Species part of Latin name, may be a morphospecies name.
ViewFullTable SubSpecies char(64) Subspecies portion of the Latin name, may be a subspecies or variety.
ViewFullTable SpeciesID int(10) unsigned MUL Foreign Key to Species table.
ViewFullTable Mnemonic char(10) Code used in the field for designating the species, usually 6 letters (4 for the genus and 2 for the species).
ViewFullTable QuadratID int(11) Foreign Key to Quadrat table.
ViewFullTable QuadratName varchar(12) MUL Descriptive name for the quadrat used in the field. The first two characters (digits) usually refer to the column and the last two to the row.
ViewFullTable QX float Distance from the lower left corner of the quadrat in meters on the X axis.
ViewFullTable QY float Distance from the lower left corner of the quadrat in meters on the Y axis.
ViewFullTable PX float Distance from the plot origin (lower left corner) in meters on the X axis.
ViewFullTable PY float Distance from the plot origin (lower left corner) in meters on the Y axis.
ViewFullTable TreeID int(11) MUL Foreign Key to Tree table.
ViewFullTable Tag char(10) MUL Tag number on the tree in the field, should be unique within each plot.
ViewFullTable StemID int(11) Foreign Key to Stem table.
ViewFullTable StemNumber int(11) Column used to carry stemid from previous database version.
ViewFullTable StemTag varchar(32) The stem tag used in the field to identify the different stems of a tree in the case of multiple-stemmed trees.
ViewFullTable PrimaryStem char(20) MUL A character description of the stem, whether it is the primary or a secondary stem, or a branch, etc.
ViewFullTable CensusID int(11) PRI Foreign Key to Census table.
ViewFullTable PlotCensusNumber int(11) MUL Census number, an integer, 1=first census, etc.
ViewFullTable DBH float MUL Stem diameter, usually at breast height. Units are user defined, but assumed to be consistent within the database. It is recommended that the dbh be rounded down to the nearest 5 mm for trees <=5 cm dbh.
ViewFullTable HOM float Height (in meters) on the stem at which the diameter was measured, usually at 1.3 meters.
ViewFullTable ExactDate date Date on which the measurement was taken (format is yyyy-mm-dd).
ViewFullTable Date int(11) Number of days since 1960-01-01
ViewFullTable ListOfTSM varchar(256) MUL Codes indicating the attributes or condition of the tree, stem, or measurement. Codes are separated by a comma in the case of more than one. An explanation of the codes is found in the TSMAttributes.txt file.
ViewFullTable HighHOM tinyint(1) Indicates whether the current height of measurement is the highest for this stem
ViewFullTable LargeStem tinyint(1) Indicates whether the current stem is the largest stem of tree
ViewFullTable Status varchar(15) MUL Indicates the status of the tree or stem. Possible values are: alive (tree or stem is alive), dead (tree is dead), lost_stem (stem is dead, not found, or broken, etc. but other stems of the tree are still alive), or missing (tree or stem was not found, so measurement is unknown).
ViewTaxonomy SpeciesID int(11) PRI Foreign Key to Species table
ViewTaxonomy SubspeciesID int(11) PRI Foreign Key to SubSpecies table, indicating the subspecies if there is one
ViewTaxonomy Family char(32) Taxonomic family name (from the Angiosperm Phylogeny Group - APG - system).
ViewTaxonomy Genus char(32) Genus the species belongs to, according to the APG system.
ViewTaxonomy Mnemonic char(10) Code used in the field for designating the species, usually 6 letters (4 for the genus and 2 for the species).
ViewTaxonomy SpeciesName char(64) Species part of Latin name; (or may be a morphospecies name).
ViewTaxonomy SubspeciesName char(64) Subspecies portion of the Latin name, may be a subspecies or variety.
ViewTaxonomy IDLevel char(8) The deepest taxonomic level for which full identification is known. Limited to values species, genus, family, none, or multiple. None is used when family is not known. Multiple is used when the name may include a mixture of more than one species.
ViewTaxonomy Authority char(124) Taxonomic authority for the classification of the species.
ViewTaxonomy ListOfOldNames varchar(255) List of old names or synonyms used previously, separated by commas
ViewTaxonomy NumberOfHerbarium int(11) Number of herbaria where specimens of this species is found
ViewTaxonomy ListOfHerbarium varchar(255) List of the names of herbaria where specimens of this species are found
ViewTaxonomy Description varchar(128) A free text description of the species, as relevant for the plot (especially, who identified and how).