|
Data about specimens
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.
- tblSpecimens, information about specimens
- SpecimenID, integer, 4 bytes, identifier for specimen
- TaxonNameID, integer, 4 bytes, = tblTaxon.TaxonNameID of a valid or unnamed taxon
that is the current identification (may be higher level taxon if not identified
further)
- DepoID, integer, 2 bytes, = tblDepos.DepoID, identifier for depository or collection
that owns the specimen, a negative value means that the depository is not certain
- DepoCatNo, text, 30 bytes maximum, catalogue number of the depository or collection
- PreparationCode, integer, 1 byte, 0 = unspecified, 1 = pin, 2 = vial, 3 = jar,
4 = alcohol (vial or jar not specified), 5 = microscope slide
dropped in version 4.0
- MaleCount, integer, 2 bytes, number of adult males (-1 means "many", -2 means "more
than 1") dropped in version 4.0
- FemaleCount, integer, 2 bytes, number of adult females (-1 means "many", -2 means
"more than 1") dropped in version 4.0
- UnsexedAdultCount, integer, 2 bytes, number of unsexed adults (-1 means "many",
-2 means "more than 1") dropped in version 4.0
- ImmatureCount, integer, 2 bytes, number of juveniles (-1 means "many", -2 means
"more than 1") dropped in version 4.0
- SpecimenStatusID, integer, 1 byte, = tblSpecimenStatus.SpecimenStatusID, (OK, damaged,
lost, etc.)
- LocalityID, integer, 4 bytes, = tblLocalities.LocalityID, identifier for the locality
- CollectEventID, integer, 4 bytes, = tblCollectEvents.CollectEventID, identifier
for the collecting event
- DataFlags, integer, 1 byte, bit flags for kinds of data available
- 1 = ecological relationship
- 2 = character data, not yet implemented
- 4 = image
- 8 = sound
- 16 = include specimen locality in maps,
implemented in version 3.5
- SourceID, integer, 2 bytes, = tblSources.SourceID, source for the data in this
table
- Note, text, 300 bytes maximum, condition of specimen or any other information
- AccessCode, integer, 2 bytes
- LastUpdate, smalldatetime, date and time of last update
- ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
- PreparationType, text, 300 byte maximum, text field replacing PreparationCode,
added in version 4.0
- BasisOfRecord, integer, 4 bytes, added in version 4.0
- 0 = 'Not specified'
- 1 = 'Preserved specimen'
- 2 = 'Fossil specimen'
- 3 = 'Image (still or video)'
- 4 = 'Audio recording'
- 5 = 'Checklist/Literature/Map'
- 6 = 'Personal observation'
- tblSpecimenCategories, categories of specimens (male, female, egg, etc.)
added in version 4.0
- SpmnCategoryID, integer, 2 bytes, primary key, identifier for the category
- Name, text, 30 characters maximum
- SeqNum, integer, 2 bytes, used to specify the list sequence in dropdownlists
- LastUpdate, smalldatetime, date and time of last update
- ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
- tblSpecimenCounts, counts for various specimen categories
added in version 4.0
- SpecimenID, integer, 4 bytes, = tblSpecimens.SpecimenID, identifier for
specimen
- SpmnCategoryID, integer, 2 bytes, identifier for the category
- Count, integer, 2 bytes, (-1 means "many", -2 means "more than 1")
- LastUpdate, smalldatetime, date and time of last update
- ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
- tblDepos, depositories (institutions or collections) that hold type
specimens, including duplicate names
- DepoID, integer, 2 bytes, identifier for the type depository
- PrefID, integer, 2 bytes, = tblDepos.DepoID for the preferred name
- Coden, text, 5 bytes maximum, standard specimen depository abbreviations as specified
by the Bishop Museum
- ShortName, text, 40 bytes maximum, city plus descriptive abbreviation if city has
multiple depositories; null string if this is not a preferred name
- FullName, text, 150 bytes maximum, full name for the depository, including duplicate names
- Link, text, 75 bytes maximum, hyperlink to depository's website
- LastUpdate, smalldatetime, date and time of last update
- ModifiedBy, integer, 2 bytes, UserID of the person that made the change
- tblSpecimenStatus, sex and maturity classification for specimens,
shared across files.
- SpecimenStatusID, integer, 1 byte, identifier for the status
- Status, text, 20 characters maximum
- 0 = presumed OK
- 1 = missing
- 2 = destroyed
- 3 = lost
- 4 = unknown
- 5 = missing?
- 6 = destroyed?
- 7 = lost?
- 8 = damaged
- 9 = damaged?
- 10 = no data entered
- tblSexes, sex and maturity classification for type series, shared across files.
- SexID, integer, 1 byte, identifier for the description
- Sex, text, 22 bytes maximum, description consisting of male, female, juv., etc.
- Letter, text, 4 characters, letter(s) used in imported data to specify sex
- tblIdentifications, record of identifications on labels
or in publications or by placement in trays. Each identification must have
either HigherTaxonName or a genus name that can be located via NomenclatorID.
If the genus is specified, then HigherTaxonName is left blank. RefID, IdentifierName,
PlaceInCollection, and IdentificationModeNote are the four possible sources of identification.
At least one source of identification must be stated. Placement in collection
is considered not worth recording if one of the other sources is listed. If the
identification is from the literature, then RefID > 0. In that case, names
of subgenera, genera and higher taxa must be present in tblTaxa. If RefID
= 0, names of taxa are not required to be present in tblTaxa.
- SpecimenID, integer, 4 bytes, = tblSpecimens.SpecimenID
- SeqNum, integer, 2 bytes, sequence number for identifications for this specimen;
SpecimenID and SeqNum together form the primary key
- HigherTaxonName, text, 30 bytes maximum, name of taxon higher than genus rank,
used only when there is no identification at genus rank
- NomenclatorID, integer, 4 bytes, = tblNomenclator.NomenclatorID
- TaxonIdentNote, text, 300 bytes maximum, any additional taxon related information
contained with the identification
- TypeKindID, integer, 1 byte, = tblTypeKinds.TypeKindID; kind of type, 0 if none
- Topotype, integer, 1 byte, 0 = not a topotype, 1 = is a topotype
- TypeTaxonNameID, integer, 4 bytes. If TypeKindID and Topotype both = 0, then
TypeTaxonNameID = 0. If either TypeKindID or Topotype > 0, then TypeTaxonNameID
= tblTaxa.TaxonNameID of the name for which this specimen is a type.
- RefID, integer, 4 bytes, = tblRefs.RefID, published reference with this specimen
identified
- IdentifierName, text, 50 bytes maximum, name of person on identification label.
To be deleted when linking table to tblPeople is available.
- IdentifierID, integer, 2 bytes, = tblPeople.PersonID.
For future implementation, probably in a separate linking table.
- Year, integer, 2 bytes, year stated on an identification label
- PlaceInCollection, integer, 1 byte, 0 = not the source of the identification, 1
= the identification information is based solely on the placement in a collection
- IdentificationModeNote, text, 200 bytes maximum, information about the source of
the identification
- LastUpdate, smalldatetime, date and time of last update
- ModifiedBy, 2 bytes, UserID of the signon that made the change
- IdentQualifier, text, 30 character maximum. Added in version 4.1.
- VerbatimLabel, text, 1000 character maximum. Added in version 4.1.
- tblTypeKinds, holotype, lectotype, neotype, etc., shared across files.
- TypeKindID, integer, 1 byte, identifier for the kind of type
- 0 = undefined
- 1 = holotype
- 2 = syntypes
- 3 = neotype
- 4 = lectotype
- 5 = unspecified primary type
- 6 = unknown
- 7 = allotype
- 8 = paratype
- 9 = topotype
- 10 = paralectotype
- Kind, text, 50 bytes maximum, name for the kind of type
- Letter, text, 1 character, letter used in imported data to specify kind
- tblLocalities, information about localities where specimens
were found.
- LocalityID, integer, 4 bytes, identifier for locality
- Level1ID, 1 character, = tblGeoLevel1.Level1ID
- Level2ID, 1 character, = tblGeoLevel2.Level2ID
- Level3ID, 3 characters, = tblGeoLevel3.Level3ID
- Level4ID, 3 characters, = tblGeoLevel4.Level4ID
- GeoID, calculated as concatenation of above 4 columns
- Latitude, decimal, precision 7, scale 4, degrees (+ for N, - for S)
- Longitude, decimal, precision 7, scale 4, degrees (+ for E, - for W)
- PrecisionCode, integer, 1 byte (still available, no longer used, will be converted for use as indicator of confidence in the supplied Lat/Long)
- 0 = unspecified (default)
- 1 = from locality label
- 2 = estimated from map and locality label
- 3 = based on county or similar modest area specified on locality label
- 4 = estimated from less specific locality label
- Elevation, integer, 2 bytes, elevation in meters
- MaxElevation, integer, 2 bytes, maximum elevation when there is a range, otherwise
null
- TimePeriodID, integer, 2 bytes, = tblTimePeriods.TimePeriodID, geologic time period
- LocalityDetail, text, 300 bytes maximum, place data not coded in any other field
- TimeDetail, text, 300 bytes maximum, details not coded in TimePeriodID
- LastUpdate, smalldatetime, date and time of last update
- ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
- DataFlags, integer, 1 byte, bit flags for data status, added in version 4.0
- 1 = The disparity between geographic levels and the Latitude & Longitude was accepted by the user
- 2 = The locality was last edited by a user during a localities test run
- Country, text, 100 characters maximum, name of the country of collection (It is not required that this name match any data supplied in the GeoId (above)), added in version 4.0
- State, text, 100 characters maximum, name of the state or province of collection, added in version 4.0
- County, text, 100 characters maximum, name of the county or precinct of collection, added in version 4.0
- BodyOfWater, text, 100 characters maximum, name of the body of water of collection, added in version 4.0
- PrecisionRadius, interger, 4 bytes, radius of the circle ot uncertainty of the accuarcy of the Latitude/Longitude data, added in version 4.0
- LatLongFrom, integer, 4 bytes, indicator of when the Latitude/Longitude data was applied, added in version 4.0
- 0 = Unknown (default)
- 1 = Provided by the collector at collection time
- 2 = Provided during data entry
- tblGeoLevel1, shared across files, continent or other major part of the
world. This table and the next two tables are copied from Brummitt, R. K. 2001: World
Geographical Scheme for Recording Plant Distributions. Plant Taxonomic Database Standards No. 2,
Edition 2. International Working Group on Taxonomic Databases (TDWG). Hunt Institute for
Botanical Documentation, Pittsburgh. [TDWG Standard] [Electronic version available under
http://www.tdwg.org/geo2.htm.]
NOTE: The row for no information (Level1ID = "0") has information for columns from
MinL2AreaValue to Level2AllMask that applies to tblTaxa.Distribution1Flags, not to
tblTaxa.Distribution2WFlags, Distribution2CFlags or Distribution2EFlags.
- Level1ID, character, 1 byte, identifier for "continent"
- Name, text, 16 bytes maximum, name
- BitValue, integer, 4 bytes, value of the bit that indicates this level 1 area in
tblTaxa.Distribution1Flags
- MinL2AreaValue, integer, 4 bytes, value of the lowest bit in tblTaxa.Distribution2WFlags,
Distribution2CFlags or Distribution2EFlags that signifies a subarea within this level 1
area. Deleted in version 3.5.
- MaxL2AreaValue, integer, 4 bytes, value of the highest bit in tblDistribution.AreaFlags
that signifies a subarea within this level 1 area
- Level2AreaMask, integer, 4 bytes, mask for all the bits in tblDistribution.AreaFlags
- Level2StatusFactor, integer, 4 bytes, value of the lower bit in tblTaxa.Distribution2WFlags,
Distribution2CFlags or Distribution2EFlags for the status of information.
Deleted in version 3.5.
- Level2StatusMask, integer, 4 bytes, mask for the two status bits (= 3 *
Level2StatusFactor). Deleted in version 3.5.
- Level2AllMask, integer, 4 bytes, combined bit values for Level2AreaMask and
Level2StatusMask. Deleted in version 3.5.
- tblGeoLevel2, shared across files, region or large country.
From World Geographical Scheme for Recording Plant Distributions, Plant Taxonomic Database
Standards No. 2. Edition 2.
- Level1ID, character, 1 byte, = tblGeoLevel1.Level1ID
- Level2ID, character, 1 byte, identifier for region within "continent"
- Name, text, 50 bytes maximum, name for the region
- BitValue, integer, 4 bytes, value of the bit in tblDistribution.AreaFlags that
indicates this level 2 area
- Level3Count, integer, 1 byte, count of level 3 areas contained in this level 2 area
- MaxL3AreaValue, integer, 4 bytes, maximum bit value in tblLevel3Dist for a contained area
- Level3AreaMask, integer, 4 bytes, mask for level 3 area flags in tblLevel3Dist.AreaFlags
- tblGeoLevel3, shared across files, country or portion of large
country. From World Geographical Scheme for Recording Plant Distributions, Plant
Taxonomic Database Standards No. 2. Edition 2.
- Index3, integer, 2 bytes, = 2000 * Level1ID + 100 * (Level2ID + 1) + sequence number of
Level3ID in alphabetic order among rows with the same Level1ID and Level2ID. When
Level3ID = "---", the sequence number is treated as 0.
- Level1ID, character, 1 byte, = tblGeoLevel1.Level1ID
- Level2ID, character, 1 byte, = tblGeoLevel2.Level2ID
- Level3ID, character, 3 bytes, identifier for "country" within region
- Name, text, 50 bytes maximum, name for the region
- ExpandedName, text 100 bytes maximum, includes identifying name (usually
tblGeoLevel2.Name) before tblGeoLevel3.Name
- BitValue, integer, 4 bytes, value of the bit that indicates this level 3 area
- tblGeoLevel4, shared across files, county or other smaller
area. From World Geographical Scheme for Recording Plant Distributions, Plant Taxonomic
Database Standards No. 2. Edition 2 and from United States Federal Information Processing
Standards Publication 6-4 (http://www.itl.nist.gov/fipspubs/fip6-4.htm) and other sources.
- Level3ID, character, 3 bytes, = tblGeoLevel3.Level3ID
- Level4ID, character, 3 bytes, identifier for fourth level in geographic classification
- Name, text, 50 bytes maximum, name for the region ("county")
- CountryCode, character, 2 bytes, ISO country code of the region
- tblGazetteer, shared across files, listing of names and the
corresponding values in the preceding four tables, used with human data entry.
- ID, integer, 2 bytes, identifier for the name
- Name, text, 50 byes maximum, place name (duplicates permitted)
- Level1ID, character, 1 byte, = tblGeoLevel1.Level1ID
- Level2ID, character, 1 byte, = tblGeoLevel2.Level2ID
- Level3ID, character, 3 bytes, = tblGeoLevel3.Level3ID
- Level4ID, character, 3 bytes, = tblGeoLevel4.Level4ID
- tblLandCoords, shared across files, organized polygon point
lists by TDWG codes for land areas
- Level1ID, character, 1 byte, = tblGeoLevel1.Level1ID
- Level2ID, character, 1 byte, = tblGeoLevel2.Level2ID
- Level3ID, character, 3 bytes, = tblGeoLevel3.Level3ID
- Level4ID, character, 3 bytes, = tblGeoLevel4.Level4ID
- Polygon, integer, 4 bytes, 1-based index of polygon list for this code combination
- Point, integer, 4 bytes, 1-based index of point list
- Xlon, float, longitiude coordinate in degrees
- Ylat, float, latitiude coordinate in degrees
- tblSeaCoords, shared across files, organized
polygon point lists by TDWG codes for oceanic areas
- Level1ID, character, 1 byte, = tblGeoLevel1.Level1ID
- Level2ID, character, 1 byte, = tblGeoLevel2.Level2ID
- Level3ID, character, 3 bytes, = tblGeoLevel3.Level3ID
- Level4ID, character, 3 bytes, = tblGeoLevel4.Level4ID
- Polygon, integer, 4 bytes, 1-based index of polygon list for this code combination
- Point, integer, 4 bytes, 1-based index of point list
- Xlon, float, longitiude coordinate in degrees
- Ylat, float, latitiude coordinate in degrees
- tblTDWGtoITIS, shared across files, for conversion of the TDWG geo classification
to the ITIS geo classification.
- TDWGcode, text, 8 bytes maximum, TDWG classification
- Level1, character, 1 byte, TDWG classification, = tblGeoLevel1.Level1ID
- Level2, character, 1 byte, TDWG classification, = tblGeoLevel2.Level2ID
- Level3, character, 3 bytes, TDWG classification, = tblGeoLevel3.Level3ID
- Location, text, 50 bytes maximum, TDWG classification
- Jurisdiction, text, 50 bytes maximum, ITIS classification
- Geographic, text, 50 bytes maximum, ITIS classification
- tblTimePeriods, shared across files, geologic time period
- TimePeriodID, integer, 2 bytes, identifier for time period
- Name, text, 20 bytes maximum, name of geologic period
- MaxID, integer, 2 bytes, upper limit for values of TimePeriodID contained within this name
- ListLevel, integer, 2 bytes, level of dropdownlist for users to select time period
- tblCollectEvents, information about the date and collector
- CollectEventID, integer, 4 bytes, identifier for the collecting event
- CollectorName, text, 100 bytes maximum. To be deleted
when CollectorID is implemented and linking table to tblPeople is available.
- Year, integer, 2 bytes, year collecting started. There is a special case of
Year = 1000, used to signify that no year is available for the collection event,
but month (and perhaps day) are significant to the event.
- Month, integer, 1 byte, month collecting started
- Day, integer, 1 byte, day of month collecting started
- DaysToEnd, integer, 2 bytes, additional days in collecting period (usually 0)
- LastUpdate, smalldatetime, date and time of last update
- ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
|