Revision as of 10:11, 8 February 2015 (view source) WikiSysop (talk | contribs) (→‎Table: singlesequencing) ← Older edit		Latest revision as of 15:46, 28 September 2016 (view source) WikiSysop (talk | contribs) (→‎Entity-relation diagram of the DNA Module V2.0)
(39 intermediate revisions by the same user not shown)
Line 1:		Line 1:
	=Entity-relation diagram of the DNA Module V2.0=		=Entity-relation diagram of the DNA Module V2.0=
−	February 2014: We are currently updating this information!	+	September 2016: The new version will be called SaM (Sample Management). The empty sql script can be found at http://www.ggbn.org/ggbn_portal/documents/sam_dev.sql
	==Overview==		==Overview==
Line 836:		Line 836:
	|		|
	|decimal value of Latitude		|decimal value of Latitude
−	|-
−	|CollectionInfo_Other
−	|varchar(500)
−	|YES
−	|
−	|
−	|deprecated
	|-		|-
	|TypeStatus		|TypeStatus
Line 1,079:		Line 1,072:
	|		|
	|		|
−	|-
−	|Other
−	|varchar(500)
−	|YES
−	|
−	|
−	|Deprecated
	|-		|-
	|NameAuthorYear		|NameAuthorYear
Line 1,935:		Line 1,921:
	|		|
	|}		|}
		+
		+	===Table: labnumber===
		+
		+	tbd
	===Table: landusecode===		===Table: landusecode===
Line 2,864:		Line 2,854:
	|MUL		|MUL
	|		|
−	|FK to tisse sample/specimen ([[#Table:_cachecollection | table:cachecollection]])	+	|FK to tisse sample/specimen ([[#Table:_sptoolcollection | table:sptoolcollection]])
	|-		|-
	|FK_Preservations		|FK_Preservations
Line 2,940:		Line 2,930:
	|List of all schemata (ABCD, DwC)		|List of all schemata (ABCD, DwC)
	|-		|-
−	|SchemaFull	+	|Created_When
−	|varchar(50)	+	|timestamp
	|YES		|YES
	|		|
		+	|CURRENT_TIMESTAMP
	|		|
−	|Deprecated, to be removed
	|-		|-
−	|QueryPart1	+	|Created_Who
−	|varchar(2000)	+	|varchar(50)
−	|YES	+	|NO
	|		|
		+	|unknown
	|		|
−	|Deprecated, to be removed	+	|}
		+
		+	===Table: seasandoceans===
		+	Belongs to: [[#Specimen_Tool | Specimen Tool]]
		+
		+	'''SQL create statement filled with standardised content. Content should not be changed in general, especially when providing data to GGBN!'''
		+	{| border="1" width="95%"
		+	!width="28%" | Field
		+	!width="12%" | Type
		+	!width="6%" | Null
		+	!width="6%" | Key
		+	!width="20%" | Default
		+	!Comment
	|-		|-
−	|QueryPart1_1	+	|ID_SeasAndOceans
−	|varchar(200)	+	|int(10) unsigned
−	|YES	+	|NO
		+	|PRI
	|		|
−	|	+	|PK of all seas
−	|Deprecated, to be removed
	|-		|-
−	|QueryPart1_2	+	|SeasAndOceans
−	|varchar(2000)	+	|varchar(255)
−	|YES	+	|NO
	|		|
	|		|
−	|Deprecated, to be removed	+	|List of all seas
	|-		|-
−	|QueryPart1_2b	+	|Ocean
−	|varchar(2000)	+	|varchar(255)
−	|YES	+	|NO
	|		|
	|		|
−	|Deprecated, to be removed	+	|the larger ocean the sea belongs to
	|-		|-
−	|QueryPart1b	+	|Created_Who
−	|varchar(2000)	+	|varchar(50)
−	|YES	+	|NO
−	|
−	|
−	|Deprecated, to be removed
−	|-
−	|QueryPart2
−	|varchar(2000)
−	|YES
−	|
−	|
−	|Deprecated, to be removed
−	|-
−	|QueryPart2a
−	|varchar(2000)
−	|YES
−	|
−	|
−	|Deprecated, to be removed
−	|-
−	|QueryPart3
−	|varchar(2000)
−	|YES
−	|
−	|
−	|Deprecated, to be removed
−	|-
−	|QueryPart4
−	|varchar(2000)
−	|YES
−	|
−	|
−	|Deprecated, to be removed
−	|-
−	|QueryPart5
−	|varchar(2000)
−	|YES
	|		|
		+	|unknown
	|		|
−	|Deprecated, to be removed
	|-		|-
	|Created_When		|Created_When
	|timestamp		|timestamp
−	|YES	+	|NO
	|		|
	|CURRENT_TIMESTAMP		|CURRENT_TIMESTAMP
−	|
−	|-
−	|Created_Who
−	|varchar(50)
−	|NO
−	|
−	|unknown
	|		|
	|}		|}
−	===Table: seasandoceans===	+	===Table: sequencing===
−	Belongs to: [[#Specimen_Tool | Specimen Tool]]	+	Belongs to: [[#Sequence_data | Sequence data]], [[#DNA_extractions | DNA extractions]]
−	'''SQL create statement filled with standardised content. Content should not be changed in general, especially when providing data to GGBN!'''	+	'''At present not in use, coming soon.'''
	{| border="1" width="95%"		{| border="1" width="95%"
	!width="28%" | Field		!width="28%" | Field
Line 3,044:		Line 3,005:
	!Comment		!Comment
	|-		|-
−	|ID_SeasAndOceans	+	|ID_Sequencing
	|int(10) unsigned		|int(10) unsigned
	|NO		|NO
	|PRI		|PRI
	|		|
−	|PK of all seas	+	|PK of all sequencings
	|-		|-
−	|SeasAndOceans	+	|FK_Amplification
−	|varchar(255)	+	|int(10) unsigned
	|NO		|NO
		+	|MUL
	|		|
−	|	+	|FK to parent amplification event [[#Table:_amplifications | table:amplifications]]
−	|List of all seas
	|-		|-
−	|Ocean	+	|CloningDate
−	|varchar(255)	+	|datetime
−	|NO	+	|YES
	|		|
	|		|
−	|the larger ocean the sea belongs to	+	|Date of cloning (format: YYYY-MM-DD)
	|-		|-
−	|Created_Who	+	|FK_CloningStaff
−	|varchar(50)	+	|int(10) unsigned
−	|NO	+	|YES
		+	|MUL
	|		|
−	|unknown	+	|FK to person or company performing DNA cloning [[#Table:_people | table:people]]
		+	|-
		+	|FK_CloningMethod
		+	|int(10) unsigned
		+	|YES
		+	|MUL
	|		|
		+	|FK to used method or protocol table:cloningmethod
	|-		|-
−	|Created_When	+	|FK_CloningPrimerForward
−	|timestamp	+	|int(10) unsigned
−	|NO	+	|YES
		+	|MUL
	|		|
−	|CURRENT_TIMESTAMP	+	|FK to used forward primer [[#Table:_primer | table:primer]]
−	|
−	|}
−
−	===Table: sequencing===
−	Belongs to: [[#Sequence_data | Sequence data]], [[#DNA_extractions | DNA extractions]]
−
−	'''At present not in use, coming soon.'''
−	{| border="1" width="95%"
−	!width="28%" | Field
−	!width="12%" | Type
−	!width="6%" | Null
−	!width="6%" | Key
−	!width="20%" | Default
−	!Comment
	|-		|-
−	|ID_Sequencing	+	|FK_CloningPrimerReverse
	|int(10) unsigned		|int(10) unsigned
−	|NO	+	|YES
−	|PRI
−	|
−	|PK of all sequencings
−	|-
−	|FK_Amplification
−	|int(10) unsigned
−	|NO
	|MUL		|MUL
	|		|
−	|FK to parent amplification event [[#Table:_amplifications | table:amplifications]]	+	|FK to used reverse primer [[#Table:_primer | table:primer]]
	|-		|-
−	|CloningDate	+	|CloneStrain
−	|datetime	+	|varchar(50)
	|YES		|YES
	|		|
	|		|
−	|Date of cloning (format: YYYY-MM-DD)	+	|Name of the individual DNA clone
	|-		|-
−	|FK_CloningStaff	+	|ConsensusSequence
−	|int(10) unsigned	+	|varchar(1000)
	|YES		|YES
−	|MUL	+	|
−	|
−	|FK to person or company performing DNA cloning [[#Table:_people | table:people]]
−	|-
−	|FK_CloningMethod
−	|int(10) unsigned
−	|YES
−	|MUL
−	|
−	|FK to used method or protocol table:cloningmethod
−	|-
−	|FK_CloningPrimerForward
−	|int(10) unsigned
−	|YES
−	|MUL
−	|
−	|FK to used forward primer [[#Table:_primer | table:primer]]
−	|-
−	|FK_CloningPrimerReverse
−	|int(10) unsigned
−	|YES
−	|MUL
−	|
−	|FK to used reverse primer [[#Table:_primer | table:primer]]
−	|-
−	|CloneStrain
−	|varchar(50)
−	|YES
−	|
−	|
−	|Name of the individual DNA clone
−	|-
−	|ConsensusSequence
−	|varchar(1000)
−	|YES
−	|
	|		|
	|Consensus sequence derived from all individual sequences		|Consensus sequence derived from all individual sequences
Line 3,291:		Line 3,204:
	|MUL		|MUL
	|		|
−	|FK to parent sequencing event	+	|FK to parent sequencing event [[#Table:_sequencing | table:sequencing]]
	|-		|-
	|FK_Amplification		|FK_Amplification
Line 3,298:		Line 3,211:
	|MUL		|MUL
	|		|
−	|FK to parent amplification event	+	|FK to parent amplification event [[#Table:_amplifications | table:amplifications]]
	|-		|-
	|SequencingDate		|SequencingDate
Line 3,312:		Line 3,225:
	|MUL		|MUL
	|		|
−	|FK to person or company performing the single sequencing	+	|FK to person or company performing the single sequencing [[#Table:_people | table:people]]
	|-		|-
	|FK_SequencingMethod		|FK_SequencingMethod
Line 3,319:		Line 3,232:
	|MUL		|MUL
	|		|
−	|FK to used method or protocol	+	|FK to used method or protocol [[#Table:_people | table:people]]
	|-		|-
	|SingleSequence		|SingleSequence
Line 3,354:		Line 3,267:
	|MUL		|MUL
	|		|
−	|FK to primer used for the single sequencing	+	|FK to primer used for the single sequencing [[#Table:_primer | table:primer]]
	|-		|-
	|Created_Who		|Created_Who
Line 3,371:		Line 3,284:
	|}		|}
−	===Table: singlesequencingchromatograms===	+	===Table: slope===
−	Belongs to: [[#Sequence_data | Sequence data]]	+	Belongs to: [[#Specimen_Tool | Specimen Tool]]
−	'''At present not in use, coming soon.'''	+	'''SQL create statement filled with standardised content. Existing content should not be changed, but you can add further entries.'''
	{| border="1" width="95%"		{| border="1" width="95%"
−	!width="15%" | Column	+	!width="28%" | Field
−	!width="9%" | Type	+	!width="12%" | Type
−	!width="4%" | Null	+	!width="6%" | Null
−	!width="4%" | Key	+	!width="6%" | Key
	!width="20%" | Default		!width="20%" | Default
−	!Description	+	!Comment
	|-		|-
−	|ID_Chromatograms	+	|ID_Slope
−	|bigint	+	|int(10) unsigned
−	|No	+	|NO
	|PRI		|PRI
−	|AUTO_INCREMENT	+	|
−	|PK of all Chromatograms	+	|PK of all slope categories
	|-		|-
−	|ID_SingleSequencing	+	|Slope
−	|bigint	+	|varchar(50)
−	|No	+	|NO
−	|Key	+	|
−	|No default	+	|
−	|FK to parent single sequencing event ([[#Table:_singlesequencings | table:singlesequencings]])	+	|List of all slope categories
	|-		|-
−	|ID_Sequencing	+	|Created_Who
−	|bigint	+	|varchar(50)
−	|No	+	|NO
−	|Key	+	|
−	|No default	+	|unknown
−	|FK to parent sequencing event ([[#Table:_sequencings | table:sequencings]])	+	|
	|-		|-
−	|ID_Amplification	+	|Created_When
−	|bigint	+	|timestamp
−	|No	+	|NO
−	|Key
−	|No default
−	|FK to parent amplification event ([[#Table:_amplifications | table:amplifications]])
−	|-
−	|FileURI
−	|varchar(500)
−	|Yes
	|		|
−	|NULL	+	|CURRENT_TIMESTAMP
−	|Link to individual chromatogram
−	|-
−	|Remarks
−	|varchar(500)
−	|Yes
	|		|
−	|NULL
−	|Notes and remarks regarding individual chromatogram
	|}		|}
−	===Table: singlesequencing_primers===	+	===Table: soilph===
−	Belongs to: [[#Sequence_data | Sequence data]]	+	Belongs to: [[#Specimen_Tool | Specimen Tool]]
−	'''At present not in use, coming soon.'''	+	'''SQL create statement filled with standardised content. Existing content should not be changed, but you can add further entries.'''
	{| border="1" width="95%"		{| border="1" width="95%"
−	!width="15%" | Column	+	!width="28%" | Field
−	!width="9%" | Type	+	!width="12%" | Type
−	!width="4%" | Null	+	!width="6%" | Null
−	!width="4%" | Key	+	!width="6%" | Key
	!width="20%" | Default		!width="20%" | Default
−	!Description	+	!Comment
	|-		|-
−	|ID_SingleSequencing	+	|ID_SoilpH
−	|bigint	+	|int(10) unsigned
−	|No	+	|NO
		+	|PRI
	|		|
−	|No default	+	|PK of all soil pH categories
−	|FK to Single sequencing event ([[#Table:_singlesequencing | table:singlesequencing]])
	|-		|-
−	|ID_Primer	+	|SoilpH
−	|bigint	+	|varchar(50)
−	|No	+	|NO
		+	|
	|		|
−	|No default	+	|List of all soil pH categories
−	|FK to used primer ([[#Table:_primer | table:primer]])
−	|}
−
−	===Table: slope===
−	Belongs to: [[#Specimen_Tool | Specimen Tool]]
−	{| border="1" width="95%"
−	!width="15%" | Column
−	!width="9%"  | Type
−	!width="4%"  | Null
−	!width="4%"  | Key
−	!width="20%" | Default
−	!Description
	|-		|-
−	|ID_Slope	+	|Created_Who
−	|int	+	|varchar(50)
−	|No	+	|NO
−	|PRI	+	|
−	|AUTO_INCREMENT	+	|unknown
−	|PK of all slope categories	+	|
	|-		|-
−	|Slope	+	|Created_When
−	|varchar(50)	+	|timestamp
−	|Yes	+	|NO
		+	|
		+	|CURRENT_TIMESTAMP
	|		|
−	|NULL
−	|List of slope categories
	|}		|}
−	===Table: soilph===	+	===Table: soiltexture===
	Belongs to: [[#Specimen_Tool | Specimen Tool]]		Belongs to: [[#Specimen_Tool | Specimen Tool]]
−	'''Create statement filled with standardised content. Existing content should not be changed, but you can add further entries.'''	+	'''SQL create statement filled with standardised content. Existing content should not be changed, but you can add further entries.'''
	{| border="1" width="95%"		{| border="1" width="95%"
−	!width="28%" | Column	+	!width="28%" | Field
−	!width="12%" | Type	+	!width="12%" | Type
−	!width="6%" | Null	+	!width="6%" | Null
−	!width="6%" | Key	+	!width="6%" | Key
	!width="20%" | Default		!width="20%" | Default
−	!Description	+	!Comment
	|-		|-
−	|ID_SoilpH	+	|ID_SoilTexture
−	|int	+	|int(10) unsigned
−	|No	+	|NO
	|PRI		|PRI
−	|AUTO_INCREMENT
−	|PK of all soil pH categories
−	|-
−	|SoilpH
−	|varchar(50)
−	|Yes
	|		|
−	|NULL
−	|List of all soil pH categories
−	|}
−
−	===Table: soiltexture===
−	Belongs to: [[#Specimen_Tool | Specimen Tool]]
−
−	'''Create statement filled with standardised content. Existing content should not be changed, but you can add further entries.'''
−	{| border="1" width="95%"
−	!width="28%" | Column
−	!width="12%" | Type
−	!width="6%"  | Null
−	!width="6%"  | Key
−	!width="20%" | Default
−	!Description
−	|-
−	|ID_SoilTexture
−	|int
−	|No
−	|PRI
−	|AUTO_INCREMENT
	|PK of all soil texture categories		|PK of all soil texture categories
	|-		|-
	|SoilTexture		|SoilTexture
	|varchar(50)		|varchar(50)
−	|Yes	+	|NO
		+	|
	|		|
−	|NULL
	|List of all soil texture categories		|List of all soil texture categories
		+	|-
		+	|Created_Who
		+	|varchar(50)
		+	|NO
		+	|
		+	|unknown
		+	|
		+	|-
		+	|Created_When
		+	|timestamp
		+	|NO
		+	|
		+	|CURRENT_TIMESTAMP
		+	|
	|}		|}
Line 3,535:		Line 3,410:
	Belongs to: [[#Specimen_Tool | Specimen Tool]]		Belongs to: [[#Specimen_Tool | Specimen Tool]]
−	'''Create statement filled with standardised content. Content should not be changed in general, especially when providing data to GBIF or the DNA Bank Network!'''	+	'''SQL create statement filled with standardised content. Content should not be changed in general, especially when providing data to GBIF or the DNA Bank Network!'''
	{| border="1" width="95%"		{| border="1" width="95%"
−	!width="15%" | Column	+	!width="28%" | Field
−	!width="9%" | Type	+	!width="12%" | Type
−	!width="4%" | Null	+	!width="6%" | Null
−	!width="4%" | Key	+	!width="6%" | Key
	!width="20%" | Default		!width="20%" | Default
−	!Description	+	!Comment
	|-		|-
	|ID_SpecimenType		|ID_SpecimenType
−	|int	+	|int(10) unsigned
−	|No	+	|NO
	|PRI		|PRI
−	|AUTO_INCREMENT	+	|
−	|PK of all sort of types	+	|PK of all sort of type status
	|-		|-
	|SpecimenType		|SpecimenType
	|varchar(50)		|varchar(50)
−	|No	+	|NO
		+	|
		+	|
		+	|List of all sort of type status
		+	|-
		+	|Created_When
		+	|timestamp
		+	|NO
		+	|
		+	|CURRENT_TIMESTAMP
		+	|
		+	|-
		+	|Created_Who
		+	|varchar(50)
		+	|NO
		+	|
		+	|unknown
	|		|
−	|No default
−	|List of all sort of types
	|}		|}
	===Table: sptoolassociatedunits===		===Table: sptoolassociatedunits===
	Belongs to: [[#Specimen_Tool | Specimen Tool]], [[#Specimen_data_providers | Specimen data providers]]		Belongs to: [[#Specimen_Tool | Specimen Tool]], [[#Specimen_data_providers | Specimen data providers]]
−
	{| border="1" width="95%"		{| border="1" width="95%"
−	!width="15%" | Column	+	!width="28%" | Field
−	!width="9%" | Type	+	!width="12%" | Type
−	!width="4%" | Null	+	!width="6%" | Null
−	!width="4%" | Key	+	!width="6%" | Key
	!width="20%" | Default		!width="20%" | Default
−	!Description	+	!Comment
	|-		|-
	|ID_Association		|ID_Association
−	|bigint	+	|int(10) unsigned
−	|No	+	|NO
	|PRI		|PRI
−	|AUTO_INCREMENT	+	|
	|PK of all associated specimens and observations		|PK of all associated specimens and observations
	|-		|-
−	|ID_Collection	+	|FK_Collection
−	|bigint	+	|int(10) unsigned
−	|No	+	|NO
−	|Key	+	|MUL
−	|No default	+	|
	|FK to parent specimen/observation (collection event) ([[#Table:_sptoolcollection | table:sptoolcollection]])		|FK to parent specimen/observation (collection event) ([[#Table:_sptoolcollection | table:sptoolcollection]])
	|-		|-
−	|UnitID_Main	+	|FK_Relation
−	|varchar(100)	+	|int(10) unsigned
−	|Yes	+	|YES
		+	|MUL
	|		|
−	|NULL	+	|FK to relation, describing relation between main unit and associated unit ([[#Table:_relation | table:relation]])
−	|GBIF-Identifier of main unit, mostly Barcode or Catalogue Number (part of triple ID), same as in [[#Table:_sptoolcollection | table:sptoolcollection]]
	|-		|-
−	|CollectionCode_Main	+	|UnitID
	|varchar(100)		|varchar(100)
−	|Yes	+	|YES
	|		|
−	|NULL	+	|
−	|GBIF-Identifier of main unit, Institution of specimen deposit (part of triple ID), same as in [[#Table:_sptoolcollection | table:sptoolcollection]]	+	|GBIF-Identifier of associated unit, UnitID or Catalogue Number (part of triple ID)
	|-		|-
−	|InstitutionCode_Main	+	|CollectionCode
	|varchar(100)		|varchar(100)
−	|Yes	+	|YES
	|		|
−	|NULL	+	|
−	|GBIF-Identifier of main unit, Institution of specimen deposit (part of triple ID), same as in [[#Table:_sptoolcollection | table:sptoolcollection]]	+	|GBIF-Identifier of associated unit, CollectionCode or SourceID (part of triple ID)
	|-		|-
−	|RelationToMainUnit	+	|InstitutionCode
	|varchar(100)		|varchar(100)
−	|Yes	+	|YES
	|		|
−	|NULL
	|		|
		+	|GBIF-Identifier of associated unit, InstitutionCode or SourceInstitutionID (part of triple ID)
	|-		|-
−	|UnitID	+	|FK_BasisOfRecord
−	|varchar(100)	+	|int(10) unsigned
−	|Yes	+	|YES
		+	|MUL
	|		|
−	|NULL	+	|FK to basisofrecord ([[#Table:_basisofrecordspecimen | table:basisofrecordspecimen]])
−	|GBIF-Identifier of associated unit, mostly Barcode or Catalogue Number (part of triple ID)
	|-		|-
−	|CollectionCode	+	|FK_Dataset
−	|varchar(100)	+	|int(10) unsigned
−	|Yes	+	|YES
		+	|MUL
	|		|
−	|NULL	+	|FK to dataset ([[#Table:_dataset | table:dataset]])
−	|GBIF-Identifier of associated unit, Collection of specimen deposit (part of triple ID)
	|-		|-
−	|InstitutionCode	+	|Notes
−	|varchar(100)	+	|varchar(200)
−	|Yes	+	|YES
		+	|
	|		|
−	|NULL	+	|Remarks on a certain associated record
−	|GBIF-Identifier of associated unit, Institution of specimen deposit (part of triple ID)
	|-		|-
−	|KindOfUnit	+	|Created_When
−	|varchar(100)	+	|timestamp
−	|Yes	+	|NO
		+	|
		+	|CURRENT_TIMESTAMP
	|		|
−	|NULL
−	|description of preservation or type of object
	|-		|-
−	|FormerCollection	+	|Created_Who
	|varchar(100)		|varchar(100)
−	|Yes	+	|NO
		+	|
		+	|unknown
	|		|
−	|NULL
−	|former collection of the specimen
	|-		|-
−	|ID_BasisOfRecord	+	|Updated_When
−	|bigint	+	|timestamp
−	|Yes	+	|YES
−	|Key	+	|
−	|NULL	+	|
−	|FK to record basis of individual associated specimen/observation ([[#Table:_basisofrecordspecimen | table:basisofrecordspecimen]])	+	|
	|-		|-
−	|ID_Dataset	+	|Updated_Who
−	|bigint	+	|varchar(100)
−	|Yes	+	|YES
−	|Key	+	|
−	|NULL	+	|
−	|FK to specimen data provider, if record is avalaibla via GBIF compliant database ([[#Table:_dataset | table:dataset]])
−	|-
−	|Notes
−	|varchar(200)
−	|Yes
	|		|
−	|NULL
−	|Notes/remarkes regarding the individual associated specimen/observation
	|}		|}
	===Table: sptoolcollection===		===Table: sptoolcollection===
	Belongs to: [[#Specimen_Tool | Specimen Tool]]		Belongs to: [[#Specimen_Tool | Specimen Tool]]
−
	{| border="1" width="95%"		{| border="1" width="95%"
−	!width="15%" | Column	+	!width="28%" | Field
−	!width="9%" | Type	+	!width="12%" | Type
−	!width="4%" | Null	+	!width="6%" | Null
−	!width="4%" | Key	+	!width="6%" | Key
	!width="20%" | Default		!width="20%" | Default
−	!Description	+	!Comment
	|-		|-
	|ID_Collection		|ID_Collection
−	|bigint	+	|int(10) unsigned
−	|No	+	|NO
	|PRI		|PRI
−	|AUTO_INCREMENT	+	|
	|PK of all specimens and observations		|PK of all specimens and observations
	|-		|-
	|UnitID_Specimen		|UnitID_Specimen
	|varchar(100)		|varchar(100)
−	|No	+	|NO
−	|Key	+	|MUL
−	|No default	+	|
−	|GBIF-Identifier of main unit, mostly Barcode or Catalogue Number (part of triple ID)	+	|(GBIF)-Identifier of main unit (part of triple ID)
	|-		|-
	|CollectionCode_Specimen		|CollectionCode_Specimen
−	|varchar(100)	+	|varchar(200)
−	|No	+	|NO
		+	|
	|		|
−	|No default	+	|(GBIF)-Identifier of main unit (part of triple ID)
−	|GBIF-Identifier of main unit, Institution of specimen deposit (part of triple ID)
	|-		|-
	|InstitutionCode_Specimen		|InstitutionCode_Specimen
−	|varchar(100)	+	|varchar(200)
−	|No	+	|NO
		+	|
	|		|
−	|No default	+	|(GBIF)-Identifier of main unit (part of triple ID)
−	|GBIF-Identifier of main unit, Institution of specimen deposit (part of triple ID)
	|-		|-
−	|FormerCollection	+	|FK_BasisOfRecord
−	|varchar(100)	+	|int(10) unsigned
−	|Yes	+	|YES
		+	|MUL
	|		|
−	|NULL	+	|FK to ([[#Table:_basisofrecordspecimen | table:basisofrecordspecimen]])
−	|former collection of the specimen
	|-		|-
−	|ID_BiogeographicRegion	+	|FK_KindOfUnit
−	|bigint	+	|int(10) unsigned
−	|Yes	+	|YES
−	|Key	+	|MUL
−	|NULL	+	|
−	|FK to [[#Table:_biogeographicregion | table:biogeographicregion]]	+	|FK to ([[#Table:_kindofunit | table:kindofunit]])
	|-		|-
−	|ID_Country	+	|PrepType
−	|bigint	+	|varchar(150)
−	|Yes	+	|YES
−	|Key	+	|
−	|NULL	+	|
−	|FK to [[#Table:_countryisocode | table:countryisocode]]	+	|Preparation Type, e.g. blood, leaf
	|-		|-
−	|ID_SeasAndOceans	+	|PrepDate
−	|bigint	+	|varchar(50)
−	|Yes	+	|YES
−	|Key	+	|
−	|NULL	+	|
−	|FK to [[#Table:_seasandoceans | table:seasandoceans]]	+	|Preparation Date
	|-		|-
		+	|FK_PrepStaff
		+	|int(10) unsigned
		+	|YES
		+	|MUL
		+	|
		+	|FK to person or company who did the preparation, often the collector ([[#Table:_people | table:people]])
	|-		|-
−	|Collectors	+	|PrepMaterials
−	|varchar(200)	+	|varchar(1000)
−	|Yes	+	|YES
		+	|
	|		|
−	|NULL	+	|Materials used for the preparation
−	|Collection Team
	|-		|-
−	|CollectionDateMin	+	|PrepProcess
−	|datetime	+	|varchar(1000)
−	|Yes	+	|YES
		+	|
	|		|
−	|NULL	+	|Individual steps of the preparation, e.g. SPREC-code
−	|Collection Date from (format: YYYY-MM-DD)
	|-		|-
−	|CollectionDateMax	+	|FK_AcquiredFrom
−	|datetime	+	|int(10) unsigned
−	|Yes	+	|YES
		+	|MUL
	|		|
−	|NULL	+	|FK to person or company who has provided the sample/specimen ([[#Table:_people | table:people]])
−	|Collection Date to (format: YYYY-MM-DD)
	|-		|-
−	|CollectionYear	+	|AccessionNo
	|varchar(50)		|varchar(50)
−	|Yes	+	|YES
		+	|
	|		|
−	|NULL	+	|Primary accession number
−	|Collection Year (generated when record has been saved)
	|-		|-
−	|CollectorsNo	+	|AccessionName
	|varchar(100)		|varchar(100)
−	|Yes	+	|YES
	|		|
−	|NULL
−	|field number of a specimen given by collector (in the field)
−	|-
−	|CollectionNotes
−	|varchar(400)
−	|Yes
	|		|
−	|NULL	+	|Primary accession name
−	|Notes and remarks regarding the collection event itself
	|-		|-
−	|CollectionMethod	+	|FK_BiogeographicRegion
−	|varchar(400)	+	|int(10) unsigned
−	|Yes	+	|YES
		+	|MUL
	|		|
−	|NULL	+	|FK to ([[#Table:_biogegraphicregion | table:biogeographicregion]])
−	|Method of collecting
	|-		|-
−	|Locality_ID	+	|FK_Country
−	|varchar(150)	+	|int(10) unsigned
−	|Yes	+	|YES
		+	|MUL
	|		|
−	|NULL	+	|FK to ([[#Table:_countryisocode | table:countryisocode]])
−	|number/name of collection site, e.g. in a river or a plot name/number
	|-		|-
−	|ExpeditionDetails	+	|FK_SeasAndOceans
−	|varchar(300)	+	|int(10) unsigned
−	|Yes	+	|YES
		+	|MUL
	|		|
−	|NULL	+	|FK to ([[#Table:_seasandoceans | table:seasandoceans]])
−	|Project or Expedition details concerning collection event
	|-		|-
−	|Region	+	|Collectors
	|varchar(500)		|varchar(500)
−	|Yes	+	|YES
	|		|
−	|NULL	+	|
−	|district or island name	+	|Collection Team
	|-		|-
−	|Locality	+	|CollectionDateMin
−	|varchar(500)	+	|datetime
−	|Yes	+	|YES
		+	|
	|		|
−	|NULL	+	|Collection Date from (format: YYYY-MM-DD)
−	|free text of locality information (below district level)
	|-		|-
−	|Habitat	+	|CollectionDateMax
−	|varchar(100)	+	|datetime
−	|Yes	+	|YES
		+	|
	|		|
−	|NULL	+	|Collection Date to (format: YYYY-MM-DD)
−	|free text of habitat information concerning collection site
	|-		|-
−	|ID_SoilTexture	+	|CollectionYear
−	|bigint	+	|varchar(50)
−	|Yes	+	|YES
−	|Key	+	|
−	|NULL	+	|
−	|FK to [[#Table:_soiltexture | table:soiltexture]]	+	|Collection Year (generated when record has been saved)
	|-		|-
−	|ID_SoilpH	+	|CollectorsNo
−	|bigint	+	|varchar(100)
−	|Yes	+	|YES
−	|Key
−	|NULL
−	|FK to [[#Table:_soilph | table:soilph]]
−	|-
−	|ID_HabitatCode
−	|bigint
−	|Yes
−	|Key
−	|NULL
−	|FK to [[#Table:_habitatcode | table:habitatcode]]
−	|-
−	|ID_LandUseCode
−	|bigint
−	|Yes
−	|Key
−	|NULL
−	|FK to [[#Table:_landusecode | table:landusecode]]
−	|-
−	|ID_Slope
−	|bigint
−	|Yes
−	|Key
−	|NULL
−	|FK to [[#Table:_slope | table:slope]]
−	|-
−	|ID_Aspect
−	|bigint
−	|Yes
−	|Key
−	|NULL
−	|FK to [[#Table:_prevalentaspect | table:prevalentaspect]]
−	|-
−	|DepthAccuracyStatement
−	|varchar(50)
−	|Yes
	|		|
−	|NULL
−	|Statement of accuracy of measurement
−	|-
−	|DepthMin
−	|varchar(50)
−	|Yes
	|		|
−	|NULL	+	|Field number of a specimen given by collector (in the field)
−	|lower or only value of measurement
	|-		|-
−	|DepthMax	+	|CollectionNotes
−	|varchar(50)	+	|varchar(400)
−	|Yes	+	|YES
	|		|
−	|NULL
−	|upper value of measurement
−	|-
−	|DepthUnit
−	|varchar(50)
−	|Yes
	|		|
−	|NULL	+	|Notes and remarks regarding the collection event itself
−	|unit of measurement
	|-		|-
−	|DepthMethod	+	|CollectionMethod
−	|varchar(50)	+	|varchar(400)
−	|Yes	+	|YES
	|		|
−	|NULL
−	|method of measurement
−	|-
−	|DepthRaw
−	|varchar(50)
−	|Yes
	|		|
−	|NULL	+	|Method of collecting
−	|original label data
	|-		|-
−	|AltitudeAccuracyStatement	+	|Locality_ID
−	|varchar(50)	+	|varchar(150)
−	|Yes	+	|YES
	|		|
−	|NULL
−	|Statement of accuracy of measurement
−	|-
−	|AltitudeMin
−	|varchar(50)
−	|Yes
	|		|
−	|NULL	+	|Number/name of collection site, e.g. in a river or a plot name/number
−	|lower or only value of measurement
	|-		|-
−	|AltitudeMax	+	|ExpeditionDetails
−	|varchar(50)	+	|varchar(300)
−	|Yes	+	|YES
	|		|
−	|NULL
−	|upper value of measurement
−	|-
−	|AltitudeUnit
−	|varchar(50)
−	|Yes
	|		|
−	|NULL	+	|Project or Expedition details concerning collection event
−	|unit of measurement
	|-		|-
−	|AltitudeMethod	+	|Region
−	|varchar(50)	+	|varchar(500)
−	|Yes	+	|YES
	|		|
−	|NULL
−	|method of measurement
−	|-
−	|AltitudeRaw
−	|varchar(50)
−	|Yes
	|		|
−	|NULL	+	|District or island name
−	|original label data
	|-		|-
−	|CoordinatesAccuracyStatement	+	|Locality
−	|varchar(50)	+	|varchar(500)
−	|Yes	+	|YES
	|		|
−	|NULL
−	|statement of degree of degree of accuracy
−	|-
−	|CoordinatesErrorDistance
−	|varchar(50)
−	|Yes
	|		|
−	|NULL	+	|Free text of locality information (below district level)
−	|An estimate of how tightly the collecting locality was specified; expressed as a distance in meters corresponding to a radius around the Lat/Long coordinates.
	|-		|-
−	|LongitudeMin	+	|Habitat
−	|varchar(50)	+	|varchar(100)
−	|Yes	+	|YES
	|		|
−	|NULL
−	|lower or only value expressed in decimal degrees
−	|-
−	|LongitudeMax
−	|varchar(50)
−	|Yes
	|		|
−	|NULL	+	|Free text of habitat information concerning collection site
−	|upper value expressed in decimal degrees
	|-		|-
−	|LatitudeMin	+	|FK_SoilTexture
−	|varchar(50)	+	|int(10) unsigned
−	|Yes	+	|YES
		+	|MUL
	|		|
−	|NULL	+	|FK to ([[#Table:_soiltexture | table:soiltexture]])
−	|lower or only value expressed in decimal degrees
	|-		|-
−	|LatitudeMax	+	|FK_SoilpH
−	|varchar(50)	+	|int(10) unsigned
−	|Yes	+	|YES
		+	|MUL
	|		|
−	|NULL	+	|FK to ([[#Table:_soilph | table:soilph]])
−	|upper value expressed in decimal degrees
	|-		|-
−	|LongitudeRaw	+	|FK_HabitatCode
−	|varchar(50)	+	|int(10) unsigned
−	|Yes	+	|YES
		+	|MUL
	|		|
−	|NULL	+	|FK to ([[#Table:_habitatcode | table:habitatcode]])
−	|original label data
	|-		|-
−	|LatitudeRaw	+	|FK_LandUseCode
−	|varchar(50)	+	|int(10) unsigned
−	|Yes	+	|YES
		+	|MUL
	|		|
−	|NULL	+	|FK to ([[#Table:_landusecode | table:landusecode]])
−	|original label data
	|-		|-
−	|GeodeticDatum	+	|FK_Slope
−	|varchar(50)	+	|int(10) unsigned
−	|Yes	+	|YES
		+	|MUL
	|		|
−	|NULL	+	|FK to ([[#Table:_slope | table:slope]])
−	|e.g. "WGS84"
	|-		|-
−	|ID_GeocodeMethod	+	|FK_Aspect
−	|int	+	|int(10) unsigned
−	|Yes	+	|YES
−	|Key	+	|MUL
−	|NULL	+	|
−	|FK to [[#Table:_geocodemethod | table:geocodemethod]]	+	|FK ([[#Table:_prevalentaspect | table:prevalentaspect]])
	|-		|-
−	|ID_Sex	+	|DepthAccuracyStatement
−	|bigint	+	|varchar(50)
−	|Yes	+	|YES
−	|Key
−	|NULL
−	|FK to [[#Table:_sex | table:sex]]
−	|-
−	|Protected
−	|varchar(20)
−	|No
	|		|
−	|No
−	|values "No", "Yes", "Unknown"
−	|-
−	|Permission
−	|varchar(20)
−	|No
	|		|
−	|NotRequired	+	|Statement of accuracy of measurement
−	|values "No", "Yes", "Unknown", "NotRequired"
	|-		|-
−	|Blocked	+	|DepthMin
−	|varchar(20)	+	|varchar(50)
−	|No	+	|YES
	|		|
−	|No
−	|values "No", "Yes", "Unknown"
−	|-
−	|ID_BasisOfRecord
−	|bigint
−	|Yes
−	|Key
−	|NULL
−	|FK to [[#Table:_basisofrecordspecimen | table:basisofrecordspecimen]]
−	|-
−	|KindOfUnit
−	|varchar(150)
−	|Yes
	|		|
−	|NULL	+	|Lower or only value of measurement
−	|description of preservation or type of object
	|-		|-
−	|ID_Cache	+	|DepthMax
−	|bigint	+	|varchar(50)
−	|Yes	+	|YES
−	|Key	+	|
−	|NULL
−	|FK to [[#Table:_cachecollection | table:cachecollection]]
−	|-
−	|ID_Type
−	|bigint
−	|Yes
−	|Key
−	|NULL
−	|FK to [[#Table:_specimentype | table:specimentype]]
−	|-
−	|Notes
−	|varchar(400)
−	|Yes
	|		|
−	|NULL	+	|Upper value of measurement
−	|Notes/remarks concerning specimen/observation in general
	|-		|-
−	|AccessionNo	+	|DepthUnit
	|varchar(50)		|varchar(50)
−	|Yes	+	|YES
	|		|
−	|NULL
−	|Primary accession number by specimen deposit
−	|-
−	|AccessionName
−	|varchar(100)
−	|Yes
	|		|
−	|NULL	+	|Unit of measurement
−	|Primary identification (e.g. "Pieris sp.") by specimen deposit
	|-		|-
−	|LifeStage	+	|DepthMethod
	|varchar(50)		|varchar(50)
−	|Yes	+	|YES
	|		|
−	|NULL	+	|
−	|Free text describing life stage of specimen/observation	+	|Method of measurement
	|-		|-
−	|LifeForm	+	|DepthRaw
	|varchar(50)		|varchar(50)
−	|Yes	+	|YES
		+	|
	|		|
−	|NULL	+	|Original label data
−	|Free text describing life form of specimen/observation
	|-		|-
−	|PrepType	+	|AltitudeAccuracyStatement
	|varchar(50)		|varchar(50)
−	|Yes	+	|YES
	|		|
−	|NULL
−	|Free text describing preservation method
−	|-
−	|PrepDate
−	|datetime
−	|Yes
	|		|
−	|NULL	+	|Statement of accuracy of measurement
−	|Date of preparation, (format: YYYY-MM-DD)
	|-		|-
−	|PrepStaff	+	|AltitudeMin
	|varchar(50)		|varchar(50)
−	|Yes	+	|YES
	|		|
−	|NULL
−	|Person who did preparation
−	|-
−	|SampleGroup
−	|varchar(15)
−	|No
	|		|
−	|'Specimen'	+	|Lower or only value of measurement
−	|either 'Specimen' or 'MixedSample'
	|-		|-
−	|GUID	+	|AltitudeMax
	|varchar(50)		|varchar(50)
−	|Yes	+	|YES
	|		|
−	|NULL	+	|
−	|GUID generated with PHP, if UnitID is empty UnitID = GUID	+	|Upper value of measurement
	|-		|-
−	|Multimedia	+	|AltitudeUnit
−	|varchar(10)	+	|varchar(50)
−	|Yes	+	|YES
		+	|
	|		|
−	|NULL	+	|Unit of measurement
−	|= 'Yes' when [[#Table:_sptoolmultimedia | table:sptoolmultimedia]] filled with content
	|-		|-
−	|VoucherRefText	+	|AltitudeMethod
−	|varchar(1000)	+	|varchar(50)
−	|Yes	+	|YES
	|		|
−	|NULL
−	|Citation format of publication where voucher is cited
−	|-
−	|VoucherRefUrl
−	|varchar(1000)
−	|Yes
	|		|
−	|NULL	+	|Method of measurement
−	|Url of publication (if available)
	|-		|-
−	|VoucherRefDoi	+	|AltitudeRaw
−	|varchar(200)	+	|varchar(50)
−	|Yes	+	|YES
	|		|
−	|NULL
−	|DOI of publication (format e.g. "10.1000/182" without "doi:" or "http://...")
−	|}
−
−	===Table: sptoolecology===
−	Belongs to: [[#Specimen_Tool | Specimen Tool]]
−
−	{| border="1" width="95%"
−	!width="15%" | Column
−	!width="9%"  | Type
−	!width="4%"  | Null
−	!width="4%"  | Key
−	!width="20%" | Default
−	!Description
−	|-
−	|ID_Ecology
−	|bigint
−	|No
−	|PRI
−	|AUTO_INCREMENT
−	|PK of all ecological facts
−	|-
−	|ID_Collection
−	|bigint
−	|Yes
−	|Key
−	|NULL
−	|FK to parent Collection event ([[#Table:_sptoolcollection | table:sptoolcollection]])
−	|-
−	|Value
−	|varchar(20)
−	|Yes
	|		|
−	|NULL	+	|Original label data
−	|value of measurement
	|-		|-
−	|Unit	+	|CoordinatesAccuracyStatement
−	|varchar(20)	+	|varchar(50)
−	|Yes	+	|YES
	|		|
−	|NULL
−	|Unit of measurement
−	|-
−	|Parameter
−	|varchar(20)
−	|Yes
	|		|
−	|NULL	+	|Statement of accuracy of measurement
−	|Name of measured parameter
	|-		|-
−	|Method	+	|CoordinatesErrorDistance
−	|varchar(20)	+	|varchar(50)
−	|Yes	+	|YES
		+	|
	|		|
−	|NULL	+	|An estimate of how tightly the collecting locality was specified; expressed as a distance in meters corresponding to a radius around the Lat/Long coordinates
−	|method used for measurement
−	|}
−
−	===Table: sptoolhighertaxon===
−	Belongs to: [[#Specimen_Tool | Specimen Tool]]
−
−	{| border="1" width="95%"
−	!width="15%" | Column
−	!width="9%"  | Type
−	!width="4%"  | Null
−	!width="4%"  | Key
−	!width="20%" | Default
−	!Description
	|-		|-
−	|ID_HigherTaxon	+	|LongitudeMin
−	|bigint	+	|varchar(50)
−	|No	+	|YES
−	|PRI	+	|
−	|AUTO_INCREMENT	+	|
−	|PK of all higher taxa	+	|Lower or only value expressed in decimal degrees
	|-		|-
−	|ID_Taxa	+	|LongitudeMax
−	|bigint	+	|varchar(50)
−	|Yes	+	|YES
−	|Key	+	|
−	|NULL	+	|
−	|FK to parent determination ([[#Table:_sptooltaxa | table:sptooltaxa]])	+	|Upper value expressed in decimal degrees
	|-		|-
−	|HigherTaxon	+	|LatitudeMin
−	|varchar(255)	+	|varchar(50)
−	|Yes	+	|YES
		+	|
	|		|
−	|NULL	+	|Lower or only value expressed in decimal degrees
−	|Name of higher taxon
	|-		|-
−	|TaxonRank	+	|LatitudeMax
−	|varchar(20)	+	|varchar(50)
−	|Yes	+	|YES
		+	|
	|		|
−	|NULL	+	|Upper value expressed in decimal degrees
−	|Rank of higher taxon
	|-		|-
−	|Synecology	+	|LongitudeRaw
−	|varchar(20)	+	|varchar(50)
−	|Yes	+	|YES
	|		|
−	|NULL
−	|='host' if taxon ist host species of specimen
−	|}
−
−	===Table: sptoolmultimedia===
−	Belongs to: [[#Specimen_Tool | Specimen Tool]]
−
−	{| border="1" width="95%"
−	!width="15%" | Column
−	!width="9%"  | Type
−	!width="4%"  | Null
−	!width="4%"  | Key
−	!width="20%" | Default
−	!Description
−	|-
−	|ID_Multimedia
−	|bigint
−	|No
−	|PRI
−	|AUTO_INCREMENT
−	|PK of all multimedia items
−	|-
−	|ID_Collection
−	|bigint
−	|Yes
−	|Key
−	|NULL
−	|FK to parent collection event ([[#Table:_sptoolcollection | table:sptoolcollection]])
−	|-
−	|File_Path
−	|varchar(500)
−	|Yes
	|		|
−	|NULL	+	|Original label data
−	|path/url of multimedia file
	|-		|-
−	|File_Type	+	|LatitudeRaw
−	|varchar(20)	+	|varchar(50)
−	|Yes	+	|YES
	|		|
−	|NULL
−	|type of multimedia item (video, sound, image etc.)
−	|-
−	|File_Created_When
−	|datetime
−	|Yes
	|		|
−	|NULL	+	|Original label data
−	|date and time when multimedia file has been created (format YYYY-MM-DD hh:mm:ss)
	|-		|-
−	|File_Created_Who	+	|GeodeticDatum
	|varchar(50)		|varchar(50)
−	|Yes	+	|YES
		+	|
	|		|
−	|NULL	+	|e.g. WGS84
−	|creator of multimedia file
	|-		|-
−	|File_Context	+	|FK_GeocodeMethod
−	|varchar(500)	+	|int(10) unsigned
−	|Yes	+	|YES
		+	|MUL
	|		|
−	|NULL	+	|FK to ([[#Table:_geocodemethod | table:geocodemethod]])
−	|comments/notes concerning individual multimedia file
	|-		|-
−	|File_Comment	+	|FK_Sex
−	|varchar(500)	+	|int(10) unsigned
−	|Yes	+	|YES
		+	|MUL
	|		|
−	|NULL	+	|FK to ([[#Table:_sex | table:sex]])
−	|The context of the object in relation to the specimen or observation. E.g. image of entire specimen, sound recording the observation is based on, image of original valid publication, etc.
	|-		|-
−	|File_Group	+	|EscapedCoordinates
−	|varchar(50)	+	|varchar(20)
−	|Yes	+	|YES
	|		|
−	|NULL
−	|='specimen' if multimedia item belongs directly to the specimen; ='site' if multimedia item belongs to collection site
−	|}
−
−	===Table: sptooltaxa===
−	Belongs to: [[#Specimen_Tool | Specimen Tool]]
−
−	{| border="1" width="95%"
−	!width="15%" | Column
−	!width="9%"  | Type
−	!width="4%"  | Null
−	!width="4%"  | Key
−	!width="20%" | Default
−	!Description
−	|-
−	|ID_TaxonIdentified
−	|bigint
	|No		|No
−	|PRI	+	|='Yes' if coordinates for a certain record have to be escaped
−	|AUTO_INCREMENT
−	|PK of all determinations
	|-		|-
−	|ID_Collection	+	|Permission
−	|bigint	+	|varchar(20)
−	|Yes	+	|YES
−	|Key	+	|
−	|NULL	+	|NotRequired
−	|FK to parent collection event ([[#Table:_sptoolcollection | table:sptoolcollection]])	+	|turn into fk and list
	|-		|-
−	|Genus	+	|Source_Gone
−	|varchar(255)	+	|int(10)
−	|Yes	+	|YES
	|		|
−	|NULL
	|		|
		+	|='1' if source material is gone
	|-		|-
−	|Subgenus	+	|Blocked_Until
−	|varchar(255)	+	|datetime
−	|Yes	+	|YES
	|		|
−	|NULL
	|		|
		+	|date until sample is blocked for delivery (format YYYY-MM-DD)
	|-		|-
−	|SpecificEpithet	+	|Blocked_For_Loaning
−	|varchar(255)	+	|int(10)
−	|Yes	+	|YES
	|		|
−	|NULL
	|		|
		+	|='1' if sample/specimen is blocked for delivery only; this record will be visible in the Network's data portal
	|-		|-
−	|InfraspecificEpithet	+	|Blocked_For_Publishing
−	|varchar(155)	+	|int(10)
−	|Yes	+	|YES
	|		|
−	|NULL
	|		|
		+	|='1' if sample/specimen is blocked in general; this record won't be visible in the Network's data portal
	|-		|-
−	|Rank	+	|Loan_Conditions
−	|varchar(50)	+	|varchar(500)
−	|Yes	+	|YES
	|		|
−	|NULL
	|		|
		+	|Certain conditions for loaning the DNA sample
	|-		|-
−	|AuthorParenth	+	|FK_Cache
−	|varchar(100)	+	|int(10) unsigned
−	|Yes	+	|YES
		+	|MUL
	|		|
−	|NULL	+	|FK to ([[#Table:_cachecollection | table:cachecollection]]), if used for DNA too
−	|basionym author team
	|-		|-
−	|YearParenth	+	|FK_Type
−	|varchar(50)	+	|int(10) unsigned
−	|Yes	+	|YES
		+	|MUL
	|		|
−	|NULL	+	|FK to ([[#Table:_specimentype | table:specimentype]])
−	|basionym year
	|-		|-
−	|Author	+	|TypeDesignation
−	|varchar(100)	+	|varchar(500)
−	|Yes	+	|YES
	|		|
−	|NULL
	|		|
		+	|Type designation if specimen/sample is type material
	|-		|-
−	|Year	+	|Notes
−	|varchar(50)	+	|varchar(500)
−	|Yes	+	|YES
	|		|
−	|NULL
	|		|
		+	|Notes/remarks concerning specimen/observation in general
	|-		|-
−	|NameAuthorYear	+	|Notes_Internal
	|varchar(500)		|varchar(500)
−	|Yes	+	|YES
	|		|
−	|NULL	+	|
−	|Full Scientific Name String including Author teams and years	+	|Notes/remarks concerning specimen/observation for internal display
	|-		|-
−	|KindOfIdentification	+	|LifeStage
−	|varchar(20)	+	|varchar(50)
−	|Yes	+	|YES
	|		|
−	|NULL
−	|="det.", "confirm." or "rev."
−	|-
−	|Identifier
−	|varchar(100)
−	|Yes
	|		|
−	|NULL	+	|Free text describing life stage of specimen/observation
−	|determinator team
	|-		|-
−	|IdentificationDate	+	|LifeForm
	|varchar(50)		|varchar(50)
−	|Yes	+	|YES
	|		|
−	|NULL	+	|
−	|free text, often month or year only	+	|Free text describing life form of specimen/observation
−	|-	+	|-
−	|PreferredFlag	+	|SampleGroup
		+	|varchar(15)
		+	|YES
		+	|
		+	|
		+	|Either 'Specimen' or 'MixedSample'
		+	|-
		+	|GUID
	|varchar(50)		|varchar(50)
−	|Yes	+	|YES
	|		|
−	|NULL	+	|
−	|='Yes' if determination is preferred	+	|GUID generated with PHP, if UnitID is empty UnitID_Specimen = GUID
	|-		|-
−	|IdentificationNotes	+	|Multimedia
−	|varchar(500)	+	|varchar(10)
−	|Yes	+	|YES
		+	|
		+	|
		+	| = 'Yes' when table:sptoolmultimedia filled with content
		+	|-
		+	|FK_Strat_Series
		+	|int(10) unsigned
		+	|YES
		+	|MUL
	|		|
−	|NULL	+	|FK to ([[#Table:_stratigraphy_series | table:stratigraphy_series]])
−	|Notes/Remarks concerning individual determination
	|-		|-
−	|Synecology	+	|Qualifier_Series
	|varchar(50)		|varchar(50)
−	|Yes	+	|YES
	|		|
−	|NULL	+	|
−	|='host' if taxon ist host species of specimen	+	|Qualifier of Series
−	|}
−
−	===Table: stock_box===
−	Belongs to: [[#Locations_stock.2Faliquots | Locations stock/aliquot]]
−	{| border="1" width="95%"
−	!width="15%" | Column
−	!width="9%"  | Type
−	!width="4%"  | Null
−	!width="4%"  | Key
−	!width="20%" | Default
−	!Description
	|-		|-
−	|ID_Stock_Box	+	|FK_Strat_Stage
−	|int	+	|int(10) unsigned
−	|No	+	|YES
−	|PRI	+	|MUL
−	|AUTO_INCREMENT	+	|
−	|PK of listed stock boxes	+	|FK to ([[#Table:_stratigraphy_stage | table:stratigraphy_stage]])
	|-		|-
−	|Stock_Box	+	|Qualifier_Stage
	|varchar(50)		|varchar(50)
−	|Yes	+	|YES
	|		|
−	|NULL	+	|
−	|List of boxes used for stock ([[#Table:_location_stock | table:location_stock]])	+	|Qualifier of Stage
−	|}
−
−	===Table: stock_fridge===
−	Belongs to: [[#Locations_stock.2Faliquots | Locations stock/aliquot]]
−	{| border="1" width="95%"
−	!width="15%" | Column
−	!width="9%"  | Type
−	!width="4%"  | Null
−	!width="4%"  | Key
−	!width="20%" | Default
−	!Description
	|-		|-
−	|ID_Stock_Fridge	+	|FossilZone
−	|int	+	|varchar(150)
−	|No	+	|YES
−	|PRI	+	|
−	|AUTO_INCREMENT	+	|
−	|PK of listed stock fridges	+	|Name of Fossil Zone
	|-		|-
−	|Stock_Fridge	+	|Formation
−	|varchar(50)	+	|varchar(150)
−	|Yes	+	|YES
		+	|
	|		|
−	|NULL	+	|Name of Formation
−	|List of fridges used for stock ([[#Table:_location_stock | table:location_stock]])
−	|}
−
−	===Table: stock_rack===
−	Belongs to: [[#Locations_stock.2Faliquots | Locations stock/aliquot]]
−	{| border="1" width="95%"
−	!width="15%" | Column
−	!width="9%"  | Type
−	!width="4%"  | Null
−	!width="4%"  | Key
−	!width="20%" | Default
−	!Description
	|-		|-
−	|ID_Stock_Rack	+	|SectionDetails
−	|int	+	|varchar(500)
−	|No	+	|YES
−	|PRI	+	|
−	|AUTO_INCREMENT	+	|
−	|PK of listed stock racks	+	|Details about the section
	|-		|-
−	|Stock_Rack	+	|SpecimenDetails
−	|varchar(50)	+	|varchar(1000)
−	|Yes	+	|YES
		+	|
	|		|
−	|NULL	+	|Details about the specimen, e.g. which bones has been found
−	|List of racks used for stock ([[#Table:_location_stock | table:location_stock]])
−	|}
−
−	===Table: tissue===
−	Belongs to: [[#DNA_extractions | DNA extractions]]
−	{| border="1" width="95%"
−	!width="15%" | Column
−	!width="9%"  | Type
−	!width="4%"  | Null
−	!width="4%"  | Key
−	!width="20%" | Default
−	!Description
	|-		|-
−	|ID_Tissue	+	|FK_ParentID_Collection
−	|int	+	|int(10) unsigned
−	|No	+	|YES
−	|PRI	+	|MUL
−	|AUTO_INCREMENT	+	|
−	|PK of all tissue types	+	|FK to ([[#Table:_sptoolcollection | table:sptoolcollection]])
	|-		|-
−	|Tissue	+	|FK_RelationToParent
−	|varchar(255)	+	|int(10) unsigned
−	|Yes	+	|YES
		+	|MUL
	|		|
−	|NULL	+	|FK to ([[#Table:_relation | table:relation]])
−	|List of all tissue types
−	|}
−
−	===Table: user===
−	Belongs to: [[#User_management | User management]]
−	{| border="1" width="95%"
−	!width="15%" | Column
−	!width="9%"  | Type
−	!width="4%"  | Null
−	!width="4%"  | Key
−	!width="20%" | Default
−	!Description
	|-		|-
−	|ID_User	+	|Created_When
−	|int	+	|timestamp
−	|No	+	|NO
−	|PRI	+	|
−	|AUTO_INCREMENT	+	|CURRENT_TIMESTAMP
−	|PK of all users	+	|
	|-		|-
−	|Login	+	|Created_Who
	|varchar(50)		|varchar(50)
−	|No	+	|NO
−	|UNIQUE	+	|
−	|No default	+	|unknown
−	|List of login names	+	|
	|-		|-
−	|password	+	|Updated_When
−	|varchar(50)	+	|timestamp
−	|No	+	|YES
		+	|
		+	|
	|		|
−	|No default
−	|md5 encoded password
	|-		|-
−	|Signature	+	|Updated_Who
	|varchar(50)		|varchar(50)
−	|No	+	|YES
		+	|
		+	|
	|		|
−	|No default
−	|real name of individual user
	|-		|-
−	|Email	+	|Lock_Who
−	|varchar(100)	+	|varchar(50)
−	|No	+	|YES
		+	|
	|		|
−	|No default	+	|Locked while editing
−	|user email
	|-		|-
−	|ID_Group	+	|Lock_When
−	|tinyint	+	|timestamp
−	|Yes	+	|YES
		+	|
	|		|
−	|NULL	+	|Locked while editing
−	|FK to [[#Table:_usergroups | table:usergroups]]
	|}		|}
−	===Table: usergroups===	+	===Table: sptoolgeneticaccessions===
−	Belongs to: [[#User_management | User management]]	+	Belongs to: [[#Specimen_Tool | Specimen Tool]]
		+
		+	This table stores information on genetic accession numbers that are related to tissue samples or specimens but not related to your own DNA samples.
	{| border="1" width="95%"		{| border="1" width="95%"
−	!width="15%" | Column	+	!width="28%" | Field
−	!width="9%" | Type	+	!width="12%" | Type
−	!width="4%" | Null	+	!width="6%" | Null
−	!width="4%" | Key	+	!width="6%" | Key
	!width="20%" | Default		!width="20%" | Default
−	!Description	+	!Comment
	|-		|-
−	|ID_Groups	+	|ID_GeneticAccession
−	|int	+	|int(10) unsigned
−	|No	+	|NO
	|PRI		|PRI
−	|AUTO_INCREMENT
−	|PK of all user groups
−	|-
−	|Group
−	|varchar(50)
−	|Yes
	|		|
−	|NULL	+	|PK of all genetic accession numbers related to a tissue/specimen
−	|Group name
	|-		|-
−	|ConfigTool	+	|FK_Collection
−	|varchar(50)	+	|int(10) unsigned
−	|Yes	+	|NO
−	|	+	|MUL
−	|NULL	+	|0
−	|	+	|FK to tissue sample/specimen ([[#Table:_sptoolcollection | table:sptoolcollection]])
	|-		|-
−	|CreatedOther	+	|FK_GeneticLocus
−	|varchar(50)	+	|int(10) unsigned
−	|Yes	+	|NO
−	|	+	|MUL
−	|NULL	+	|0
		+	|FK to ([[#Table:_geneticlocus | table:geneticlocus]])
		+	|-
		+	|GenBankNumber
		+	|varchar(100)
		+	|YES
	|		|
		+	|
		+	|List of genetic accession numbers (EMBL, NCBI, DDBJ)
	|-		|-
−	|CreatedSelf	+	|SampleID
−	|varchar(50)	+	|varchar(100)
−	|Yes	+	|YES
	|		|
−	|NULL
	|		|
		+	|Additional sample number, often provided via EMBL/NCBI/DDBJ
	|-		|-
−	|BlockGeneral	+	|BOLD
−	|varchar(50)	+	|varchar(20)
−	|Yes	+	|YES
	|		|
−	|NULL
	|		|
		+	|List of BOLD accession numbers
	|-		|-
−	|BlockTemp	+	|Created_Who
	|varchar(50)		|varchar(50)
−	|Yes	+	|NO
	|		|
−	|NULL	+	|unknown
	|		|
	|-		|-
−	|AvailableDNA	+	|Created_When
−	|varchar(50)	+	|timestamp
−	|Yes	+	|NO
	|		|
−	|NULL	+	|CURRENT_TIMESTAMP
	|		|
	|-		|-
−	|NotesInternal	+	|Updated_Who
	|varchar(50)		|varchar(50)
−	|Yes	+	|YES
		+	|
	|		|
−	|NULL
	|		|
	|-		|-
−	|NotesExternal	+	|Updated_When
−	|varchar(50)	+	|timestamp
−	|Yes	+	|YES
		+	|
	|		|
−	|NULL
	|		|
	|}		|}
−	===Table: usersettings===	+	===Table: sptoolhighertaxon===
−	Belongs to: [[#User_management | User management]]	+	Belongs to: [[#Specimen_Tool | Specimen Tool]]
	{| border="1" width="95%"		{| border="1" width="95%"
−	!width="15%" | Column	+	!width="28%" | Field
−	!width="9%" | Type	+	!width="12%" | Type
−	!width="4%" | Null	+	!width="6%" | Null
−	!width="4%" | Key	+	!width="6%" | Key
	!width="20%" | Default		!width="20%" | Default
−	!Description	+	!Comment
	|-		|-
−	|ID_Setting	+	|ID_HigherTaxon
−	|int	+	|int(10) unsigned
−	|No	+	|NO
	|PRI		|PRI
−	|AUTO_INCREMENT	+	|
−	|PK of all user settings	+	|PK of all higher taxa
		+	|-
		+	|FK_TaxonIdentified
		+	|int(10) unsigned
		+	|NO
		+	|MUL
		+	|
		+	|FK to parent determination ([[#Table:_sptooltaxa | table:sptooltaxa]])
	|-		|-
−	|ID_User	+	|HigherTaxon
−	|int	+	|varchar(255)
−	|No	+	|YES
−	|Key	+	|
−	|No default	+	|
−	|FK to [[#Table:_user | table:user]]	+	|Name of higher taxon
	|-		|-
−	|RecordsPerPage	+	|TaxonRank
−	|float	+	|varchar(255)
−	|Yes	+	|YES
		+	|
	|		|
−	|NULL	+	|Rank of higher taxon
−	|records per page hitlist (Search Tool)
	|-		|-
−	|Sorting	+	|Synecology
−	|varchar(50)	+	|varchar(255)
−	|Yes	+	|YES
		+	|
	|		|
−	|NULL	+	|='host' if taxon ist host species of specimen
−	|sorting of hitlist (Search Tool)
	|-		|-
−	|RecordsPerpageSP	+	|Created_When
−	|float	+	|timestamp
−	|Yes	+	|NO
		+	|
		+	|CURRENT_TIMESTAMP
	|		|
−	|NULL
−	|records per page hitlist (Specimen Tool)
	|-		|-
−	|SortingSP	+	|Created_Who
	|varchar(50)		|varchar(50)
−	|Yes	+	|NO
		+	|
		+	|unknown
		+	|
		+	|-
		+	|Updated_When
		+	|timestamp
		+	|YES
		+	|
		+	|
		+	|
		+	|-
		+	|Updated_Who
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|
		+	|}
		+
		+	===Table: sptoolmeasurementorfact===
		+	Belongs to: [[#Specimen_Tool | Specimen Tool]]
		+	{| border="1" width="95%"
		+	!width="28%" | Field
		+	!width="12%" | Type
		+	!width="6%" | Null
		+	!width="6%" | Key
		+	!width="20%" | Default
		+	!Comment
		+	|-
		+	|ID_Measurement
		+	|int(20) unsigned
		+	|NO
		+	|PRI
		+	|
		+	|PK of all facts
		+	|-
		+	|FK_Collection
		+	|int(20) unsigned
		+	|YES
		+	|MUL
		+	|
		+	|FK to parent Collection event ([[#Table:_sptoolcollection | table:sptoolcollection]])
		+	|-
		+	|Value
		+	|varchar(200)
		+	|YES
		+	|
		+	|
		+	|Value of measurement
		+	|-
		+	|Unit
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|Unit of measurement
		+	|-
		+	|Parameter
		+	|varchar(100)
		+	|YES
		+	|
		+	|
		+	|Name of measured parameter
		+	|-
		+	|Method
		+	|varchar(200)
		+	|YES
		+	|
		+	|
		+	|Method used for measurement
		+	|-
		+	|Rank
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|Fact rank/group, e.g. "ecology", "morphology"
		+	|-
		+	|Created_When
		+	|timestamp
		+	|NO
		+	|
		+	|CURRENT_TIMESTAMP
		+	|
		+	|-
		+	|Created_Who
		+	|varchar(50)
		+	|NO
		+	|
		+	|unknown
		+	|
		+	|-
		+	|Updated_When
		+	|timestamp
		+	|YES
		+	|
		+	|
		+	|
		+	|-
		+	|Updated_Who
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|
		+	|}
		+
		+	===Table: sptoolmultimedia===
		+	Belongs to: [[#Specimen_Tool | Specimen Tool]]
		+	{| border="1" width="95%"
		+	!width="28%" | Field
		+	!width="12%" | Type
		+	!width="6%" | Null
		+	!width="6%" | Key
		+	!width="20%" | Default
		+	!Comment
		+	|-
		+	|ID_Multimedia
		+	|int(10) unsigned
		+	|NO
		+	|PRI
		+	|
		+	|PK of all multimedia items
		+	|-
		+	|FK_Collection
		+	|int(10) unsigned
		+	|NO
		+	|MUL
		+	|
		+	|FK to parent collection event ([[#Table:_sptoolcollection | table:sptoolcollection]])
		+	|-
		+	|File_Path
		+	|varchar(500)
		+	|NO
		+	|
		+	|
		+	|Path/url of multimedia file
		+	|-
		+	|File_Type
		+	|varchar(20)
		+	|NO
		+	|
		+	|
		+	|Type of multimedia item (video, sound, image etc.)
		+	|-
		+	|File_Created_When
		+	|datetime
		+	|YES
		+	|
		+	|
		+	|Date and time when multimedia file has been created (format YYYY-MM-DD hh:mm:ss)
		+	|-
		+	|File_Created_Who
		+	|varchar(100)
		+	|NO
		+	|
		+	|
		+	|Creator of multimedia file
		+	|-
		+	|File_Context
		+	|varchar(500)
		+	|NO
		+	|
		+	|
		+	|Comments/notes concerning individual multimedia file
		+	|-
		+	|File_Comment
		+	|varchar(500)
		+	|NO
		+	|
		+	|
		+	|The context of the object in relation to the specimen or observation. E.g. image of entire specimen, sound recording the observation is based on, image of original valid publication, etc.
		+	|-
		+	|File_Group
		+	|varchar(50)
		+	|NO
		+	|MUL
		+	|
		+	|='specimen' if multimedia item belongs directly to the specimen; ='site' if multimedia item belongs to collection site
		+	|-
		+	|File_License
		+	|varchar(50)
		+	|NO
		+	|MUL
		+	|
		+	|Lincense for multimedia item
		+	|-
		+	|Created_When
		+	|timestamp
		+	|NO
		+	|
		+	|CURRENT_TIMESTAMP
		+	|
		+	|-
		+	|Created_Who
		+	|varchar(50)
		+	|NO
		+	|
		+	|unknown
		+	|
		+	|-
		+	|Updated_When
		+	|timestamp
		+	|YES
		+	|
		+	|
		+	|
		+	|-
		+	|Updated_Who
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|
		+	|}
		+
		+	===Table: sptoolreferences===
		+	Belongs to: [[#Specimen_Tool | Specimen Tool]]
		+	{| border="1" width="95%"
		+	!width="28%" | Field
		+	!width="12%" | Type
		+	!width="6%" | Null
		+	!width="6%" | Key
		+	!width="20%" | Default
		+	!Comment
		+	|-
		+	|ID_Reference
		+	|int(10) unsigned
		+	|NO
		+	|PRI
		+	|
		+	|PK of all references related to an observation or specimen/tissue sample
		+	|-
		+	|FK_Collection
		+	|int(10) unsigned
		+	|YES
		+	|MUL
		+	|
		+	|FK to parent collection event ([[#Table:_sptoolcollection | table:sptoolcollection]])
		+	|-
		+	|FK_References
		+	|int(10) unsigned
		+	|YES
		+	|MUL
		+	|
		+	|FK to ([[#Table:_reference | table:reference]])
		+	|-
		+	|ReferenceDetail
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|Page detail used for citation
		+	|-
		+	|Created_When
		+	|timestamp
		+	|NO
		+	|
		+	|CURRENT_TIMESTAMP
		+	|
		+	|-
		+	|Created_Who
		+	|varchar(50)
		+	|NO
		+	|
		+	|unknown
		+	|
		+	|-
		+	|Updated_When
		+	|timestamp
		+	|YES
		+	|
		+	|
		+	|
		+	|-
		+	|Updated_Who
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|
		+	|}
		+
		+	===Table: sptooltaxa===
		+	Belongs to: [[#Specimen_Tool | Specimen Tool]]
		+	{| border="1" width="95%"
		+	!width="28%" | Field
		+	!width="12%" | Type
		+	!width="6%" | Null
		+	!width="6%" | Key
		+	!width="20%" | Default
		+	!Comment
		+	|-
		+	|ID_TaxonIdentified
		+	|int(10) unsigned
		+	|NO
		+	|PRI
		+	|
		+	|PK of all determinations
		+	|-
		+	|FK_Collection
		+	|int(10) unsigned
		+	|NO
		+	|MUL
		+	|
		+	|FK to parent collection event ([[#Table:_sptoolcollection | table:sptoolcollection]])
		+	|-
		+	|Qualifier
		+	|varchar(50)
		+	|NO
		+	|
		+	|
		+	|e.g. cf.
		+	|-
		+	|QualifierBelongsTo
		+	|varchar(50)
		+	|NO
		+	|
		+	|
		+	|Tank rank qualifier belongs to
		+	|-
		+	|Genus
		+	|varchar(255)
		+	|YES
		+	|
		+	|
		+	|
		+	|-
		+	|Subgenus
		+	|varchar(255)
		+	|YES
		+	|
		+	|
		+	|
		+	|-
		+	|SpecificEpithet
		+	|varchar(255)
		+	|YES
		+	|
		+	|
		+	|
		+	|-
		+	|InfraspecificEpithet
		+	|varchar(255)
		+	|YES
		+	|
		+	|
		+	|
		+	|-
		+	|Rank
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|
		+	|-
		+	|AuthorParenth
		+	|varchar(100)
		+	|YES
		+	|
		+	|
		+	|Basionym author team
		+	|-
		+	|YearParenth
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|Basionym year
		+	|-
		+	|Author
		+	|varchar(100)
		+	|YES
		+	|
		+	|
		+	|
		+	|-
		+	|Year
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|
		+	|-
		+	|NameAddendum
		+	|varchar(50)
		+	|NO
		+	|
		+	|
		+	|e.g. s.l., s.str.
		+	|-
		+	|NameAuthorYear
		+	|varchar(500)
		+	|YES
		+	|
		+	|
		+	|Full Scientific Name String including Author teams and years
		+	|-
		+	|KindOfIdentification
		+	|varchar(20)
		+	|YES
		+	|
		+	|
		+	|="det.", "confirm." or "rev."
		+	|-
		+	|Identifier
		+	|varchar(100)
		+	|YES
		+	|
		+	|
		+	|Person or Team that identified the specimen
		+	|-
		+	|IdentificationDate
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|Free text, often month or year only
		+	|-
		+	|PreferredFlag
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|='Yes' if identification is preferred
		+	|-
		+	|IdentificationNotes
		+	|varchar(500)
		+	|YES
		+	|
		+	|
		+	|Notes/Remarks on an individual identification
		+	|-
		+	|Synecology
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|='host' if taxon ist host species of specimen
		+	|-
		+	|Created_When
		+	|timestamp
		+	|NO
		+	|
		+	|CURRENT_TIMESTAMP
		+	|
		+	|-
		+	|Created_Who
		+	|varchar(50)
		+	|NO
		+	|
		+	|unknown
		+	|
		+	|-
		+	|Updated_When
		+	|timestamp
		+	|YES
		+	|
		+	|
		+	|
		+	|-
		+	|Updated_Who
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|
		+	|}
		+
		+	===Table: stock_box===
		+	Belongs to: [[#Locations_stock.2Faliquots | Locations stock/aliquot]]
		+	{| border="1" width="95%"
		+	!width="28%" | Field
		+	!width="12%" | Type
		+	!width="6%" | Null
		+	!width="6%" | Key
		+	!width="20%" | Default
		+	!Comment
		+	|-
		+	|ID_Stock_Box
		+	|int(10) unsigned
		+	|NO
		+	|PRI
		+	|
		+	|PK of listed stock boxes
		+	|-
		+	|Stock_Box
		+	|varchar(50)
		+	|NO
		+	|
		+	|
		+	|List of boxes used for stock ([[#Table:_location_stock | table:location_stock]])
		+	|-
		+	|Created_When
		+	|timestamp
		+	|NO
		+	|
		+	|CURRENT_TIMESTAMP
		+	|
		+	|-
		+	|Created_Who
		+	|varchar(50)
		+	|NO
		+	|
		+	|unknown
		+	|
		+	|}
		+
		+	===Table: stock_fridge===
		+	Belongs to: [[#Locations_stock.2Faliquots | Locations stock/aliquot]]
		+	{| border="1" width="95%"
		+	!width="28%" | Field
		+	!width="12%" | Type
		+	!width="6%" | Null
		+	!width="6%" | Key
		+	!width="20%" | Default
		+	!Comment
		+	|-
		+	|ID_Stock_Fridge
		+	|int(10) unsigned
		+	|NO
		+	|PRI
		+	|
		+	|PK of listed stock fridges
		+	|-
		+	|Stock_Fridge
		+	|varchar(50)
		+	|NO
		+	|
		+	|
		+	|List of fridges used for stock ([[#Table:_location_stock | table:location_stock]])
		+	|-
		+	|Created_When
		+	|timestamp
		+	|NO
		+	|
		+	|CURRENT_TIMESTAMP
		+	|
		+	|-
		+	|Created_Who
		+	|varchar(50)
		+	|NO
		+	|
		+	|unknown
		+	|
		+	|}
		+
		+	===Table: stock_rack===
		+	Belongs to: [[#Locations_stock.2Faliquots | Locations stock/aliquot]]
		+	{| border="1" width="95%"
		+	!width="28%" | Field
		+	!width="12%" | Type
		+	!width="6%" | Null
		+	!width="6%" | Key
		+	!width="20%" | Default
		+	!Comment
		+	|-
		+	|ID_Stock_Fridge
		+	|int(10) unsigned
		+	|NO
		+	|PRI
		+	|
		+	|PK of listed stock fridges
		+	|-
		+	|Stock_Fridge
		+	|varchar(50)
		+	|NO
		+	|
		+	|
		+	|List of fridges used for stock ([[#Table:_location_stock | table:location_stock]])
		+	|-
		+	|Created_When
		+	|timestamp
		+	|NO
		+	|
		+	|CURRENT_TIMESTAMP
		+	|
		+	|-
		+	|Created_Who
		+	|varchar(50)
		+	|NO
		+	|
		+	|unknown
		+	|
		+	|}
		+
		+	===Table: stratigraphy_series===
		+	Belongs to: [[#Specimen_Tool | Specimen Tool]]
		+	{| border="1" width="95%"
		+	!width="28%" | Field
		+	!width="12%" | Type
		+	!width="6%" | Null
		+	!width="6%" | Key
		+	!width="20%" | Default
		+	!Comment
		+	|-
		+	|ID_Strat_Series
		+	|int(10) unsigned
		+	|NO
		+	|PRI
		+	|
		+	|PK of all series (stratigraphy)
		+	|-
		+	|Series
		+	|varchar(50)
		+	|NO
		+	|
		+	|
		+	|List of all series (stratigraphy)
		+	|}
		+
		+	===Table: stratigraphy_stage===
		+	Belongs to: [[#Specimen_Tool | Specimen Tool]]
		+	{| border="1" width="95%"
		+	!width="28%" | Field
		+	!width="12%" | Type
		+	!width="6%" | Null
		+	!width="6%" | Key
		+	!width="20%" | Default
		+	!Comment
		+	|-
		+	|ID_Strat_Stage
		+	|int(10) unsigned
		+	|NO
		+	|PRI
		+	|
		+	|PK of all stages (Stratigraphy)
		+	|-
		+	|Stage
		+	|varchar(50)
		+	|NO
		+	|
		+	|
		+	|List of all stages (Stratigraphy)
		+	|-
		+	|FK_Strat_Series
		+	|int(10) unsigned
		+	|NO
		+	|MUL
		+	|
		+	|FK to parent series ([[#Table:_stratigraphy_series | table:stratigraphy_series]])
		+	|}
		+
		+	===Table: subfragment===
		+	Belongs to: [[#Sequence_data | Sequence data]], [[#DNA_extractions | DNA extractions]]
		+	{| border="1" width="95%"
		+	!width="28%" | Field
		+	!width="12%" | Type
		+	!width="6%" | Null
		+	!width="6%" | Key
		+	!width="20%" | Default
		+	!Comment
		+	|-
		+	|ID_Subfragment
		+	|int(10) unsigned
		+	|NO
		+	|PRI
		+	|
		+	|PK of all subfragments
		+	|-
		+	|Subfragment
		+	|varchar(100)
		+	|YES
		+	|
		+	|
		+	|List of all subfragments
		+	|-
		+	|SubfragmentRemarks
		+	|varchar(500)
		+	|YES
		+	|
		+	|
		+	|Remarks on a certain subfragment
		+	|-
		+	|Created_Who
		+	|varchar(100)
		+	|NO
		+	|
		+	|unknown
		+	|
		+	|-
		+	|Created_When
		+	|timestamp
		+	|NO
		+	|
		+	|CURRENT_TIMESTAMP
		+	|
		+	|-
		+	|Updated_Who
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|
		+	|-
		+	|Updated_When
		+	|timestamp
		+	|YES
		+	|
		+	|
		+	|
		+	|}
		+
		+	===Table: user===
		+	Belongs to: [[#User_management | User management]]
		+	{| border="1" width="95%"
		+	!width="28%" | Field
		+	!width="12%" | Type
		+	!width="6%" | Null
		+	!width="6%" | Key
		+	!width="20%" | Default
		+	!Comment
		+	|-
		+	|ID_User
		+	|int(10) unsigned
		+	|NO
		+	|PRI
		+	|
		+	|PK of all users
		+	|-
		+	|Login
		+	|varchar(50)
		+	|NO
		+	|UNI
		+	|
		+	|List of login names
		+	|-
		+	|Password
		+	|varchar(50)
		+	|NO
		+	|
		+	|
		+	|md5 encoded password
		+	|-
		+	|Signature
		+	|varchar(50)
		+	|NO
		+	|
		+	|
		+	|real name of individual user
		+	|-
		+	|Email
		+	|varchar(100)
		+	|NO
		+	|
		+	|
		+	|user email
		+	|-
		+	|FK_Group
		+	|int(10) unsigned
		+	|YES
		+	|MUL
		+	|
		+	|FK to [[#Table:_usergroups | table:usergroups]]
		+	|-
		+	|Created_Who
		+	|varchar(50)
		+	|NO
		+	|
		+	|unknown
		+	|
		+	|-
		+	|Created_When
		+	|timestamp
		+	|YES
		+	|
		+	|CURRENT_TIMESTAMP
		+	|
		+	|}
		+
		+	===Table: usergroups===
		+	Belongs to: [[#User_management | User management]]
		+	{| border="1" width="95%"
		+	!width="28%" | Field
		+	!width="12%" | Type
		+	!width="6%" | Null
		+	!width="6%" | Key
		+	!width="20%" | Default
		+	!Comment
		+	|-
		+	|ID_Group
		+	|int(10) unsigned
		+	|NO
		+	|PRI
		+	|
		+	|PK of all user groups
		+	|-
		+	|Group
		+	|varchar(50)
		+	|NO
		+	|
		+	|
		+	|Group name
		+	|-
		+	|Created_Who
		+	|varchar(50)
		+	|NO
		+	|
		+	|unknown
		+	|
		+	|-
		+	|Created_When
		+	|timestamp
		+	|NO
		+	|
		+	|CURRENT_TIMESTAMP
		+	|
		+	|}
		+
		+	===Table: usersettings===
		+	Belongs to: [[#User_management | User management]]
		+	{| border="1" width="95%"
		+	!width="28%" | Field
		+	!width="12%" | Type
		+	!width="6%" | Null
		+	!width="6%" | Key
		+	!width="20%" | Default
		+	!Comment
		+	|-
		+	|ID_Setting
		+	|int(10) unsigned
		+	|NO
		+	|PRI
		+	|
		+	|PK of all user settings
		+	|-
		+	|FK_User
		+	|int(10) unsigned
		+	|NO
		+	|MUL
		+	|
		+	|FK to [[#Table:_user | table:user]]
		+	|-
		+	|RecordsPerPage
		+	|float
		+	|YES
		+	|
		+	|
		+	|Records per page hitlist (Search Tool)
		+	|-
		+	|Sorting
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|Sorting of hitlist (Search Tool)
		+	|-
		+	|MainMMPath
		+	|varchar(500)
		+	|YES
		+	|
		+	|
		+	|Main Multimedia Path (Specimen Tool)
		+	|-
		+	|MainMMCreator
		+	|varchar(100)
		+	|YES
		+	|
		+	|
		+	|Main Multimedia Creator (Specimen Tool)
		+	|-
		+	|MainMMType
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|Main Multimedia Type (Specimen Tool)
		+	|-
		+	|MainMMLicense
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|Main Multimedia License (Specimen Tool)
		+	|-
		+	|MainMMUseEXIF
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|if 'Yes' use EXIF information (Specimen Tool)
		+	|-
		+	|CollDataSameAsMM
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|if 'Yes' use Multimedia Creator und Date als Collector and Collection Date
		+	|-
		+	|RecordsPerPageSP
		+	|float
		+	|YES
		+	|
		+	|
		+	|Records per page hitlist (Specimen Tool)
		+	|-
		+	|SortingSP
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|Sorting of hitlist (Specimen Tool)
		+	|-
		+	|ShowUnitIDSP
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|
		+	|-
		+	|ShowCollCodeSP
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|
		+	|-
		+	|ShowInstCodeSP
		+	|varchar(50)
		+	|YES
		+	|
		+	|
		+	|
		+	|-
		+	|Created_Who
		+	|varchar(50)
		+	|NO
		+	|
		+	|unknown
		+	|
		+	|-
		+	|Created_When
		+	|timestamp
		+	|NO
		+	|
		+	|CURRENT_TIMESTAMP
	|		|
−	|NULL
−	|sorting of hitlist (Specimen Tool)
	|}		|}
	==View descriptions==		==View descriptions==
−	===View: wrapper_view_amplifications===	+	coming soon, required for BioCASe mapping
−	===View: wrapper_view_dna===
−	===View: wrapper_view_providers===
−	===View: wrapper_view_publications_dna===
−	===View: wrapper_view_specimens===
	[[Category:DNA_Module]]		[[Category:DNA_Module]]

---

Latest revision as of 15:46, 28 September 2016

Entity-relation diagram of the DNA Module V2.0

September 2016: The new version will be called SaM (Sample Management). The empty sql script can be found at http://www.ggbn.org/ggbn_portal/documents/sam_dev.sql

Overview

DNA extractions

Main package of the DNA Module, related to many other packages. The main table is dnabanknumbers with several related list tables. The table people is connected to the dna extractions via three IDs: ExtractionStaff, Provided_By and AmplificationStaff.

• Main tables:
  • dnabanknumbers
  • people
• List tables:
  • dnatype
  • relationdna_voucher
  • tissue
  • preservation
  • extractionmethod
  • purification
  • degradation
  • images
• Related packages:
  • Amplifications
  • Locations Aliquots/stock
  • Specimen cache
  • Requests
  • Molecular publications
  • Log tables

Specimen data providers

List of all connected specimen data providers (GBIF compliant databases). More than one dataset per provider is possible. The required schema and provider software is recorded by ID_Schema. The Specimen Tool is predefined with ID_Dataset = '1'. The Wrapper urls of both the Specimen Tool and the DNA data are left empty until definition via the Configuration Tool.

• Main table:
  • dataset
• Related packages:
• Specimen cache
• Specimen Tool

Specimen cache

A few specimen parameters have to be recorded within the DNA Module database to enable search for exisiting taxa in the DNA database et cetera. The specimen data are recorded in four tables to get all available determinations and higher taxa. One specimen can be connected to several DNA extractions.

• Main table:
  • cachecollection
• Related packages:
  • Specimen data providers
  • DNA extractions

Locations stock/aliquots

This package records the location and volumes of all aliquot and stock samples in the freezers. The information in this package is completely internal and help to find out easily which samples can be send to customers and which have to be extracted.

• Main tables:
  • aliquots
  • location_stock
• List tables:
  • aliquots_box
  • aliquots_fridge
  • aliquots_rack
  • stock_box
  • stock_fridge
  • stock_rack
• Related packages:
  • DNA extraction
  • Customer requests

Publications

UPDATE of docu required. This package allows to associate any publications to DNA or samples and specimen data. It should not be a complete substitute for a literature management software.

• Main table:
  • reference
• Related packages:
  • DNA extractions
  • Specimen Tool
  • Sequence data

Sequence data

This package is for management of GenBank Accession Numbers, BOLD process IDs and Amplifications in general. More tables have been modelled for future management of raw sequence data, including primers, references, cloning etc. The frontend for this tables will be ready for use until end of 2011. For now only GenBank/BOLD accessions and Amplifications can be administrated.

• Main tables:
  • amplifications
  • sequencing
• List tables:
  • geneticlocus
  • primer
  • cloningmethod
  • sequencingmethod
• Related packages:
  • DNA extractions
  • Molecular publications

Customer requests

This package includes management of all customer requests respectively orders. Since way of administer payments depends on institution this package is for the lab site only.

• Main table:
  • request
• Related packages:
  • Location stock/aliquots
  • DNA extractions

Specimen Tool

This is a stand-alone package that interact with the DNA Module via wrapper. It has been developed for specimen data that are not available via GBIF. Furthermore external duplicates or similiar relevant objects can be associated.

• Main table:
  • sptoolcollection
• List tables:
  • basisofrecordspecimen
  • countryisocode
  • prevalentaspect
  • seasandoceans
  • sex
  • slope
  • specimentype
• Related packages:
• Specimen data providers

Table descriptions

Table: abcdmetadata

See Metadata for DNA data

Table: abcdmetadataspecimens

See Metadata for Specimen Tool; will be removed and merged with abcdmetadata soon

Table: aliquots

Belongs to: Locations stock/aliquot, Customer requests, DNA extractions

Field	Type	Null	Key	Default	Comment
ID_Aliquots	int(10) unsigned	NO	PRI		PK of all aliquots
FK_DNA	int(10) unsigned	NO	MUL		FK to parent DNA sample ( table:dnabanknumbers)
FK_Aliquots_Box	int(10) unsigned	YES	MUL		FK to table:aliquots_box
FK_Aliquots_Rack	int(10) unsigned	YES	MUL		FK to table:aliquots_rack
FK_Aliquots_Fridge	int(10) unsigned	YES	MUL		FK to table:aliquots_fridge
Aliquot_Number	varchar(100)	YES			The number/name of the individual aliquot, mostly the DNA extraction no combined with '-A', '-B' et cetera
Origin_Quantity	float	YES			quantity in µl when first prepared
Rest_Quantity	float	YES			quantity in µl after ordering
Aliquot_Position	varchar(50)	YES			position of an individual aliquot on a plate
Aliquot_Barcode	varchar(255)	YES			barcode of an individual aliquot on a plate
Order_All	varchar(50)	YES			='yes' when whole aliquot has been ordered
Order_Partial	varchar(50)	YES			='yes' when only a part of the aliquot has been ordered
Order_Rest	varchar(50)	YES			='yes' when a part of the aliquot has been shipped earlier and now the rest has been ordered
Shipping_All	varchar(50)	YES			='yes' when only a part of the aliquot has been shipped
Shipping_Partial	varchar(50)	YES			='yes' when only a part of the aliquot has been shipped
Shipping_Rest	varchar(50)	YES			='yes' when a part of the aliquot has been shipped earlier and now the rest has been shipped
Price	varchar(50)	YES			price of the individual aliquot (defined via General Settings)
Currency	varchar(50)	YES			currency of the price (defined via General Settings)
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown
Update_Who	varchar(50)	YES
Update_When	timestamp	YES

Table: aliquots_box

Belongs to: Locations stock/aliquot

Field	Type	Null	Key	Default	Comment
ID_Aliquots_Box	int(10) unsigned	NO	PRI		PK of listed aliquot boxes
Aliquots_Box	varchar(50)	NO			List of boxes used for aliquots ( table:aliquots)
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown

Table: aliquots_fridge

Belongs to: Locations stock/aliquot

Field	Type	Null	Key	Default	Comment
ID_Aliquots_Fridge	int(10) unsigned	NO	PRI		PK of listed aliquot fridges
Aliquots_Fridge	varchar(50)	NO			List of fridges/freezers used for aliquots ( table:aliquots)
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown

Table: aliquots_rack

Belongs to: Locations stock/aliquot

Field	Type	Null	Key	Default	Comment
ID_Aliquots_Rack	int(10) unsigned	NO	PRI		PK of listed aliquot racks
Aliquots_Rack	varchar(50)	NO			List of racks used for aliquots ( table:aliquots)
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown

Table: amplifications

UNDER CONSTRUCTION! Belongs to: Sequence data, DNA extractions

Field	Type	Null	Key	Default	Comment
ID_Amplification	int(10) unsigned	NO	PRI		PK of all amplifications
FK_DNA	int(10) unsigned	NO	MUL		FK to parent DNA sample ( table:dnabanknumbers)
FK_GeneticLocus	int(10) unsigned	NO	MUL		FK to used Genetic locus ( table:geneticlocus)
FK_Subfragment	int(10) unsigned	YES	MUL		FK to used Genetic locus ( table:subfragment)
Amplification_Date	datetime	YES			Date of an individual amplification
FK_Amplification_Staff	int(20) unsigned	YES	MUL		FK to table:people contains person who made the amplification
Success	varchar(50)	YES			contains "Yes" or "No"
SuccessDetails	varchar(500)	YES			remarks on amplification, e.g. why it has been failed
FK_Amplification_Method	int(10) unsigned	YES	MUL		FK to table:methods
FK_Purification_Method	int(10) unsigned	YES	MUL		FK to table:methods
FK_PrimerForward	int(10) unsigned	YES	MUL		FK to table:primer
FK_PrimerReverse	int(10) unsigned	YES	MUL		FK to table:primer
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown
Update_Who	varchar(50)	YES
Update_When	timestamp	YES

Table: basisofrecordspecimen

Belongs to: Specimen Tool

SQL create statement filled with standardised content. Content should not be changed in general, especially when providing data to GBIF or GGBN!

Field	Type	Null	Key	Default	Comment
ID_BasisOfRecord_Specimen	int(10) unsigned	NO	PRI		PK of listed record bases in accordance with GBIF and GGBN
BasisOfRecord_Specimen	varchar(50)	NO			List of record bases used by package Specimen Tool; important for providing data to GBIF or GGBN
Description	varchar(255)	YES
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP

Table: biogeographicregion

Belongs to: Specimen Tool

SQL create statement filled with standardised content. Existing table contains European categories. Of course you can use other standardised lists.

Field	Type	Null	Key	Default	Comment
ID_BiogeographicRegion	int(10) unsigned	NO	PRI		PK of all biogeographic categories
BiogeographicRegion	varchar(50)	NO			List of all biogeographic categories
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP

Table: cacheassociatedunits

Belongs to: Specimen Cache

Field	Type	Null	Key	Default	Comment
ID_Association	int(10) unsigned	NO	PRI		PK of all associated records for specimens or tissues; e.g. the "grandparent" record of a DNA sample
FK_Cache	int(10) unsigned	NO	MUL		FK to table:cachecollection
AssociatedUnitID	varchar(100)	NO			UnitID/Catalogue Number of associated record
AssociatedCollectionCode	varchar(100)	NO			SourceID/CollectionCode of associated record
AssociatedInstitutionCode	varchar(100)	NO			SourceInstitutionID/InstitutionCode of associated record
AssociationType	varchar(100)	YES			Description of relation between associated records
WrapperUrl	varchar(200)	YES			access point, for BioCASe records only
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Updated_Who	varchar(50)	YES
Updated_When	timestamp	YES

Table: cachecollection

Belongs to: Specimen Cache, Specimen data providers, DNA extractions

Field	Type	Null	Key	Default	Comment
ID_Cache	int(10) unsigned	NO	PRI		PK of all DNA voucher specimens or tissues referenced via BioCASE or GBIF (parent record of a DNA sample)
FK_Dataset_Specimen	int(10) unsigned	NO	MUL		FK to table:dataset
UnitID_Specimen	varchar(255)	NO			GBIF-Identifier, UnitID or Catalogue Number (part of triple ID)
CollectionCode_Specimen	varchar(255)	NO			GBIF-Identifier, CollectionCode or SourceID (part of triple ID)
InstitutionCode_Specimen	varchar(255)	NO			GBIF-Identifier, InstitutionCode or SourceInstitutionID (part of triple ID)
ContinentOrOcean	varchar(255)	YES			Continent or Ocean (collection site)
CountryName	varchar(255)	YES			English Name of the Country (collection site)
CountryISO2	varchar(50)	YES			ISO 3166-2 (collection site)
Collectors	varchar(200)	YES			Collection Team
CollectionDate	varchar(200)	YES			Collection Date (free text)
CollectionDateMin	varchar(200)	YES			Collection Date from (free text or ISO, depends on original data provider)
CollectionDateMax	varchar(200)	YES			Collection Date to (free text or ISO, depends on original data provider)
CollectorsNo	varchar(100)	YES			field number of a specimen given by collector (in the field)
Locality	varchar(300)	YES			free text of locality information (below country level)
Altitude	varchar(50)	YES			free text of Altitude measurement (from - to - unit)
Longitude	varchar(50)	YES			decimal value of Longitude
Latitude	varchar(50)	YES			decimal value of Latitude
TypeStatus	varchar(150)	YES			type status of the specimen
GUID_Specimen	varchar(50)	YES			GUID of the record if provided
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown
Updated_When	timestamp	YES
Updated_Who	varchar(50)	YES

Table: cachehighertaxa

Belongs to: Specimen Cache

Field	Type	Null	Key	Default	Comment
ID_HigherTaxon	int(10) unsigned	NO	PRI		PK of all Higher Taxa, sent by original data provider (wrappers)
FK_CacheTaxonIdentified	int(10) unsigned	NO	MUL		FK to table:cachetaxonidentified
HigherTaxon	varchar(255)	NO			Name of the HigherTaxon
TaxonRank	varchar(255)	YES			Rank of the Higher Taxon
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown
Updated_When	timestamp	YES
Updated_Who	varchar(50)	YES

Table: cachemultimedia

Belongs to: Specimen Cache

Field	Type	Null	Key	Default	Comment
ID_Multimedia	int(10) unsigned	NO	PRI		PK of all Multimedia URLs, sent by original data provider (wrappers)
FK_Cache	int(10) unsigned	NO	MUL		FK to table:cache
Multimedia	varchar(500)	NO			List of all Multimedia URLs
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Updated_When	timestamp	YES
Updated_Who	varchar(50)	YES

Table: cachetaxonidentified

Belongs to: Specimen Cache

Field	Type	Null	Key	Default	Comment
ID_TaxonIdentified	int(10) unsigned	NO	PRI		PK of all specimen determinations, sent by original data provider (wrappers)
FK_Cache	int(10) unsigned	NO	MUL		FK to table:cache
Genus	varchar(255)	YES	MUL
Subgenus	varchar(255)	YES
FirstEpithet	varchar(255)	YES	MUL
SecondEpithet	varchar(255)	YES	MUL
Rank	varchar(50)	YES			Rank of the taxon
HybridFlag	varchar(50)	YES
NameAuthorYear	varchar(500)	YES			Full Scientific Name String including Author teams and years
PreferredFlag	varchar(50)	YES			mostly provided as "1" or "true" ; if empty filled with "1"
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown
Updated_When	timestamp	YES
Updated_Who	varchar(50)	YES

Table: cloningmethod

Belongs to: Sequence data

At present not in use, coming soon.

Table: countrorcontinent

Belongs to Specimen Tool SQL create statement filled with standardised content. Content should not be changed in general, especially when providing data to GBIF or the DNA Bank Network!

Field	Type	Null	Key	Default	Comment
ID_Continent	int(10) unsigned	NO	PRI		PK of all continents and oceans
ContinentOrOcean	varchar(50)	NO			List of all continents and oceans; no FK

Table: country_continent

Belongs to: Specimen Tool

SQL create statement filled with standardised content. Content should not be changed in general, especially when providing data to GBIF or the DNA Bank Network!

Field	Type	Null	Key	Default	Comment
FK_Country	int(10) unsigned	NO	MUL		FK of all Countries
Continent	varchar(50)	NO	MUL		Contintens belonging to Country (e.g. Turkey belongs to Asia and Europe)

Table: countryisocode

Belongs to: Specimen Tool

Create statement filled with standardised content. Content should not be changed in general, especially when providing data to GBIF or the DNA Bank Network!

Field	Type	Null	Key	Default	Comment
ID_Country	int(10) unsigned	NO	PRI		PK of all Countries
IsoCode	varchar(50)	NO			ISO 3611-2 code of the country
Country	varchar(150)	NO			English name of the country

Table: dataset

Belongs to: Specimen data providers, Specimen Tool

SQL create statement filled with standardised first row. Please don't make any changes at first row manually!

Field	Type	Null	Key	Default	Comment
ID_Dataset	int(10) unsigned	NO	PRI		PK of all connected specimen datasets (GBIF compliant databases, first row contains the Specimen Tool)
FK_Provider	int(10) unsigned	YES	MUL		FK to table:provider
Digir_Resource	varchar(255)	YES			Parameter important for Digir only
Digir_Source	varchar(255)	YES			Parameter important for Digir only, mostly an URL
Display	varchar(255)	YES			Name of this dataset given by DNA Module-user
Internal_External	varchar(10)	YES			DNA Module-user decide if this dataset appears in the external or internal dataset list
DatasetKey_GBIF	varchar(50)	YES			Dataset guid (key) provided by GBIF via web services
Protocol	varchar(50)	YES			Protocol name provided by GBIF via web services (e.g. BIOCASE)
InstitutionCode	varchar(50)	YES			Deprecated?
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown

Table: dnabanknumbers

Belongs to: DNA extractions, Specimen cache, Location stock/aliquots, Sequence data

Field	Type	Null	Key	Default	Comment
ID_DNA	int(10) unsigned	NO	PRI		PK of all DNA extraction numbers
FK_Cache	int(10) unsigned	YES	MUL		FK to table:cachecollection, underlying specimen
FK_AcquiredFrom	int(10) unsigned	YES	MUL		FK to table:people, Person who provided the sample (used for donations)
FK_Type	int(10) unsigned	YES	MUL		FK to table:dnatype, Type of the DNA
FK_Location_Stock	int(10) unsigned	YES	MUL		FK to table:location_stock
DNA_Bank_Number	varchar(50)	NO			DNA extraction number; Must be unique
FK_Relation	int(10) unsigned	NO	MUL		FK to table:relation
FK_Tissue	int(10) unsigned	YES	MUL		FK to table:tissue, type of tissue used for extraction
FK_Preservation	int(10) unsigned	YES	MUL		FK to table:preservation, preservation of underyling tissue/specimen
Extraction_Date	datetime	YES			date of extraction (format YYYY-MM-DD) if known
NoExtractDate	int(11)	NO			= '1' if date of extraction is not known
FK_Extraction_Method	int(10) unsigned	YES	MUL		FK to table:methods, extraction kit
FK_Purification_Method	int(10) unsigned	YES	MUL		FK to table:methods, method of purification
FK_Extraction_Staff	int(10) unsigned	YES	MUL		FK to table:people, person who extracted the DNA
Quality_Check_Date	datetime	YES			date of quality check (format YYYY-MM-DD)
FK_Concentration_Method	int(10) unsigned	YES	MUL		FK to table:methods, method to determine concentration
FK_GelImage	int(11) unsigned	YES	MUL		FK to table:gelimages
Concentration	varchar(10)	YES			Concentration value of DNA sample in ng/µl
Absorbance280	varchar(10)	YES			Ratio of absorbance (value, 260/280)
Absorbance230	varchar(10)	YES			Ratio of absorbance (value, 260/230)
Weight	varchar(10)	YES			DNA weight
WeightUnit	varchar(10)	YES			Unit of measuring DNA weight
FK_Weight_Method	int(10) unsigned	YES	MUL		FK to table:methods, method to measure weight
Stock_Gone	int(10)	YES			='1' if stock is gone
Source_Gone	int(10)	YES			='1' of source (tissue/specimen) is gone
Notes	varchar(500)	YES			Notes related to DNA sample, visible for all user groups
Notes_Intern	varchar(500)	YES			Notes related to DNA sample, not visible for guests (user group)
Blocked_Until	datetime	YES			date until sample is blocked for delivery (format YYYY-MM-DD)
Blocked_For_Loaning	int(10)	YES			='1' if DNA sample is blocked for delivery only; this record will be visible in the Network's data portal
Blocked_For_Publishing	int(10)	YES			='1' if DNA sample is blocked in general; this record won't be visible in the Network's data portal
Loan_Conditions	varchar(500)	YES			certain conditions for loaning the DNA sample
Backup_Aliquot	int(10)	YES			='1' if a backup aliquot is deposited in an external institution
GUID	varchar(50)	YES			GUID of the DNA sample
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Update_Who	varchar(50)	YES
Update_When	timestamp	YES
Lock_Who	varchar(50)	YES
Lock_When	timestamp	YES

Table: dnatype

Belongs to: DNA extractions

SQL create statement filled with standardised content. Existing content should not be changed, but you can add further entries.

Field	Type	Null	Key	Default	Comment
ID_Type	int(10) unsigned	NO	PRI		PK of all DNA Types
Type	varchar(50)	NO			List of all DNA types
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown

Table: gelimages

Belongs to: DNA extractions

Field	Type	Null	Key	Default	Comment
ID_Images	int(10) unsigned	NO	PRI		PK of all gel images
FileURI	varchar(500)	NO			URL of gel image
GelRemarks	varchar(150)	YES			Remarks on a gel
GelVoltage	varchar(50)	YES			Gel voltage
GelConcentration	varchar(50)	YES			Gel concentration
GelDuration	varchar(50)	YES			Gel duration
GelLadder	varchar(100)	YES			Gel ladder
GelStain	varchar(100)	YES			Gel stain
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Updated_Who	varchar(50)	YES
Updated_When	timestamp	YES

Table: geneticlocus

Belongs to: DNA extractions

Field	Type	Null	Key	Default	Comment
ID_GeneticLocus	int(10) unsigned	NO	PRI		PK of all Genetic Loci/Fragments
GeneticLocus	varchar(150)	NO			List of all Genetic Loci/Fragments
GeneticLocusRemarks	varchar(255)	NO			Remarks on Genetic Locus/Fragment
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown
Updated_Who	varchar(50)	YES
Updated_When	timestamp	YES

Table: geocodemethod

Belongs to: Specimen Tool Include into methods table?

SQL create statement filled with standardised content. Existing content should not be changed, but you can add further entries.

Field	Type	Null	Key	Default	Comment
ID_Geocode	int(10) unsigned	NO	PRI		PK of all geocode methods
Geocode	varchar(50)	NO			List of all geocode methods
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP

Table: habitatcode

Belongs to: Specimen Tool

SQL create statement filled with standardised content. Existing table contains European categories (EUNIS). Of course you can use other standardised lists.

Field	Type	Null	Key	Default	Comment
ID_HabitatCode	int(10) unsigned	NO	PRI		PK of all habitat code categories
HabitatCode	varchar(50)	NO			List of all habitat code categories
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP

Table: kindofunit

Belongs to: Specimen Tool

Field	Type	Null	Key	Default	Comment
ID_KindOfUnit	int(10) unsigned	NO	PRI		PK of all Kind of Units (materialSampleType)
KindOfUnit	varchar(100)	NO			List of all Kind of Units (materialSampleType)
KindOfUnitRemarks	varchar(500)	YES			Remarks on a Kind of Unit
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Updated_Who	varchar(50)	YES
Updated_When	timestamp	YES

Table: labnumber

tbd

Table: landusecode

Belongs to: Specimen Tool

SQL create statement filled with standardised content. Existing table contains European categories. Of course you can use other standardised lists.

Field	Type	Null	Key	Default	Comment
ID_LandUseCode	int(10) unsigned	NO	PRI		PK of all land use codes
LandUseCode	varchar(50)	NO			List of all land use codes
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP

Table: location_stock

Belongs to: DNA extractions, Locations stock/aliquots

Field	Type	Null	Key	Default	Comment
ID_Location_Stock	int(10) unsigned	NO	PRI		PK of all stock locations
FK_Stock_Box	int(10) unsigned	YES	MUL		FK to table:stock_box
FK_Stock_Rack	int(10) unsigned	YES	MUL		FK to table:stock_rack
FK_Stock_Fridge	int(10) unsigned	YES	MUL		FK to table:stock_fridge
Stock_Position	varchar(50)	YES			position of the stock tube on a plate
Stock_Barcode	varchar(200)	YES			barcode of the stock tube on a plate
Origin_Quantity	float	YES			quantity in µl when first prepared
Rest_Quantity	float	YES			quantity in µl after using stock partly for an aliquot
Created_When	timestamp	YES		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown
Update_Who	varchar(50)	YES
Update_When	timestamp	YES

Table: methods

Belongs to: DNA extractions, Specimen Tool, Sequence data

Field	Type	Null	Key	Default	Comment
ID_Method	int(10) unsigned	NO	PRI		PK of all methods
Method	varchar(200)	NO			List of all methods
MethodGroup	varchar(50)	NO			tbd; group for drop-down lists
MethodRemarks	varchar(500)	YES			remarks on a certain method
FK_Reference	int(10) unsigned	YES	MUL		FK to table:reference
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown
Updated_Who	varchar(50)	YES
Updated_When	timestamp	YES

Table: people

Belongs to: DNA extractions, Customer requests, Sequence data, Specimen Tool

Field	Type	Null	Key	Default	Comment
ID_People	int(20) unsigned	NO	PRI		PK of all Persons
FormOfAddress	varchar(50)	YES
Title	varchar(100)	YES
Forename	varchar(50)	YES
Surname	varchar(50)	YES
Phone	varchar(100)	YES
Email	varchar(100)	YES
Remarks	varchar(500)	YES
Name_All	varchar(50)	YES			name string (Surname, Forename)
Institut	varchar(150)	YES
Division	varchar(150)	YES
Street	varchar(150)	YES
City	varchar(150)	YES
Postal_Code	varchar(150)	YES
Country	varchar(150)	YES
Institut_Delivery	varchar(150)	YES			if shipping/delivery address differs from billing address
Division_Delivery	varchar(150)	YES
Street_Delivery	varchar(150)	YES
City_Delivery	varchar(150)	YES
Postal_Code_Delivery	varchar(150)	YES
Country_Delivery	varchar(150)	YES
Staff	varchar(10)	YES			='Yes' if person is part of the lab, also externals that have done the extractions
Provider	varchar(10)	YES			='Yes' if person has provided samples
Customer	varchar(10)	YES			='Yes' if person has ordered DNA sample
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown
Updated_When	timestamp	YES
Updated_Who	varchar(50)	YES

Table: preservations

Belongs to: DNA extractions, Specimen Tool

Field	Type	Null	Key	Default	Comment
ID_Preservation	int(10) unsigned	NO	PRI		PK of all Preservation types (DNA,tissue,specimen)
Preservation	varchar(200)	NO			List of all preservation types (DNA,tissue,specimen)
PreservationRemarks	varchar(500)	NO			Remarks on a certain preservation
FK_Reference	int(11) unsigned	NO	MUL		FK to table:reference
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown
Updated_When	timestamp	YES
Updated_Who	varchar(50)	YES

Table: prevalentaspect

Belongs to: Specimen Tool

Field	Type	Null	Key	Default	Comment
ID_Aspect	int(10) unsigned	NO	PRI		PK of all aspect categories
Aspect	varchar(50)	NO			List of all aspect categories
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP

Table: primer

Belongs to: Sequence data

At present not in use, coming soon.

Field	Type	Null	Key	Default	Comment
ID_Primer	int(10) unsigned	NO	PRI		PK of all primers
Primer	varchar(100)	NO		unknown	List of all primer names
PrimerSequence	varchar(5000)	YES			List of all primer sequences
PrimerRemarks	varchar(500)	YES			Remarks on a certain primer
FK_Reference	int(10) unsigned	YES	MUL		FK to table:reference
adapters	varchar(100)	YES			MIxS term
multiplexIdentifiers	varchar(100)	YES			MIxS term
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Updated_Who	varchar(50)	NO		unknown
Updated_When	timestamp	YES

Table: provider

Belongs to: Specimen data providers

Field	Type	Null	Key	Default	Comment
ID_Provider	int(10) unsigned	NO	PRI		PK of all specimen data provider urls
Provider	varchar(500)	NO			List of all specimen data providers urls (access points)
FK_Schema	int(10) unsigned	NO	MUL		FK to required Schema ( table:schemata)
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown

Table: reference

Belongs to: Sequence data, DNA extractions, Specimen Tool

Field	Type	Null	Key	Default	Comment
ID_References	int(10) unsigned	NO	PRI		PK of all references, protocols etc.
ReferenceText	varchar(1000)	YES	MUL		Citation format of all references
ReferenceShort	varchar(100)	YES			Abbreviation for a reference, user-defined
ReferenceUrl	varchar(1000)	YES			URL to online version of reference
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Update_When	timestamp	YES
Update_Who	varchar(50)	YES

Table: relation

Belongs to: DNA extractions, [[#Specimen_Tool | Specimen_Tool]

SQL create statement filled with standardised content. You can change or add relationships if needed. Ideally you should use the existing relationships.

Field	Type	Null	Key	Default	Comment
ID_Relation	int(10) unsigned	NO	PRI		PK of all relationships
Relation	varchar(250)	NO			List of all relationships
RelationRemarks	varchar(250)	NO			Remarks on a certain relation
FK_Reference	int(10) unsigned	YES	MUL		FK to ( table:reference)
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown
Updated_Who	varchar(50)	YES		unknown
Updated_When	timestamp	YES

Table: request

Belongs to: Customer requests, Locations stock/aliquots, DNA extractions

Field	Type	Null	Key	Default	Comment
ID_Request	int(10) unsigned	NO	PRI		PK of all customer requests
FK_People	int(10) unsigned	NO	MUL		FK to customer ( table:people)
FK_DNA	int(10) unsigned	NO	MUL		FK to parent DNA sample ( table:dnabanknumbers)
FK_Aliquots	int(10) unsigned	NO	MUL		FK to table:aliquots
Request_Date	datetime	YES			Date of request
Request_Number_Aliquots	varchar(150)	YES			Individual Aliquot Number requested
Request_Volume	float	YES			requested/shipped volume in µl
Request_Notes	varchar(500)	YES			Remarks on a certain request
Shipping_Date	datetime	YES			Date of shipping
PriceAndCurrency	varchar(50)	YES			Price and Currency of requested aliquot
Agreement	varchar(10)	YES			='Yes' if Material transfer agreement has been signed by customer
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown

Table: samples_preservations

Belongs to: DNA extractions, Specimen_Tool

Table to enable n:m relations between DNA or tissue sample and the preservation type

Field	Type	Null	Key	Default	Comment
ID_Sample_Preservation	int(10) unsigned	NO	PRI		PK of all preservations of a certain sample
FK_DNA	int(10) unsigned	YES	MUL		FK to DNA sample ( table:dnabanknumbers)
FK_Cache	int(10) unsigned	YES	MUL		FK to tisse sample/specimen ( table:sptoolcollection)
FK_Preservations	int(10) unsigned	NO	MUL		FK to preservation list ( table:preservations)
PreservationDateBegin	varchar(100)	YES
PreservationTemp	varchar(50)	YES
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Updated_Who	varchar(50)	YES
Updated_When	timestamp	YES

Table: schemata

Belongs to: Specimen data providers

Field	Type	Null	Key	Default	Comment
ID_Schema	int(10) unsigned	NO	PRI		PK of all schemata (ABCD, DwC)
Schemata	varchar(50)	YES			List of all schemata (ABCD, DwC)
Created_When	timestamp	YES		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown

Table: seasandoceans

Belongs to: Specimen Tool

SQL create statement filled with standardised content. Content should not be changed in general, especially when providing data to GGBN!

Field	Type	Null	Key	Default	Comment
ID_SeasAndOceans	int(10) unsigned	NO	PRI		PK of all seas
SeasAndOceans	varchar(255)	NO			List of all seas
Ocean	varchar(255)	NO			the larger ocean the sea belongs to
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP

Table: sequencing

Belongs to: Sequence data, DNA extractions

At present not in use, coming soon.

Field	Type	Null	Key	Default	Comment
ID_Sequencing	int(10) unsigned	NO	PRI		PK of all sequencings
FK_Amplification	int(10) unsigned	NO	MUL		FK to parent amplification event table:amplifications
CloningDate	datetime	YES			Date of cloning (format: YYYY-MM-DD)
FK_CloningStaff	int(10) unsigned	YES	MUL		FK to person or company performing DNA cloning table:people
FK_CloningMethod	int(10) unsigned	YES	MUL		FK to used method or protocol table:cloningmethod
FK_CloningPrimerForward	int(10) unsigned	YES	MUL		FK to used forward primer table:primer
FK_CloningPrimerReverse	int(10) unsigned	YES	MUL		FK to used reverse primer table:primer
CloneStrain	varchar(50)	YES			Name of the individual DNA clone
ConsensusSequence	varchar(1000)	YES			Consensus sequence derived from all individual sequences
ConsensusSequenceLength	varchar(10)	YES			Length of the consensus sequence (number of base pairs)
ConsensusSequenceChromatogram	varchar(500)	YES			Link to chromatogram of the consensus sequence
BarcodeSequence	varchar(1000)	YES			DNA barcode sequence (part or 100% of the consensus sequence)
GeneticAccessionNumber	varchar(50)	YES			Definite number or ID under which the DNA sequence is deposited at EMBL,NCBI or DDBJ
BOLD	varchar(50)	YES			Definite number or ID under which the DNA barcode is deposited at BOLD
Haplotype	varchar(50)	YES			Namer of the haplotype
FK_Reference	int(10) unsigned	YES	MUL		FK to used table:reference
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP

Table: sex

Belongs to: Specimen Tool

SQL create statement filled with standardised content. Content should not be changed in general, especially when providing data to GBIF or GGBN!

Field	Type	Null	Key	Default	Comment
ID_Sex	int(10) unsigned	NO	PRI		PK of all sex types
Sex	varchar(50)	NO			List of all sex types
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown

Table: singlesequencing

Belongs to: Sequence data, DNA extractions

At present not in use, coming soon.

Field	Type	Null	Key	Default	Comment
ID_SingleSequencing	int(10) unsigned	NO	PRI		PK of all single sequencings/single reads
SequencingDirection	varchar(50)	NO		unknown	Direction of sequencing (forward/reverse)
FK_Sequencing	int(10) unsigned	NO	MUL		FK to parent sequencing event table:sequencing
FK_Amplification	int(10) unsigned	NO	MUL		FK to parent amplification event table:amplifications
SequencingDate	varchar(50)	YES			Date of sequencing (format: YYYY-MM-DD)
FK_SequencingStaff	int(10) unsigned	YES	MUL		FK to person or company performing the single sequencing table:people
FK_SequencingMethod	int(10) unsigned	YES	MUL		FK to used method or protocol table:people
SingleSequence	varchar(1000)	YES			Sequence of the individual sequence (A,T,G,C; 5' to 3')
SequenceLength	varchar(50)	YES			Length of the individual sequence
FragmentLength	varchar(50)	YES			Length of fragments
ChromatogramFileURI	varchar(500)	YES			Link to chromatogram of individual sequence
FK_SequencingPrimer	int(10) unsigned	YES	MUL		FK to primer used for the single sequencing table:primer
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP

Table: slope

Belongs to: Specimen Tool

SQL create statement filled with standardised content. Existing content should not be changed, but you can add further entries.

Field	Type	Null	Key	Default	Comment
ID_Slope	int(10) unsigned	NO	PRI		PK of all slope categories
Slope	varchar(50)	NO			List of all slope categories
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP

Table: soilph

Belongs to: Specimen Tool

SQL create statement filled with standardised content. Existing content should not be changed, but you can add further entries.

Field	Type	Null	Key	Default	Comment
ID_SoilpH	int(10) unsigned	NO	PRI		PK of all soil pH categories
SoilpH	varchar(50)	NO			List of all soil pH categories
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP

Table: soiltexture

Belongs to: Specimen Tool

SQL create statement filled with standardised content. Existing content should not be changed, but you can add further entries.

Field	Type	Null	Key	Default	Comment
ID_SoilTexture	int(10) unsigned	NO	PRI		PK of all soil texture categories
SoilTexture	varchar(50)	NO			List of all soil texture categories
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP

Table: specimentype

Belongs to: Specimen Tool

SQL create statement filled with standardised content. Content should not be changed in general, especially when providing data to GBIF or the DNA Bank Network!

Field	Type	Null	Key	Default	Comment
ID_SpecimenType	int(10) unsigned	NO	PRI		PK of all sort of type status
SpecimenType	varchar(50)	NO			List of all sort of type status
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown

Table: sptoolassociatedunits

Belongs to: Specimen Tool, Specimen data providers

Field	Type	Null	Key	Default	Comment
ID_Association	int(10) unsigned	NO	PRI		PK of all associated specimens and observations
FK_Collection	int(10) unsigned	NO	MUL		FK to parent specimen/observation (collection event) ( table:sptoolcollection)
FK_Relation	int(10) unsigned	YES	MUL		FK to relation, describing relation between main unit and associated unit ( table:relation)
UnitID	varchar(100)	YES			GBIF-Identifier of associated unit, UnitID or Catalogue Number (part of triple ID)
CollectionCode	varchar(100)	YES			GBIF-Identifier of associated unit, CollectionCode or SourceID (part of triple ID)
InstitutionCode	varchar(100)	YES			GBIF-Identifier of associated unit, InstitutionCode or SourceInstitutionID (part of triple ID)
FK_BasisOfRecord	int(10) unsigned	YES	MUL		FK to basisofrecord ( table:basisofrecordspecimen)
FK_Dataset	int(10) unsigned	YES	MUL		FK to dataset ( table:dataset)
Notes	varchar(200)	YES			Remarks on a certain associated record
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(100)	NO		unknown
Updated_When	timestamp	YES
Updated_Who	varchar(100)	YES

Table: sptoolcollection

Belongs to: Specimen Tool

Field	Type	Null	Key	Default	Comment
ID_Collection	int(10) unsigned	NO	PRI		PK of all specimens and observations
UnitID_Specimen	varchar(100)	NO	MUL		(GBIF)-Identifier of main unit (part of triple ID)
CollectionCode_Specimen	varchar(200)	NO			(GBIF)-Identifier of main unit (part of triple ID)
InstitutionCode_Specimen	varchar(200)	NO			(GBIF)-Identifier of main unit (part of triple ID)
FK_BasisOfRecord	int(10) unsigned	YES	MUL		FK to ( table:basisofrecordspecimen)
FK_KindOfUnit	int(10) unsigned	YES	MUL		FK to ( table:kindofunit)
PrepType	varchar(150)	YES			Preparation Type, e.g. blood, leaf
PrepDate	varchar(50)	YES			Preparation Date
FK_PrepStaff	int(10) unsigned	YES	MUL		FK to person or company who did the preparation, often the collector ( table:people)
PrepMaterials	varchar(1000)	YES			Materials used for the preparation
PrepProcess	varchar(1000)	YES			Individual steps of the preparation, e.g. SPREC-code
FK_AcquiredFrom	int(10) unsigned	YES	MUL		FK to person or company who has provided the sample/specimen ( table:people)
AccessionNo	varchar(50)	YES			Primary accession number
AccessionName	varchar(100)	YES			Primary accession name
FK_BiogeographicRegion	int(10) unsigned	YES	MUL		FK to ( table:biogeographicregion)
FK_Country	int(10) unsigned	YES	MUL		FK to ( table:countryisocode)
FK_SeasAndOceans	int(10) unsigned	YES	MUL		FK to ( table:seasandoceans)
Collectors	varchar(500)	YES			Collection Team
CollectionDateMin	datetime	YES			Collection Date from (format: YYYY-MM-DD)
CollectionDateMax	datetime	YES			Collection Date to (format: YYYY-MM-DD)
CollectionYear	varchar(50)	YES			Collection Year (generated when record has been saved)
CollectorsNo	varchar(100)	YES			Field number of a specimen given by collector (in the field)
CollectionNotes	varchar(400)	YES			Notes and remarks regarding the collection event itself
CollectionMethod	varchar(400)	YES			Method of collecting
Locality_ID	varchar(150)	YES			Number/name of collection site, e.g. in a river or a plot name/number
ExpeditionDetails	varchar(300)	YES			Project or Expedition details concerning collection event
Region	varchar(500)	YES			District or island name
Locality	varchar(500)	YES			Free text of locality information (below district level)
Habitat	varchar(100)	YES			Free text of habitat information concerning collection site
FK_SoilTexture	int(10) unsigned	YES	MUL		FK to ( table:soiltexture)
FK_SoilpH	int(10) unsigned	YES	MUL		FK to ( table:soilph)
FK_HabitatCode	int(10) unsigned	YES	MUL		FK to ( table:habitatcode)
FK_LandUseCode	int(10) unsigned	YES	MUL		FK to ( table:landusecode)
FK_Slope	int(10) unsigned	YES	MUL		FK to ( table:slope)
FK_Aspect	int(10) unsigned	YES	MUL		FK ( table:prevalentaspect)
DepthAccuracyStatement	varchar(50)	YES			Statement of accuracy of measurement
DepthMin	varchar(50)	YES			Lower or only value of measurement
DepthMax	varchar(50)	YES			Upper value of measurement
DepthUnit	varchar(50)	YES			Unit of measurement
DepthMethod	varchar(50)	YES			Method of measurement
DepthRaw	varchar(50)	YES			Original label data
AltitudeAccuracyStatement	varchar(50)	YES			Statement of accuracy of measurement
AltitudeMin	varchar(50)	YES			Lower or only value of measurement
AltitudeMax	varchar(50)	YES			Upper value of measurement
AltitudeUnit	varchar(50)	YES			Unit of measurement
AltitudeMethod	varchar(50)	YES			Method of measurement
AltitudeRaw	varchar(50)	YES			Original label data
CoordinatesAccuracyStatement	varchar(50)	YES			Statement of accuracy of measurement
CoordinatesErrorDistance	varchar(50)	YES			An estimate of how tightly the collecting locality was specified; expressed as a distance in meters corresponding to a radius around the Lat/Long coordinates
LongitudeMin	varchar(50)	YES			Lower or only value expressed in decimal degrees
LongitudeMax	varchar(50)	YES			Upper value expressed in decimal degrees
LatitudeMin	varchar(50)	YES			Lower or only value expressed in decimal degrees
LatitudeMax	varchar(50)	YES			Upper value expressed in decimal degrees
LongitudeRaw	varchar(50)	YES			Original label data
LatitudeRaw	varchar(50)	YES			Original label data
GeodeticDatum	varchar(50)	YES			e.g. WGS84
FK_GeocodeMethod	int(10) unsigned	YES	MUL		FK to ( table:geocodemethod)
FK_Sex	int(10) unsigned	YES	MUL		FK to ( table:sex)
EscapedCoordinates	varchar(20)	YES		No	='Yes' if coordinates for a certain record have to be escaped
Permission	varchar(20)	YES		NotRequired	turn into fk and list
Source_Gone	int(10)	YES			='1' if source material is gone
Blocked_Until	datetime	YES			date until sample is blocked for delivery (format YYYY-MM-DD)
Blocked_For_Loaning	int(10)	YES			='1' if sample/specimen is blocked for delivery only; this record will be visible in the Network's data portal
Blocked_For_Publishing	int(10)	YES			='1' if sample/specimen is blocked in general; this record won't be visible in the Network's data portal
Loan_Conditions	varchar(500)	YES			Certain conditions for loaning the DNA sample
FK_Cache	int(10) unsigned	YES	MUL		FK to ( table:cachecollection), if used for DNA too
FK_Type	int(10) unsigned	YES	MUL		FK to ( table:specimentype)
TypeDesignation	varchar(500)	YES			Type designation if specimen/sample is type material
Notes	varchar(500)	YES			Notes/remarks concerning specimen/observation in general
Notes_Internal	varchar(500)	YES			Notes/remarks concerning specimen/observation for internal display
LifeStage	varchar(50)	YES			Free text describing life stage of specimen/observation
LifeForm	varchar(50)	YES			Free text describing life form of specimen/observation
SampleGroup	varchar(15)	YES			Either 'Specimen' or 'MixedSample'
GUID	varchar(50)	YES			GUID generated with PHP, if UnitID is empty UnitID_Specimen = GUID
Multimedia	varchar(10)	YES			= 'Yes' when table:sptoolmultimedia filled with content
FK_Strat_Series	int(10) unsigned	YES	MUL		FK to ( table:stratigraphy_series)
Qualifier_Series	varchar(50)	YES			Qualifier of Series
FK_Strat_Stage	int(10) unsigned	YES	MUL		FK to ( table:stratigraphy_stage)
Qualifier_Stage	varchar(50)	YES			Qualifier of Stage
FossilZone	varchar(150)	YES			Name of Fossil Zone
Formation	varchar(150)	YES			Name of Formation
SectionDetails	varchar(500)	YES			Details about the section
SpecimenDetails	varchar(1000)	YES			Details about the specimen, e.g. which bones has been found
FK_ParentID_Collection	int(10) unsigned	YES	MUL		FK to ( table:sptoolcollection)
FK_RelationToParent	int(10) unsigned	YES	MUL		FK to ( table:relation)
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown
Updated_When	timestamp	YES
Updated_Who	varchar(50)	YES
Lock_Who	varchar(50)	YES			Locked while editing
Lock_When	timestamp	YES			Locked while editing

Table: sptoolgeneticaccessions

Belongs to: Specimen Tool

This table stores information on genetic accession numbers that are related to tissue samples or specimens but not related to your own DNA samples.

Field	Type	Null	Key	Default	Comment
ID_GeneticAccession	int(10) unsigned	NO	PRI		PK of all genetic accession numbers related to a tissue/specimen
FK_Collection	int(10) unsigned	NO	MUL	0	FK to tissue sample/specimen ( table:sptoolcollection)
FK_GeneticLocus	int(10) unsigned	NO	MUL	0	FK to ( table:geneticlocus)
GenBankNumber	varchar(100)	YES			List of genetic accession numbers (EMBL, NCBI, DDBJ)
SampleID	varchar(100)	YES			Additional sample number, often provided via EMBL/NCBI/DDBJ
BOLD	varchar(20)	YES			List of BOLD accession numbers
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Updated_Who	varchar(50)	YES
Updated_When	timestamp	YES

Table: sptoolhighertaxon

Belongs to: Specimen Tool

Field	Type	Null	Key	Default	Comment
ID_HigherTaxon	int(10) unsigned	NO	PRI		PK of all higher taxa
FK_TaxonIdentified	int(10) unsigned	NO	MUL		FK to parent determination ( table:sptooltaxa)
HigherTaxon	varchar(255)	YES			Name of higher taxon
TaxonRank	varchar(255)	YES			Rank of higher taxon
Synecology	varchar(255)	YES			='host' if taxon ist host species of specimen
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown
Updated_When	timestamp	YES
Updated_Who	varchar(50)	YES

Table: sptoolmeasurementorfact

Belongs to: Specimen Tool

Field	Type	Null	Key	Default	Comment
ID_Measurement	int(20) unsigned	NO	PRI		PK of all facts
FK_Collection	int(20) unsigned	YES	MUL		FK to parent Collection event ( table:sptoolcollection)
Value	varchar(200)	YES			Value of measurement
Unit	varchar(50)	YES			Unit of measurement
Parameter	varchar(100)	YES			Name of measured parameter
Method	varchar(200)	YES			Method used for measurement
Rank	varchar(50)	YES			Fact rank/group, e.g. "ecology", "morphology"
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown
Updated_When	timestamp	YES
Updated_Who	varchar(50)	YES

Table: sptoolmultimedia

Belongs to: Specimen Tool

Field	Type	Null	Key	Default	Comment
ID_Multimedia	int(10) unsigned	NO	PRI		PK of all multimedia items
FK_Collection	int(10) unsigned	NO	MUL		FK to parent collection event ( table:sptoolcollection)
File_Path	varchar(500)	NO			Path/url of multimedia file
File_Type	varchar(20)	NO			Type of multimedia item (video, sound, image etc.)
File_Created_When	datetime	YES			Date and time when multimedia file has been created (format YYYY-MM-DD hh:mm:ss)
File_Created_Who	varchar(100)	NO			Creator of multimedia file
File_Context	varchar(500)	NO			Comments/notes concerning individual multimedia file
File_Comment	varchar(500)	NO			The context of the object in relation to the specimen or observation. E.g. image of entire specimen, sound recording the observation is based on, image of original valid publication, etc.
File_Group	varchar(50)	NO	MUL		='specimen' if multimedia item belongs directly to the specimen; ='site' if multimedia item belongs to collection site
File_License	varchar(50)	NO	MUL		Lincense for multimedia item
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown
Updated_When	timestamp	YES
Updated_Who	varchar(50)	YES

Table: sptoolreferences

Belongs to: Specimen Tool

Field	Type	Null	Key	Default	Comment
ID_Reference	int(10) unsigned	NO	PRI		PK of all references related to an observation or specimen/tissue sample
FK_Collection	int(10) unsigned	YES	MUL		FK to parent collection event ( table:sptoolcollection)
FK_References	int(10) unsigned	YES	MUL		FK to ( table:reference)
ReferenceDetail	varchar(50)	YES			Page detail used for citation
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown
Updated_When	timestamp	YES
Updated_Who	varchar(50)	YES

Table: sptooltaxa

Belongs to: Specimen Tool

Field	Type	Null	Key	Default	Comment
ID_TaxonIdentified	int(10) unsigned	NO	PRI		PK of all determinations
FK_Collection	int(10) unsigned	NO	MUL		FK to parent collection event ( table:sptoolcollection)
Qualifier	varchar(50)	NO			e.g. cf.
QualifierBelongsTo	varchar(50)	NO			Tank rank qualifier belongs to
Genus	varchar(255)	YES
Subgenus	varchar(255)	YES
SpecificEpithet	varchar(255)	YES
InfraspecificEpithet	varchar(255)	YES
Rank	varchar(50)	YES
AuthorParenth	varchar(100)	YES			Basionym author team
YearParenth	varchar(50)	YES			Basionym year
Author	varchar(100)	YES
Year	varchar(50)	YES
NameAddendum	varchar(50)	NO			e.g. s.l., s.str.
NameAuthorYear	varchar(500)	YES			Full Scientific Name String including Author teams and years
KindOfIdentification	varchar(20)	YES			="det.", "confirm." or "rev."
Identifier	varchar(100)	YES			Person or Team that identified the specimen
IdentificationDate	varchar(50)	YES			Free text, often month or year only
PreferredFlag	varchar(50)	YES			='Yes' if identification is preferred
IdentificationNotes	varchar(500)	YES			Notes/Remarks on an individual identification
Synecology	varchar(50)	YES			='host' if taxon ist host species of specimen
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown
Updated_When	timestamp	YES
Updated_Who	varchar(50)	YES

Table: stock_box

Belongs to: Locations stock/aliquot

Field	Type	Null	Key	Default	Comment
ID_Stock_Box	int(10) unsigned	NO	PRI		PK of listed stock boxes
Stock_Box	varchar(50)	NO			List of boxes used for stock ( table:location_stock)
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown

Table: stock_fridge

Belongs to: Locations stock/aliquot

Field	Type	Null	Key	Default	Comment
ID_Stock_Fridge	int(10) unsigned	NO	PRI		PK of listed stock fridges
Stock_Fridge	varchar(50)	NO			List of fridges used for stock ( table:location_stock)
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown

Table: stock_rack

Belongs to: Locations stock/aliquot

Field	Type	Null	Key	Default	Comment
ID_Stock_Fridge	int(10) unsigned	NO	PRI		PK of listed stock fridges
Stock_Fridge	varchar(50)	NO			List of fridges used for stock ( table:location_stock)
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Created_Who	varchar(50)	NO		unknown

Table: stratigraphy_series

Belongs to: Specimen Tool

Field	Type	Null	Key	Default	Comment
ID_Strat_Series	int(10) unsigned	NO	PRI		PK of all series (stratigraphy)
Series	varchar(50)	NO			List of all series (stratigraphy)

Table: stratigraphy_stage

Belongs to: Specimen Tool

Field	Type	Null	Key	Default	Comment
ID_Strat_Stage	int(10) unsigned	NO	PRI		PK of all stages (Stratigraphy)
Stage	varchar(50)	NO			List of all stages (Stratigraphy)
FK_Strat_Series	int(10) unsigned	NO	MUL		FK to parent series ( table:stratigraphy_series)

Table: subfragment

Belongs to: Sequence data, DNA extractions

Field	Type	Null	Key	Default	Comment
ID_Subfragment	int(10) unsigned	NO	PRI		PK of all subfragments
Subfragment	varchar(100)	YES			List of all subfragments
SubfragmentRemarks	varchar(500)	YES			Remarks on a certain subfragment
Created_Who	varchar(100)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP
Updated_Who	varchar(50)	YES
Updated_When	timestamp	YES

Table: user

Belongs to: User management

Field	Type	Null	Key	Default	Comment
ID_User	int(10) unsigned	NO	PRI		PK of all users
Login	varchar(50)	NO	UNI		List of login names
Password	varchar(50)	NO			md5 encoded password
Signature	varchar(50)	NO			real name of individual user
Email	varchar(100)	NO			user email
FK_Group	int(10) unsigned	YES	MUL		FK to table:usergroups
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	YES		CURRENT_TIMESTAMP

Table: usergroups

Belongs to: User management

Field	Type	Null	Key	Default	Comment
ID_Group	int(10) unsigned	NO	PRI		PK of all user groups
Group	varchar(50)	NO			Group name
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP

Table: usersettings

Belongs to: User management

Field	Type	Null	Key	Default	Comment
ID_Setting	int(10) unsigned	NO	PRI		PK of all user settings
FK_User	int(10) unsigned	NO	MUL		FK to table:user
RecordsPerPage	float	YES			Records per page hitlist (Search Tool)
Sorting	varchar(50)	YES			Sorting of hitlist (Search Tool)
MainMMPath	varchar(500)	YES			Main Multimedia Path (Specimen Tool)
MainMMCreator	varchar(100)	YES			Main Multimedia Creator (Specimen Tool)
MainMMType	varchar(50)	YES			Main Multimedia Type (Specimen Tool)
MainMMLicense	varchar(50)	YES			Main Multimedia License (Specimen Tool)
MainMMUseEXIF	varchar(50)	YES			if 'Yes' use EXIF information (Specimen Tool)
CollDataSameAsMM	varchar(50)	YES			if 'Yes' use Multimedia Creator und Date als Collector and Collection Date
RecordsPerPageSP	float	YES			Records per page hitlist (Specimen Tool)
SortingSP	varchar(50)	YES			Sorting of hitlist (Specimen Tool)
ShowUnitIDSP	varchar(50)	YES
ShowCollCodeSP	varchar(50)	YES
ShowInstCodeSP	varchar(50)	YES
Created_Who	varchar(50)	NO		unknown
Created_When	timestamp	NO		CURRENT_TIMESTAMP

View descriptions

coming soon, required for BioCASe mapping