Specify Data Dictionary
Document Actions
The document contains a complete list of all Specify's data tables and fields.
It is intended as a technical reference. It can also be helpful, however, in
understanding the scope and usage of certain attributes. When you have a question
like "where does this information fit in", reading the attribute description
of related attributes may help.
This document is about 56 printed pages and was generated by the Diversity Model Documenter (v1.7) available under a GNU General Public License (v2) from Dr. Gregor Hagedorn, Federal Biological Research Center for Agriculture and Forestry, Berlin, Germany. We gratefully acknowledge the use of his software.
Index to Specify Data Tables
Accession, AccessionAgents, AccessionAuthorizations, Address, Agent, AgentAddress, Authors, BiologicalObjectAttributes, BiologicalObjectRelation, BiologicalObjectRelationType, Borrow, BorrowAgents, BorrowMaterial, BorrowReturnMaterial, BorrowShipments, CatalogSeries, CatalogSeriesDefinition, CollectingEvent, Collection, CollectionObject, CollectionObjectCatalog, CollectionObjectCitation, CollectionObjectType, CollectionTaxonomyTypes, Collectors, DATAVIEWS, Deaccession, DeaccessionAgents, DeaccessionCollectionObject, Determination, DeterminationCitation, ExchangeIn, ExchangeOut, Geography, GeologicTimeBoundary, GeologicTimePeriod, GroupPersons, Habitat, Image, ImageAgents, ImageCollectionObjects, ImageLocalities, Journal, Loan, LoanAgents, LoanPhysicalObject, LoanReturnPhysicalObject, Locality, LocalityCitation, Observation, OtherIdentifier, Permit, Preparation, Project, ProjectCollectionObjects, ReferenceWork, REPORTS, Shipment, Sound, SoundEventStorage, Stratigraphy, TaxonCitation, TaxonName, TaxonomicUnitType, TaxonomyType.
Accession
Information about a collection's accessions.
| Name | Type | Description / Default value & validation | Rqrd./Index | |
|---|---|---|---|---|
| AccessionID | Autonumber | Primary key. Default value: GenUniqueID() |
- | I (U) |
| CollectionID | Long | Collection making the accession. | R | I/I (UM) |
| Number | Text (30) | A user-visible identifier of the Accession. Typically an integer, but may include alphanumeric characters as prefix, suffix, and separators. | R | I |
| Status | Text (30) | Status of Accession, e.g. 'In process', 'Complete' ... validation message: List. |
- | - |
| Type | Text (30) | Source of Accession, e.g. 'Collecting', 'Gift', 'Bequest' ... validation message: List. |
- | - |
| VerbatimDate | Text (50) | accomodates historical accessions. | - | - |
| DateAccessioned | Long | Date of Accession. | - | I |
| DateReceived | Long | Date material was received. | - | - |
| Text1 | Text (50) | User definable. | - | - |
| Text2 | Text (50) | User definable. | - | - |
| Text3 | Text (50) | User definable. | - | - |
| Number1 | Single | User definable. | - | I |
| Number2 | Single | User definable. | - | I |
| Remarks | Memo | Comments. | - | - |
| TimestampCreated | Date/Time | Default value: Now() | - | - |
| TimestampModified | Date/Time | Default value: Now() | - | I |
| LastEditedBy | Text (50) | Login name of user who last edited the record. | - | I |
| StatusID | Long | Link to Lookup table for Status. | - | I |
| TypeID | Long | Link to Lookup table for Type. | - | I |
| YesNo1 | Integer | - | - | |
| YesNo2 | Integer | - | - | |
| Index name: | Attributes & index properties | |||
| {32B88AE3-69C6-11D2-A962-00...: | CollectionID (Duplicates OK) | |||
| CandidateKey: | CollectionID; Number (Unique values) | |||
| DateAccessioned: | DateAccessioned (Duplicates OK) | |||
| LastEditedBy: | LastEditedBy (Duplicates OK) | |||
| Number: | Number (Duplicates OK) | |||
| Number1: | Number1 (Duplicates OK) | |||
| Number2: | Number2 (Duplicates OK) | |||
| PrimaryKey: | AccessionID (Primary key; Unique values) | |||
| StatusID: | StatusID (Duplicates OK) | |||
| TimestampModified: | TimestampModified (Duplicates OK) | |||
| TypeID: | TypeID (Duplicates OK) | |||
| Relation type: | Fields involved | |||
| Deletions cascade | Accession.AccessionID ? AccessionAgents.AccessionID | |||
| Deletions cascade | Accession.AccessionID ? AccessionAuthorizations.AccessionID | |||
| Referential integrity | Accession.AccessionID ? CollectionObjectCatalog.AccessionID | |||
| Referential integrity | Collection.CollectionID ? Accession.CollectionID | |||
AccessionAgents
Associates agents with accessions.
| Name | Type | Description / Default value & validation | Rqrd./Index | |
|---|---|---|---|---|
| AccessionAgentsID | Autonumber | Default value: GenUniqueID() | - | I (U) |
| AccessionID | Long | Accession in which the Agent played a role. | R | I/I (UM) |
| AgentAddressID | Long | AgentAdress of agent playing role in Accession. | R | I |
| Role | Text (50) | Role the agent played in the accession process. validation message: List. |
R | - |
| Remarks | Memo | - | - | |
| TimestampModified | Date/Time | Default value: Now() | - | - |
| TimestampCreated | Date/Time | Default value: Now() | - | - |
| LastEditedBy | Text (50) | - | - | |
| RoleID | Long | Link to Lookup table for Role. | - | I |
| Index name: | Attributes & index properties | |||
| {32B88AB5-69C6-11D2-A962-00...: | AccessionID (Duplicates OK) | |||
| {32B88AB9-69C6-11D2-A962-00...: | AgentAddressID (Duplicates OK) | |||
| AccessionAgentsID: | AccessionAgentsID (Primary key; Unique values) | |||
| CandidateKey: | AccessionID; AgentAddressID; Role (Unique values) | |||
| RoleID: | RoleID (Duplicates OK) | |||
| Relation type: | Fields involved | |||
| Deletions cascade | Accession.AccessionID ? AccessionAgents.AccessionID | |||
| Referential integrity | AgentAddress.AgentAddressID ? AccessionAgents.AgentAddressID | |||
AccessionAuthorizations
Associates permits with accessions.
| Name | Type | Description / Default value & validation | Rqrd./Index | |
|---|---|---|---|---|
| AccessionAuthorizationsID | Autonumber | Default value: GenUniqueID() | - | I (U) |
| AccessionID | Long | Accession authorized by permit. | R | I/I (UM) |
| PermitID | Long | Permit authorizing accession. | R | I |
| Remarks | Memo | - | - | |
| TimestampModified | Date/Time | Default value: Now() | - | - |
| TimestampCreated | Date/Time | Default value: Now() | - | - |
| LastEditedBy | Text (50) | - | - | |
| Index name: | Attributes & index properties | |||
| {32B88AB6-69C6-11D2-A962-00...: | AccessionID (Duplicates OK) | |||
| {32B88B19-69C6-11D2-A962-00...: | PermitID (Duplicates OK) | |||
| AccessionAuthorizationsID: | AccessionAuthorizationsID (Unique values) | |||
| PrimaryKey: | AccessionID; PermitID (Primary key; Unique values) | |||
| Relation type: | Fields involved | |||
| Deletions cascade | Accession.AccessionID ? AccessionAuthorizations.AccessionID | |||
| Referential integrity | Permit.PermitID ? AccessionAuthorizations.PermitID | |||
Address
Addresses of persons and organizations.
| Name | Type | Description / Default value & validation | Rqrd./Index | |
|---|---|---|---|---|
| AddressID | Autonumber | PrimaryKey. Default value: GenUniqueID() |
- | I (U) |
| Address | Memo | Address as it should appear on mailing labels. | R | - |
| City | Text (32) | - | - | |
| State | Text (32) | - | - | |
| Country | Text (32) | - | - | |
| Postalcode | Text (32) | - | I | |
| Remarks | Memo | - | - | |
| TimestampModified | Date/Time | Default value: Now() | - | - |
| TimestampCreated | Date/Time | Default value: Now() | - | - |
| LastEditedBy | Text (50) | - | - | |
| Index name: | Attributes & index properties | |||
| AddressID: | AddressID (Primary key; Unique values) | |||
| Postalcode: | Postalcode (Duplicates OK) | |||
| Relation type: | Fields involved | |||
| Referential integrity | Address.AddressID ? AgentAddress.AddressID | |||
Agent
Information about people, groups, and organizations.
| Name | Type | Description / Default value & validation | Rqrd./Index | |
|---|---|---|---|---|
| AgentID | Autonumber | Primary key. Default value: GenUniqueID() |
- | I (U) |
| AgentType | Byte | Discriminator between Organization(0), Person(1),Other(2), and Group(3). Default value: 1 |
R | - |
| FirstName | Text (50) | of Person. | - | - |
| LastName | Text (50) | of Person. | - | I |
| MiddleInitial | Text (1) | of Person. | - | - |
| Title | Text (50) | of Person. | - | - |
| Interests | Text (255) | of Person or Organization. | - | - |
| Abbreviation | Text (50) | of organization. | - | I |
| Name | Text (120) | of organization/group/Folks (and maybe persons). | - | I |
| ParentOrganizationID | Long | of organization. | - | I |
| Remarks | Memo | - | - | |
| TimestampModified | Date/Time | Default value: Now() | R | I |
| TimestampCreated | Date/Time | Default value: Now() | R | - |
| LastEditedBy | Text (50) | - | I | |
| Index name: | Attributes & index properties | |||
| {32B88AC3-69C6-11D2-A962-00...: | ParentOrganizationID (Duplicates OK) | |||
| Abbreviation: | Abbreviation (Duplicates OK) | |||
| AgentID: | AgentID (Primary key; Unique values) | |||
| LastEditedBy: | LastEditedBy (Duplicates OK) | |||
| LastName: | LastName (Duplicates OK) | |||
| Name: | Name (Duplicates OK) | |||
| TimestampModified: | TimestampModified (Duplicates OK) | |||
| Relation type: | Fields involved | |||
| Referential integrity | Agent.AgentID ? Agent.ParentOrganizationID | |||
| Deletions cascade | Agent.AgentID ? AgentAddress.AgentID | |||
| Referential integrity | Agent.AgentID ? AgentAddress.OrganizationID | |||
| Referential integrity | Agent.AgentID ? Authors.AgentID | |||
| Referential integrity | Agent.AgentID ? BorrowReturnMaterial.ReturnedByID | |||
| Referential integrity | Agent.AgentID ? Collection.OrganizationID | |||
| Referential integrity | Agent.AgentID ? Collectors.AgentID | |||
| Referential integrity | Agent.AgentID ? Determination.DeterminerID | |||
| Referential integrity | Agent.AgentID ? ExchangeIn.CatalogedByID | |||
| Referential integrity | Agent.AgentID ? ExchangeOut.CatalogedByID | |||
| Deletions cascade | Agent.AgentID ? GroupPersons.GroupID | |||
| Referential integrity | Agent.AgentID ? GroupPersons.MemberID | |||
| Referential integrity | Agent.AgentID ? CollectionObjectCatalog.CatalogerID | |||
| Referential integrity | Agent.AgentID ? Image.MadeByID | |||
| Referential integrity | Agent.AgentID ? Image.CopyrightOwnerID | |||
| Referential integrity | Agent.AgentID ? ImageAgents.AgentID | |||
| Referential integrity | Agent.AgentID ? LoanReturnPhysicalObject.ReceivedByID | |||
| Referential integrity | Agent.AgentID ? Preparation.PreparedByID | |||
| Referential integrity | Agent.AgentID ? Shipment.ShippedByID | |||
| Referential integrity | Agent.AgentID ? Sound.RecordedByID | |||
| Referential integrity | Agent.AgentID ? Project.ProjectAgentID | |||
AgentAddress
Associates agents, addresses, and organizaions.
| Name | Type | Description / Default value & validation | Rqrd./Index | |
|---|---|---|---|---|
| AgentAddressID | Autonumber | PrimaryKey. Default value: GenUniqueID() |
- | I (U) |
| TypeOfAgentAddressed | Integer | 0 for organization, 1 for person. Default value: 1 |
- | - |
| AddressID | Long | Associated record in Address table. Default value: Null |
- | I |
| AgentID | Long | Associated record in Agent table. Default value: Null |
- | I |
| OrganizationID | Long | Associates Agent identified by AgentID (type Person) as a member of Agent
identified by OrganizationID (type Organization). Default value: Null |
- | I |
| JobTitle | Text (50) | Agent's (person) job title at specified address and organization. | - | - |
| Phone1 | Text (50) | - | - | |
| Phone2 | Text (50) | - | - | |
| Fax | Text (50) | - | - | |
| RoomOrBuilding | Text (50) | - | - | |
| Text (50) | - | - | ||
| URL | Text (50) | - | - | |
| Remarks | Memo | - | - | |
| TimestampModified | Date/Time | Default value: Now() | - | - |
| TimestampCreated | Date/Time | Default value: Now() | - | - |
| LastEditedBy | Text (50) | - | - | |
| IsCurrent | Integer | - | - | |
| Index name: | Attributes & index properties | |||
| {32B88AB8-69C6-11D2-A962-00...: | AddressID (Duplicates OK) | |||
| {32B88AC4-69C6-11D2-A962-00...: | AgentID (Duplicates OK) | |||
| {32B88AC5-69C6-11D2-A962-00...: | OrganizationID (Duplicates OK) | |||
| AgentAddressID: | AgentAddressID (Primary key; Unique values) | |||
| Relation type: | Fields involved | |||
| Referential integrity | Address.AddressID ? AgentAddress.AddressID | |||
| Referential integrity | AgentAddress.AgentAddressID ? AccessionAgents.AgentAddressID | |||
| Referential integrity | AgentAddress.AgentAddressID ? BorrowAgents.AgentAddressID | |||
| Referential integrity | AgentAddress.AgentAddressID ? DeaccessionAgents.AgentAddressID | |||
| Referential integrity | AgentAddress.AgentAddressID ? ExchangeIn.ReceivedFromOrganizationID | |||
| Referential integrity | AgentAddress.AgentAddressID ? ExchangeOut.SentToOrganizationID | |||
| Referential integrity | AgentAddress.AgentAddressID ? LoanAgents.AgentAddressID | |||
| Referential integrity | AgentAddress.AgentAddressID ? Permit.IssuerID | |||
| Referential integrity | AgentAddress.AgentAddressID ? Permit.IssueeID | |||
| Referential integrity | AgentAddress.AgentAddressID ? Shipment.ShipperID | |||
| Referential integrity | AgentAddress.AgentAddressID ? Shipment.ShippedToID | |||
| Deletions cascade | Agent.AgentID ? AgentAddress.AgentID | |||
| Referential integrity | Agent.AgentID ? AgentAddress.OrganizationID | |||
Authors
Associates agents with referenceworks.
| Name | Type | Description / Default value & validation | Rqrd./Index | |
|---|---|---|---|---|
| AuthorsID | Autonumber | Default value: GenUniqueID() | - | I (U) |
| AgentID | Long | Agent record representing the Author. | R | I/I (UM) |
| ReferenceWorkID | Long | Reference record the Agent authored. | R | I/I (UM) |
| Order | Integer | the order of AgentID relative to other authors of reference. | R | - |
| Remarks | Memo | - | - | |
| TimestampModified | Date/Time | Default value: Now() | - | - |
| TimestampCreated | Date/Time | Default value: Now() | - | - |
| LastEditedBy | Text (50) | - | - | |
| Index name: | Attributes & index properties | |||
| {32B88AC6-69C6-11D2-A962-00...: | AgentID (Duplicates OK) | |||
| {32B88B1B-69C6-11D2-A962-00...: | ReferenceWorkID (Duplicates OK) | |||
| CandidateKey: | AgentID; ReferenceWorkID (Unique values) | |||
| CitationOrder: | ReferenceWorkID; Order (Unique values) | |||
| PrimaryKey: | AuthorsID (Primary key; Unique values) | |||
| Relation type: | Fields involved | |||
| Referential integrity | Agent.AgentID ? Authors.AgentID | |||
| Deletions cascade | ReferenceWork.ReferenceWorkID ? Authors.ReferenceWorkID | |||
BiologicalObjectAttributes
Measurements and other attributes of BiologicalObjects.
| Name | Type | Description / Default value & validation | Rqrd./Index | |
|---|---|---|---|---|
| BiologicalObjectAttributesID | Long | R | I (U)/I (U) | |
| BiologicalObjectTypeID | Long | R | I | |
| Sex | Text (50) | validation message: List*. | - | - |
| Age | Text (50) | - | - | |
| Stage | Text (50) | validation message: List*. | - | - |
| Weight | Single | Default value: Null | - | - |
| Length | Single | Default value: Null | - | - |
| GosnerStage | Byte | Default value: Null | - | - |
| SnoutVentLength | Single | Default value: Null | - | - |
| Activity | Text (50) | - | - | |
| LengthTail | Single | Default value: Null | - | - |
| ReproductiveCondition | Text (50) | - | - | |
| Condition | Text (50) | - | - | |
| LengthTarsus | Single | Default value: Null | - | - |
| LengthWing | Single | Default value: Null | - | - |
| LengthHead | Single | Default value: Null | - | - |
| LengthBody | Single | Default value: Null | - | - |
| LengthMiddleToe | Single | Default value: Null | - | - |
| LengthBill | Single | Default value: Null | - | - |
| TotalExposedCulmen | Single | Default value: Null | - | - |
| MaxLength | Single | Default value: Null | - | - |
| MinLength | Single | Default value: Null | - | - |
| LengthHindFoot | Single | Default value: Null | - | - |
| LengthForeArm | Single | Default value: Null | - | - |
| LengthTragus | Single | Default value: Null | - | - |
| LengthEar | Single | Default value: Null | - | - |
| EarFromNotch | Single | Default value: Null | - | - |
| Wingspan | Single | Default value: Null | - | - |
| LengthGonad | Single | Default value: Null | - | - |
| WidthGonad | Single | Default value: Null | - | - |
| LengthHeadBody | Single | Default value: Null | - | - |
| Width | Single | Default value: Null | - | - |
| HeightFinalWhorl | Single | Default value: Null | - | - |
| InsideHeightAperture | Single | Default value: Null | - | - |
| InsideWidthAperture | Single | Default value: Null | - | - |
| NumberWhorls | Integer | Default value: Null | - | I |
| OuterLipThickness | Single | Default value: Null | - | - |
| Mantle | Single | Default value: Null | - | - |
| Height | Single | Default value: Null | - | - |
| Diameter | Single | Default value: Null | - | - |
| BranchingAt | Text (50) | - | - | |
| Text1 | Text (50) | User definable. | - | - |
| Text2 | Text (50) | User definable. | - | - |
| Text3 | Text (50) | User definable. | - | - |
| Text4 | Text (50) | User definable. | - | - |
| Text5 | Text (50) | User definable. | - | - |
| Remarks | Memo | - | - | |
| TimestampModified | Date/Time | Default value: Now() | - | - |
| TimestampCreated | Date/Time | Default value: Now() | - | - |
| LastEditedBy | Text (50) | - | - | |
| SexID | Long | Link to Lookup table for Sex. | - | I |
| StageID | Long | Link to Lookup table for Stage. | - | I |
| YesNo1 | Integer | - | - | |
| YesNo2 | Integer | - | - | |
| YesNo3 | Integer | - | - | |
| Number1 | Single | - | - | |
| Number2 | Single | - | - | |
| Number3 | Single | - | - | |
| Number4 | Single | - | - | |
| Number5 | Single | - | - | |
| Number6 | Single | - | - | |
| Number7 | Single | - | - | |
| Text6 | Text (100) | - | - | |
| Text7 | Text (100) | - | - | |
| YesNo4 | Integer | - | - | |
| YesNo5 | Integer | - | - | |
| YesNo6 | Integer | - | - | |
| YesNo7 | Integer | - | - | |
| Index name: | Attributes & index properties | |||
| {32B88AEA-69C6-11D2-A962-00...: | BiologicalObjectAttributesID (Unique values) | |||
| {32B88AF9-69C6-11D2-A962-00...: | BiologicalObjectTypeID (Duplicates OK) | |||
| BiologicalObjectID: | BiologicalObjectAttributesID (Primary key; Unique values) | |||
| NumberWhorls: | NumberWhorls (Duplicates OK) | |||
| SexID: | SexID (Duplicates OK) | |||
| StageID: | StageID (Duplicates OK) | |||
| Relation type: | Fields involved | |||
| Deletions cascade, one-to-one | CollectionObject.CollectionObjectID ? BiologicalObjectAttributes.BiologicalObjectAttributesID | |||
| Referential integrity | CollectionObjectType.CollectionObjectTypeID ? BiologicalObjectAttributes.BiologicalObjectTypeID | |||
BiologicalObjectRelation
Information about relationships (Parent-Child, Host-Parasite, etc.) between BiologicalObjects.
| Name | Type | Description / Default value & validation | Rqrd./Index | |
|---|---|---|---|---|
| BiologicalObjectRelationID | Autonumber | Default value: GenUniqueID() | - | I (U) |
| BiologicalObjectID | Long | R | I/I (UM) | |
| RelatedBiologicalObjectID | Long | R | I | |
| BiologicalObjectRelationTypeID | Long | R | I | |
| Remarks | Memo | - | - | |
| TimestampModified | Date/Time | Default value: Now() | - | - |
| TimestampCreated | Date/Time | Default value: Now() | - | - |
| LastEditedBy | Text (50) | - | - | |
| Index name: | Attributes & index properties | |||
| {32B88AD8-69C6-11D2-A962-00...: | BiologicalObjectRelationTypeID (Duplicates OK) | |||
| {32B88AEB-69C6-11D2-A962-00...: | BiologicalObjectID (Duplicates OK) | |||
| {32B88AEC-69C6-11D2-A962-00...: | RelatedBiologicalObjectID (Duplicates OK) | |||
| BiologicalObjectRelationID: | BiologicalObjectRelationID (Primary key; Unique values) | |||
| CandidateKey: | BiologicalObjectID; RelatedBiologicalObjectID; BiologicalObjectRelationTypeID (Unique values) | |||
| Relation type: | Fields involved | |||
| Referential integrity | BiologicalObjectRelationType.BiologicalObjectRelationTypeID ? BiologicalObjectRelation.BiologicalObjectRelationTypeID | |||
| Referential integrity | CollectionObject.CollectionObjectID ? BiologicalObjectRelation.BiologicalObjectID | |||
| Referential integrity | CollectionObject.CollectionObjectID ? BiologicalObjectRelation.RelatedBiologicalObjectID | |||
BiologicalObjectRelationType
Information about the valid types of relationships between BiologicalObjects. .
| Name | Type | Description / Default value & validation | Rqrd./Index | |
|---|---|---|---|---|
| BiologicalObjectRelationTypeID | Autonumber | Default value: GenUniqueID() | - | I (U) |
| RelationshipName | Text (50) | - | - | |
| LeftSideRoleName | Text (50) | - | - | |
| RightSideRoleName | Text (50) | - | - | |
| Remarks | Memo | - | - | |
| TimestampModified | Date/Time | Default value: Now() | - | - |
| TimestampCreated | Date/Time | Default value: Now() | - | - |
| LastEditedBy | Text (50) | - | - | |
| IsReflexive | Integer | - | - | |
| Index name: | Attributes & index properties | |||
| PrimaryKey: | BiologicalObjectRelationTypeID (Primary key; Unique values) | |||
| Relation type: | Fields involved | |||
| Referential integrity | BiologicalObjectRelationType.BiologicalObjectRelationTypeID ? BiologicalObjectRelation.BiologicalObjectRelationTypeID | |||
Borrow
Invoice information for borrowed (incoming) material.
| Name | Type | Description / Default value & validation | Rqrd./Index | |
|---|---|---|---|---|
| BorrowID | Autonumber | Primary key. Default value: GenUniqueID() |
- | I/I (U) |
| CollectionID | Long | Department receiving the loan. | R | I (UM)/ |