Specifications for tables with data about taxa
Home
Manual
Fields are shown under each table.
Comments about future changes or unresolved design issues are shown in contrasting color.
Version specific features are noted by highlighted text.
tblTaxa
, taxon names at all ranks, including synonyms and other non valid names
TaxonNameID, integer, 4 bytes, unique identifier for the taxon name. This number can be used to enter the database at this name.
The apex taxon, which defines the primary scope of the database always has TaxonNameID = 1.
TaxonNameStr, text, 300 characters maximum. A series of TaxonNameID in text form and separated by "-".
If a taxon is at genus rank and is not a synonym, the text of TaxonNameID is followed by "g". This makes forming binomial names more efficient.
If a taxon is at subgenus rank and is not a synonym, the text of TaxonNameID is followed by "s".
The apex taxon always has TaxonNameStr = "1".
For all taxa subordinate to the apex taxon, TaxonNameStr begins with "1-" (or "1g-" if the apex taxon is a genus rank).
For all taxa outside the primary scope, TaxonNameStr begins with "0-".
For genera outside the primary scope (needed for tblTaxa.OriginalGenusID), TaxonNameStr begins with "0-0-".
For context taxa outside the primary scope, TaxonNameStr begins with "0-1-". Such taxa provide some context around the apex taxon.
For taxa previously within the primary scope but now outside, TaxonNameStr begins with "0-2-". This enables finding such taxa in searches so that users can be told about the change in status.
For reserved values of TaxonNameID, TaxonNameStr begins with "0-3-". These values are held in reserve for when private species files are merged.
For family-group taxa outside the primary scope (needed for tblTypeSpecies.FirstFamGrpNameID), TaxonNameStr begins with "0-4-".
RankID, integer, 1 byte, = tblRanks.RankID, number for rank in hierarchy
Name, text, 30 bytes maximum, name of taxon
Parens, boolean, enclose author name in parens?
AboveID, integer, 4 bytes, = tblTaxa.TaxonNameID of the next higher taxon in the hierarchy. If this taxon is a synonym or other not valid name, then this is the TaxonNameID for the valid taxon. AboveID cannot be the TaxonNameID for a genus having subgenera or a species having subspecies: In those cases, use the TaxonNameID of the subgenus or subspecies.
LikeNameID, integer 4 bytes, = 0 unless this is a special case. Special cases are
Valid or temporary taxa
Genus, then = tblTaxa.TaxonNameID of subgenus by same name
Subgenus, then = tblTaxa.TaxonNameID of genus by same name
Species, then = tblTaxa.TaxonNameID of subspecies by same name
Subspecies, then = tblTaxa.TaxonNameID of species by same name
Extinct, integer, 1 byte, 0 = extinct, 1 = extant; if NameStatus > 2, then Extinct is NULL.)
Could be changed to allow additional values such as threatened, endangered and presumed extirpated.
RefID, integer, 4 bytes, = tblRefs.RefID for the original description of the taxon. CAUTION: For family-group taxa, this is the first use of the name in its current form. The author and date for priority are based on the first use of any name based on the same type genus. To locate this information, trace via tblTypeGenera.GenusNameID to tblTypeSpecies.FirstFamGrpNameID to tblTaxa.RefID of that first name. For taxa above superfamily rank, RefID is always 0. Taxa at a rank above species and below subgenus also have RefID = 0.
NecAuthor, text, 200 bytes maximum, author who used name correctly sometimes with additional information
DataFlags, integer, 2 bytes, with bitwise flags for type of data present
1 = image
2 = sound
4 = link to other website
8 = specimen data
0x10 (16) = scrutiny
0x20 (32) = key group
0x40 (64) = supplementary information
0x80 (128) = common name
0x100 (256) = related taxon
0x200 (512) = automatic link to other website
0x400 (1024) = ecological relationship
0x800 (2048) = valid taxon name string (used exclusively in spTestAboveIDvsTaxonNameStr)
0x1000 (4096) = has specimen(s) with latitude/longitude data useable for maps, applies to taxa at rank tribe or lower
AccessCode, integer, 2 bytes
NameStatus, integer, 1 byte
0 = valid
2 = temporary name for use until a valid name is provided (subordinate names limited to temporary names and synonyms)
4 = nomen nudum, must be at lower rank than parent (subordinate names limited to nomina nuda)
5 = nomen dubium, must be at lower rank than parent (subordinate names limited to nomina nuda and dubia)
7 = synonym (subordinate taxa not allowed)
StatusFlags, integer, 4 bytes, interpreted bitwise
1 = informal name, compatible only with tblTaxa.NameStatus = 2 or 7
2 = subsequent misspelling, compatible only with tblTaxa.NameStatus = 7
4 = unjustified emendation, compatible only with tblTaxa.NameStatus = 7
8 = nomen nudum, compatible only with tblTaxa.NameStatus = 2 or 4 when rank is less than parent rank or = 7 when rank is same as parent
0x10 (16) = nomen dubium, compatible only with tblTaxa.NameStatus = 5 when rank is less than parent rank or = 7 when rank is same as parent
0x20 (32) = incertae sedis, compatible only with tblTaxa.NameStatus = 0 or 2
0x40 (64) = required emendation, compatible only with tblTaxa.NameStatus = 0, 2 or 7
0x80 (128) = nomen protectum, compatible only with tblTaxa.NameStatus = 0 or 7
0x100 (256) = suppressed, compatible only with tblTaxa.NameStatus = 7
0x200 (512) = misapplied, compatible only with tblTaxa.NameStatus = 2, 4, 5 or 7
0x400 (1024) = preoccupied (genus-group names), compatible only with tblTaxa.NameStatus = 2 or 7
0x800 (2048) = primary homonym (species-group names), compatible only with tblTaxa.NameStatus = 2 or 7
0x1000 (4096) = secondary homonym (species-group names), compatible only with tblTaxa.NameStatus = 2 or 7
0x2000 (8192) = nomen oblitum, compatible only with tblTaxa.NameStatus = 7
0x4000 (16384) = unnecessary replacement name, compatible only with tblTaxa.NameStatus = 7
0x8000 (32768) = incorrect original spelling, compatible only with tblTaxa.Namestatus = 2, 4, 5 or 7
0x10000 (65536) = other, explained by tblTaxa.Comment, compatible only with tblTaxa.NameStatus = 2 or 7
0x20000 (131072) = unavailable, other reason, compatible only with tblTaxa.NameStatus = 2 or 7
0x40000 (262144) = junior synonym, compatible only with tblTaxa.NameStatus = 7
0x80000 (524288) = nomen novum, compatible only with tblTaxa.Namestatus = 0, 2, or 7
0x100000 (1048576) = original name, used only for family-group names, compatible only with tblTaxa.NameStatus = 7
0x200000 (2097152) = subsequent name, used only for family-group names, compatible with any tblTaxa.NameStatus value
0x400000 (4194304) = junior homonym for species-group name not specified as primary or secondary, compatible only with tblTaxa.NameStatus = 2 or 7
0x800000 (8388608) = lapsus calami, compatible only with tblTaxa.NameStatus = 2, 4, 5 or 7
0x1000000 (16777216) = corrected lapsus calami, compatible only with tblTaxa.NameStatus = 0, 2 or 7
0x2000000 (33554432) = nomen nudum made available, compatible only with tblTaxa.NameStatus = 0, 2 or 7
Notes about StatusFlags
Type data for taxa is available based on rank and name status:
No type data for ranks above superfamily.
No type data for ranks of subgenus through superfamily with name status of misapplied (0x200).
No type data for ranks above species and below subgenus.
No type data for ranks of species level and below for emendation (unjustified and required), misspelling, nomen nudum, unnecessary replacement, nomen novum, and misapplied (0x8424E) OR NameStatus = 4 (nomen nudum).
Age qualification is required for the following NameStatus values:
Junior synonym must not be older than the valid name.
Unjustified emendation, misspelling, and unnecessary replacement must not be older than the correct name.
Incorrectly formed name must not be younger than the justified emendation name.
Nomen novum must not be younger than the homonym replaced.
Nomen oblitum must not be younger than the valid name.
Taxa may have more than one status flag set. However, there are restrictions for certain status values. Unless stated below, more than one status flag may be set:
Emendation is not compatible with misspelling, unnecessary replacement, nomen nudum, incorrect original spelling, and misapplied.
Misspelling is not compatible with with emendation, unnecessary replacement, nomen nudum, incorrect original spelling, junior synonym, and misapplied.
Unnecessary replacement is not compatible with emendation, misspelling, junior synonym, and misapplied.
Nomen nudum is not compatible with emendation, misspelling, junior synonym, and misapplied.
Incorrect original spelling is not compatible with misspelling, unnecessary replacement, nomen nudum, and misapplied.
Junior synonym is not compatible with misspelling, unnecessary replacement, nomen nudum, and misapplied.
Misapplied is not compatible with emendation, misspelling, unnecessary replacement, nomen nudum, incorrect original spelling, junior synonym, and misapplied.
Note that, theoretically, the following status values may be used in combination with any other status value, but in some cases this may be illogical: Junior homonym, suppressed by ruling, unavailable (other reason), incertae sedis, nomen dubium, nomen oblitum, nomen novum, nomen protectum, unnamed.
OriginalGenusID, integer, 4 bytes, = tblTaxa.TaxonNameID, used only for species-group taxa other than a species that has subspecies, TaxonNameID for the genus in which the species was originally described, = 0 if unknown, -1 identifies cases where the previously recorded original genus did not exist when the species was described. If a genus and subgenus have the same name, use the TaxonNameID of the subgenus.
Distribution, text, 400 characters maximum.
Ecology, text, 400 characters maximum
Comment, text, 400 characters maximum, explanation relating to the status of the name
ExpertID, integer, 2 bytes, = tblExperts.ExpertID for the person recorded as expert for this taxon
ExpertReason, integer, 1 byte, reason expert assigned, 0 = no expert assigned, 1 = by default, 2 = by hierarchy only, 3 = by hierarchy and geography, 11 = cascaded by default, 12 = cascaded by hierarchy only, 13 = cascaded by hierarchy and geography
When a new taxon is created or a taxon becomes valid, the ExpertID is copied from the new AboveID. If the ExpertID and ExpertReason of the AboveID is 0, the new/now valid taxon values are also 0. If the ExpertReason of the AboveID is less than 10 (but greater than 0), add 10 to the ExpertReason of the AboveID to derive the ExpertReason value for the new/now valid taxon. Use the same ExpertReason value for the new/now valid taxon if the ExpertReason of the AboveID is greater than 9.
LastUpdate, smalldatetime, date and time row last updated
ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
CurrentConceptRefID, integer, 4 bytes, = tblRefs.RefID, identifies the reference that initiated the currently accepted concept. If there is no currently accepted concept for the name, then this is the reference that terminated the concept.
LifeZone, integer, 1 byte,
added in version 4.0
, bit values:
1 = Marine
2 = Brackish
4 = Freshwater
8 = Terrestrial
Distribution1Flags, integer, 4 bytes. The nine low order bits are used to indicate presence in the nine "continents" as defined in tblGeoLevel1. The next 2 bits are used for status of the data. When divided by the value of the lower status bit, the resulting values have the following meaning.
Replaced in version 3.5 by tblDistribution.
0 = no data
1 = areas recorded are known to be incomplete
2 = areas recorded may be incomplete
3 = areas recorded include all of the known distribution
Disttribution2WFlags, integer, 4 bytes. The 20 low order bits are used to indicate presence in the Pacific and american (western) regions as defined in tblGeoLevel2 and the existance of a row in tblLevel3Dist. The next 6 bits (2 bits per continent) are used for status of the data.
Developed in version 3.4, replaced in version 3.5 by tblDistribution.
Distribution2CFlags, integer, 4 bytes. The 17 low order bits are used to indicate presence in Europe, Africa and Antarctic (central) regions as defined in tblGeoLevel2 and the existance of a row in tblLevel3Dist. The next 6 bits (2 bits per continent) are used for status of the data.
Developed in version 3.4, replaced in version 3.5 by tblDistribution.
Distribution2EFlags, integer, 4 bytes. The 15 low order bits are used to indicate presence in Asia and Australasia (eastern) regions as defined in tblGeoLevel2 and the existance of a row in tblLevel3Dist. The next 6 bits (2 bits per continent) are used for status of the data.
Developed in version 3.4, replaced in version 3.5 by tblDistribution.
tblRelatedTaxa
, linking table to record nomenclatural relationships between taxonomic names
OlderNameID, integer, 4 bytes, = tblTaxa.TaxonNameID, TaxonNameID for the older name in the relationship
YoungerNameID, integer, 4 bytes, = tblTaxa.TaxonNameID, TaxonNameID for the younger name in the relationship
Relationship, integer, 1 byte
1 = junior homonym/nomen novum
2 = nomen oblitum/nomen protectum
3 = incorrect original spelling/justified emendation
4 = correct spelling/subsequent misspelling
5 = correct name/unjustified emendation
6 = correct name/unnecessary replacement
7 = correct use of name/misapplied name
8 = lapsus calami/corrected lapsus
9 = nomen nudum/nomen nudum made available
LastUpdate, smalldatetime, date and time row last updated
ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
tblTaxonHistory
, history of concepts and nomenclature for protonyms
added in version 4.0
TaxonHistoryID, integer, 4 bytes, primary key, identifier for taxon name and concept.
TaxonNameID, integer, 4 bytes, = tblTaxon.TaxonNameID, identifier for the monomial name (protonym) associated with the history.
SeqNum, integer, 2 bytes, sequence number within this TaxonNameID, 1-based values without gaps.
NomenclatorID, integer, 4 bytes, = tblNomenclator.NomenclatorID, identifier for binomial name associated with the history.
CiteRefID, integer, 4 bytes, = tblRefs.RefID, identifier for the reference that defined the nomenclatural or concept change. May = 0 if change comes from other source explained in note or if the reference has not been identified.
NewNameStatus, integer, 1 byte, status of name when changed
0 = unspecified
1 = unchanged
2 = new taxon name
3 = made a synonym
4 = made valid or temporary
5 = new combination
6 = name deleted with no related name
7 = name deleted because of duplicate protonym, related name can be found in tblRelatedHistory
8 = genus or species lowered to nominotypical sub
9 = nominotypical sub raised to genus or species
10 = name replaced by nominotypical sub
11 = new nominotypical sub
ConceptChange, integer, 1 byte, values in effect on completion of the change, permitted values:
0 = unspecified
1 = unchanged
2 = new
3 = restored
4 = expanded
5 = shrunk
6 = modified
7 = terminated
8 = transferred
OtherHistoryID, integer, 4 bytes, = tblTaxonHistory.TaxonHistoryID, identifier for the other history ID involved in a relationship change specified in RelatedHistoryID
RelatedHistoryID, integer, 2 bytes, = tblRelatedHistory.RelatedHistoryID, identifier for the type of relationship to OtherHistoryID
UserConfirmed, bit, 1 = user has confirmed data in this row or generated from user supplied data in tblCites
UnreliableAssumption, bit, 1 = program used an unreliable assumption to create row
ArbitraryRow, bit, 1 = arbitrary row generated by program to resolve conflict, user resolution needed
Note, text, 300 bytes maximum. When Status is set = 2, values for NomenclatorID and CiteRefID are set = 0, but those items are expanded to text in this column. When Status is set = 3, the domain name of the split out species file is placed in this column.
LastUpdate, smalldatetime, date and time row last changed
ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
Status, integer, 1 byte, status of the row
0 = active
1 = dormant, used for deleted nominotypiical subs, which might be re-instated at a later time
2 = artifact, used for rows erroneously added. The row cannot be deleted once TaxonHistoryID has been exposed in an LSID or other GUID for a taxon concept.
3 = moved to other species file identified in Note
4 = moved out of scope
tblRelatedHistory
, definition of values used in tblTaxonHistory.RelatedHistoryID, shared across files
added in version 4.0
RelatedHistoryID, integer, 2 bytes, identifier for relationship
Name, text, 40 bytes maximum
Description, text, 200 bytes maximum, description
tblRanks
, ranks in the taxonomic hierarchy, shared across files. All programming should be done in a manner that does not cause problems if more levels are inserted into the hierarchy.
RankID, integer 1 byte. Gaps between numbers allow insertion of new levels.
RankName, text, 17 bytes maximum
DefaultLevel, integer, 1 byte used to select a commonly used rank as the default when user is adding a new taxon
tblSupplTaxonInfo
, supplementary information about taxa
SupplInfoID, integer, 2 bytes, identifier for the information
TaxonNameID, integer, 4 bytes, = tblTaxa.TaxonNameID
Title, text, 60 bytes maximum, title for this supplementary information item
SourceID, integer, 2 bytes, = tblSources.SourceID
Content, text, 8000 bytes maximum
AccessCode, integer, 2 bytes
LastUpdate, smalldatetime, date and time of last update; TaxonNameID and LastUpdate comprise the composite primary key
ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
tblExperts
, information about experts familiar with taxa
ExpertID, integer, 2 bytes, identifier for expert
ShortName, text, 30 bytes maximum
FullName, text, 100 bytes maximum
Address, text, 400 bytes maximum
Email, text, 100 bytes maximum
SeqNum, integer, 2 bytes, number used to place experts in alphabetical order. A value of zero indicates that the person is not currently acting as an expert.
LastUpdate, smalldatetime, date and time of last update
ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
tblSources
, sources of images, sound recordings and other data
SourceID, integer, 2 bytes, identifier for the source
RefID, integer, 2 bytes, = tblRefs.RefID
PersonID, integer, 2 bytes, = tblPeople.PersonID
Not yet in use.
LinkID, integer, 2 bytes, = tblLinks.LinkID
Not yet in use.
Date, smalldatetime
Not yet in use.
DataFlags, integer, 2 bytes, bitwise flags for type of data present: 1 = image, 2 = sound, 4 = specimen, 8 = key block, 16 = SupplTaxonInfo, 32 = ecological relationship
Description, text, 600 bytes maximum
LastUpdate, smalldatetime, date and time of last update
ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
tblCommonNames
, common names for taxa
TaxonNameID, integer, 4 bytes, = tblTaxa.TaxonNameID
SeqNum, integer, 2 bytes, used with TaxonNameID to form the primary key
LanguageID, integer, 2 bytes, = tblLanguages.LanguageID
Name, text, 50 bytes maximum, accepts Unicode characters
LastUpdate, smalldatetime, date and time of last update
ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
tblLanguages
, the language used for common names
LanguageID, integer, 2 bytes, identifier for the language
Name, text, 30 bytes maximum
LngAbbreviation, text, 5 bytes maximum
LngCodePage, text, 5 bytes maximum
tblTypeGenera
, citation to genus that is the type for a family-group name and to oldest use of a name based on the same genus, used only for valid family group taxa
FamilyNameID, integer, 4 bytes, = tblTaxa.TaxonNameID of a family group name
GenusNameID, integer, 4 bytes, = tblTaxa.TaxonNameID of the genus or subgenus that is type for the family group name. If a genus and subgenus have the same name, use the TaxonNameID of the subgenus.
LastUpdate, smalldatetime, date and time of last update
ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
tblTypeSpecies
, citation to species that is the type for a genus-group name
GenusNameID, integer, 4 bytes, = tblTaxa.TaxonNameID for the genus. If a genus and subgenus have the same name, use the ID of the subgenus.
SpeciesNameID, integer, 4 bytes, = tblTaxa.TaxonNameID for the type species of the genus. If a species and subspecies have the same name, use the ID of the subspecies.
Reason, integer, 1 byte
0 = unknown
1 = original monotypy
2 = original designation
3 = subsequent designation
4 = monotypy and original designation
5 = subsequent monotypy
6 = absolute tautonomy
7 = Linnaean tautonomy
8 = ruling by Commission
9 = inherited from replaced name
AuthorityRefID, integer, 4 bytes, = tblRefs.RefID, used only when Reason = 3 (otherwise AuthorityRefID = 0). Citation can be found by tblCites.TaxonNameID = tblTypeSpecies.GenusNameID and tblCites.RefID = tblTypeSpecies.AuthorityRefID.
FirstFamGrpNameID, integer, 4 bytes, = tblTaxa.TaxonNameID for the first family-group name based on this genus
LastUpdate, smalldatetime, date and time of last update
ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
tblTypeSpeciesReasons
, reasons a type species has been validly designated, shared across files
Reason, text, 50 characters maximum
ListNumber, integer, 1 byte, sequence number for display in dropdownlists
tblTypeInfo
, effects of citations on types, shared across files
TypeInfoID, integer, 1 byte, identifier for type of information
SpeciesSeqNum, integer, 1 byte, sequence of choices for species-group ranks
GenusSeqNum, integer, 1 byte, sequence of choices for genus-group ranks
FamilySeqNum, integer, 2 byte, sequence of choices for family-group ranks
Description, text, 40 bytes maximum, description of the effect
tblDistribution
, recorded occurences in geographic areas,
added in version 3.5 to replace tblTaxa.Distribution1Flags, Distribution2WFlags, Distribution2CFlags and Distribution2EFlags
TaxonNameID, integer, 4 bytes, = tblTaxa.TaxonNameID
Level1ID, character, 1 byte, = tblGeoLevel2.Level1ID
Level2ID, character, 1 byte, = tblGeoLevel2.Level2ID
AreaFlags, integer, 4 bytes, interpreted bitwise to indicate presence in geographic areas as defined in tblGeoLevel1, tblGeoLevel2 and tblGeoLevel3. If no flag is on, the row is not recorded.
ShadeFlags, integer, 2 bytes, interpreted bitwise to indicate map shading needed, applies only at levels 1 and 2 where specifications for level 2 or 3 might make the level 1 or 2 shading not wanted.
Status, integer, 1 byte
0 = no data (does not occur, row is absent)
1 = known incomplete
2 = may be incomplete
3 = includes known distribution
LastUpdate, smalldatetime, date and time of last update
ModifiedBy, integer, 2 bytes, UserID of the signon that made the change