CTFS Data Model: Tables, Attributes, Schema

Richard Condit
Suzanne Lao
Anudeep Singh
Shameema Esufali
Steven Dolins
Smithsonian Tropical Research Institute and Bradley University

Table list : CTFS Data Model. All tables in the database. Righthand column has link to details about each table.

Table	Description	Link



Census	One record per plot-census combination.	Details
CensusQuadrat	One record per quadrat-census combination.	Details
Coordinates	One record for all coordinates of a defined point in the plot, typically a vertex of the plot or quadrat or any defined feature within the plot.	Details
Country	Names of the countries in which the plots of the database are located (for reporting reasons, and typically just one country).	Details
CurrentObsolete	One record for each event changing the name of one species. The Primary Key is compound, SpeciesID-ObsoleteSpeciesID-ChangeDate. Each record is one case where a species name has been changed, with SpeciesID holding the identifier for the new (correct) species name (as given in the Species table) and ObsoleteSpeciesID the identifier for the old (incorrect) name, also in the Species table.	Details
DataCollection	One record for each quadrat-census-person combination, indicating the person who collected data (ie the collector) in one quadrat during one census, as well as the type of work done.	Details
DBH	One record for each diameter measurement of a single stem in a single census.	Details
DBHAttributes	One record for a single attribute of one diameter measurement (so there can be > 1 row for a single diameter).	Details
Family	Single record per family, following Angiosperm Phylogeny Group (APG) classification. All databases include the entire table of all Angiosperm families. ReferenceID points to the published reference as given in the Reference table.	Details
Features	Descriptions of any relevant geographical features in the plot, such as streams, swamps, etc.	Details
FeatureTypes	Types of features found in the plot	Details
Genus	Single record per genus, following Angiosperm Phylogeny Group classification. All databases include the entire table of all Angiosperm genera. ReferenceID points to the published reference as given in the Reference table.	Details
Log	Records all the changes made to any columns in the tables of the database	Details
Measurement	Measurements other than dbh collected on any stem.	Details
MeasurementAttributes	Any attribute linked to any of the measurements in the Measurement table, using codes from the TSMAttributes table.	Details
MeasurementType	Type of measurements recorded in the Measurement table and the units of measure used	Details
Personnel	Names of all personnel involved with the plot	Details
PersonnelRole	Roles the personnel have played in the plot. Some people may have more than one role.	Details
Quadrat	One record for every quadrat in every plot of the database.	Details
Reference	All the references and citations referred to in the Family, Genus, Species and SubSpecies tables	Details

RemeasAttribs	Any attribute linked to the remeasurements in the Remeasurement table, using codes from the TSMAttributes table.	Details
Remeasurement	Extra DBH measurements for any stem within a census. These measurements are not used as primary data, but rather as a check for accuracy of the initial DBH measurement (in the DBH table). Table structure is precisely the same as DBH table.	Details
RoleReference	All the roles performed by personnel within the plot	Details
Site	Names for all plots and inventories in the database, including geographical and descriptive data about the site. Crucial for having precise map location of the plot.	Details
Species	Single record for every species name ever used in the plot, whether current or obsolete. May be morphospecies.	Details
SpeciesInventory	Used only for plot-less inventories in which species presence alone is indicated. Each record indicates a single species observation at one site.	Details
Specimen	Information of any specimen collected from the site: who collected it, who determined its species identification, whether it is a voucher in an herbarium, etc.	Details
Stem	All the stems in the plot from all the censuses, and their location. A stem may have moved to another location due to landslides.	Details
SubSpecies	Lists the subspecies portion of the taxonomic species in the Species table, may be a subspecies or variety.	Details
Tree	Lists all the tags and species identifications of every tree ever censused in any of the plots	Details
TreeAttributes	Records any attribute linked to a tree in the Tree table, using codes from the TSMAttributes table.	Details
TreeTaxChange	The taxonomic change codes, i.e. why a taxonomic name changed	Details
TSMAttributes	Codes or attributes used in the TreeAttributes, DBHAttributes, MeasurementAttributes, and RemeasAttribs tables.	Details
ViewFullTable	Merges all the relevant variables from the corresponding tables in the database back into a flat file. Each record represents one measurement and/or attribute of one stem of one tree of one plot from one census. Because buttresses can grow, for trees with buttresses, a stem may be measured at more than one height. So some stems may have more than one measurement in a census, but at different hom	Details
ViewTaxonomy	All the current taxonomic species and subspecies names, with their corresponding genus and family, identification level, authority, any synonyms and obsolete species names used, and what herbariums their specimens are found in.	Details

Definitions : Census table. One record per plot-census combination.

Column	Type	Description

CensusID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify a census.
PlotID	int(10) unsigned	Foreign Key to Site table.
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.
StartDate	date	Date on which the first measurement of the census was taken.
EndDate	date	Date on which the last measurement of the census was taken.
Description	varchar(128)	Notes pertinent to the census or general description of the conditions prevailing at the time.

Definitions : Coordinates table. One record for all coordinates of a defined point in the plot, typically a vertex of the plot or quadrat or any defined feature within the plot.

Column	Type	Description

CoorID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify each record.
FeatureID	int(10) unsigned	Foreign key to Feature table. This is only filled if the coordinate is a vertex of a feature.
PlotID	int(10) unsigned	Foreign key to Site table. This is only filled if the coordinate is a vertex of a plot.
QuadratID	int(10) unsigned	Foreign key to Quadrat table. This is only filled if the coordinate is a vertex of a quadrat.
GX	float	Global coordinate on the X-axis (longitude or UTM easting)
GY	float	Global coordinate on the Y-axis (latitude or UTM northing)
GZ	float	Global elevation (ie from sea level) of the point
PX	float	Distance from the plot origin (lower left corner) in meters on the X axis.
PY	float	Distance from the plot origin (lower left corner) in meters on the Y axis.
PZ	float	Elevation of the point in the plot (relative to plot origin’s elevation)
QX	float	Distance from the lower left corner of the quadrat in meters on the X axis.
QY	float	Distance from the lower left corner of the quadrat in meters on the Y axis.
QZ	float	Elevation of the point in the quadrat (relative to quadrat origin’s elevation)
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

Definitions : Country table. Names of the countries in which the plots of the database are located (for reporting reasons, and typically just one country).

Column	Type	Description

CountryID	smallint(5) unsigned	Primary key, an integer automatically generated to uniquely identify a country.
CountryName	varchar(64)	Country name

Definitions : CurrentObsolete table. One record for each event changing the name of one species. The Primary Key is compound, SpeciesID-ObsoleteSpeciesID-ChangeDate. Each record is one case where a species name has been changed, with SpeciesID holding the identifier for the new (correct) species name (as given in the Species table) and ObsoleteSpeciesID the identifier for the old (incorrect) name, also in the Species table.

Column	Type	Description

SpeciesID	int(10) unsigned	Foreign key to Species table, indicating the new and correct species name.
ObsoleteSpeciesID	int(10) unsigned	Foreign key to Species table, indicating the incorrect and thus obsolete species name.
ChangeDate	datetime	Date on which data change was made.
ChangeCodeID	int(10) unsigned	Foreign key to TreeTaxChange table, referring to a tree taxonomic change code.
ChangeNote	varchar(128)	Descriptive reason for the change of species.

Definitions : DataCollection table. One record for each quadrat-census-person combination, indicating the person who collected data (ie the collector) in one quadrat during one census, as well as the type of work done.

Column	Type	Description

CensusID	int(10) unsigned	Foreign Key to Census table.
StartDate	date	Date on which the first measurement was taken by collector in a quadrat.
EndDate	date	Date on which last measurement was taken by collector in a quadrat (format is yyyy-mm-dd).
DataCollectionID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify each data collection record.
PersonnelRoleID	int(10) unsigned	Foreign key to PersonnelRole table indicating the data collector and his/her role
QuadratID	int(10) unsigned	Foreign key to Quadrat table identifying the quadrat.

Definitions : DBH table. One record for each diameter measurement of a single stem in a single census.

Column	Type	Description

CensusID	int(10) unsigned	Foreign Key to Census table.
StemID	int(10) unsigned	Foreign Key to Stem table.
DBH	float	Stem diameter in user-defined units, consistent within the database.
HOM	char(16)	Height along the stem from the ground at which the diameter was measured; usually in meters, and usually =1.3 meters.
PrimaryStem	varchar(20)	A text code for the stem (Primary, Secondary, or Branch, but other values could be defined).
ExactDate	date	Date on which the measurement was taken.
DBHID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify a single DBH measurement.
Comments	varchar(128)	Descriptive text regarding the measurement: any comment, note, or problem entered on the field sheet.

Definitions : DBHAttributes table. One record for a single attribute of one diameter measurement (so there can be > 1 row for a single diameter).

Column	Type	Description

CensusID	int(10) unsigned	Foreign Key to Census table.
TSMID	int(10) unsigned	Foreign Key to TSMAttributes (Tree,Stem and dbh Measurement codes) table.
DBHID	int(10) unsigned	Foreign Key to DBH table.
DBHAttID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify the attribute.

Definitions : Family table. Single record per family, following Angiosperm Phylogeny Group (APG) classification. All databases include the entire table of all Angiosperm families. ReferenceID points to the published reference as given in the Reference table.

Column	Type	Description

FamilyID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify a family.
Family	char(32)	Taxonomic family name (from the Angiosperm Phylogeny Group - APG - system).
ReferenceID	smallint(5) unsigned	Foreign key to Reference table, indicating a reference for the taxonomic family

Definitions : Features table. Descriptions of any relevant geographical features in the plot, such as streams, swamps, etc.

Column	Type	Description

FeatureID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify a landscape feature such as a hill or stream.
FeatureTypeID	int(10) unsigned	Foreign Key to FeatureTypes table.
Name	varchar(32)	Feature name.
ShortDescrip	varchar(32)	Short description of feature.
LongDescrip	varchar(128)	Longer (128 chars) description of feature.

Definitions : FeatureTypes table. Types of features found in the plot

Column	Type	Description

FeatureTypeID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify a Feature type.
Type	varchar(32)	User defined feature types, eg, rock outcrop, stream, etc.

Definitions : Genus table. Single record per genus, following Angiosperm Phylogeny Group classification. All databases include the entire table of all Angiosperm genera. ReferenceID points to the published reference as given in the Reference table.

Column	Type	Description

GenusID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify a plant genus.
Genus	char(32)	Taxonomic genus of the plant, according to the APG system.
ReferenceID	smallint(5) unsigned	Foreign key to Reference table indicating a citation for taxonomic work on the genus.
Authority	char(32)	Taxonomic authority for the classification of the genus.
FamilyID	int(10) unsigned	Foreign Key to Family table, indicating which family it belongs to.

Definitions : Log table. Records all the changes made to any columns in the tables of the database

Column	Type	Description

LogID	bigint(20) unsigned	Primary key, an integer automatically generated to uniquely identify a Log record.
PersonnelID	smallint(5) unsigned	Foreign Key to Personnel table - indicating the person making the change.
ChangedTable	varchar(32)	Table name whose data has been modified.
PrimaryKey	varchar(32)	Text giving the columns and values required to uniquely identify the changed row in the ChangedTable. Eg. "MeasureID=4820,CensusID=1".
ChangedColumn	varchar(32)	Column name if a single column has been changed. Null when a row delete is being logged.
ChangeDate	date	Date on which data change has been done (format is yyyy-mm-dd).
ChangeTime	timestamp	Time at which data change has been done (format is yyyy-mm-dd hh:mm:ss).
Description	varchar(256)	Descriptive text explaining change in data.
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’.
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".
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".

Definitions : Measurement table. Measurements other than dbh collected on any stem.

Column	Type	Description

MeasureID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify a Measurement.
CensusID	int(10) unsigned	Foreign Key to Census table.
TreeID	int(10) unsigned	Foreign Key to Tree table.
StemID	int(10) unsigned	Foreign Key to Stem table.
MeasurementTypeID	int(10) unsigned	Foreign Key to MeasurementType table, indicating the type of measurement.
Measure	varchar(256)	The measurement, which may be a continuous numeric or categorical variable.
ExactDate	date	Date on which measurement has been done (format is yyyy-mm-dd).
Comments	varchar(128)	Descriptive text entered on the field sheet: comments, notes, or problem regarding the measurement

Definitions : MeasurementAttributes table. Any attribute linked to any of the measurements in the Measurement table, using codes from the TSMAttributes table.

Column	Type	Description

MAttID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify a particular combination of a TSM code with a measurement.
MeasureID	int(10) unsigned	Foreign Key to Measurement table.
CensusID	int(10) unsigned	Foreign Key to Census table.
TSMID	int(10) unsigned	Foreign Key to TSMAttributes table.

Definitions : MeasurementType table. Type of measurements recorded in the Measurement table and the units of measure used

Column	Type	Description

MeasurementTypeID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify a user-defined measurement type.
UOM	varchar(32)	Unit of Measure.
Type	varchar(256)	Name of a user defined measurement type. Eg. Tree height, leaf area index etc.

Definitions : Personnel table. Names of all personnel involved with the plot

Column	Type	Description

PersonnelID	smallint(5) unsigned	Primary key, an integer automatically generated to uniquely identify a person.
FirstName	varchar(32)	First name of person.
LastName	varchar(32)	Last name of person.

Definitions : PersonnelRole table. Roles the personnel have played in the plot. Some people may have more than one role.

Column	Type	Description

PersonnelRoleID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify the allocation of a person to a role.
PersonnelID	smallint(5) unsigned	Foreign Key to Personnel table.
RoleID	smallint(5) unsigned	Foreign Key to RoleReference table.

Definitions : Quadrat table. One record for every quadrat in every plot of the database.

Column	Type	Description

PlotID	int(10) unsigned	Foreign Key to Site table.
QuadratName	char(8)	The character name for the quadrat, usually the name used in the field; may be the row and column. eg. "0322"
Area	float unsigned	Area of quadrat in square meters.
IsStandardShape	enum(’Y’,’N’)	Y if quadrat is a square, otherwise N.
QuadratID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify a quadrat.

Definitions : Reference table. All the references and citations referred to in the Family, Genus, Species and SubSpecies tables

Column	Type	Description

ReferenceID	smallint(5) unsigned	Primary key, an integer automatically generated to uniquely identify a reference or citation.
PublicationTitle	varchar(64)	Title of journal, book or other publication.
FullReference	varchar(256)	Complete reference or citation ideally in format required for publication.
DateofPublication	date	Date of publication of journal etc. (format is yyyy-mm-dd).

Definitions : RemeasAttribs table. Any attribute linked to the remeasurements in the Remeasurement table, using codes from the TSMAttributes table.

Column	Type	Description

CensusID	int(10) unsigned	Foreign Key to Census table.
TSMID	int(10) unsigned	Foreign Key to TSM Attributes table.
RemeasureID	int(10) unsigned	Foreign Key to Reameasurement table.
RmAttID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify a particular combination of a TSM code with the remeasurement.

Definitions : Remeasurement table. Extra DBH measurements for any stem within a census. These measurements are not used as primary data, but rather as a check for accuracy of the initial DBH measurement (in the DBH table). Table structure is precisely the same as DBH table.

Column	Type	Description

CensusID	int(10) unsigned	Foreign Key to Census table.
StemID	int(10) unsigned	Foreign Key to Stem table.
DBH	float	DBH measurement when remeasured. Units of measure must match those in DBH table.
HOM	float	Height on stem at which diameter was measured; must match definition of HOM in DBH table.
ExactDate	date	Date of remeasurement. (format is yyyy-mm-dd).
RemeasureID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify a remeasurement.

Definitions : RoleReference table. All the roles performed by personnel within the plot

Column	Type	Description

RoleID	smallint(5) unsigned	Primary key, an integer automatically generated to uniquely identify a person’s role in the project.
Description	varchar(128)	Description of a role, e.g. field worker, field supervisor, data entry technician, principal investigator, etc.

Definitions : Site table. Names for all plots and inventories in the database, including geographical and descriptive data about the site. Crucial for having precise map location of the plot.

Column	Type	Description

PlotID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify a plot site.
PlotName	char(64)	Name of the plot eg. BCI, Sinharaja.
LocationName	varchar(128)	Geographical location. Eg ’Barro Colorado Island’, ’Central Province’.
CountryID	smallint(5) unsigned	Foreign Key to Country table.
ShapeOfSite	char(32)	A character description of the plot’s shape (user-defined; might be dimensions, e.g. 1000x500m or 500x500m)).
DescriptionOfSite	varchar(128)	A free text description of the site.
Area	float unsigned	Area of the plot in square meters.
QDimX	float unsigned	Length of quadrat in meters along the X axis.
QDimY	float unsigned	Length of quadrat in meters along the Y axis.
GUOM	varchar(32)	Unit of Measure for global coordinates.
GZUOM	varchar(32)	Unit of Measure for global elevation coordinates.
PUOM	varchar(32)	Unit of Measure for plot coordinates.
QUOM	varchar(32)	Unit of Measure for quadrat coordinates.
GCoorCollected	varchar(32)	Were global coordinates collected: Y or N?
PCoorCollected	varchar(32)	Were plot coordinates collected: Y or N?
QCoorCollected	varchar(32)	Were quadrat coordinates collected: Y or N?
IsStandardSize	enum(’Y’,’N’)	Y if plot is rectangular, N if circular or irregularly shaped.

Definitions : Species table. Single record for every species name ever used in the plot, whether current or obsolete. May be morphospecies.

Column	Type	Description

SpeciesID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify a taxonomic species.
CurrentTaxonFlag	smallint(6)	1 if name is current, 0 if not current.
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.
GenusID	int(10) unsigned	Foreign Key to Genus table. GenusID=9999 when genus is unknown.
ReferenceID	smallint(5) unsigned	Foreign Key to Reference table giving the citation for the taxonomic work.
SpeciesName	char(64)	Species part of Latin name; (or may be a morphospecies name).
Mnemonic	char(10)	Code used in the field for designating the species, usually 6 letters (4 for the genus and 2 for the species).
Authority	varchar(128)	Taxonomic authority for the classification of the 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.
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.
Description	varchar(128)	A free text description of the species, as relevant for the plot (especially, who identified and how).

Definitions : SpeciesInventory table. Used only for plot-less inventories in which species presence alone is indicated. Each record indicates a single species observation at one site.

Column	Type	Description

SpeciesInvID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify a species inventory record.
CensusID	int(10) unsigned	Foreign Key to Census table.
PlotID	int(10) unsigned	Foreign Key to Site table.
SpeciesID	int(10) unsigned	Foreign Key to Species table.
SubSpeciesID	int(10) unsigned	Foreign Key to SubSpecies table.

Definitions : Specimen table. Information of any specimen collected from the site: who collected it, who determined its species identification, whether it is a voucher in an herbarium, etc.

Column	Type	Description

SpecimenID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify a specimen.
TreeID	int(10) unsigned	Foreign Key to Tree table identifying the tree from which the specimen was taken.
Collector	char(64)	Foreign Key to Personnel table, indicating who collected the specimen
SpecimenNumber	int(10) unsigned	Index number of specimen.
SpeciesID	int(10) unsigned	Foreign Key to Species table.
SubSpeciesID	int(10) unsigned	Foreign Key to SubSpecies table.
Herbarium	char(32)	Name of herbarium in which the specimen is lodged.
Voucher	smallint(5) unsigned	Whether the specimen number is a voucher in a herbarium. True or False.
CollectionDate	date	Date specimen was collected. (format is yyyy-mm-dd).
DeterminedBy	char(64)	Name of person who determined the species name of the specimen.
Description	varchar(128)	Free text description of the specimen and its collection circumstances.

Definitions : Stem table. All the stems in the plot from all the censuses, and their location. A stem may have moved to another location due to landslides.

Column	Type	Description

StemID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify a stem.
TreeID	int(10) unsigned	Foreign Key to Tree table, indicating which tree the stem belongs to
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.
StemDescription	varchar(128)	Free text description of the stem.
QuadratID	int(10) unsigned	Foreign Key to Quadrat table, indicating which quadrat the stem is found in
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.
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.
GX	float	GPS coordinate on the X axis.
GY	float	GPS coordinate on the Y axis.
GZ	float	Global elevation (using GPS)
PX	float	Distance from the plot origin (lower left corner) in meters on the X axis.
PY	float	Distance from the plot origin (lower left corner) in meters on the Y axis.
PZ	float	Elevation of the point where stem is found (elevation may be a relative number).
QX	float	Distance from the lower left corner of the quadrat in meters on the X axis.
QY	float	Distance from the lower left corner of the quadrat in meters on the Y axis.
QZ	float	Elevation of the point where stem is found (elevation may be a relative number).

Definitions : SubSpecies table. Lists the subspecies portion of the taxonomic species in the Species table, may be a subspecies or variety.

Column	Type	Description

SubSpeciesID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify a subspecies.
SpeciesID	int(10) unsigned	Foreign Key to Species table.
CurrentTaxonFlag	smallint(6)	1 if subspecies name is current, 0 if not current
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)
SubSpeciesName	char(64)	Subspecies portion of the Latin name, may be a subspecies or variety.
Mnemonic	char(10)	Code used in the field for designating the subspecies.
Authority	varchar(128)	Taxonomic authority for the classification of the subspecies.
InfraSpecificLevel	char(32)	Indicates whether the name refers to a subspecies, a variety, a subvariety, a form, etc.

Definitions : Tree table. Lists all the tags and species identifications of every tree ever censused in any of the plots

Column	Type	Description

TreeID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify a Tree.
Tag	char(10)	Tag number on the tree in the field, should be unique within each plot.
SpeciesID	int(10) unsigned	Foreign Key to Species table, indicating the species identification of the tree
SubSpeciesID	int(10) unsigned	Foreign Key to SubSpecies table, indicating the subspecies if there is one

Definitions : TreeAttributes table. Records any attribute linked to a tree in the Tree table, using codes from the TSMAttributes table.

Column	Type	Description

CensusID	int(10) unsigned	Foreign Key to Census table.
TreeID	int(10) unsigned	Foreign Key to Tree table.
TSMID	int(10) unsigned	Foreign Key to TSMAttributes table.
TAttID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify a particular combination of a tree with a TSM code.

Definitions : TreeTaxChange table. The taxonomic change codes, i.e. why a taxonomic name changed

Column	Type	Description

ChangeCodeID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify a taxonomic change code.
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’.

Definitions : TSMAttributes table. Codes or attributes used in the TreeAttributes, DBHAttributes, MeasurementAttributes, and RemeasAttribs tables.

Column	Type	Description

TSMID	int(10) unsigned	Primary key, an integer automatically generated to uniquely identify a Tree, Stem or Measurement Code.
TSMCode	char(10)	Code describing or explaining tree, stem or measurements. Eg "Dead", "Lost", "Leaning" etc.
Description	varchar(128)	Free text description of the code above..

Definitions : ViewTaxonomy table. All the current taxonomic species and subspecies names, with their corresponding genus and family, identification level, authority, any synonyms and obsolete species names used, and what herbariums their specimens are found in.

Column	Type	Description

SpeciesID	int(11)	Foreign Key to Species table
SubspeciesID	int(11)	Foreign Key to SubSpecies table, indicating the subspecies if there is one
Family	char(32)	Taxonomic family name (from the Angiosperm Phylogeny Group - APG - system).
Genus	char(32)	Genus the species belongs to, according to the APG system.
Mnemonic	char(10)	Code used in the field for designating the species, usually 6 letters (4 for the genus and 2 for the species).
SpeciesName	char(64)	Species part of Latin name; (or may be a morphospecies name).
SubspeciesName	char(64)	Subspecies portion of the Latin name, may be a subspecies or variety.
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.
Authority	char(124)	Taxonomic authority for the classification of the species.
ListOfOldNames	varchar(255)	List of old names or synonyms used previously, separated by commas
NumberOfHerbarium	int(11)	Number of herbaria where specimens of this species is found
ListOfHerbarium	varchar(255)	List of the names of herbaria where specimens of this species are found
Description	varchar(128)	A free text description of the species, as relevant for the plot (especially, who identified and how).

Definitions : ViewFullTable table. Merges all the relevant variables from the corresponding tables in the database back into a flat file. Each record represents one measurement and/or attribute of one stem of one tree of one plot from one census. Because buttresses can grow, for trees with buttresses, a stem may be measured at more than one height. So some stems may have more than one measurement in a census, but at different hom

Column	Type	Description



DBHID	int(11)	Foreign Key to DBH table.
PlotID	int(11)	Foreign Key to Site table.
Plot	varchar(35)	Descriptive name of the site also referred to as the plot.
Family	char(32)	Taxonomic family name (from the Angiosperm Phylogeny Group - APG - system).
GenusSpecies	char(64)	Scientific Latin name that includes the genus and species.
Genus	char(32)	Genus of the plant, according to the APG system.
SpeciesName	char(64)	Species part of Latin name, may be a morphospecies name.
SubSpeciesName	char(64)	Subspecies portion of the Latin name, may be a subspecies or variety.
SpeciesID	int(10) unsigned	Foreign Key to Species table.
Mnemonic	char(10)	Code used in the field for designating the species, usually 6 letters (4 for the genus and 2 for the species).
QuadratID	int(11)	Foreign Key to Quadrat table.
QuadratName	varchar(12)	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.
QX	float	Distance from the lower left corner of the quadrat in meters on the X axis.
QY	float	Distance from the lower left corner of the quadrat in meters on the Y axis.
PX	float	Distance from the plot origin (lower left corner) in meters on the X axis.
PY	float	Distance from the plot origin (lower left corner) in meters on the Y axis.
TreeID	int(11)	Foreign Key to Tree table.
Tag	char(10)	Tag number on the tree in the field, should be unique within each plot.
StemID	int(11)	Foreign Key to Stem table.
StemNumber	int(11)	Column used to carry stemid from previous database version.

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.
PrimaryStem	char(20)	A character description of the stem, whether it is the primary or a secondary stem, or a branch, etc.
CensusID	int(11)	Foreign Key to Census table.
PlotCensusNumber	int(11)	Census number, an integer, 1=first census, etc.
DBH	float	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.
HOM	float	Height (in meters) on the stem at which the diameter was measured, usually at 1.3 meters.
ExactDate	date	Date on which the measurement was taken (format is yyyy-mm-dd).
ListOfTSM	varchar(256)	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.
Status	varchar(15)	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).

Figure 1: CTFS Data Model schema, including all tables and relationships.