Database Schema Changes for Age & Stratigraphy

[grantfitzsimmons]

New Tables

Tectonics:

These 'Tectonic' records use the exact same tree structure as other existing Specify trees. There is no special logic or requirements for this tree, and the default should simply be a root node and 5 simple ranks:

• Superstructure
• Tectonic domain
• Tectonic subdomain
• Tectonic unit
• Tectonic subunit

TectonicUnit

• TectonicID – int(11) NOT NULL – PK
• FullName – varchar(255)
• GUID – varchar(128)
• HighestChildNodeNumber – int(11)
• IsAccepted – bit(1) NOT NULL
• Name – varchar(64) NOT NULL
• NodeNumber – int(11)
• Number1 – decimal(20,10)
• Number2 – decimal(20,10)
• RankID – int(11) NOT NULL
• Remarks – text
• Text1 – text
• Text2 – text
• TimestampCreated – datetime NOT NULL
• TimestampModified – datetime
• Version – int(11)
• YesNo1 – bit(1)
• YesNo2 – bit(1)

-- Relationships

• AcceptedID – int(11) – FK to TectonicUnit, represents the record it was synonymized to
• CreatedByAgentID – int(11) – FK to Agent
• ModifiedByAgentID – int(11) – FK to Agent
• ParentID – int(11) – FK to TectonicUnit parent record
• TectonicTreeDefID – int(11) NOT NULL – FK to TectonicTreeDef
• TectonicTreeDefItemID – int(11) NOT NULL – FK to TectonicTreeDefItem rank record

TectonicTreeDefItem

• TectonicTreeDefItemID – int(11) NOT NULL – PK
• FullNameSeparator – varchar(32)
• IsEnforced – bit(1)
• IsInFullName – bit(1)
• Name – varchar(64) NOT NULL
• RankID – int(11) NOT NULL
• Remarks – text
• TextAfter – varchar(64)
• TextBefore – varchar(64)
• TimestampCreated – datetime NOT NULL
• TimestampModified – datetime
• Title – varchar(64)
• Version – int(11)

-- Relationship

• CreatedByAgentID – int(11) – FK to Agent
• ModifiedByAgentID – int(11) – FK to Agent
• ParentItemID – int(11) – FK to TectonicTreeDefItem parent record
• TectonicTreeDefID – int(11) – FK to TectonicTreeDef

TectonicTreeDef

• TectonicTreeDefID – int(11) NOT NULL
• FullNameDirection – int(11)
• Name – varchar(64) NOT NULL
• Remarks – text
• TimestampCreated – datetime NOT NULL
• TimestampModified – datetime
• Version – int(11)

-- Relationship

• CreatedByAgentID – int(11) – FK to Agent
• DisciplineID – int(11) – FK to Discipline for scoping
• ModifiedByAgentID – int(11) – FK to Agent

Age Tables:

Each ‘*Age’ record contains a FK to CollectionObject. There may be many AbsoluteAges and Relative Ages linked to one CO, functions as a one-to-many

Gray indicates these are free fields for users to assign any purpose. Other fields have an expected usage.

AbsoluteAge:

• AbsoluteAgeID – int(11) – PK
• AbsoluteAge – decimal(20,10)
• AgeType – varchar(64) (from control vocab (sedimentation, metamorphic, inclusion, original, fall,...)
• AgeUncertainty – decimal(20,10)
• CollectionDate – date
• DatingMethod – varchar(64)
• DatingMethodRemarks – text
• Date1 – date
• Date2 – date
• Number1 – decimal(20,10)
• Number2 – decimal(20,10)
• Remarks – text
• Text1 – text
• Text2 – text
• TimestampCreated – datetime (system field)
• TimestampModified – datetime (system field)
• YesNo1 – boolean
• YesNo2 – boolean

-- Relationship

• Agent1 – FK to agent
• CollectionObjectID – FK to collectionobject
• CreatedByAgent – FK to agent (system field)
• ModifiedByAgent – FK to agent (system field)

RelativeAge:

• RelativeAgeID – int(11) – PK
• AgeType – varchar(64) (from control vocab (sedimentation, metamorphic, inclusion, original, fall,...)
• AgeUncertainty – decimal(20,10)
• CollectionDate – date
• Date1 – date
• Date2 – date
• DatingMethod – varchar(64)
• DatingMethodRemarks – text
• Number1 – decimal(20,10)
• Number2 – decimal(20,10)
• RelativeAgePeriod – decimal(20,10)
• Remarks – text
• Text1 – text
• Text2 – text
• TimestampCreated – datetime (system field)
• TimestampModified – datetime (system field)
• VerbatimName – text
• VerbatimPeriod – text
• YesNo1 – boolean
• YesNo2 – boolean

-- Relationship

• AgeNameID – FK to geologictimeperiod (chronostrat tree)
• Agent1 – FK to agent
• Agent2 – FK to agent
• CollectionObjectID – FK to collectionobject
• CreatedByAgent – FK to agent

Age Attachment Tables

These tables allow us to link Specify attachment records directly to either type of age record. This follows the exact same format as the other ‘*Attachment’ records in Specify (e.g. CollectionObjectAttachment, AccessionAttachment, CollectingEventAttachment, etc.).

RelativeAgeAttachment

• RelativeAgeAttachmentID – int(11) NOT NULL AUTO_INCREMENT – PK
• Ordinal – int(11) NOT NULL
• Remarks – text DEFAULT NULL
• TimestampCreated – datetime NOT NULL
• TimestampModified – datetime DEFAULT NULL
• Version – int(11) DEFAULT NULL

-- Relationship

• AttachmentID – int(11) NOT NULL – FK to attachment
• CollectionMemberID – int(11) NOT NULL – FK to collection for scoping
• CreatedByAgentID – int(11) DEFAULT NULL – FK to agent
• ModifiedByAgentID – int(11) DEFAULT NULL – FK to agent
• RelativeAgeID – int(11) NOT NULL – FK to relativeAge

AbsoluteAgeAttachment

• AbsoluteAgeAttachmentID – int(11) NOT NULL AUTO_INCREMENT – PK
• Ordinal – int(11) NOT NULL
• Remarks – text DEFAULT NULL
• TimestampCreated – datetime NOT NULL
• TimestampModified – datetime DEFAULT NULL
• Version – int(11) DEFAULT NULL

-- Relationship

• AbsoluteAgeID – int(11) NOT NULL – FK to absoluteAge
• AttachmentID – int(11) NOT NULL – FK to attachment
• CollectionMemberID – int(11) NOT NULL – FK to collection for scoping
• CreatedByAgentID – int(11) DEFAULT NULL – FK to agent
• ModifiedByAgentID – int(11) DEFAULT NULL – FK to agent

Age Citation Tables

These tables allow us to link Specify ‘reference work’ records directly to either type of age record. This follows the exact same format as the other ‘*Citation records in Specify (e.g. CollectionObjectCitation, DeterminationCitation, TaxonCitation, etc.).

RelativeAgeCitation

• RelativeAgeCitationID – int(11) NOT NULL AUTO_INCREMENT – PK
• FigureNumber – varchar(50) DEFAULT NULL
• IsFigured – bit(1) DEFAULT NULL
• PageNumber – varchar(50) DEFAULT NULL
• PlateNumber – varchar(50) DEFAULT NULL
• Remarks – text DEFAULT NULL
• TimestampCreated – datetime NOT NULL
• TimestampModified – datetime DEFAULT NULL
• Version – int(11) DEFAULT NULL

-- Relationship

• CollectionMemberID – int(11) NOT NULL – FK to collection
• CreatedByAgentID – int(11) DEFAULT NULL – FK to agent
• ModifiedByAgentID – int(11) DEFAULT NULL – FK to agent
• ReferenceWorkID – int(11) NOT NULL – FK to referenceWork
• RelativeAgeID – int(11) NOT NULL – FK to relativeAge

AbsoluteAgeCitation

• AbsoluteAgeCitationID – int(11) NOT NULL AUTO_INCREMENT – PK
• FigureNumber – varchar(50) DEFAULT NULL
• IsFigured – bit(1) DEFAULT NULL
• PageNumber – varchar(50) DEFAULT NULL
• PlateNumber – varchar(50) DEFAULT NULL
• Remarks – text DEFAULT NULL
• TimestampCreated – datetime NOT NULL
• TimestampModified – datetime DEFAULT NULL
• Version – int(11) DEFAULT NULL

-- Relationship

• AbsoluteAgeID – int(11) NOT NULL – FK to relativeAge
• CollectionMemberID – int(11) NOT NULL – FK to collection
• CreatedByAgentID – int(11) DEFAULT NULL – FK to agent
• ModifiedByAgentID – int(11) DEFAULT NULL – FK to agent
• ReferenceWorkID – int(11) NOT NULL – FK to referenceWork

Modifications to Existing Tables

Paleo Context:

• New Fields:
  • TectonicUnitID (FK to TectonicUnit record)