|
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.
- tblAddrCountries, a list of country names and codes used by the address book.
- CountryName, text, 50 characters maximum, name of the country.
- CountryCode, text, 2 characters, the country code.
- tblAddrStates, a list of country codes and state names used
by the address book.
- CountryCode, text, 2 characters, the country code.
- StateName, text, 50 characters maximum, name of the state. For the US, the state name
is followed by the 2 letter state postal code in parenthesis.
- tblCites, citations to a reference and associated with a specific taxon
- TaxonNameID, integer, 4 bytes, = tblTaxa.TaxonNameID for the associated taxon
- SeqNum, integer, 2 bytes, a sequence number for the citations for an individual
taxon
- RefID, integer, 4 bytes, = tblRefs.RefID
- CitePages, text, 50 bytes maximum, list of pages, plates, etc. cited for this taxon
- Note, text, 400 bytes maximum, any additional information about what is included
in the citation
- NomenclatorID, integer, 4 bytes, = tblNomenclator.NomenclatorID, applies to taxa at rank genus
or lower
- LastUpdate, smalldatetime, date and time of last update
- ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
- ContentFlags, integer, 4 bytes, kinds of content as documented for
tblCiteContentFlags
- DataStatus, integer, 1 byte, status of recording content of the citation, bit values:
- 1 = concept flags recorded, may need review
- 2 = concept flags recorded, considered done (When this bit is on, the bit in the previous line is also on.)
- 4 = nomen flags recorded, may need review
- 8 = nomen flags recorded, considered done (When this bit is on, the bit in the previous line is also on.)
- 16 = info flags recorded, may need review
- 32 = info flags recorded, considered done (When this bit is on, the bit in the previous line is also on.)
- tblCiteTypes, relationship of citation to the status
of a name. Deleted in version 3.3.
- CiteTypeID, integer, 1 byte, identifier for the citation type
- Type, text, 16 bytes maximum, name for the citation type
- 0 = not specified
- 1 = validity
- 2 = acceptance
- 3 = synonymy
- 4 = misapplication
- 5 = correction
- other values in the future?
- tblCiteContentFlags, shared across files, flags that identify
information contained in citations, completed in version 3.3
- FlagValue, integer, 4 bytes, value of the bit for the flag
- Dealing with taxon concepts
- 1 = creation of a new concept
- 2 = enlargement of concept
- 4 = shrinkage of concept
- 8 = changed or clarified concept
- &H10 = termination of concept
- Dealing with nomenclature
- &H100 = original description of a name
- &H200 = name usage not available under rules of nomenclature
- &H400 = new combination
- &H800 = new replacement name
- &H1000 = type designation or clarification
- &H2000 = made a synonym
- &H4000 = made valid
- &H8000 = spelling change
- &H10000 = initial misapplication
- &H20000 = other status change
- Dealing with other kinds of information
- &H100000 = image or description
- &H200000 = evolutionary or hierachical relationships
- &H400000 = ecological data
- &H800000 = specimen or distribution data
- &H1000000 = key
- &H2000000 = life history
- &H4000000 = behavior
- &H8000000 = economic matters including control
- Name, text, 30 character maximum, short name for the change
- Explanation, text, 200 character maximum, explanation for display for users
- tblRefs, references
- RefID, integer, 4 bytes, identifier for reference
- ContainingRefID, integer, 4 bytes, when the reference is included within a different
reference, it is = tblReference.ID for the different reference. When ContainingRefID
> 0, ActualYear and StatedYear must = values for row where RefID = ContainingRefID,
PubID must = 0, Volume must = "". Title and RefPages may have values separate
from the row where RefID = ContainingRefID.
- Title, text, 600 bytes maximum, title of reference
- PubID, integer, 2 bytes, = tblPubs.PubID for publication containing the reference
- Series, text, 25 bytes maximum, series number or other identification for a series
- Volume, text, 30 bytes maximum, volume of the publication (older data often includes issue number in parentheses after the volume)
- Issue, 25 bytes maximum, issue number or other identification within a volume.
- RefPages, text, 70 bytes maximum, pages in the reference plus any plates, etc. that
lack page numbers
- ActualYear, text, 10 bytes maximum
- StatedYear, text, 10 bytes maximum, null string except where different from ActualYear
- AccessCode, integer, 2 bytes
- Flags, integer, 2 bytes, internal use only: local copy, loan copy, 0 = no
copy of reference on site, 1 = have copy on site
- Note, text, 300 bytes maximum
- LinkID, smallint, 2 bytes, = tblLinks.LinkID, represents the location of an
electronic copy of the reference.
- LastUpdate, smalldatetime, date and time of last update
- ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
- CiteDataStatus, integer, 2 bytes, least status of recording citation type information among the citations to this reference.
- tblRefAuthors, linking table to match references with authors
(Every reference must have an entry in the linking table to make the views
work correctly. For references without and author, use person "Unknown", PersonID
= 0.)
- RefID, integer, 2 bytes, = tblRefs.RefID
- PersonID, integer, 2 bytes, = tblPeople.PersonID
- SeqNum, integer, 2 bytes, position in sequence of names for this author
- AuthorCount, integer, 2 bytes, number of co-authors for this reference
- LastUpdate, smalldatetime, date and time of last update
- ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
- tblPeople, names of people
- PersonID, integer, 2 bytes, identifier for person
- PrefID, integer, 2 bytes, if PrefID > 0, then this is a duplicate or misspelled
name and PrefID = PersonID for preferred name
- PersonRegID, integer, 4 bytes, = tblPeopleReg.PersonRegID
- FamilyName, text, 75 bytes maximum, last name(s)
- GivenNames, text, 75 bytes maximum, given name(s)
- GivenInitials, text, 20 bytes maximum, initials of given names(s)
- Suffix, text, 10 bytes maximum, last name suffix
- Role, integer, 2 bytes, contains data flags indicating (not necessarily in the
same order in the bitmap). These
bits have been reserved but are currently unused (6/22/06).
- bit value 1 if this person is an author
- bit value 2 if this person is a data source
- bit value 4 if this person is a collector
- bit value 8 if this person is an identifier
- bit value 16 if this person is an expert
- bit value 32 if this person is a character recorder
- bit value 64 if there is another person in the database with the same family name
- bit value 128 if there is addressbook information available for this person
- bit value 256 if this person is unused (deletable)
- bit value 512 if this person has alternate names
- bit value 1024 if this person is assigned a taxon as an expert
- bit value 2048 if this person provides a scrutiny
- Status, integer, 1 byte, status of reconciliation with tblPeopleReg, for future development
- 0 = no reconciliation attempted
- 10 = sent add request to SFSdb
- 20 = add request acknowledged by SFSdb, no LSID available yet
- 30 = sent modify request to SFSdb
- 40 = modify request acknowledged by SFSdb
- 70 = received request from SFSdb for editor decision
- 80 = editor decision received from editor and sent to SFSdb, return acknowledgement not yet received
- 100 = all questions resolved, LSID available from SFSdb
- LastUpdate, smalldatetime, date and time of last update
- ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
- tblPeopleReg, registry of data about authors shared across species files, for future development
- PersonRegID, integer, 4 bytes, identifier for registered person
- PrefRegID, integer, 4 bytes, if PrefID > 0, then this is a duplicate or misspelled name and PrefID = PersonRegID for the preferred name. Note that even if the following five columns have the same values, a separate row is needed when PrefID differs.
- FamilyName, text, 50 characters maximum
- GivenNames, text, 75 characters maximum
- GivenInitials, text, 20 characters maximum
- LSID, text, 100 bytes maximum, the life science ID as assigned by ZooBank
- Status, integer, 1 byte, status of reconciling tblPeopleReg with ZooBank
- 0 = no reconciliation attempted
- 5 = waiting for software development
- 10 = sent query to ZooBank
- 20 = received response from ZooBank with one or more possible matches
- 30 = initiated inquiries to one or more species files that use the author name
- 40 = sent query to ZooBank requesting that the name be added and an LSID be assigned
- 50 = reconciliation with ZooBank completed
- tblPendingPeopleFiles, a linking table used to track the reconciliation of data in tblPeopleReg
and the separate tblPeople in different species files, proposed future development
- PersonRegID, integer, 4 bytes, = tblPeopleReg.PersonRegID
- FileID, integer, 2 bytes, = tblFiles,FileID, this together with PersonRegID forms the primary key
- Status, integer, 1 byte, status of reconciling tblPeopleReg with the tblPeople in the species file
- 10 = received new name from species file and acknowledged receipt, reconciliation with ZooBank incomplete
- 20 = sent completion notice to species file, acknowledgement not yet received
- 40 = received modification request from species file and acknowledge receipt, reconciliation with ZooBank incomplete
- 50 = sent completion notice to species file, acknowledgement not yet received
- 70 = sent request for editor decision to species file, acknowledgement not yet received
- 80 = species file has acknowledged receipt of request
- There is no status for process completed. When this happens, the row is delete from the table.
- tblPubs, publications, including duplicate and misspelled names
- PubID, integer, 2 bytes, identifier for the publication
- PrefID, integer, 2 bytes, if PrefID > 0, then this is a duplicate or misspelled
name and PrefID = PubID for preferred name
- PubRegID, integer, 4 bytes = tblPubReg.PubRegID, under
development in versions 3.4 and 3.5
- Status, integer, 1 byte, status of reconciliation with tblPubReg,
under development in versions 3.4 and 3.5
- 0 = reconciliation not attempted, this value should not continue after conversion to version 3.5
- 1 = Waiting for a response from species file editor
- 2 = Waiting for a response from registry editor
- 3 = Species file editor has decided it does not belong in registry
- 4 = Fully reconciled with the registry
- PubType, 1 byte, 1 = journal, (2 not used), 3 = book or CD, 4 = unpublished
- ShortName, text, 500 characters maximum, abbreviated journal name or book title
or unpublished source
- FullName, text, 500 characters maximum, full journal name or book title or unpublished source
- Note, text, 500 characters maximum, miscellaneous information
- Publisher, text, 300 characters maximum, publisher for PubType = 3 (otherwise = "")
- PlacePublished, text, 200 characters maximum, place published for PubType = 3 (otherwise = "")
- LastUpdate, smalldatetime, date and time of last update
- ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
- tblPubReg, registry of data about publications shared across species files,
under development in version 3.4
- PubRegID, integer, 4 bytes, primary key
- PrefRegID, integer, 4 bytes, if PrefRegID > 0, then this is a duplicate or misspelled name and PrefRegID = PubRegID for preferred name
- PubType, integer, 1 byte, 1 = journal, (2 not used), 3 = book or CD, 4 = unpublished
- ShortName, text, 500 characters maximum, abbreviated journal name or book title or unpublished source
- FullName, text, 500 characters maximum, full journal name or book title or unpublished source
- Note, text, 500 characters maximum, miscellaneous information
- Publisher, text, 300 characters maximum, publisher for PubType = 3 (otherwise = "")
- PlacePublished, text, 200 characters maximum, place published for PubType = 3 (otherwise = ''")
- ISBN, text, 17 characters, ISBN number
- ISSN, text, 9 characters, ISSN number
- StartYear, integer, 2 bytes, starting or only year of publication
- EndYear, integer, 2 bytes, ending year of publication, 0 = StartYear is the only year, -1 = publication has not terminated
- SourceID, integer, 2 bytes, = tblSources.SourceID, source of this row of data
- LastUpdate, smalldatetime, date and time of last update
- tblPendingPubFiles, linking table used to track the reconciliation
of data in tblPubReg and the separate tblPubs in different species files,
under development in versions 3.4 and 3.5
- PubRegID, integer, 4 bytes, = tblPubReg.PubRegID
- FileID = tblFiles.FileID, integer, 2 bytes, this plus PubRegID constitutes the primary key
- PubID, integer, 2 bytes, = tblPubs.PubID
- Status, integer, 1 byte, status of reconciling tblPubReg with tblPubs in the species file
- 1-50 = waiting for response from species file editor
- 1 = Preferred pub not found in tblPubReg
- 2 = Registry editor has proposed change
- 11 = Registry editor rejected proposed addition
- 12 = Registry editor rejected proposed change
- 13 = Message from registry editor waiting
- 21 = Registry has accepted proposal from species file
- 51-100 = waiting for response from registry editor
- 51 = Species file editor proposed adding a pub to the registry
- 52 = Species file editor initiated a proposed change
- 61 = Species file editor indicated belief that species file version is correct
- 71 = Species file editor chooses not to include this pub in registry
- 81 = Message from species file editor waiting
- 91 = Species file has accepted proposal from registry editor
- No code for completed; the row is deleted.
- Messages, text, 700 character maximum
- LastUpdate, smalldatetime, date and time of last update
- tblNomenclator, name combinations at rank of genus
or lower
- NomenclatorID, integer, 4 bytes, identifier for the name combination
- GenusNameID, integer, 2 bytes, = tblGenusNames.GenusNameID
- SubgenusNameID, integer, 2 bytes, = tblGenusNames.GenusNameID
- SpeciesNameID, integer, 2 bytes, = tblSpeciesNames.SpeciesNameID
- SubspeciesNameID, integer, 2 bytes, = tblSpeciesNames.SpeciesNameID
- InfrasubKind, integer, 2 bytes, kind of infrasubspecies name
- 0 = [none]
- 1 = form
- 2 = variety
- InfrasubspeciesNameID, integer, 2 bytes, = tblSpeciesNames.SpeciesNameID
- TSN, integer, 4 bytes, taxon serial number from ITIS.
This column has not been implemented.
- LastUpdate, smalldatetime, date and time of last update
- ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
- tblGenusNames, genus and subgenus names for lookup
in tblNomenclator. The names in this table are guaranteed to be in tblTaxa except when their usage is based on a specimen identification. TaxonNameID is not recorded here because the name may appear more than once in tblTaxa.
- GenusNameID, integer, 2 bytes, primary key
- Name, text, 30 characters maximum, = tblTaxa.Name at a rank of genus or subgenus.
Names are limited to genus and subgenus names found in tblTaxa.Name or required
for tblIdentifications.NomenclatorID.
- LastUpdate, smalldatetime, date and time of last update
- ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
- Italicize, integer, 1 byte, value of 1 means italicize the name,
added in version 3.4
- tblSpeciesNames, species and subspecies names for lookup
in tblNomenclator. Names in this table are not cross checked against tblTaxa. They are automatically deleted if not used.
- SpeciesNameID, integer, 2 bytes, primary key
- Name, text, 30 characters maximum, may not be present as tblTaxa.Name
- LastUpdate, smalldatetime, date and time of last update
- ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
- Italicize, integer, 1 byte, value of 1 means italicize the name,
added in version 3.4
- tblScrutinies, years and comments from persons working on this database
- ScrutinyID, integer, 2 bytes, primary key
- Year, integer, 2 bytes, the year of the scrutiny
- Comment, text, 1000 characters maximum, comment or explanation
- LastUpdate, smalldatetime, date and time of last update
- ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
- tblTaxonScrutinies, linking table to match taxa with scrutinies
- TaxonNameID, integer, 4 bytes, = tblTaxa.TaxonNameID for the associated taxon
- ScrutinyID, integer, 2 bytes, = tblScrutinies.ScrutinyID
- SeqNum, integer, 2 bytes, sequence number for scrutinies for an individual taxon
- LastUpdate, smalldatetime, date and time of last update
- ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
- tblScrutinyAuthors, linking table to match scrutinies
with authors
- ScrutinyID, integer, 2 bytes, = tblScrutinies.ScrutinyID
- PersonID, integer, 2 bytes, = tblPeople.PersonID
- SeqNum, integer, 2 bytes, position in sequence of names for this author
- AuthorCount, integer, 2 bytes, number of co-authors for this scrutiny
- LastUpdate, smalldatetime, date and time of last update
- ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
|