Home     Manual

This page is provided to facilitate export of data from species files for incorporation into the Catalogue of Life. The documentation will be updated from time to time as design changes occur. The names of all these tables begin with "tmp" to indicate their temporary nature as compared with the regular tables that begin with "tbl".

• tmpCoLAcceptedNames
  • AcceptedNameID, integer, 4 bytes, identity, primary key for this table
  • TaxonNameID, integer, 4 bytes, = tblTaxa.TaxonNameID, primary key for tblTaxa in the species file
  • OrderName, text, 30 character maximum, name of the order
  • SuperFamilyName, text, 30 character maximum, name of the superfamily
  • Family, text, 30 character maximum, name of the family
  • Genus, text, 30 character maximum, name of the genus
  • Subgenus, text, 30 character maximum, name of the subgenus
  • Species, text, 30 character maximum, name of the species
  • AuthorityString, text (nvarchar), 500 character maximum, author and year for the taxon. This matches AuthorString for CoL, but SFS uses AuthorString for author names without year.
  • InfraSpecies, text, 30 character maximum, infraspecific name
  • InfraSpMarker, text, 10 character maximum
  • InfraSpecificAuthorString, text (nvarchar), 500 character maximum, author and year for the taxon
  • ParentSpeciesID, integer, 4 bytes, = tmpCoLAcceptedNames.AcceptedNameID
  • AdditionalData, text, 400 character maximum
  • Specialist, text, 50 character maximum, name of the expert taxonomist responsible
  • GSDNameStatus, text, 1000 character maximum, nomenclatural information about the taxon
  • Sp2000NameStatus, text, 30 character maximum, restricted vocabulary specified by CoL
  • LifeZone, 4 characters; M for marine, B for Brackish, F for freshwater and T for Terrestrial. The letters when present are always in that sequence. Missing letters are replaced by blanks at the end.
  • SpeciesURL, text, 100 character maximum, URL to find the taxon in the species file
  • GSDNameGUI, text, 100 character maximum, for example "urn:lsid:blattodea.speciesfile.org:taxonName:12345"
• tmpCoLDistribution
  • AcceptedNameID, integer, 4 bytes, = tmpCoLAcceptedNames.AcceptedNameID
  • GeoCode, text, 6 characters, TDWG level 1 and level 2 IDs (2 character total) or else TDWG level 3 and level 4 IDs (6 character total). However note that in some cases SFS has added level 4 IDs not recognized by TDWG. These can be recognized as digits in contrast to TDWG level 4 IDs that are alphabetic.
• tmpCoLSynonyms
  • SynonymNameID, integer, 4 bytes, identity, primary key for this table
  • AcceptedNameID, integer, 4 bytes, = tmpCoLAcceptedNames.AcceptedNameID
  • TaxonNameID, integer, 4 bytes, = tblTaxa.TaxonNameID, primary key in tblTaxa
  • OrderName, text, 30 character maximum, name of the order
  • SuperFamilyName, text, 30 character maximum, name of the superfamily
  • Family, text, 30 character maximum, name of the family
  • Genus, text, 30 character maximum, name of the genus
  • Subgenus, text, 30 character maximum, name of the subgenus
  • Species, text, 30 character maximum, name of the species
  • AuthorityString, text (nvarchar), 500 character maximum, author and year for the taxon. This matches AuthorString for CoL, but SFS uses AuthorString for author names without year.
  • InfraSpecies, text, 30 character maximum, infraspecific name
  • InfraSpMarker, text, 10 character maximum
  • InfraSpecificAuthorString, text (nvarchar), 500 character maximum, author and year for the name
  • AdditionalData, text, 400 character maximum
  • GSDNameStatus, text, 1000 character maximum, nomenclatural information about the taxon
  • Sp2000NameStatus, text, 30 character maximum, restricted vocabulary specified by CoL
  • SpeciesURL, text, 100 character maximum, URL to find the name in the species file
  • GSDNameGUI, text, 100 character maximum, for example "urn:lsid:blattodea.speciesfile.org:taxonName:12345"
• tmpCoLCommonNames
  • AcceptedNameID, integer, 4 bytes, = tmpCoLAcceptedNames.AcceptedNameID
  • CommonName, text (nvarchar), 100 character maximum
  • Language, text, 35 character maximum
• tmpCoLReferences
  • ReferenceID integer, 4 bytes, identity, primary key
  • SFRefID, integer, 4 bytes, = tblRefs.RefID primary for tblRefs in the species file
  • Authors, text (nvarchar), 500 character maximum, author names
  • Year, text, 22 character maximum, may be a range of years separated by a hyphen, may be followed by another year or range of years enclosed in square brackets to indicate a stated year that varies from the actual year
  • Title, text (nvarchar), 600 character maximum, title of the reference
  • Details, text, 700 character maximum, additional information
• tmpCoLAcceptedRef, linking table for references and accepted names
  • AcceptedID, integer, 4 bytes, = tmpCoLAcceptedNames.AcceptedNameID
  • ReferenceID, integer, 4 bytes, = tmpColReferences.ReferenceID
• tmpCoLSynonymRef, linking table for references and synonyms
  • SynonymID, integer, 4 bytes, = tmpCoLSynonyms.SynonymNameID
  • ReferenceID, integer, 4 bytes, = tmpColReferences.ReferenceID
• tmpCoLPending, a utility table used during generation of the other tables, not sent on to CoL
  • TaxonNameID, integer, 4 bytes, = tblTaxa.TaxonNameID
  • AcceptedID, integer, 4 bytes, = tmpCoLAcceptedNames.AcceptedNameID
  • TaxonNameStr, text, 300 character maximum, = tblTaxa.TaxonNameStr
  • Name, text (nvarchar), 30 character maximum, = tblTaxa.Name
  • RankID, integer, 4 bytes, = tblTaxa.RankID
  • Parens, integer, 4 bytes, = tblTaxa.Parens
  • AboveID, integer, 4 bytes, = tblTaxa.AboveID
  • LikeNameID, integer, 4 bytes, = tblTaxa.LikeNameID
  • NameStatus, integer, 4 bytes, = tblTaxa.NameStatus
  • StatusFlags, integer, 4 bytes, = tblTaxa.StatusFlags
  • ExpertID, integer, 4 bytes, = tblTaxa.ExpertID
  • OriginalGenusID, integer, 4 bytes, = tblTaxa.OriginalGenusID
  • RefID, integer, 4 bytes, = tblTaxa.RefID
  • Distribution, text, 400 character maximum, = tblTaxa.Distribution
  • LifeZone, integer, 4 bytes, = tblTaxa.LifeZone
  • PendingID, integer, identity, primary key