Difference between revisions of "ER diagram"

From GGBN Wiki
Jump to: navigation, search
(→‎Table: amplifications)
(→‎Entity-relation diagram of the DNA Module V2.0)
 
(187 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=
 +
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==
−
Bild einbauen
+
[[File:ER diagram blank.jpg|650px]]
 
===DNA extractions===
 
===DNA extractions===
 
[[File:DNA Module Package1 DNA extractions.jpg|thumb|200px|ER diagram Package "DNA extractions"]]
 
[[File:DNA Module Package1 DNA extractions.jpg|thumb|200px|ER diagram Package "DNA extractions"]]
Line 63: Line 65:
 
**Customer requests
 
**Customer requests
  
−
===Molecular publications===
+
===Publications===
−
[[File:DNA Module Package5 Molecular publications.jpg|thumb|200px|ER diagram package “Molecular publications”]]
+
UPDATE of docu required.
−
This package allows to associate molecular publications to DNA samples and sequence data. It should not be a complete substitute for a literature management software.
+
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:
 
*Main table:
−
**publications
+
**reference
 
*Related packages:
 
*Related packages:
 
**DNA extractions
 
**DNA extractions
 +
**Specimen Tool
 
**Sequence data
 
**Sequence data
 
<div style="clear:both;"></div>
 
<div style="clear:both;"></div>
Line 114: Line 117:
 
*Specimen data providers
 
*Specimen data providers
  
−
===User management===
 
−
===DNA Bank Network===
 
−
Kurze Einführung, worum gehts, Allgemeiner Aufbau
 
 
==Table descriptions==
 
==Table descriptions==
 +
<div id="wikinote">All of the following information is stored directly in the tables, including the description and comments. Wiki-syntax is generated by running a php script. '''Last update: 2015-02-08'''</div>
 +
 
===Table: abcdmetadata===
 
===Table: abcdmetadata===
 +
See [http://wiki.bgbm.org/dnabankwiki/index.php/Configuration_Tool#Metadata_for_DNA_data Metadata for DNA data]
 +
 
===Table: abcdmetadataspecimens===
 
===Table: abcdmetadataspecimens===
 +
See [http://wiki.bgbm.org/dnabankwiki/index.php/Configuration_Tool#Metadata_for_Specimen_Tool Metadata for Specimen Tool]; will be removed and merged with abcdmetadata soon
 +
 
===Table: aliquots===
 
===Table: aliquots===
 
Belongs to: [[#Locations_stock.2Faliquots | Locations stock/aliquot]], [[#Customer_requests | Customer requests]], [[#DNA_extractions | DNA extractions]]
 
Belongs to: [[#Locations_stock.2Faliquots | Locations stock/aliquot]], [[#Customer_requests | Customer requests]], [[#DNA_extractions | DNA extractions]]
 
{| 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_Aliquots
 
|ID_Aliquots
−
|int
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
−
|ID of the aliquots (Primary Key)
+
|PK of all aliquots
 
|-
 
|-
−
|ID_DNA
+
|FK_DNA
−
|int
+
|int(10) unsigned
−
|No
+
|NO
−
|Key
+
|MUL
−
|No default
+
|
 
|FK to parent DNA sample ([[#Table:_dnabanknumbers | table:dnabanknumbers]])
 
|FK to parent DNA sample ([[#Table:_dnabanknumbers | table:dnabanknumbers]])
 
|-
 
|-
−
|ID_Aliquots_Box
+
|FK_Aliquots_Box
−
|int
+
|int(10) unsigned
−
|Yes
+
|YES
−
|Key
+
|MUL
−
|NULL
+
|
 
|FK to [[#Table:_aliquots_box | table:aliquots_box]]
 
|FK to [[#Table:_aliquots_box | table:aliquots_box]]
 
|-
 
|-
−
|ID_Aliquots_Rack
+
|FK_Aliquots_Rack
−
|int
+
|int(10) unsigned
−
|Yes
+
|YES
−
|Key
+
|MUL
−
|NULL
+
|
 
|FK to [[#Table:_aliquots_rack | table:aliquots_rack]]
 
|FK to [[#Table:_aliquots_rack | table:aliquots_rack]]
 
|-
 
|-
−
|ID_Aliquots_fridge
+
|FK_Aliquots_Fridge
−
|int
+
|int(10) unsigned
−
|Yes
+
|YES
−
|Key
+
|MUL
−
|NULL
+
|
 
|FK to [[#Table:_aliquots_fridge | table:aliquots_fridge]]
 
|FK to [[#Table:_aliquots_fridge | table:aliquots_fridge]]
 
|-
 
|-
 
|Aliquot_Number
 
|Aliquot_Number
 
|varchar(100)
 
|varchar(100)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
 
 
|The number/name of the individual aliquot, mostly the DNA extraction no combined with '-A', '-B' et cetera
 
|The number/name of the individual aliquot, mostly the DNA extraction no combined with '-A', '-B' et cetera
 
|-
 
|-
 
|Origin_Quantity
 
|Origin_Quantity
 
|float
 
|float
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
 
 
|quantity in µl when first prepared
 
|quantity in µl when first prepared
 
|-
 
|-
 
|Rest_Quantity
 
|Rest_Quantity
 
|float
 
|float
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
 
 
|quantity in µl after ordering
 
|quantity in µl after ordering
 
|-
 
|-
−
|Alquot_Position
+
|Aliquot_Position
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
 
 
|position of an individual aliquot on a plate
 
|position of an individual aliquot on a plate
 
|-
 
|-
 
|Aliquot_Barcode
 
|Aliquot_Barcode
 
|varchar(255)
 
|varchar(255)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
 
 
|barcode of an individual aliquot on a plate
 
|barcode of an individual aliquot on a plate
 
|-
 
|-
 
|Order_All
 
|Order_All
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
 
 
|='yes' when whole aliquot has been ordered
 
|='yes' when whole aliquot has been ordered
 
|-
 
|-
 
|Order_Partial
 
|Order_Partial
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
 
 
|='yes' when only a part of the aliquot has been ordered
 
|='yes' when only a part of the aliquot has been ordered
 
|-
 
|-
 
|Order_Rest
 
|Order_Rest
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
 
 
|='yes' when a part of the aliquot has been shipped earlier and now the rest has been ordered
 
|='yes' when a part of the aliquot has been shipped earlier and now the rest has been ordered
 
|-
 
|-
 
|Shipping_All
 
|Shipping_All
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
 
 
|='yes' when only a part of the aliquot has been shipped
 
|='yes' when only a part of the aliquot has been shipped
 
|-
 
|-
 
|Shipping_Partial
 
|Shipping_Partial
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
 
 
|='yes' when only a part of the aliquot has been shipped
 
|='yes' when only a part of the aliquot has been shipped
 
|-
 
|-
 
|Shipping_Rest
 
|Shipping_Rest
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
 
 
|='yes' when a part of the aliquot has been shipped earlier and now the rest has been shipped
 
|='yes' when a part of the aliquot has been shipped earlier and now the rest has been shipped
 
|-
 
|-
 
|Price
 
|Price
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
+
|
−
|price of the individual aliquot (defined via [[General Settings]])
+
|price of the individual aliquot (defined via General Settings)
 
|-
 
|-
 
|Currency
 
|Currency
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
+
|currency of the price (defined via General Settings)
−
|currency of the price (defined via [[General Settings]])
+
|-
−
|}
+
|Created_When
−
 
+
|timestamp
−
===Table: aliquots_box===
+
|NO
−
Belongs to: [[#Locations_stock.2Faliquots | Locations stock/aliquot]]
+
|
−
{| border="1" width="95%"
+
|CURRENT_TIMESTAMP
−
!width="28%" | Column
+
|
−
!width="12%" | Type  
+
|-
−
!width="6%" | Null
+
|Created_Who
−
!width="6%" | Key
+
|varchar(50)
−
!width="20%" | Default
+
|NO
−
!Description
+
|
 +
|unknown
 +
|
 +
|-
 +
|Update_Who
 +
|varchar(50)
 +
|YES
 +
|
 +
|
 +
|
 +
|-
 +
|Update_When
 +
|timestamp
 +
|YES
 +
|
 +
|
 +
|
 +
|}
 +
 
 +
===Table: aliquots_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_Aliquots_Box
 
|ID_Aliquots_Box
−
|int
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
 
|PK of listed aliquot boxes
 
|PK of listed aliquot boxes
 
|-
 
|-
 
|Aliquots_Box
 
|Aliquots_Box
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|NO
 +
|
 
|
 
|
−
|NULL
 
 
|List of boxes used for aliquots ([[#Table:_aliquots | table:aliquots]])
 
|List of boxes used for aliquots ([[#Table:_aliquots | table:aliquots]])
 +
|-
 +
|Created_When
 +
|timestamp
 +
|NO
 +
|
 +
|CURRENT_TIMESTAMP
 +
|
 +
|-
 +
|Created_Who
 +
|varchar(50)
 +
|NO
 +
|
 +
|unknown
 +
|
 
|}
 
|}
  
Line 285: Line 333:
 
Belongs to: [[#Locations_stock.2Faliquots | Locations stock/aliquot]]
 
Belongs to: [[#Locations_stock.2Faliquots | Locations stock/aliquot]]
 
{| 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_Aliquots_Fridge
 
|ID_Aliquots_Fridge
−
|int
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
 
|PK of listed aliquot fridges
 
|PK of listed aliquot fridges
 
|-
 
|-
 
|Aliquots_Fridge
 
|Aliquots_Fridge
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|NO
 +
|
 
|
 
|
−
|NULL
 
 
|List of fridges/freezers used for aliquots ([[#Table:_aliquots | table:aliquots]])
 
|List of fridges/freezers used for aliquots ([[#Table:_aliquots | table:aliquots]])
 +
|-
 +
|Created_When
 +
|timestamp
 +
|NO
 +
|
 +
|CURRENT_TIMESTAMP
 +
|
 +
|-
 +
|Created_Who
 +
|varchar(50)
 +
|NO
 +
|
 +
|unknown
 +
|
 
|}
 
|}
  
Line 310: Line 372:
 
Belongs to: [[#Locations_stock.2Faliquots | Locations stock/aliquot]]
 
Belongs to: [[#Locations_stock.2Faliquots | Locations stock/aliquot]]
 
{| 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_Aliquots_Rack
 
|ID_Aliquots_Rack
−
|int
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
 
|PK of listed aliquot racks
 
|PK of listed aliquot racks
 
|-
 
|-
 
|Aliquots_Rack
 
|Aliquots_Rack
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|NO
 +
|
 
|
 
|
−
|NULL
 
 
|List of racks used for aliquots ([[#Table:_aliquots | table:aliquots]])
 
|List of racks used for aliquots ([[#Table:_aliquots | table:aliquots]])
 +
|-
 +
|Created_When
 +
|timestamp
 +
|NO
 +
|
 +
|CURRENT_TIMESTAMP
 +
|
 +
|-
 +
|Created_Who
 +
|varchar(50)
 +
|NO
 +
|
 +
|unknown
 +
|
 
|}
 
|}
  
 
===Table: amplifications===
 
===Table: amplifications===
−
Belongs to: [[#Sequence_data | Sequence data]], [[#DNA_extractions | DNA extractions]]
+
UNDER CONSTRUCTION! Belongs to: [[#Sequence_data | Sequence data]], [[#DNA_extractions | DNA extractions]]
−
{| border="1" width="98%"
+
{| 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_Amplification
 
|ID_Amplification
−
|int
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
 
|PK of all amplifications
 
|PK of all amplifications
 
|-
 
|-
−
|ID_DNA
+
|FK_DNA
−
|int
+
|int(10) unsigned
−
|No
+
|NO
−
|Key
+
|MUL
−
|No default
+
|
 
|FK to parent DNA sample ([[#Table:_dnabanknumbers | table:dnabanknumbers]])
 
|FK to parent DNA sample ([[#Table:_dnabanknumbers | table:dnabanknumbers]])
 
|-
 
|-
−
|ID_GeneticLocus
+
|FK_GeneticLocus
−
|int
+
|int(10) unsigned
−
|No
+
|NO
−
|Key
+
|MUL
−
|No default
+
|
 
|FK to used Genetic locus ([[#Table:_geneticlocus | table:geneticlocus]])
 
|FK to used Genetic locus ([[#Table:_geneticlocus | table:geneticlocus]])
 
|-
 
|-
−
|GenBankNumber
+
|FK_Subfragment
−
|varchar(150)
+
|int(10) unsigned
−
|Yes
+
|YES
−
|
+
|MUL
−
|NULL
 
−
|At present in use, will be substituted by [[#Table:_sequencing | table:sequencing]]: GenBankNumber; contains the GenBank Accession number or BOLD process ID
 
−
|-
 
−
|Link
 
−
|varchar(500)
 
−
|Yes
 
 
|
 
|
−
|NULL
+
|FK to used Genetic locus ([[#Table:_subfragment | table:subfragment]])
−
|At present in use, will be substituted by [[#Table:_sequencing | table:sequencing]]: GenBankNumber-URI; contains the Link to GenBank or BOLD
 
 
|-
 
|-
 
|Amplification_Date
 
|Amplification_Date
−
|DATE
+
|datetime
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
 
 
|Date of an individual amplification
 
|Date of an individual amplification
 
|-
 
|-
−
|ID_Amplification_Staff
+
|FK_Amplification_Staff
−
|int
+
|int(20) unsigned
−
|Yes
+
|YES
−
|Key
+
|MUL
−
|NULL
+
|
 
|FK to [[#Table:_people | table:people]] contains person who made the amplification
 
|FK to [[#Table:_people | table:people]] contains person who made the amplification
 
|-
 
|-
−
|Amplification_Success
+
|Success
−
|varchar(10)
+
|varchar(50)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
 
 
|contains "Yes" or "No"
 
|contains "Yes" or "No"
 
|-
 
|-
−
|ID_Purification_Method
+
|SuccessDetails
−
|int
+
|varchar(500)
−
|Yes
+
|YES
−
|Key
+
|
−
|NULL
+
|
−
|FK to [[#Table:_purificationmethod | table:purificationmethod]] at present not in use
+
|remarks on amplification, e.g. why it has been failed
 
|-
 
|-
−
|ID_PrimerForward
+
|FK_Amplification_Method
−
|int
+
|int(10) unsigned
−
|Yes
+
|YES
−
|Key
+
|MUL
−
|NULL
+
|
−
|FK to [[#Table:_primer | table:primer]] at present not in use
+
|FK to [[#Table:_methods | table:methods]]
 
|-
 
|-
−
|ID_PrimerReverse
+
|FK_Purification_Method
−
|int
+
|int(10) unsigned
−
|Yes
+
|YES
−
|Key
+
|MUL
−
|NULL
+
|
−
|FK to [[#Table:_primer | table:primer]] at present not in use
+
|FK to [[#Table:_methods | table:methods]]
−
|}
 
−
 
 
−
===Table: authors===
 
−
Belongs to: [[#Molecular_publications | Molecular publications]]
 
−
{| border="1" width="95%"
 
−
!width="15%" | Column
 
−
!width="9%"  | Type
 
−
!width="4%"  | Null
 
−
!width="4%"  | Key
 
−
!width="20%" | Default
 
−
!Description
 
 
|-
 
|-
−
|ID_Author
+
|FK_PrimerForward
−
|bigint
+
|int(10) unsigned
−
|No
+
|YES
−
|PRI
+
|MUL
−
|AUTO_INCREMENT
+
|
−
|PK of listed authors
+
|FK to [[#Table:_primer | table:primer]]
 
|-
 
|-
−
|Author
+
|FK_PrimerReverse
−
|varchar(150)
+
|int(10) unsigned
−
|Yes
+
|YES
 +
|MUL
 
|
 
|
−
|NULL
+
|FK to [[#Table:_primer | table:primer]]
−
|List of authors used by package [[#Molecular_publications |Molecular Publications]]
 
−
|}
 
−
 
 
−
===Table: basisofrecordspecimen===
 
−
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!'''
 
−
{| border="1" width="95%"
 
−
!width="28%" | Column
 
−
!width="12%"  | Type
 
−
!width="6%"  | Null
 
−
!width="6%"  | Key
 
−
!width="20%"  | Default
 
−
!Description
 
 
|-
 
|-
−
|ID_BasisOfRecord_Specimen
+
|Created_When
−
|bigint
+
|timestamp
−
|No
+
|NO
−
|PRI
+
|
−
|AUTO_INCREMENT
+
|CURRENT_TIMESTAMP
−
|PK of listed record bases
+
|
 
|-
 
|-
−
|BasisOfRecord_Specimen
+
|Created_Who
−
|varchar(150)
+
|varchar(50)
−
|Yes
+
|NO
 +
|
 +
|unknown
 +
|
 +
|-
 +
|Update_Who
 +
|varchar(50)
 +
|YES
 +
|
 +
|
 +
|
 +
|-
 +
|Update_When
 +
|timestamp
 +
|YES
 +
|
 +
|
 
|
 
|
−
|NULL
 
−
|List of record bases used by package [[#Specimen_tool | Specimen Tool]]; important for providing data to GBIF or the DNA Bank Network
 
 
|}
 
|}
  
−
===Table: cachecollection===
+
===Table: basisofrecordspecimen===
−
Belongs to: [[#Specimen_cache | Specimen Cache]], [[#Specimen_data_providers | Specimen data providers]], [[#DNA_extractions | DNA extractions]]
+
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 GBIF or GGBN!'''
 
{| border="1" width="95%"
 
{| border="1" width="95%"
−
!width="20%" | Column
+
!width="28%" | Field
−
!width="9%" | Type  
+
!width="12%" | Type
−
!width="4%" | Null
+
!width="6%" | Null
−
!width="4%" | Key
+
!width="6%" | Key
−
! | Default
+
!width="20%" | Default
−
!Description
+
!Comment
 
|-
 
|-
−
|ID_Cache
+
|ID_BasisOfRecord_Specimen
−
|int
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
−
|PK of all DNA voucher specimens referenced via BioCASe or DiGIR (wrappers)
+
|PK of listed record bases in accordance with GBIF and GGBN
 
|-
 
|-
−
|ID_Dataset_Specimen
+
|BasisOfRecord_Specimen
−
|int
+
|varchar(50)
−
|No
+
|NO
−
|Key
+
|
−
|No default
+
|
−
|FK to [[#Table:_dataset | table:dataset]]
+
|List of record bases used by package Specimen Tool; important for providing data to GBIF or GGBN
 
|-
 
|-
−
|UnitID_Specimen
+
|Description
 
|varchar(255)
 
|varchar(255)
−
|No
+
|YES
 +
|
 +
|
 
|
 
|
−
|No default
 
−
|GBIF-Identifier, mostly Barcode or Catalogue Number (part of triple ID)
 
 
|-
 
|-
−
|CollectionCode_Specimen
+
|Created_Who
−
|varchar(255)
+
|varchar(50)
−
|No
+
|NO
 +
|
 +
|unknown
 
|
 
|
−
|No default
 
−
|GBIF-Identifier, Collection of specimen deposit (part of triple ID)
 
 
|-
 
|-
−
|InstitutionCode_Specimen
+
|Created_When
−
|varchar(255)
+
|timestamp
−
|No
+
|NO
 
|
 
|
−
|No default
+
|CURRENT_TIMESTAMP
−
|GBIF-Identifier, Institution of specimen deposit (part of triple ID)
 
−
|-
 
−
|ContinentOrOcean
 
−
|varchar(255)
 
−
|Yes
 
 
|
 
|
−
|NULL
+
|}
−
|Continent or Ocean (collection site)
+
 
 +
===Table: biogeographicregion===
 +
Belongs to: [[#Specimen_Tool | Specimen Tool]]
 +
 
 +
'''SQL create statement filled with standardised content. Existing table contains European categories. Of course you can use other standardised lists.'''
 +
{| border="1" width="95%"
 +
!width="28%" | Field
 +
!width="12%" | Type
 +
!width="6%" | Null
 +
!width="6%" | Key
 +
!width="20%" | Default
 +
!Comment
 
|-
 
|-
−
|CountryName
+
|ID_BiogeographicRegion
−
|varchar(255)
+
|int(10) unsigned
−
|Yes
+
|NO
 +
|PRI
 
|
 
|
−
|Null
+
|PK of all biogeographic categories
−
|English Name of the Country (collection site)
 
 
|-
 
|-
−
|CountryISO2
+
|BiogeographicRegion
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|NO
 
|
 
|
−
|NULL
+
|
−
|[http://en.wikipedia.org/wiki/ISO_3166-2 ISO 3166-2] (collection site)
+
|List of all biogeographic categories
 
|-
 
|-
−
|Collectors
+
|Created_Who
−
|varchar(200)
+
|varchar(50)
−
|Yes
+
|NO
 
|
 
|
−
|NULL
+
|unknown
−
|Collection Team
 
−
|-
 
−
|CollectionDate
 
−
|varchar(200)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|Collection Date (free text)
 
 
|-
 
|-
−
|CollectionDateMin
+
|Created_When
−
|varchar(200)
+
|timestamp
−
|Yes
+
|NO
 
|
 
|
−
|NULL
+
|CURRENT_TIMESTAMP
−
|Collection Date from (free text or ISO, depends on original data provider)
 
−
|-
 
−
|CollectionDateMax
 
−
|varcahr(200)
 
−
|Yes
 
 
|
 
|
−
|NULL
+
|}
−
|Collection Date to (free text or ISO, depends on original data provider)
+
 
 +
===Table: cacheassociatedunits===
 +
Belongs to: [[#Specimen_cache | Specimen Cache]]
 +
{| border="1" width="95%"
 +
!width="28%" | Field
 +
!width="12%" | Type
 +
!width="6%" | Null
 +
!width="6%" | Key
 +
!width="20%" | Default
 +
!Comment
 
|-
 
|-
−
|CollectorsNo
+
|ID_Association
−
|varchar(100)
+
|int(10) unsigned
−
|Yes
+
|NO
 +
|PRI
 
|
 
|
−
|NULL
+
|PK of all associated records for specimens or tissues; e.g. the "grandparent" record of a DNA sample
−
|field number of a specimen given by collector (in the field)
 
 
|-
 
|-
−
|Locality
+
|FK_Cache
−
|varchar(300)
+
|int(10) unsigned
−
|Yes
+
|NO
 +
|MUL
 
|
 
|
−
|NULL
+
|FK to [[#Table:_cachecollection | table:cachecollection]]
−
|free text of locality information (below country level)
 
 
|-
 
|-
−
|Altitude
+
|AssociatedUnitID
−
|varchar(50)
+
|varchar(100)
−
|Yes
+
|NO
 +
|
 
|
 
|
−
|NULL
+
|UnitID/Catalogue Number of associated record
−
|free text of Altitude measurement (from - to - unit)
 
 
|-
 
|-
−
|Longitude
+
|AssociatedCollectionCode
−
|varchar(50)
+
|varchar(100)
−
|Yes
+
|NO
 +
|
 
|
 
|
−
|NULL
+
|SourceID/CollectionCode of associated record
−
|decimal value of Longitude
 
 
|-
 
|-
−
|Latitude
+
|AssociatedInstitutionCode
−
|varchar(50)
+
|varchar(100)
−
|Yes
+
|NO
 +
|
 
|
 
|
−
|NULL
+
|SourceInstitutionID/InstitutionCode of associated record
−
|decimal value of Latitude
 
 
|-
 
|-
−
|CollectionInfo_Other
+
|AssociationType
−
|varchar(500)
+
|varchar(100)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
+
|Description of relation between associated records
−
|no longer in use
 
 
|-
 
|-
−
|TypeStatus
+
|WrapperUrl
−
|varchar(150)
+
|varchar(200)
−
|Yes
+
|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
 +
|
 +
|
 
|
 
|
−
|NULL
 
−
|type status of the specimen
 
 
|}
 
|}
  
−
===Table: cachehighertaxa===
+
===Table: cachecollection===
−
Belongs to: [[#Specimen_cache | Specimen Cache]]
+
Belongs to: [[#Specimen_cache | Specimen Cache]], [[#Specimen_data_providers | Specimen data providers]], [[#DNA_extractions | DNA extractions]]
 
{| 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_HigherTaxon
+
|ID_Cache
−
|int
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
−
|PK of all Higher Taxa, sent by original data provider (wrappers)
+
|PK of all DNA voucher specimens or tissues referenced via BioCASE or GBIF (parent record of a DNA sample)
−
|-
 
−
|ID_CacheTaxonIdentified
 
−
|int
 
−
|No
 
−
|Key
 
−
|No default
 
−
|FK to  [[#Table:_cachetaxonidentified | table:cachetaxonidentified]]
 
 
|-
 
|-
−
|HigherTaxon
+
|FK_Dataset_Specimen
−
|varchar(255)
+
|int(10) unsigned
−
|Yes
+
|NO
 +
|MUL
 
|
 
|
−
|NULL
+
|FK to [[#Table:_dataset | table:dataset]]
−
|Name of the HigherTaxon
 
 
|-
 
|-
−
|TaxonRank
+
|UnitID_Specimen
 
|varchar(255)
 
|varchar(255)
−
|Yes
+
|NO
 
|
 
|
−
|NULL
 
−
|Rank of the Higher Taxon
 
−
|}
 
−
 
−
===Table: cacheimages===
 
−
Belongs to: [[#Specimen_cache | Specimen Cache]]
 
−
{| border="1" width="95%"
 
−
!width="15%" | Column
 
−
!width="9%"  | Type
 
−
!width="4%"  | Null
 
−
!width="4%"  | Key
 
−
!width="20%" | Default
 
−
!Description
 
−
|-
 
−
|ID_Images
 
−
|int
 
−
|No
 
−
|PRI
 
−
|AUTO_INCREMENT
 
−
|PK of all Image URLs, sent by original data provider (wrappers)
 
−
|-
 
−
|ID_Cache
 
−
|int
 
−
|No
 
−
|Key
 
−
|No default
 
−
|FK to  [[#Table:_cache | table:cache]]
 
−
|-
 
−
|Images
 
−
|varchar(500)
 
−
|Yes
 
 
|
 
|
−
|NULL
+
|GBIF-Identifier, UnitID or Catalogue Number (part of triple ID)
−
|List of all Image URLs
 
−
|}
 
−
 
 
−
===Table: cachetaxonidentified===
 
−
Belongs to: [[#Specimen_cache | Specimen Cache]]
 
−
{| border="1" width="95%"
 
−
!width="15%" | Column
 
−
!width="9%"  | Type
 
−
!width="4%"  | Null
 
−
!width="4%"  | Key
 
−
!width="20%" | Default
 
−
!Description
 
 
|-
 
|-
−
|ID_TaxonIdentified
+
|CollectionCode_Specimen
−
|int
 
−
|No
 
−
|PRI
 
−
|AUTO_INCREMENT
 
−
|PK of all specimen determinations, sent by original data provider (wrappers)
 
−
|-
 
−
|ID_Cache
 
−
|int
 
−
|No
 
−
|Key
 
−
|No default
 
−
|FK to  [[#Table:_cache | table:cache]]
 
−
|-
 
−
|Genus
 
 
|varchar(255)
 
|varchar(255)
−
|Yes
+
|NO
−
|Key
+
|
−
|NULL
 
 
|
 
|
 +
|GBIF-Identifier, CollectionCode or SourceID (part of triple ID)
 
|-
 
|-
−
|Subgenus
+
|InstitutionCode_Specimen
 
|varchar(255)
 
|varchar(255)
−
|Yes
+
|NO
 
|
 
|
−
|NULL
 
 
|
 
|
 +
|GBIF-Identifier, InstitutionCode or SourceInstitutionID (part of triple ID)
 
|-
 
|-
−
|FirstEpithet
+
|ContinentOrOcean
 
|varchar(255)
 
|varchar(255)
−
|Yes
+
|YES
−
|Key
 
−
|NULL
 
 
|
 
|
 +
|
 +
|Continent or Ocean (collection site)
 
|-
 
|-
−
|Genus
+
|CountryName
 
|varchar(255)
 
|varchar(255)
−
|Yes
+
|YES
−
|Key
 
−
|NULL
 
 
|
 
|
−
|-
 
−
|SecondEpithet
 
−
|varchar(255)
 
−
|Yes
 
−
|Key
 
−
|NULL
 
 
|
 
|
 +
|English Name of the Country (collection site)
 
|-
 
|-
−
|Rank
+
|CountryISO2
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
+
|ISO 3166-2 (collection site)
−
|Rank of the taxon
 
 
|-
 
|-
−
|HybridFlag
+
|Collectors
−
|varchar(50)
+
|varchar(200)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
 
|
 
|
 +
|Collection Team
 
|-
 
|-
−
|Other
+
|CollectionDate
−
|varchar(500)
+
|varchar(200)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
+
|Collection Date (free text)
−
|No longer in use
 
 
|-
 
|-
−
|NameAuthorYear
+
|CollectionDateMin
−
|varchar(500)
+
|varchar(200)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
+
|Collection Date from (free text or ISO, depends on original data provider)
−
|Full Scientific Name String including Author teams and years
 
 
|-
 
|-
−
|PreferredFlag
+
|CollectionDateMax
−
|varchar(50)
+
|varchar(200)
−
|Yes
+
|YES
−
|
+
|
−
|NULL
+
|
−
|mostly provided as "1" or "true"
+
|Collection Date to (free text or ISO, depends on original data provider)
−
|}
+
|-
−
 
+
|CollectorsNo
−
===Table: cloningmethod===
+
|varchar(100)
−
Belongs to: [[#Sequence_data | Sequence data]]
+
|YES
−
 
+
|
−
'''At present not in use, coming soon.'''
+
|
−
{| border="1" width="95%"
+
|field number of a specimen given by collector (in the field)
−
!width="15%" | Column
 
−
!width="9%"  | Type
 
−
!width="4%"  | Null
 
−
!width="4%"  | Key
 
−
!width="20%" | Default
 
−
!Description
 
 
|-
 
|-
−
|ID_CloningMethod
+
|Locality
−
|int
+
|varchar(300)
−
|No
+
|YES
−
|PRI
+
|
−
|AUTO_INCREMENT
+
|
−
|PK of all Cloning Methos
+
|free text of locality information (below country level)
 
|-
 
|-
−
|CloningMethod
+
|Altitude
 
|varchar(50)
 
|varchar(50)
−
|No
+
|YES
 +
|
 
|
 
|
−
|No default
+
|free text of Altitude measurement (from - to - unit)
−
|List of all cloning methods
 
−
|}
 
−
 
 
−
===Table: continentorocean===
 
−
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!'''
 
−
{| border="1" width="95%"
 
−
!width="15%" | Column
 
−
!width="9%"  | Type
 
−
!width="4%"  | Null
 
−
!width="4%"  | Key
 
−
!width="20%" | Default
 
−
!Description
 
 
|-
 
|-
−
|ID_Continent
+
|Longitude
−
|int
+
|varchar(50)
−
|No
+
|YES
−
|PRI
+
|
−
|AUTO_INCREMENT
+
|
−
|PK of all Continents and Oceans
+
|decimal value of Longitude
 
|-
 
|-
−
|ContinentOrOcean
+
|Latitude
 
|varchar(50)
 
|varchar(50)
−
|No
+
|YES
 +
|
 
|
 
|
−
|No default
+
|decimal value of Latitude
−
|List of all Continents and Oceans
 
−
|}
 
−
 
 
−
===Table: country_continent===
 
−
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!'''
 
−
{| border="1" width="95%"
 
−
!width="15%" | Column
 
−
!width="9%"  | Type
 
−
!width="4%"  | Null
 
−
!width="4%"  | Key
 
−
!width="20%" | Default
 
−
!Description
 
 
|-
 
|-
−
|ID_Country
+
|TypeStatus
−
|int
+
|varchar(150)
−
|No
+
|YES
−
|PRI
+
|
−
|AUTO_INCREMENT
+
|
−
|PK of all Countrys
+
|type status of the specimen
 
|-
 
|-
−
|Continent
+
|GUID_Specimen
 
|varchar(50)
 
|varchar(50)
−
|No
+
|YES
−
|Key
+
|
−
|No default
+
|
−
|Contintens belonging to Country (e.g. Turkey belongs to Asia and Europe)
+
|GUID of the record if provided
−
|}
 
−
 
 
−
===Table: countryisocode===
 
−
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!'''
 
−
{| border="1" width="95%"
 
−
!width="15%" | Column
 
−
!width="9%"  | Type
 
−
!width="4%"  | Null
 
−
!width="4%"  | Key
 
−
!width="20%" | Default
 
−
!Description
 
 
|-
 
|-
−
|ID_Country
+
|Created_When
−
|int
+
|timestamp
−
|No
+
|NO
−
|PRI
+
|
−
|AUTO_INCREMENT
+
|CURRENT_TIMESTAMP
−
|PK of all Countries
+
|
 
|-
 
|-
−
|IsoCode
+
|Created_Who
 
|varchar(50)
 
|varchar(50)
−
|No
+
|NO
 +
|
 +
|unknown
 +
|
 +
|-
 +
|Updated_When
 +
|timestamp
 +
|YES
 +
|
 +
|
 
|
 
|
−
|No default
 
−
|ISO 3611-2 code of the country
 
 
|-
 
|-
−
|Country
+
|Updated_Who
 
|varchar(50)
 
|varchar(50)
−
|No
+
|YES
 +
|
 +
|
 
|
 
|
−
|No default
 
−
|English name of the country
 
 
|}
 
|}
  
−
===Table: dataset===
+
===Table: cachehighertaxa===
−
Belongs to: [[#Specimen_data_providers | Specimen data providers]], [[#Specimen_Tool | Specimen Tool]]
+
Belongs to: [[#Specimen_cache | Specimen Cache]]
−
 
 
−
'''Create statement filled with standardised first row. Please don't make any changes at first row manually!'''
 
 
{| 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_Dataset
+
|ID_HigherTaxon
−
|int
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
−
|PK of all connected specimen datasets (GBIF compliant databases, first row contains the [[#Specimen_Tool | Specimen Tool]])
+
|PK of all Higher Taxa, sent by original data provider (wrappers)
 
|-
 
|-
−
|ID_Provider
+
|FK_CacheTaxonIdentified
−
|int
+
|int(10) unsigned
−
|No
+
|NO
−
|Key
+
|MUL
−
|No default
+
|
−
|FK to [[#Table:_provider | table:provider]]
+
|FK to [[#Table:_cachetaxonidentified | table:cachetaxonidentified]]
 
|-
 
|-
−
|Digir_Resource
+
|HigherTaxon
 
|varchar(255)
 
|varchar(255)
−
|Yes
+
|NO
 +
|
 
|
 
|
−
|NULL
+
|Name of the HigherTaxon
−
|Parameter important for Digir only
 
 
|-
 
|-
−
|Digir_Source
+
|TaxonRank
 
|varchar(255)
 
|varchar(255)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
+
|Rank of the Higher Taxon
−
|Parameter important for Digir only, mostly an URL
 
 
|-
 
|-
−
|Display
+
|Created_When
−
|varchar(255)
+
|timestamp
−
|Yes
+
|NO
 +
|
 +
|CURRENT_TIMESTAMP
 
|
 
|
−
|NULL
 
−
|name of this dataset given by DNA Module-user
 
 
|-
 
|-
−
|Intern_Extern
+
|Created_Who
−
|varchar(10)
+
|varchar(50)
−
|Yes
+
|NO
 +
|
 +
|unknown
 
|
 
|
−
|NULL
 
−
|DNA Module-user decide if this dataset appears in the external or internal dataset list
 
−
|}
 
−
 
−
===Table: degradation===
 
−
Belongs to: [[#DNA_extractions | DNA extractions]]
 
−
 
−
'''Create statement filled with standardised content. Content should not be changed in general, especially when providing data to the DNA Bank Network!'''
 
−
{| border="1" width="95%"
 
−
!width="15%" | Column
 
−
!width="9%"  | Type
 
−
!width="4%"  | Null
 
−
!width="4%"  | Key
 
−
!width="20%" | Default
 
−
!Description
 
 
|-
 
|-
−
|ID_Degradation
+
|Updated_When
−
|int
+
|timestamp
−
|No
+
|YES
−
|PRI
+
|
−
|AUTO_INCREMENT
+
|
−
|PK of all Degradations
+
|
 
|-
 
|-
−
|Degradation
+
|Updated_Who
 
|varchar(50)
 
|varchar(50)
−
|No
+
|YES
 +
|
 +
|
 
|
 
|
−
|No default
 
−
|List of all degradation levels
 
 
|}
 
|}
  
−
===Table: dnabanknumbers===
+
===Table: cachemultimedia===
−
Belongs to: [[#DNA_extractions | DNA extractions]], [[#Specimen_cache | Specimen cache]], [[#Locations_stock.2Faliquots | Location stock/aliquots]], [[#Sequence_data | Sequence data]], [[#Molecular_publications | Molecular publications]]
+
Belongs to: [[#Specimen_cache | Specimen Cache]]
 
{| 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_DNA
+
|ID_Multimedia
−
|bigint
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
−
|PK of all DNA extraction numbers
+
|PK of all Multimedia URLs, sent by original data provider (wrappers)
 
|-
 
|-
−
|ID_Cache
+
|FK_Cache
−
|bigint
+
|int(10) unsigned
−
|No
+
|NO
−
|Key
+
|MUL
−
|No default
+
|
−
|FK to [[#Table:_cachecollection | table:cachecollection]], underlying specimen
+
|FK to [[#Table:_cache | table:cache]]
 
|-
 
|-
−
|ID_Provided_By_People
+
|Multimedia
−
|bigint
+
|varchar(500)
−
|Yes
+
|NO
−
|Key
+
|
−
|NULL
+
|
−
|FK to [[#Table:_people | table:people]], Person who provided the sample (used for donations)
+
|List of all Multimedia URLs
 
|-
 
|-
−
|ID_Type
+
|Created_Who
−
|bigint
 
−
|Yes
 
−
|Key
 
−
|NULL
 
−
|FK to [[#Table:_dnatype | table:dnatype]], Type of the DNA
 
−
|-
 
−
|ID_Location_Stock
 
−
|bigint
 
−
|Yes
 
−
|Key
 
−
|NULL
 
−
|FK to [[#Table:_location_stock | table:location_stock]]
 
−
|-
 
−
|DNA_Bank_Number
 
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|NO
 +
|
 +
|unknown
 
|
 
|
−
|NULL
 
−
|DNA extraction number
 
 
|-
 
|-
−
|ID_Location_Stock
+
|Created_When
−
|bigint
+
|timestamp
−
|Yes
+
|NO
−
|Key
+
|
−
|NULL
+
|CURRENT_TIMESTAMP
−
|FK to [[#Table:_location_stock | table:location_stock]]
+
|
 
|-
 
|-
−
|ID_RelationDNA_Voucher
+
|Updated_When
−
|bigint
+
|timestamp
−
|Yes
+
|YES
−
|Key
+
|
−
|NULL
+
|
−
|FK to [[#Table:_relationdna_voucher | table:relationdna_voucher]]
+
|
 
|-
 
|-
−
|ID_Tissue
+
|Updated_Who
−
|bigint
 
−
|Yes
 
−
|Key
 
−
|NULL
 
−
|FK to [[#Table:_tissue | table:tissue]], type of tissue used for extraction
 
−
|-
 
−
|ID_Preservation
 
−
|bigint
 
−
|Yes
 
−
|Key
 
−
|NULL
 
−
|FK to [[#Table:_preservation | table:preservation]], preservation of underyling tissue/specimen
 
−
|-
 
−
|Extraction_Date
 
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
−
|date of extraction (format YYYY-MM-DD) if known
 
−
|-
 
−
|NoExtractDate
 
−
|tinyint
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|= '1' if date of extraction is not known
 
−
|-
 
−
|ID_Extraction_Method
 
−
|bigint
 
−
|Yes
 
−
|Key
 
−
|NULL
 
−
|FK to [[#Table:_extractionmethod | table:extractionmethod]]
 
−
|-
 
−
|ID_Purification
 
−
|bigint
 
−
|Yes
 
−
|Key
 
−
|NULL
 
−
|FK to [[#Table:_purification | table:purification]], method of purification
 
−
|-
 
−
|ID_Extraction_Staff
 
−
|bigint
 
−
|Yes
 
−
|Key
 
−
|NULL
 
−
|FK to [[#Table:_people | table:people]], person who extracted the DNA
 
−
|-
 
−
|ID_Degradation
 
−
|bigint
 
−
|Yes
 
−
|Key
 
−
|NULL
 
−
|FK to [[#Table:_degradation | table:degradation]]
 
−
|-
 
−
|Check_Date
 
−
|varchar(50)
 
−
|Yes
 
 
|
 
|
−
|NULL
+
|}
−
|date of quality check (format YYYY-MM-DD)
+
 
 +
===Table: cachetaxonidentified===
 +
Belongs to: [[#Specimen_cache | Specimen Cache]]
 +
{| border="1" width="95%"
 +
!width="28%" | Field
 +
!width="12%" | Type
 +
!width="6%" | Null
 +
!width="6%" | Key
 +
!width="20%" | Default
 +
!Comment
 
|-
 
|-
−
|Concentration
+
|ID_TaxonIdentified
−
|varchar(5)
+
|int(10) unsigned
−
|Yes
+
|NO
 +
|PRI
 
|
 
|
−
|NULL
+
|PK of all specimen determinations, sent by original data provider (wrappers)
−
|Concentration value of DNA sample in ng/µl
 
 
|-
 
|-
−
|Absorbance280
+
|FK_Cache
−
|varchar(5)
+
|int(10) unsigned
−
|Yes
+
|NO
 +
|MUL
 
|
 
|
−
|NULL
+
|FK to [[#Table:_cache | table:cache]]
−
|Ratio of absorbance (value, 260/280)
 
 
|-
 
|-
−
|Absorbance230
+
|Genus
−
|varchar(5)
+
|varchar(255)
−
|Yes
+
|YES
 +
|MUL
 +
|
 
|
 
|
−
|NULL
 
−
|Ratio of absorbance (value, 260/230)
 
 
|-
 
|-
−
|Stock_Gone
+
|Subgenus
−
|tinyint
+
|varchar(255)
−
|Yes
+
|YES
 +
|
 +
|
 
|
 
|
−
|NULL
 
−
|='1' if stock is gone
 
 
|-
 
|-
−
|Source_Gone
+
|FirstEpithet
−
|tinyint
+
|varchar(255)
−
|Yes
+
|YES
 +
|MUL
 +
|
 
|
 
|
−
|NULL
 
−
|='1' of source (tissue/specimen) is gone
 
 
|-
 
|-
−
|Notes
+
|SecondEpithet
−
|varchar(500)
+
|varchar(255)
−
|Yes
+
|YES
 +
|MUL
 +
|
 
|
 
|
−
|NULL
 
−
|Notes related to DNA sample, visible for all user groups
 
 
|-
 
|-
−
|Notes_Intern
+
|Rank
−
|varchar(500)
+
|varchar(50)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
+
|Rank of the taxon
−
|Notes related to DNA sample, not visible for guests (user group)
 
 
|-
 
|-
−
|Block_Until
+
|HybridFlag
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
−
|date until sample is blocked for delivery (format YYYY-MM-DD)
 
−
|-
 
−
|Block_General
 
−
|tinyint
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|='1' if DNA sample is blocked for delivery in general; this record won't be visible in the Network's webportal
 
−
|-
 
−
|Backup_Aliquot
 
−
|tinyint
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|='1' if a backup aliquot is deposited in an external institution
 
−
|}
 
−
 
−
===Table: dnatype===
 
−
Belongs to: [[#DNA_extractions | DNA extractions]]
 
−
 
−
'''Create statement filled with standardised content. Existing content should not be changed, but you can add further entries.'''
 
−
{| border="1" width="95%"
 
−
!width="15%" | Column
 
−
!width="9%"  | Type
 
−
!width="4%"  | Null
 
−
!width="4%"  | Key
 
−
!width="20%" | Default
 
−
!Description
 
 
|-
 
|-
−
|ID_Type
+
|NameAuthorYear
−
|int
+
|varchar(500)
−
|No
+
|YES
−
|PRI
+
|
−
|AUTO_INCREMENT
+
|
−
|PK of all Types
+
|Full Scientific Name String including Author teams and years
 
|-
 
|-
−
|Type
+
|PreferredFlag
 
|varchar(50)
 
|varchar(50)
−
|No
+
|YES
 +
|
 
|
 
|
−
|No default
+
|mostly provided as "1" or "true" ; if empty filled with "1"
−
|List of all DNA types
 
−
|}
 
−
 
 
−
===Table: extractionmethod===
 
−
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_Method
+
|Created_When
−
|int
+
|timestamp
−
|No
+
|NO
−
|PRI
+
|
−
|AUTO_INCREMENT
+
|CURRENT_TIMESTAMP
−
|PK of all Extraction methods
+
|
 
|-
 
|-
−
|Method
+
|Created_Who
 
|varchar(50)
 
|varchar(50)
−
|No
+
|NO
 +
|
 +
|unknown
 
|
 
|
−
|No default
 
−
|List of all Extraction Methods (Kits)
 
 
|-
 
|-
−
|Company
+
|Updated_When
 +
|timestamp
 +
|YES
 +
|
 +
|
 +
|
 +
|-
 +
|Updated_Who
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|YES
 +
|
 +
|
 
|
 
|
−
|NULL
 
−
|List of all Companies
 
 
|}
 
|}
  
−
===Table: geneticlocus===
+
===Table: cloningmethod===
−
Belongs to: [[#DNA_extractions | DNA extractions]]
+
Belongs to: [[#Sequence_data | Sequence data]]
 +
 
 +
'''At present not in use, coming soon.'''
 +
 
 +
===Table: countrorcontinent===
 +
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 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_GeneticLocus
+
|ID_Continent
−
|int
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
−
|PK of all Genetic Loci/Fragments
+
|PK of all continents and oceans
 
|-
 
|-
−
|GeneticLocus
+
|ContinentOrOcean
−
|varchar(150)
+
|varchar(50)
−
|No
+
|NO
 +
|
 
|
 
|
−
|No default
+
|List of all continents and oceans; no FK
−
|List of all Genetic Loci/Fragments
 
 
|}
 
|}
  
−
===Table: images===
+
===Table: country_continent===
−
Belongs to: [[#DNA_extractions | DNA extractions]]
+
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 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_Images
+
|FK_Country
−
|int
+
|int(10) unsigned
−
|No
+
|NO
−
|PRI
+
|MUL
−
|AUTO_INCREMENT
+
|
−
|PK of all Gel images
+
|FK of all Countries
 
|-
 
|-
−
|ID_DNA
+
|Continent
−
|int
+
|varchar(50)
−
|Yes
+
|NO
−
|Key
+
|MUL
−
|NULL
 
−
|FK to parent DNA sample ([[#Table:_dnabanknumbers | table:dnabanknumbers]])
 
−
|-
 
−
|ImageUrl
 
−
|varchar(500)
 
−
|Yes
 
 
|
 
|
−
|NULL
+
|Contintens belonging to Country (e.g. Turkey belongs to Asia and Europe)
−
|List of all Gel image URLs
 
−
|-
 
−
|ImageRemarks
 
−
|varchar(150)
 
−
|Yes
 
−
|
 
−
|NULL
 
−
|Remarks to individual Gel images
 
 
|}
 
|}
  
−
===Table: journals===
+
===Table: countryisocode===
−
Belongs to: [[#Molecular_publications | Molecular publications]]
+
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!'''
 
{| 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_Journal
+
|ID_Country
−
|int
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
−
|PK of all Journals
+
|PK of all Countries
 
|-
 
|-
−
|Journal_Display
+
|IsoCode
−
|varchar(255)
+
|varchar(50)
−
|No
+
|NO
 +
|
 
|
 
|
−
|No default
+
|ISO 3611-2 code of the country
−
|List of all Journal abbreviations; this column is used for display purposes
 
 
|-
 
|-
−
|Journal_All
+
|Country
−
|varchar(500)
+
|varchar(150)
−
|No
+
|NO
 +
|
 
|
 
|
−
|No default
+
|English name of the country
−
|List of all Journals; this column is used for search purposes
 
 
|}
 
|}
  
−
===Table: location_stock===
+
===Table: dataset===
−
Belongs to: [[#DNA_extractions | DNA extractions]], [[#Locations_stock.2Faliquots | Locations stock/aliquots]]
+
Belongs to: [[#Specimen_data_providers | Specimen data providers]], [[#Specimen_Tool | Specimen Tool]]
  
 +
'''SQL create statement filled with standardised first row. Please don't make any changes at first row manually!'''
 
{| 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_Location_Stock
+
|ID_Dataset
−
|int
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
−
|PK of all stock locations
+
|PK of all connected specimen datasets (GBIF compliant databases, first row contains the Specimen Tool)
 
|-
 
|-
−
|ID_Stock_Box
+
|FK_Provider
−
|int
+
|int(10) unsigned
−
|Yes
+
|YES
−
|Key
+
|MUL
−
|NULL
+
|
−
|FK to [[#Table:_stock_box | table:stock_box]]
+
|FK to [[#Table:_provider | table:provider]]
 
|-
 
|-
−
|ID_Stock_Rack
+
|Digir_Resource
−
|int
+
|varchar(255)
−
|Yes
+
|YES
−
|Key
+
|
−
|NULL
+
|
−
|FK to [[#Table:_stock_rack | table:stock_rack]]
+
|Parameter important for Digir only
 
|-
 
|-
−
|ID_Stock_Fridge
+
|Digir_Source
−
|int
+
|varchar(255)
−
|Yes
+
|YES
−
|Key
+
|
−
|NULL
+
|
−
|FK to [[#Table:_stock_fridge | table:stock_fridge]]
+
|Parameter important for Digir only, mostly an URL
 
|-
 
|-
−
|Stock_Position
+
|Display
−
|varchar(50)
+
|varchar(255)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
+
|Name of this dataset given by DNA Module-user
−
|position of the stock tube on a plate
 
 
|-
 
|-
−
|Stock_Barcode
+
|Internal_External
−
|varchar(200)
+
|varchar(10)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
−
|barcode of the stock tube on a plate
 
−
|-
 
−
|Origin_Quantity
 
−
|float
 
−
|Yes
 
 
|
 
|
−
|NULL
+
|DNA Module-user decide if this dataset appears in the external or internal dataset list
−
|quantity in µl when first prepared
 
−
|-
 
−
|Rest_Quantity
 
−
|float
 
−
|Yes
 
−
|
 
−
|NULL
 
−
|quantity in µl after using stock partly for an aliquot
 
−
|}
 
−
 
 
−
===Table: people===
 
−
Belongs to: [[#DNA_extractions | DNA extractions]], [[#Customer_requests | Customer requests]], [[#Sequence_data | Sequence data]]
 
−
 
 
−
{| border="1" width="95%"
 
−
!width="15%" | Column
 
−
!width="9%"  | Type
 
−
!width="4%"  | Null
 
−
!width="4%"  | Key
 
−
!width="20%" | Default
 
−
!Description
 
−
|-
 
−
|ID_People
 
−
|int
 
−
|No
 
−
|PRI
 
−
|AUTO_INCREMENT
 
−
|PK of all Persons
 
 
|-
 
|-
−
|FormOfAddress
+
|DatasetKey_GBIF
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
 
|
 
|
 +
|Dataset guid (key) provided by GBIF via web services
 
|-
 
|-
−
|Title
+
|Protocol
−
|varchar(100)
+
|varchar(50)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
 
|
 
|
 +
|Protocol name provided by GBIF via web services (e.g. BIOCASE)
 
|-
 
|-
−
|Forename
+
|InstitutionCode
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
 
|
 
|
 +
|Deprecated?
 
|-
 
|-
−
|Surname
+
|Created_When
−
|varchar(50)
+
|timestamp
−
|Yes
+
|NO
 
|
 
|
−
|NULL
+
|CURRENT_TIMESTAMP
 
|
 
|
 
|-
 
|-
−
|Phone
+
|Created_Who
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|NO
 
|
 
|
−
|NULL
+
|unknown
 
|
 
|
 +
|}
 +
 +
===Table: dnabanknumbers===
 +
Belongs to: [[#DNA_extractions | DNA extractions]], [[#Specimen_cache | Specimen cache]], [[#Locations_stock.2Faliquots | Location stock/aliquots]], [[#Sequence_data | Sequence data]]
 +
{| border="1" width="95%"
 +
!width="28%" | Field
 +
!width="12%" | Type
 +
!width="6%" | Null
 +
!width="6%" | Key
 +
!width="20%" | Default
 +
!Comment
 
|-
 
|-
−
|Email
+
|ID_DNA
−
|varchar(100)
+
|int(10) unsigned
−
|Yes
+
|NO
 +
|PRI
 
|
 
|
−
|NULL
+
|PK of all DNA extraction numbers
 +
|-
 +
|FK_Cache
 +
|int(10) unsigned
 +
|YES
 +
|MUL
 
|
 
|
 +
|FK to [[#Table:_cachecollection | table:cachecollection]], underlying specimen
 
|-
 
|-
−
|Remarks
+
|FK_AcquiredFrom
−
|varchar(500)
+
|int(10) unsigned
−
|Yes
+
|YES
 +
|MUL
 
|
 
|
−
|NULL
+
|FK to [[#Table:_people | table:people]], Person who provided the sample (used for donations)
 +
|-
 +
|FK_Type
 +
|int(10) unsigned
 +
|YES
 +
|MUL
 
|
 
|
 +
|FK to [[#Table:_dnatype | table:dnatype]], Type of the DNA
 
|-
 
|-
−
|Name_All
+
|FK_Location_Stock
−
|varchar(100)
+
|int(10) unsigned
−
|Yes
+
|YES
 +
|MUL
 
|
 
|
−
|NULL
+
|FK to [[#Table:_location_stock | table:location_stock]]
−
|name string (Surname, Forename)
 
 
|-
 
|-
−
|Institut
+
|DNA_Bank_Number
−
|varchar(150)
+
|varchar(50)
−
|Yes
+
|NO
 
|
 
|
−
|NULL
 
 
|
 
|
 +
|DNA extraction number; Must be unique
 
|-
 
|-
−
|Division
+
|FK_Relation
−
|varchar(150)
+
|int(10) unsigned
−
|Yes
+
|NO
 +
|MUL
 
|
 
|
−
|NULL
+
|FK to [[#Table:_relation | table:relation]]
 +
|-
 +
|FK_Tissue
 +
|int(10) unsigned
 +
|YES
 +
|MUL
 
|
 
|
 +
|FK to [[#Table:_tissue | table:tissue]], type of tissue used for extraction
 
|-
 
|-
−
|Street
+
|FK_Preservation
−
|varchar(150)
+
|int(10) unsigned
−
|Yes
+
|YES
−
|
+
|MUL
−
|NULL
 
 
|
 
|
 +
|FK to [[#Table:_preservation | table:preservation]], preservation of underyling tissue/specimen
 
|-
 
|-
−
|City
+
|Extraction_Date
−
|varchar(150)
+
|datetime
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
 
|
 
|
 +
|date of extraction (format YYYY-MM-DD) if known
 
|-
 
|-
−
|Postal_Code
+
|NoExtractDate
−
|varchar(150)
+
|int(11)
−
|Yes
+
|NO
 
|
 
|
−
|NULL
 
 
|
 
|
 +
|= '1' if date of extraction is not known
 
|-
 
|-
−
|Country
+
|FK_Extraction_Method
−
|varchar(150)
+
|int(10) unsigned
−
|Yes
+
|YES
−
|
+
|MUL
−
|NULL
+
|
 +
|FK to [[#Table:_methods | table:methods]], extraction kit
 +
|-
 +
|FK_Purification_Method
 +
|int(10) unsigned
 +
|YES
 +
|MUL
 
|
 
|
 +
|FK to [[#Table:_methods | table:methods]], method of purification
 
|-
 
|-
−
|Institut_Delivery
+
|FK_Extraction_Staff
−
|varchar(150)
+
|int(10) unsigned
−
|Yes
+
|YES
 +
|MUL
 
|
 
|
−
|NULL
+
|FK to [[#Table:_people | table:people]], person who extracted the DNA
−
|if shipping/delivery address differs from billing address
 
 
|-
 
|-
−
|Division_Delivery
+
|Quality_Check_Date
−
|varchar(150)
+
|datetime
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
 
|
 
|
 +
|date of quality check (format YYYY-MM-DD)
 
|-
 
|-
−
|Street_Delivery
+
|FK_Concentration_Method
−
|varchar(150)
+
|int(10) unsigned
−
|Yes
+
|YES
 +
|MUL
 
|
 
|
−
|NULL
+
|FK to [[#Table:_methods | table:methods]], method to determine concentration
 +
|-
 +
|FK_GelImage
 +
|int(11) unsigned
 +
|YES
 +
|MUL
 
|
 
|
 +
|FK to [[#Table:_gelimages | table:gelimages]]
 
|-
 
|-
−
|City_Delivery
+
|Concentration
−
|varchar(150)
+
|varchar(10)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
 
|
 
|
 +
|Concentration value of DNA sample in ng/µl
 
|-
 
|-
−
|Postal_Code_Delivery
+
|Absorbance280
−
|varchar(150)
+
|varchar(10)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
 
|
 
|
 +
|Ratio of absorbance (value, 260/280)
 
|-
 
|-
−
|Country_Delivery
+
|Absorbance230
−
|varchar(150)
+
|varchar(10)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
 
|
 
|
 +
|Ratio of absorbance (value, 260/230)
 
|-
 
|-
−
|ExtractionStaff
+
|Weight
 
|varchar(10)
 
|varchar(10)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
+
|DNA weight
−
|='Yes' if person has extracted DNA samples
 
 
|-
 
|-
−
|Provider
+
|WeightUnit
 
|varchar(10)
 
|varchar(10)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
+
|Unit of measuring DNA weight
−
|='Yes' if person has provided DNA samples
 
 
|-
 
|-
−
|Customer
+
|FK_Weight_Method
−
|varchar(10)
+
|int(10) unsigned
−
|Yes
+
|YES
 +
|MUL
 
|
 
|
−
|NULL
+
|FK to [[#Table:_methods | table:methods]], method to measure weight
−
|='Yes' if person has ordered DNA samples
 
−
|}
 
−
 
 
−
===Table: preservation===
 
−
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_Preservation
+
|Stock_Gone
−
|int
+
|int(10)
−
|No
+
|YES
−
|PRI
+
|
−
|AUTO_INCREMENT
+
|
−
|PK of all Preservation types (preservation of Tissue/Specimen)
+
|='1' if stock is gone
 
|-
 
|-
−
|Preservation
+
|Source_Gone
−
|varchar(150)
+
|int(10)
−
|No
+
|YES
 +
|
 
|
 
|
−
|No default
+
|='1' of source (tissue/specimen) is gone
−
|List of all Preservations
 
−
|}
 
−
 
 
−
===Table: prevalentaspect===
 
−
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_Aspect
+
|Notes
−
|int
+
|varchar(500)
−
|No
+
|YES
−
|PRI
+
|
−
|AUTO_INCREMENT
 
−
|PK of all aspect categories
 
−
|-
 
−
|Stock_Rack
 
−
|varchar(50)
 
−
|Yes
 
 
|
 
|
−
|NULL
+
|Notes related to DNA sample, visible for all user groups
−
|List of all aspect categories
 
−
|}
 
−
 
 
−
===Table: primer===
 
−
Belongs to: [[#Sequence_data | Sequence data]]
 
−
 
 
−
'''At present not in use, coming soon.'''
 
−
{| border="1" width="95%"
 
−
!width="15%" | Column
 
−
!width="9%"  | Type
 
−
!width="4%"  | Null
 
−
!width="4%"  | Key
 
−
!width="20%" | Default
 
−
!Description
 
 
|-
 
|-
−
|ID_Primer
+
|Notes_Intern
−
|int
+
|varchar(500)
−
|No
+
|YES
−
|PRI
+
|
−
|AUTO_INCREMENT
+
|
−
|PK of all Primers
+
|Notes related to DNA sample, not visible for guests (user group)
 
|-
 
|-
−
|Name
+
|Blocked_Until
−
|varchar(100)
+
|datetime
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
+
|date until sample is blocked for delivery (format YYYY-MM-DD)
−
|List of all Primer names
 
 
|-
 
|-
−
|Sequence
+
|Blocked_For_Loaning
−
|varchar(5000)
+
|int(10)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
+
|='1' if DNA sample is blocked for delivery only; this record will be visible in the Network's data portal
−
|List of all Primer sequences
 
 
|-
 
|-
−
|ReferenceCitation
+
|Blocked_For_Publishing
−
|varchar(500)
+
|int(10)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
+
|='1' if DNA sample is blocked in general; this record won't be visible in the Network's data portal
−
|List of all Primer references
 
 
|-
 
|-
−
|ReferenceLink
+
|Loan_Conditions
 
|varchar(500)
 
|varchar(500)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
+
|
−
|List of all links to Primer references
+
|certain conditions for loaning the DNA sample
−
|}
 
−
 
 
−
===Table: provider===
 
−
Belongs to: [[#Specimen_data_providers | Specimen data providers]]
 
−
 
 
−
{| border="1" width="95%"
 
−
!width="15%" | Column
 
−
!width="9%"  | Type
 
−
!width="4%"  | Null
 
−
!width="4%"  | Key
 
−
!width="20%" | Default
 
−
!Description
 
 
|-
 
|-
−
|ID_Provider
+
|Backup_Aliquot
−
|int
+
|int(10)
−
|No
+
|YES
−
|PRI
 
−
|AUTO_INCREMENT
 
−
|PK of all Specimen Data Providers
 
−
|-
 
−
|Provider
 
−
|varchar(100)
 
−
|No
 
 
|
 
|
−
|No default
 
−
|List of all Wrapper URLs
 
−
|-
 
−
|ID_Schema
 
−
|int
 
−
|No
 
−
|Key
 
−
|No default
 
−
|FK to required Schema ([[#Table:_schemata | table:schemata]])
 
−
|}
 
−
 
−
===Table: publications===
 
−
Belongs to: [[#Molecular_publications | Molecular publications]]
 
−
 
−
{| border="1" width="95%"
 
−
!width="15%" | Column
 
−
!width="9%"  | Type
 
−
!width="4%"  | Null
 
−
!width="4%"  | Key
 
−
!width="20%" | Default
 
−
!Description
 
−
|-
 
−
|ID_Publications
 
−
|int
 
−
|No
 
−
|PRI
 
−
|AUTO_INCREMENT
 
−
|PK of all Molecular Publications
 
−
|-
 
−
|Editors
 
−
|varchar(10)
 
−
|Yes
 
 
|
 
|
−
|NULL
+
|='1' if a backup aliquot is deposited in an external institution
−
|Editor(s) of publications
 
 
|-
 
|-
−
|Year
+
|GUID
−
|varchar(20)
+
|varchar(50)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
−
|Year of publication
 
−
|-
 
−
|Title
 
−
|varchar(1000)
 
−
|Yes
 
 
|
 
|
−
|NULL
+
|GUID of the DNA sample
−
|Title of publication
 
 
|-
 
|-
−
|ID_Journal
+
|Created_Who
−
|int
+
|varchar(50)
−
|No
+
|NO
−
|Key
 
−
|No default
 
−
|FK to [[#Table:_journals | table:journals]]
 
−
|-
 
−
|Volume
 
−
|varchar(10)
 
−
|Yes
 
 
|
 
|
−
|NULL
+
|unknown
 
|
 
|
 
|-
 
|-
−
|Pages
+
|Created_When
−
|varchar(20)
+
|timestamp
−
|Yes
+
|NO
 
|
 
|
−
|NULL
+
|CURRENT_TIMESTAMP
 
|
 
|
 
|-
 
|-
−
|Book
+
|Update_Who
−
|varchar(100)
+
|varchar(50)
−
|Yes
+
|YES
 +
|
 +
|
 +
|
 +
|-
 +
|Update_When
 +
|timestamp
 +
|YES
 +
|
 +
|
 
|
 
|
−
|NULL
 
−
|Book authors and title
 
 
|-
 
|-
−
|Publisher
+
|Lock_Who
−
|varchar(250)
+
|varchar(50)
−
|Yes
+
|YES
 +
|
 +
|
 
|
 
|
−
|NULL
 
−
|Book publisher
 
 
|-
 
|-
−
|Link
+
|Lock_When
−
|varchar(400)
+
|timestamp
−
|Yes
+
|YES
 +
|
 +
|
 
|
 
|
−
|NULL
 
−
|url to online version if available
 
 
|}
 
|}
  
−
===Table: publications_amplifications===
+
===Table: dnatype===
−
Belongs to: [[#Molecular_publications | Molecular publications]], [[#Sequence_data | Sequence data]], [[#DNA_extractions | DNA extractions]]
+
Belongs to: [[#DNA_extractions | DNA extractions]]
  
 +
'''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_Pub_Amp
+
|ID_Type
−
|bigint
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
−
|PK of all references between amplifications/DNA samples and publications
+
|PK of all DNA Types
 
|-
 
|-
−
|ID_Publications
+
|Type
−
|bigint
+
|varchar(50)
−
|No
+
|NO
−
|Key
+
|
−
|No default
+
|
−
|FK to [[#Table:_publications | table:publications]]
+
|List of all DNA types
 
|-
 
|-
−
|ID_Amplification
+
|Created_When
−
|bigint
+
|timestamp
−
|No
+
|NO
−
|Key
+
|
−
|No default
+
|CURRENT_TIMESTAMP
−
|FK to [[#Table:_amplifications | table:amplifications]]
 
−
|-
 
−
|ID_DNA
 
−
|bigint
 
−
|No
 
−
|Key
 
−
|No default
 
−
|FK to [[#Table:_dnabanknumbers | table:dnabanknumbers]]
 
−
|-
 
−
|Paper_Cache
 
−
|varchar(500)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|Citation string of publication
 
 
|-
 
|-
−
|GeneticLocus
+
|Created_Who
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|NO
 +
|
 +
|unknown
 
|
 
|
−
|NULL
 
−
|Genetic Locus/Fragment used for underlying analysis
 
 
|}
 
|}
  
−
===Table: publications_authors===
+
===Table: gelimages===
−
Belongs to: [[#Molecular_publications | Molecular publications]]
+
Belongs to: [[#DNA_extractions | DNA extractions]]
−
 
 
 
{| 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
 
|-
 
|-
−
|Order
+
|ID_Images
−
|bigint
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
−
|Sort sequence of authors of an individual publication
+
|PK of all gel images
 
|-
 
|-
−
|ID_Publications
+
|FileURI
−
|bigint
+
|varchar(500)
−
|No
+
|NO
−
|Key
+
|
−
|No default
+
|
−
|FK to [[#Table:_publications | table:publications]]
+
|URL of gel image
−
|-
 
−
|ID_Author
 
−
|bigint
 
−
|No
 
−
|Key
 
−
|No default
 
−
|FK to [[#Table:_authors | table:authors]]
 
−
|}
 
−
 
 
−
===Table: purification===
 
−
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_Purification
+
|GelRemarks
−
|bigint
 
−
|No
 
−
|PRI
 
−
|AUTO_INCREMENT
 
−
|PK of all purification methods
 
−
|-
 
−
|Method
 
 
|varchar(150)
 
|varchar(150)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|No default
+
|Remarks on a gel
−
|List of all purification methods
 
 
|-
 
|-
−
|Company
+
|GelVoltage
−
|varchar(150)
+
|varchar(50)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|No default
+
|Gel voltage
−
|List of all companies
 
−
|}
 
−
 
 
−
===Table: relationdna_voucher===
 
−
Belongs to: [[#DNA_extractions | DNA extractions]]
 
−
 
 
−
'''Create statement filled with standardised content. You can change or add relationships if needed. Ideally you should use the existing relationships.'''
 
−
{| border="1" width="95%"
 
−
!width="15%" | Column
 
−
!width="9%"  | Type
 
−
!width="4%"  | Null
 
−
!width="4%"  | Key
 
−
!width="20%" | Default
 
−
!Description
 
 
|-
 
|-
−
|ID_RelationDNA_Voucher
+
|GelConcentration
−
|bigint
+
|varchar(50)
−
|No
+
|YES
−
|PRI
+
|
−
|AUTO_INCREMENT
+
|
−
|PK of all relationships
+
|Gel concentration
 
|-
 
|-
−
|RelationDNA_Voucher
+
|GelDuration
−
|varchar(250)
+
|varchar(50)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
+
|Gel duration
−
|List of all relationships
 
−
|}
 
−
 
 
−
===Table: request===
 
−
Belongs to: [[#Customer_requests | Customer requests]], [[#Locations_stock.2Faliquots | Locations stock/aliquots]], [[#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_Request
+
|GelLadder
−
|bigint
+
|varchar(100)
−
|No
+
|YES
−
|PRI
+
|
−
|AUTO_INCREMENT
+
|
−
|PK of all customer requests
+
|Gel ladder
 
|-
 
|-
−
|ID_People
+
|GelStain
−
|bigint
+
|varchar(100)
−
|No
+
|YES
−
|Key
+
|
−
|No default
+
|
−
|FK to customer ([[#Table:_people | table:people]])
+
|Gel stain
 
|-
 
|-
−
|ID_DNA
+
|Created_Who
−
|bigint
+
|varchar(50)
−
|No
+
|NO
−
|Key
+
|
−
|No default
+
|unknown
−
|FK to parent DNA sample ([[#Table:_dnabanknumbers | table:dnabanknumbers]])
+
|
 
|-
 
|-
−
|ID_Aliquots
+
|Created_When
−
|bigint
+
|timestamp
−
|No
+
|NO
−
|Key
+
|
−
|No default
+
|CURRENT_TIMESTAMP
−
|FK to [[#Table:_aliquots | table:aliquots]]
+
|
 
|-
 
|-
−
|Request_Date
+
|Updated_Who
−
|date
+
|varchar(50)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
−
|Date of request
 
−
|-
 
−
|Request_Number_Aliquots
 
−
|varchar(150)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|Aliquot Number requested
 
−
|-
 
−
|Request_Volume
 
−
|float
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|requested/shipped volume in µl
 
 
|-
 
|-
−
|Request_Notes
+
|Updated_When
−
|varchar(500)
+
|timestamp
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
−
|Notes regarding the request
 
−
|-
 
−
|Shipping_Date
 
−
|date
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|Date of shipping
 
−
|-
 
−
|Agreement
 
−
|varchar(10)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|='Yes' if Material transfer agreement has been signed by customer
 
 
|}
 
|}
  
−
===Table: schemata===
+
===Table: geneticlocus===
−
Belongs to: [[#Specimen_data_providers | Specimen data providers]]
+
Belongs to: [[#DNA_extractions | DNA extractions]]
−
 
 
 
{| 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_Schema
+
|ID_GeneticLocus
−
|int
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
−
|PK of all schema queries
+
|PK of all Genetic Loci/Fragments
 
|-
 
|-
−
|Schemata
+
|GeneticLocus
−
|varchar(50
+
|varchar(150)
−
|Yes
+
|NO
 +
|
 
|
 
|
−
|NULL
+
|List of all Genetic Loci/Fragments
−
|Name of the Schema
 
 
|-
 
|-
−
|QueryPart1
+
|GeneticLocusRemarks
−
|varchar(2000)
+
|varchar(255)
−
|Yes
+
|NO
 +
|
 
|
 
|
−
|NULL
+
|Remarks on Genetic Locus/Fragment
−
|Part one of required query
 
 
|-
 
|-
−
|QueryPart1_1
+
|Created_When
−
|varchar(200)
+
|timestamp
−
|Yes
+
|NO
 +
|
 +
|CURRENT_TIMESTAMP
 
|
 
|
−
|NULL
 
−
|special part one of required query
 
 
|-
 
|-
−
|QueryPart1_2
+
|Created_Who
−
|varchar(2000)
+
|varchar(50)
−
|Yes
+
|NO
 +
|
 +
|unknown
 
|
 
|
−
|NULL
 
−
|special part two of required query
 
 
|-
 
|-
−
|QueryPart1_2b
+
|Updated_Who
−
|varchar(2000)
+
|varchar(50)
−
|Yes
+
|YES
 +
|
 +
|
 
|
 
|
−
|NULL
 
−
|special part three of required query
 
 
|-
 
|-
−
|QueryPart1b
+
|Updated_When
−
|varchar(2000)
+
|timestamp
−
|Yes
+
|YES
 +
|
 +
|
 
|
 
|
−
|NULL
+
|}
−
|Part one B of required query
+
 
 +
===Table: geocodemethod===
 +
Belongs to: [[#Specimen_Tool | 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.'''
 +
{| border="1" width="95%"
 +
!width="28%" | Field
 +
!width="12%" | Type
 +
!width="6%" | Null
 +
!width="6%" | Key
 +
!width="20%" | Default
 +
!Comment
 
|-
 
|-
−
|QueryPart2
+
|ID_Geocode
−
|varchar(2000)
+
|int(10) unsigned
−
|Yes
+
|NO
 +
|PRI
 
|
 
|
−
|NULL
+
|PK of all geocode methods
−
|Part two of required query
 
 
|-
 
|-
−
|QueryPart2a
+
|Geocode
−
|varchar(2000)
+
|varchar(50)
−
|Yes
+
|NO
 
|
 
|
−
|NULL
+
|
−
|Part two A of required query
+
|List of all geocode methods
 
|-
 
|-
−
|QueryPart3
+
|Created_Who
−
|varchar(2000)
+
|varchar(50)
−
|Yes
+
|NO
 +
|
 +
|unknown
 
|
 
|
−
|NULL
 
−
|Part three of required query
 
 
|-
 
|-
−
|QueryPart4
+
|Created_When
−
|varchar(2000)
+
|timestamp
−
|Yes
+
|NO
 
|
 
|
−
|NULL
+
|CURRENT_TIMESTAMP
−
|Part four of required query
 
−
|-
 
−
|QueryPart5
 
−
|varchar(2000)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|Part five of required query
 
 
|}
 
|}
  
−
===Table: seasandoceans===
+
===Table: habitatcode===
 
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 the DNA Bank Network!'''
+
'''SQL create statement filled with standardised content. Existing table contains European categories (EUNIS). Of course you can use other standardised lists.'''
 
{| 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_SeasAndOceans
+
|ID_HabitatCode
−
|int
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
−
|PK of all seas
+
|PK of all habitat code categories
 
|-
 
|-
−
|SeasAndOceans
+
|HabitatCode
−
|varchar(255)
+
|varchar(50)
−
|No
+
|NO
 +
|
 +
|
 +
|List of all habitat code categories
 +
|-
 +
|Created_Who
 +
|varchar(50)
 +
|NO
 +
|
 +
|unknown
 
|
 
|
−
|No default
 
−
|List of all seas
 
 
|-
 
|-
−
|Ocean
+
|Created_When
−
|varchar(255)
+
|timestamp
−
|Yes
+
|NO
 +
|
 +
|CURRENT_TIMESTAMP
 
|
 
|
−
|No default
 
−
|the larger ocean the sea belongs to
 
 
|}
 
|}
  
−
===Table: sequencing===
+
===Table: kindofunit===
−
Belongs to: [[#Sequence_data | Sequence data]], [[#DNA_extractions | DNA extractions]]
+
Belongs to: [[#Specimen_Tool | Specimen Tool]]
−
 
 
−
'''At present not in use, coming soon.'''
 
 
{| 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_Sequencing
+
|ID_KindOfUnit
−
|int
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
−
|PK of all sequencings
+
|PK of all Kind of Units (materialSampleType)
 
|-
 
|-
−
|ID_Amplification
+
|KindOfUnit
−
|int
+
|varchar(100)
−
|No
+
|NO
−
|Key
+
|
−
|No default
+
|
−
|FK to parent amplification event ([[#Table:_amplifications | table:amplifications]])
+
|List of all Kind of Units (materialSampleType)
 
|-
 
|-
−
|CloningDate
+
|KindOfUnitRemarks
−
|date
+
|varchar(500)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
+
|Remarks on a Kind of Unit
−
|Date of cloning (format: YYYY-MM-DD)
 
 
|-
 
|-
−
|ID_CloningStaff
+
|Created_Who
−
|int
+
|varchar(50)
−
|Yes
+
|NO
−
|Key
+
|
−
|NULL
+
|unknown
−
|FK to person or company permorming DNA cloning[[#Table:_people | table:people]]
+
|
 
|-
 
|-
−
|ID_CloningMethod
+
|Created_When
−
|int
+
|timestamp
−
|Yes
+
|NO
−
|Key
+
|
−
|NULL
+
|CURRENT_TIMESTAMP
−
|FK to used method or protocol [[#Table:_cloningmethod | table:cloningmethod]]
 
−
|-
 
−
|CloneStrain
 
−
|varchar(50
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|Name of the individual DNA clone
 
 
|-
 
|-
−
|ConsensusSequence
+
|Updated_Who
−
|varchar(1000)
+
|varchar(50)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
−
|Consensus sequence derived from all individual sequences
 
−
|-
 
−
|ConsensusSequenceLength
 
−
|varchar(10)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|Length of the consensus sequence (number of base pairs)
 
−
|-
 
−
|ConsensusSequenceChromatogram
 
−
|varchar(500)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|Link to chromatogram of the consensus sequence
 
 
|-
 
|-
−
|BarcodeSequence
+
|Updated_When
−
|varchar(1000)
+
|timestamp
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
−
|DNA barcode sequence (part or 100% of the consensus sequence)
 
−
|-
 
−
|GenBankNumber
 
−
|varchar(50)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|Definite number or ID under which the DNA sequence is deposited in a public database (e.g. GenBank accession number, BOLD process ID)
 
−
|-
 
−
|GenBankNumber-URI
 
−
|varchar(50)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|Link to the related record in a public database (e.g. link to a GenBank or BOLD record)
 
 
|}
 
|}
  
−
===Table: sequencingmethod===
+
===Table: labnumber===
−
Belongs to: [[#Sequence_data | Sequence data]]
+
 
 +
tbd
 +
 
 +
===Table: landusecode===
 +
Belongs to: [[#Specimen_Tool | Specimen Tool]]
  
−
'''At present not in use, coming soon.'''
+
'''SQL create statement filled with standardised content. Existing table contains European categories. Of course you can use other standardised lists.'''
 
{| 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_SequencingMethod
+
|ID_LandUseCode
−
|int
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
−
|PK of all sequencing methods
+
|PK of all land use codes
 
|-
 
|-
−
|SequencingMethod
+
|LandUseCode
 
|varchar(50)
 
|varchar(50)
−
|No
+
|NO
 
|
 
|
−
|No default
+
|
−
|List of all sequencing methods
+
|List of all land use codes
−
|}
 
−
 
 
−
===Table: sex===
 
−
Belongs to: [[#Specimen_Tool | Specimen Tool]]
 
−
 
 
−
'''Create statement filled with standardised content. Content should not be changed in general, especially when providing data to the DNA Bank Network!'''
 
−
{| border="1" width="95%"
 
−
!width="15%" | Column
 
−
!width="9%"  | Type
 
−
!width="4%"  | Null
 
−
!width="4%"  | Key
 
−
!width="20%" | Default
 
−
!Description
 
 
|-
 
|-
−
|ID_Sex
+
|Created_Who
−
|int
+
|varchar(50)
−
|No
+
|NO
−
|PRI
+
|
−
|AUTO_INCREMENT
+
|unknown
−
|PK of all sex types
+
|
 
|-
 
|-
−
|Sex
+
|Created_When
−
|varchar(50)
+
|timestamp
−
|No
+
|NO
 +
|
 +
|CURRENT_TIMESTAMP
 
|
 
|
−
|No default
 
−
|List of all sex types
 
 
|}
 
|}
  
−
===Table: singlesequencing===
+
===Table: location_stock===
−
Belongs to: [[#Sequence_data | Sequence data]], [[#DNA_extractions | DNA extractions]]
+
Belongs to: [[#DNA_extractions | DNA extractions]], [[#Locations_stock.2Faliquots | Locations stock/aliquots]]
−
 
 
−
'''At present not in use, coming soon.'''
 
 
{| 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_Location_Stock
−
|bigint
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
−
|PK of all single sequencings
+
|PK of all stock locations
 
|-
 
|-
−
|ID_Sequencing
+
|FK_Stock_Box
−
|bigint
+
|int(10) unsigned
−
|No
+
|YES
−
|Key
+
|MUL
−
|No default
+
|
−
|FK to parent sequencing event ([[#Table:_sequencings | table:sequencings]])
+
|FK to [[#Table:_stock_box | table:stock_box]]
 
|-
 
|-
−
|ID_Amplification
+
|FK_Stock_Rack
−
|bigint
+
|int(10) unsigned
−
|No
+
|YES
−
|Key
+
|MUL
−
|No default
+
|
−
|FK to parent amplification event ([[#Table:_amplifications | table:amplifications]])
+
|FK to [[#Table:_stock_rack | table:stock_rack]]
 
|-
 
|-
−
|SequencingDate
+
|FK_Stock_Fridge
−
|date
+
|int(10) unsigned
−
|Yes
+
|YES
 +
|MUL
 
|
 
|
−
|NULL
+
|FK to [[#Table:_stock_fridge | table:stock_fridge]]
−
|Date of sequencing (format: YYYY-MM-DD)
 
 
|-
 
|-
−
|ID_SequencingStaff
+
|Stock_Position
−
|int
+
|varchar(50)
−
|Yes
+
|YES
−
|Key
+
|
−
|NULL
+
|
−
|FK to person or company permorming DNA cloning[[#Table:_people | table:people]]
+
|position of the stock tube on a plate
 
|-
 
|-
−
|ID_SequencingMethod
+
|Stock_Barcode
−
|int
+
|varchar(200)
−
|Yes
+
|YES
−
|Key
+
|
−
|NULL
+
|
−
|FK to used method or protocol [[#Table:_sequencingmethod | table:sequencingmethod]]
+
|barcode of the stock tube on a plate
 
|-
 
|-
−
|SingleSequence
+
|Origin_Quantity
−
|varchar(1000)
+
|float
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
+
|quantity in µl when first prepared
−
|Sequence of the individual DNA sequence (A,T,G,C; 5' to 3')
 
−
|}
 
−
 
 
−
===Table: singlesequencingchromatograms===
 
−
Belongs to: [[#Sequence_data | Sequence data]]
 
−
 
 
−
'''At present not in use, coming soon.'''
 
−
{| border="1" width="95%"
 
−
!width="15%" | Column
 
−
!width="9%"  | Type
 
−
!width="4%"  | Null
 
−
!width="4%"  | Key
 
−
!width="20%" | Default
 
−
!Description
 
 
|-
 
|-
−
|ID_Chromatograms
+
|Rest_Quantity
−
|bigint
+
|float
−
|No
+
|YES
−
|PRI
+
|
−
|AUTO_INCREMENT
+
|
−
|PK of all Chromatograms
+
|quantity in µl after using stock partly for an aliquot
 
|-
 
|-
−
|ID_SingleSequencing
+
|Created_When
−
|bigint
+
|timestamp
−
|No
+
|YES
−
|Key
+
|
−
|No default
+
|CURRENT_TIMESTAMP
−
|FK to parent single sequencing event ([[#Table:_singlesequencings | table:singlesequencings]])
+
|
 
|-
 
|-
−
|ID_Sequencing
+
|Created_Who
−
|bigint
+
|varchar(50)
−
|No
+
|NO
−
|Key
+
|
−
|No default
+
|unknown
−
|FK to parent sequencing event ([[#Table:_sequencings | table:sequencings]])
+
|
 
|-
 
|-
−
|ID_Amplification
+
|Update_Who
−
|bigint
+
|varchar(50)
−
|No
+
|YES
−
|Key
+
|
−
|No default
+
|
−
|FK to parent amplification event ([[#Table:_amplifications | table:amplifications]])
+
|
 
|-
 
|-
−
|FileURI
+
|Update_When
−
|varchar(500)
+
|timestamp
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
 
−
|Link to individual chromatogram
 
−
|-
 
−
|Remarks
 
−
|varchar(500)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|Notes and remarks regarding individual chromatogram
 
 
|}
 
|}
  
−
===Table: singlesequencing_primers===
+
===Table: methods===
−
Belongs to: [[#Sequence_data | Sequence data]]
+
Belongs to: [[#DNA_extractions | DNA extractions]], [[#Specimen_Tool | Specimen Tool]], [[#Sequence_data | Sequence data]]
−
 
 
−
'''At present not in use, coming soon.'''
 
 
{| 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_Method
−
|bigint
+
|int(10) unsigned
−
|No
+
|NO
 +
|PRI
 
|
 
|
−
|No default
+
|PK of all methods
−
|FK to Single sequencing event ([[#Table:_singlesequencing | table:singlesequencing]])
 
 
|-
 
|-
−
|ID_Primer
+
|Method
−
|bigint
+
|varchar(200)
−
|No
+
|NO
 +
|
 
|
 
|
−
|No default
+
|List of all methods
−
|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
+
|MethodGroup
−
|int
 
−
|No
 
−
|PRI
 
−
|AUTO_INCREMENT
 
−
|PK of all slope categories
 
−
|-
 
−
|Slope
 
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|NO
 +
|
 
|
 
|
−
|NULL
+
|tbd; group for drop-down lists
−
|List of slope categories
 
−
|}
 
−
 
 
−
===Table: specimentype===
 
−
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!'''
 
−
{| border="1" width="95%"
 
−
!width="15%" | Column
 
−
!width="9%"  | Type
 
−
!width="4%"  | Null
 
−
!width="4%"  | Key
 
−
!width="20%" | Default
 
−
!Description
 
 
|-
 
|-
−
|ID_SpecimenType
+
|MethodRemarks
−
|int
+
|varchar(500)
−
|No
+
|YES
−
|PRI
+
|
−
|AUTO_INCREMENT
+
|
−
|PK of all sort of types
+
|remarks on a certain method
 
|-
 
|-
−
|SpecimenType
+
|FK_Reference
−
|varchar(50)
+
|int(10) unsigned
−
|No
+
|YES
 +
|MUL
 +
|
 +
|FK to [[#Table:_reference | table:reference]]
 +
|-
 +
|Created_When
 +
|timestamp
 +
|NO
 +
|
 +
|CURRENT_TIMESTAMP
 
|
 
|
−
|No default
 
−
|List of all sort of types
 
−
|}
 
−
 
−
===Table: sptoolassociatedunits===
 
−
Belongs to: [[#Specimen_Tool | Specimen Tool]], [[#Specimen_data_providers | Specimen data providers]]
 
−
 
−
{| border="1" width="95%"
 
−
!width="15%" | Column
 
−
!width="9%"  | Type
 
−
!width="4%"  | Null
 
−
!width="4%"  | Key
 
−
!width="20%" | Default
 
−
!Description
 
 
|-
 
|-
−
|ID_Association
+
|Created_Who
−
|bigint
+
|varchar(50)
−
|No
+
|NO
−
|PRI
 
−
|AUTO_INCREMENT
 
−
|PK of all associated specimens and observations
 
−
|-
 
−
|ID_Collection
 
−
|bigint
 
−
|No
 
−
|Key
 
−
|No default
 
−
|FK to parent specimen/observation (collection event) ([[#Table:_sptoolcollection | table:sptoolcollection]])
 
−
|-
 
−
|UnitID_Main
 
−
|varchar(100)
 
−
|Yes
 
 
|
 
|
−
|NULL
+
|unknown
−
|GBIF-Identifier of main unit, mostly Barcode or Catalogue Number (part of triple ID), same as in [[#Table:_sptoolcollection | table:sptoolcollection]]
 
−
|-
 
−
|CollectionCode_Main
 
−
|varchar(100)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|GBIF-Identifier of main unit, Institution of specimen deposit (part of triple ID), same as in [[#Table:_sptoolcollection | table:sptoolcollection]]
 
 
|-
 
|-
−
|InstitutionCode_Main
+
|Updated_Who
−
|varchar(100)
+
|varchar(50)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
−
|GBIF-Identifier of main unit, Institution of specimen deposit (part of triple ID), same as in [[#Table:_sptoolcollection | table:sptoolcollection]]
 
−
|-
 
−
|RelationToMainUnit
 
−
|varchar(100)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
 
|
 
|
 
|-
 
|-
−
|UnitID
+
|Updated_When
−
|varchar(100)
+
|timestamp
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
−
|GBIF-Identifier of associated unit, mostly Barcode or Catalogue Number (part of triple ID)
 
−
|-
 
−
|CollectionCode
 
−
|varchar(100)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|GBIF-Identifier of associated unit, Collection of specimen deposit (part of triple ID)
 
−
|-
 
−
|InstitutionCode
 
−
|varchar(100)
 
−
|Yes
 
 
|
 
|
−
|NULL
+
|}
−
|GBIF-Identifier of associated unit, Institution of specimen deposit (part of triple ID)
+
 
 +
===Table: people===
 +
Belongs to: [[#DNA_extractions | DNA extractions]], [[#Customer_requests | Customer requests]], [[#Sequence_data | Sequence data]], [[#Specimen_Tool | Specimen Tool]]
 +
{| border="1" width="95%"
 +
!width="28%" | Field
 +
!width="12%" | Type
 +
!width="6%" | Null
 +
!width="6%" | Key
 +
!width="20%" | Default
 +
!Comment
 
|-
 
|-
−
|KindOfUnit
+
|ID_People
−
|varchar(100)
+
|int(20) unsigned
−
|Yes
+
|NO
 +
|PRI
 +
|
 +
|PK of all Persons
 +
|-
 +
|FormOfAddress
 +
|varchar(50)
 +
|YES
 +
|
 +
|
 
|
 
|
−
|NULL
 
−
|description of preservation or type of object
 
 
|-
 
|-
−
|FormerCollection
+
|Title
 
|varchar(100)
 
|varchar(100)
−
|Yes
+
|YES
 +
|
 +
|
 
|
 
|
−
|NULL
 
−
|former collection of the specimen
 
 
|-
 
|-
−
|ID_BasisOfRecord
+
|Forename
−
|bigint
+
|varchar(50)
−
|Yes
+
|YES
−
|Key
+
|
−
|NULL
+
|
−
|FK to record basis of individual associated specimen/observation ([[#Table:_basisofrecordspecimen | table:basisofrecordspecimen]])
+
|
 
|-
 
|-
−
|ID_Dataset
+
|Surname
−
|bigint
+
|varchar(50)
−
|Yes
+
|YES
−
|Key
+
|
−
|NULL
+
|
−
|FK to specimen data provider, if record is avalaibla via GBIF compliant database ([[#Table:_dataset | table:dataset]])
+
|
 
|-
 
|-
−
|Notes
+
|Phone
−
|varchar(200)
+
|varchar(100)
−
|Yes
+
|YES
 +
|
 +
|
 
|
 
|
−
|NULL
 
−
|Notes/remarkes regarding the individual associated specimen/observation
 
−
|}
 
−
 
−
===Table: sptoolcollection===
 
−
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_Collection
+
|Email
−
|bigint
 
−
|No
 
−
|PRI
 
−
|AUTO_INCREMENT
 
−
|PK of all specimens and observations
 
−
|-
 
−
|UnitID_Specimen
 
 
|varchar(100)
 
|varchar(100)
−
|No
+
|YES
−
|Key
+
|
−
|No default
+
|
−
|GBIF-Identifier of main unit, mostly Barcode or Catalogue Number (part of triple ID)
+
|
 
|-
 
|-
−
|CollectionCode_Specimen
+
|Remarks
−
|varchar(100)
+
|varchar(500)
−
|No
+
|YES
 +
|
 
|
 
|
−
|No default
 
−
|GBIF-Identifier of main unit, Institution of specimen deposit (part of triple ID)
 
−
|-
 
−
|InstitutionCode_Specimen
 
−
|varchar(100)
 
−
|No
 
 
|
 
|
−
|No default
 
−
|GBIF-Identifier of main unit, Institution of specimen deposit (part of triple ID)
 
 
|-
 
|-
−
|FormerCollection
+
|Name_All
−
|varchar(100)
+
|varchar(50)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
+
|
−
|former collection of the specimen
+
|name string (Surname, Forename)
−
|-
 
−
|ID_Country
 
−
|bigint
 
−
|Yes
 
−
|Key
 
−
|NULL
 
−
|FK to [[#Table:_countryisocode | table:countryisocode]]
 
 
|-
 
|-
−
|ID_SeasAndOceans
+
|Institut
−
|bigint
+
|varchar(150)
−
|Yes
+
|YES
−
|Key
+
|
−
|NULL
 
−
|FK to [[#Table:_seasandoceans | table:seasandoceans]]
 
−
|-
 
−
|-
 
−
|Collectors
 
−
|varchar(200)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|Collection Team
 
−
|-
 
−
|CollectionDateMin
 
−
|varchar(200)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|Collection Date from (format: YYYY-MM-DD)
 
 
|-
 
|-
−
|CollectionDateMax
+
|Division
−
|varcahr(200)
+
|varchar(150)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
−
|Collection Date to (format: YYYY-MM-DD)
 
−
|-
 
−
|CollectionYear
 
−
|varchar(50)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|Collection Year (generated when record has been saved)
 
−
|-
 
−
|CollectorsNo
 
−
|varchar(100)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|field number of a specimen given by collector (in the field)
 
 
|-
 
|-
−
|Locality_ID
+
|Street
 
|varchar(150)
 
|varchar(150)
−
|Yes
+
|YES
 +
|
 +
|
 
|
 
|
−
|NULL
 
−
|number/name of collection site, e.g. in a river or a plot name/number
 
 
|-
 
|-
−
|ExpeditionDetails
+
|City
−
|varchar(300)
+
|varchar(150)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
 
−
|Project or Expedition details concerning collection event
 
−
|-
 
−
|Region
 
−
|varchar(500)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|district or island name
 
 
|-
 
|-
−
|Locality
+
|Postal_Code
−
|varchar(500)
+
|varchar(150)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
−
|free text of locality information (below district level)
 
−
|-
 
−
|Habitat
 
−
|varchar(100)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|free text of habitat information concerning collection site
 
−
|-
 
−
|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
+
|Country
−
|varchar(50)
+
|varchar(150)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
−
|lower or only value of measurement
 
−
|-
 
−
|DepthMax
 
−
|varchar(50)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|upper value of measurement
 
−
|-
 
−
|DepthUnit
 
−
|varchar(50)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|unit of measurement
 
 
|-
 
|-
−
|AltitudeAccuracyStatement
+
|Institut_Delivery
−
|varchar(50)
+
|varchar(150)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
+
|if shipping/delivery address differs from billing address
−
|Statement of accuracy of measurement
 
 
|-
 
|-
−
|AltitudeMin
+
|Division_Delivery
−
|varchar(50)
+
|varchar(150)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
 
−
|lower or only value of measurement
 
−
|-
 
−
|AltitudeMax
 
−
|varchar(50)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|upper value of measurement
 
 
|-
 
|-
−
|AltitudeUnit
+
|Street_Delivery
−
|varchar(50)
+
|varchar(150)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
−
|unit of measurement
 
−
|-
 
−
|CoordinatesAccuracyStatement
 
−
|varchar(50)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|statement of degree of degree of accuracy
 
−
|-
 
−
|CoordinatesErrorDistance
 
−
|varchar(50)
 
−
|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.
 
 
|-
 
|-
−
|LongitudeMin
+
|City_Delivery
−
|varchar(50)
+
|varchar(150)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
−
|lower or only value expressed in decimal degrees
 
−
|-
 
−
|LongitudeMax
 
−
|varchar(50)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|upper value expressed in decimal degrees
 
−
|-
 
−
|LatitudeMin
 
−
|varchar(50)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|lower or only value expressed in decimal degrees
 
 
|-
 
|-
−
|LatitudeMax
+
|Postal_Code_Delivery
−
|varchar(50)
+
|varchar(150)
−
|Yes
+
|YES
 +
|
 +
|
 
|
 
|
−
|NULL
 
−
|upper value expressed in decimal degrees
 
 
|-
 
|-
−
|LongitudeOther
+
|Country_Delivery
−
|varchar(50)
+
|varchar(150)
−
|Yes
+
|YES
 +
|
 +
|
 
|
 
|
−
|NULL
 
−
|original label data
 
 
|-
 
|-
−
|LatitudeOther
+
|Staff
−
|varchar(50)
+
|varchar(10)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
+
|='Yes' if person is part of the lab, also externals that have done the extractions
−
|original label data
 
 
|-
 
|-
−
|ID_Sex
+
|Provider
−
|bigint
+
|varchar(10)
−
|Yes
+
|YES
−
|Key
+
|
−
|NULL
+
|
−
|FK to [[#Table:_sex | table:sex]]
+
| ='Yes' if person has provided samples
 
|-
 
|-
−
|Protected
+
|Customer
−
|varchar(20)
+
|varchar(10)
−
|No
+
|YES
 +
|
 
|
 
|
−
|No
+
|='Yes' if person has ordered DNA sample
−
|values "No", "Yes", "Unknown"
 
 
|-
 
|-
−
|Permission
+
|Created_When
−
|varchar(20)
+
|timestamp
−
|No
+
|NO
 +
|
 +
|CURRENT_TIMESTAMP
 
|
 
|
−
|NotRequired
 
−
|values "No", "Yes", "Unknown", "NotRequired"
 
 
|-
 
|-
−
|Blocked
+
|Created_Who
−
|varchar(20)
+
|varchar(50)
−
|No
+
|NO
 +
|
 +
|unknown
 
|
 
|
−
|No
 
−
|values "No", "Yes", "Unknown"
 
 
|-
 
|-
−
|ID_BasisOfRecord
+
|Updated_When
−
|bigint
+
|timestamp
−
|Yes
+
|YES
−
|Key
+
|
−
|NULL
 
−
|FK to [[#Table:_basisofrecordspecimen | table:basisofrecordspecimen]]
 
−
|-
 
−
|KindOfUnit
 
−
|varchar(150)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|description of preservation or type of object
 
−
|-
 
−
|ID_Cache
 
−
|bigint
 
−
|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
 
−
|Notes/remarks concerning specimen/observation in general
 
 
|-
 
|-
−
|AccessionNo
+
|Updated_Who
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|YES
−
|
 
−
|NULL
 
−
|Primary accession number by specimen deposit
 
−
|-
 
−
|AccessionName
 
−
|varchar(100)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|Primary identification (e.g. "Pieris sp.") by specimen deposit
 
−
|-
 
−
|LifeStage
 
−
|varchar(50)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|Free text describing life stage of specimen/observation
 
−
|-
 
−
|LifeForm
 
−
|varchar(50)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|Free text describing life form of specimen/observation
 
 
|}
 
|}
  
−
===Table: sptoolecology===
+
===Table: preservations===
−
Belongs to: [[#Specimen_Tool | Specimen Tool]]
+
Belongs to: [[#DNA_extractions | DNA extractions]], [[#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_Ecology
+
|ID_Preservation
−
|bigint
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
−
|PK of all ecological facts
+
|PK of all Preservation types (DNA,tissue,specimen)
 
|-
 
|-
−
|ID_Collection
+
|Preservation
−
|bigint
+
|varchar(200)
−
|Yes
+
|NO
−
|Key
+
|
−
|NULL
+
|
−
|FK to parent Collection event ([[#Table:_sptoolcollection | table:sptoolcollection]])
+
|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 | table:reference]]
 
|-
 
|-
−
|Value
+
|Created_When
−
|varchar(20)
+
|timestamp
−
|Yes
+
|NO
 +
|
 +
|CURRENT_TIMESTAMP
 
|
 
|
−
|NULL
 
−
|value of measurement
 
 
|-
 
|-
−
|Unit
+
|Created_Who
−
|varchar(20)
+
|varchar(50)
−
|Yes
+
|NO
 +
|
 +
|unknown
 
|
 
|
−
|NULL
 
−
|Unit of measurement
 
 
|-
 
|-
−
|Parameter
+
|Updated_When
−
|varchar(20)
+
|timestamp
−
|Yes
+
|YES
 +
|
 +
|
 
|
 
|
−
|NULL
 
−
|Name of measured parameter
 
 
|-
 
|-
−
|Method
+
|Updated_Who
−
|varchar(20)
+
|varchar(50)
−
|Yes
+
|YES
 +
|
 +
|
 
|
 
|
−
|NULL
 
−
|method used for measurement
 
 
|}
 
|}
  
−
===Table: sptoolhighertaxon===
+
===Table: prevalentaspect===
 
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_HigherTaxon
+
|ID_Aspect
−
|bigint
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
−
|PK of all higher taxa
+
|PK of all aspect categories
 
|-
 
|-
−
|ID_Taxa
+
|Aspect
−
|bigint
+
|varchar(50)
−
|Yes
+
|NO
−
|Key
+
|
−
|NULL
+
|
−
|FK to parent determination ([[#Table:_sptooltaxa | table:sptooltaxa]])
+
|List of all aspect categories
 
|-
 
|-
−
|HigherTaxon
+
|Created_Who
−
|varchar(255)
+
|varchar(50)
−
|Yes
+
|NO
 +
|
 +
|unknown
 
|
 
|
−
|NULL
 
−
|Name of higher taxon
 
 
|-
 
|-
−
|TaxonRank
+
|Created_When
−
|varchar(20)
+
|timestamp
−
|Yes
+
|NO
 
|
 
|
−
|NULL
+
|CURRENT_TIMESTAMP
−
|Rank of higher taxon
 
−
|-
 
−
|Synecology
 
−
|varchar(20)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|='host' if taxon ist host species of specimen
 
 
|}
 
|}
  
−
===Table: sptoolmultimedia===
+
===Table: primer===
−
Belongs to: [[#Specimen_Tool | Specimen Tool]]
+
Belongs to: [[#Sequence_data | Sequence data]]
  
 +
'''At present not in use, coming soon.'''
 
{| 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_Multimedia
+
|ID_Primer
−
|bigint
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
−
|PK of all multimedia items
+
|PK of all primers
 
|-
 
|-
−
|ID_Collection
+
|Primer
−
|bigint
+
|varchar(100)
−
|Yes
+
|NO
−
|Key
+
|
−
|NULL
+
|unknown
−
|FK to parent collection event ([[#Table:_sptoolcollection | table:sptoolcollection]])
+
|List of all primer names
 
|-
 
|-
−
|File_Path
+
|PrimerSequence
 +
|varchar(5000)
 +
|YES
 +
|
 +
|
 +
|List of all primer sequences
 +
|-
 +
|PrimerRemarks
 
|varchar(500)
 
|varchar(500)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
+
|
−
|path/url of multimedia file
+
|Remarks on a certain primer
 
|-
 
|-
−
|File_Type
+
|FK_Reference
−
|varchar(20)
+
|int(10) unsigned
−
|Yes
+
|YES
 +
|MUL
 +
|
 +
|FK to [[#Table:_reference | table:reference]]
 +
|-
 +
|adapters
 +
|varchar(100)
 +
|YES
 +
|
 
|
 
|
−
|NULL
+
|MIxS term
−
|type of multimedia item (video, sound, image etc.)
 
 
|-
 
|-
−
|File_Created_When
+
|multiplexIdentifiers
−
|date
+
|varchar(100)
−
|Yes
+
|YES
 +
|
 
|
 
|
−
|NULL
+
|MIxS term
−
|date when multimedia file has been created
 
 
|-
 
|-
−
|File_Created_Who
+
|Created_Who
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|NO
 +
|
 +
|unknown
 
|
 
|
−
|NULL
 
−
|creator of multimedia file
 
 
|-
 
|-
−
|File_Context
+
|Created_When
−
|varchar(500)
+
|timestamp
−
|Yes
+
|NO
 +
|
 +
|CURRENT_TIMESTAMP
 
|
 
|
−
|NULL
 
−
|comments/notes concerning individual multimedia file
 
 
|-
 
|-
−
|File_Comment
+
|Updated_Who
−
|varchar(500)
+
|varchar(50)
−
|Yes
+
|NO
 +
|
 +
|unknown
 
|
 
|
−
|NULL
 
−
|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
+
|Updated_When
−
|varchar(50)
+
|timestamp
−
|Yes
+
|YES
 +
|
 +
|
 
|
 
|
−
|NULL
 
−
|='specimen' if multimedia item belongs directly to the specimen; ='site' if multimedia item belongs to collection site
 
 
|}
 
|}
  
−
===Table: sptooltaxa===
+
===Table: provider===
−
Belongs to: [[#Specimen_Tool | Specimen Tool]]
+
Belongs to: [[#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_TaxonIdentified
+
|ID_Provider
−
|bigint
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
 
−
|PK of all determinations
 
−
|-
 
−
|ID_Collection
 
−
|bigint
 
−
|Yes
 
−
|Key
 
−
|NULL
 
−
|FK to parent collection event ([[#Table:_sptoolcollection | table:sptoolcollection]])
 
−
|-
 
−
|Genus
 
−
|varchar(255)
 
−
|Yes
 
−
|
 
−
|NULL
 
 
|
 
|
 +
|PK of all specimen data provider urls
 
|-
 
|-
−
|Subgenus
+
|Provider
−
|varchar(255)
+
|varchar(500)
−
|Yes
+
|NO
 
|
 
|
−
|NULL
 
 
|
 
|
 +
|List of all specimen data providers urls (access points)
 
|-
 
|-
−
|SpecificEpithet
+
|FK_Schema
−
|varchar(255)
+
|int(10) unsigned
−
|Yes
+
|NO
−
|
+
|MUL
−
|NULL
 
 
|
 
|
 +
|FK to required Schema ([[#Table:_schemata | table:schemata]])
 
|-
 
|-
−
|InfraspecificEpithet
+
|Created_When
−
|varchar(155)
+
|timestamp
−
|Yes
+
|NO
 
|
 
|
−
|NULL
+
|CURRENT_TIMESTAMP
 
|
 
|
 
|-
 
|-
−
|Rank
+
|Created_Who
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|NO
 
|
 
|
−
|NULL
+
|unknown
 
|
 
|
 +
|}
 +
 +
===Table: reference===
 +
Belongs to: [[#Sequence_data | Sequence data]], [[#DNA_extractions | DNA extractions]], [[#Specimen_Tool | Specimen Tool]]
 +
{| border="1" width="95%"
 +
!width="28%" | Field
 +
!width="12%" | Type
 +
!width="6%" | Null
 +
!width="6%" | Key
 +
!width="20%" | Default
 +
!Comment
 
|-
 
|-
−
|AuthorParenth
+
|ID_References
−
|varchar(100)
+
|int(10) unsigned
−
|Yes
+
|NO
 +
|PRI
 
|
 
|
−
|NULL
+
|PK of all references, protocols etc.
−
|basionym author team
 
 
|-
 
|-
−
|YearParenth
+
|ReferenceText
−
|varchar(50)
+
|varchar(1000)
−
|Yes
+
|YES
 +
|MUL
 
|
 
|
−
|NULL
+
|Citation format of all references
−
|basionym year
 
 
|-
 
|-
−
|Author
+
|ReferenceShort
 
|varchar(100)
 
|varchar(100)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
 
|
 
|
 +
|Abbreviation for a reference, user-defined
 
|-
 
|-
−
|Year
+
|ReferenceUrl
 +
|varchar(1000)
 +
|YES
 +
|
 +
|
 +
|URL to online version of reference
 +
|-
 +
|Created_Who
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|NO
 
|
 
|
−
|NULL
+
|unknown
 
|
 
|
 
|-
 
|-
−
|NameAuthorYear
+
|Created_When
−
|varchar(500)
+
|timestamp
−
|Yes
+
|NO
 +
|
 +
|CURRENT_TIMESTAMP
 
|
 
|
−
|NULL
 
−
|Full Scientific Name String including Author teams and years
 
 
|-
 
|-
−
|KindOfIdentification
+
|Update_When
−
|varchar(20)
+
|timestamp
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
−
|="det.", "confirm." or "rev."
 
−
|-
 
−
|Identifier
 
−
|varchar(100)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|determinator team
 
−
|-
 
−
|IdentificationDate
 
−
|varchar(50)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|free text, often month or year only
 
 
|-
 
|-
−
|PreferredFlag
+
|Update_Who
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
−
|='Yes' if determination is preferred
 
−
|-
 
−
|IdentificationNotes
 
−
|varchar(500)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|Notes/Remarks concerning individual determination
 
−
|-
 
−
|Synecology
 
−
|varchar(50)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|='host' if taxon ist host species of specimen
 
 
|}
 
|}
  
−
===Table: stock_box===
+
===Table: relation===
−
Belongs to: [[#Locations_stock.2Faliquots | Locations stock/aliquot]]
+
Belongs to: [[#DNA_extractions | 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.'''
 
{| 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_Stock_Box
+
|ID_Relation
−
|int
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
−
|PK of listed stock boxes
+
|PK of all relationships
 
|-
 
|-
−
|Stock_Box
+
|Relation
−
|varchar(50)
+
|varchar(250)
−
|Yes
+
|NO
 
|
 
|
−
|NULL
+
|
−
|List of boxes used for stock ([[#Table:_location_stock | table:location_stock]])
+
|List of all relationships
−
|}
 
−
 
 
−
===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
+
|RelationRemarks
−
|int
+
|varchar(250)
−
|No
+
|NO
−
|PRI
+
|
−
|AUTO_INCREMENT
+
|
−
|PK of listed stock fridges
+
|Remarks on a certain relation
 
|-
 
|-
−
|Stock_Fridge
+
|FK_Reference
−
|varchar(50)
+
|int(10) unsigned
−
|Yes
+
|YES
 +
|MUL
 
|
 
|
−
|NULL
+
|FK to ([[#Table:_reference | table:reference]])
−
|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
+
|Created_When
−
|int
+
|timestamp
−
|No
+
|NO
−
|PRI
+
|
−
|AUTO_INCREMENT
+
|CURRENT_TIMESTAMP
−
|PK of listed stock racks
+
|
 
|-
 
|-
−
|Stock_Rack
+
|Created_Who
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|NO
 +
|
 +
|unknown
 +
|
 +
|-
 +
|Updated_Who
 +
|varchar(50)
 +
|YES
 +
|
 +
|unknown
 
|
 
|
−
|NULL
 
−
|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
+
|Updated_When
−
|int
+
|timestamp
−
|No
+
|YES
−
|PRI
+
|
−
|AUTO_INCREMENT
+
|
−
|PK of all tissue types
 
−
|-
 
−
|Tissue
 
−
|varchar(255)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|List of all tissue types
 
 
|}
 
|}
  
−
===Table: user===
+
===Table: request===
−
Belongs to: [[#User_management | User management]]
+
Belongs to: [[#Customer_requests | Customer requests]], [[#Locations_stock.2Faliquots | Locations stock/aliquots]], [[#DNA_extractions | DNA extractions]]
 
{| 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_User
+
|ID_Request
−
|int
+
|int(10) unsigned
−
|No
+
|NO
 
|PRI
 
|PRI
−
|AUTO_INCREMENT
+
|
−
|PK of all users
+
|PK of all customer requests
 +
|-
 +
|FK_People
 +
|int(10) unsigned
 +
|NO
 +
|MUL
 +
|
 +
|FK to customer ([[#Table:_people | table:people]])
 
|-
 
|-
−
|Login
+
|FK_DNA
−
|varchar(50)
+
|int(10) unsigned
−
|No
+
|NO
−
|UNIQUE
+
|MUL
−
|No default
+
|
−
|List of login names
+
|FK to parent DNA sample ([[#Table:_dnabanknumbers | table:dnabanknumbers]])
 
|-
 
|-
−
|password
+
|FK_Aliquots
−
|varchar(50)
+
|int(10) unsigned
−
|No
+
|NO
 +
|MUL
 +
|
 +
|FK to [[#Table:_aliquots | 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 | DNA extractions]], [[#Specimen_Tool | Specimen_Tool]]
 +
 
 +
Table to enable n:m relations between DNA or tissue sample and the preservation type
 +
{| border="1" width="95%"
 +
!width="28%" | Field
 +
!width="12%" | Type
 +
!width="6%" | Null
 +
!width="6%" | Key
 +
!width="20%" | 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 | table:dnabanknumbers]])
 +
|-
 +
|FK_Cache
 +
|int(10) unsigned
 +
|YES
 +
|MUL
 +
|
 +
|FK to tisse sample/specimen ([[#Table:_sptoolcollection | table:sptoolcollection]])
 +
|-
 +
|FK_Preservations
 +
|int(10) unsigned
 +
|NO
 +
|MUL
 +
|
 +
|FK to preservation list ([[#Table:_preservations | 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 | Specimen data providers]]
 +
{| border="1" width="95%"
 +
!width="28%" | Field
 +
!width="12%" | Type
 +
!width="6%" | Null
 +
!width="6%" | Key
 +
!width="20%" | 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 | 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
 +
|-
 +
|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 | 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
 +
|int(10) unsigned
 +
|NO
 +
|PRI
 +
|
 +
|PK of all sequencings
 +
|-
 +
|FK_Amplification
 +
|int(10) unsigned
 +
|NO
 +
|MUL
 +
|
 +
|FK to parent amplification event [[#Table:_amplifications | 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 | 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
 +
|-
 +
|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 | table:reference]]
 +
|-
 +
|Created_Who
 +
|varchar(50)
 +
|NO
 +
|
 +
|unknown
 +
|
 +
|-
 +
|Created_When
 +
|timestamp
 +
|NO
 +
|
 +
|CURRENT_TIMESTAMP
 +
|
 +
|}
 +
 
 +
===Table: sex===
 +
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 GBIF or GGBN!'''
 +
{| border="1" width="95%"
 +
!width="28%" | Field
 +
!width="12%" | Type
 +
!width="6%" | Null
 +
!width="6%" | Key
 +
!width="20%" | 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 | 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_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 | table:sequencing]]
 +
|-
 +
|FK_Amplification
 +
|int(10) unsigned
 +
|NO
 +
|MUL
 +
|
 +
|FK to parent amplification event [[#Table:_amplifications | 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 | table:people]]
 +
|-
 +
|FK_SequencingMethod
 +
|int(10) unsigned
 +
|YES
 +
|MUL
 +
|
 +
|FK to used method or protocol [[#Table:_people | 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 | table:primer]]
 +
|-
 +
|Created_Who
 +
|varchar(50)
 +
|NO
 +
|
 +
|unknown
 +
|
 +
|-
 +
|Created_When
 +
|timestamp
 +
|NO
 +
|
 +
|CURRENT_TIMESTAMP
 +
|
 +
|}
 +
 
 +
===Table: slope===
 +
Belongs to: [[#Specimen_Tool | Specimen Tool]]
 +
 
 +
'''SQL create statement filled with standardised content. Existing content should not be changed, but you can add further entries.'''
 +
{| border="1" width="95%"
 +
!width="28%" | Field
 +
!width="12%" | Type
 +
!width="6%" | Null
 +
!width="6%" | Key
 +
!width="20%" | 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 | Specimen Tool]]
 +
 
 +
'''SQL create statement filled with standardised content. Existing content should not be changed, but you can add further entries.'''
 +
{| border="1" width="95%"
 +
!width="28%" | Field
 +
!width="12%" | Type
 +
!width="6%" | Null
 +
!width="6%" | Key
 +
!width="20%" | 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 | Specimen Tool]]
 +
 
 +
'''SQL create statement filled with standardised content. Existing content should not be changed, but you can add further entries.'''
 +
{| border="1" width="95%"
 +
!width="28%" | Field
 +
!width="12%" | Type
 +
!width="6%" | Null
 +
!width="6%" | Key
 +
!width="20%" | 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 | 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!'''
 +
{| border="1" width="95%"
 +
!width="28%" | Field
 +
!width="12%" | Type
 +
!width="6%" | Null
 +
!width="6%" | Key
 +
!width="20%" | 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 Tool]], [[#Specimen_data_providers | Specimen data providers]]
 +
{| border="1" width="95%"
 +
!width="28%" | Field
 +
!width="12%" | Type
 +
!width="6%" | Null
 +
!width="6%" | Key
 +
!width="20%" | 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 | table:sptoolcollection]])
 +
|-
 +
|FK_Relation
 +
|int(10) unsigned
 +
|YES
 +
|MUL
 +
|
 +
|FK to relation, describing relation between main unit and associated unit ([[#Table:_relation | 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 | table:basisofrecordspecimen]])
 +
|-
 +
|FK_Dataset
 +
|int(10) unsigned
 +
|YES
 +
|MUL
 +
|
 +
|FK to dataset ([[#Table:_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 | Specimen Tool]]
 +
{| border="1" width="95%"
 +
!width="28%" | Field
 +
!width="12%" | Type
 +
!width="6%" | Null
 +
!width="6%" | Key
 +
!width="20%" | 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 | table:basisofrecordspecimen]])
 +
|-
 +
|FK_KindOfUnit
 +
|int(10) unsigned
 +
|YES
 +
|MUL
 +
|
 +
|FK to ([[#Table:_kindofunit | 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 | 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 | 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:_biogegraphicregion | table:biogeographicregion]])
 +
|-
 +
|FK_Country
 +
|int(10) unsigned
 +
|YES
 +
|MUL
 +
|
 +
|FK to ([[#Table:_countryisocode | table:countryisocode]])
 +
|-
 +
|FK_SeasAndOceans
 +
|int(10) unsigned
 +
|YES
 +
|MUL
 +
|
 +
|FK to ([[#Table:_seasandoceans | 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 | table:soiltexture]])
 +
|-
 +
|FK_SoilpH
 +
|int(10) unsigned
 +
|YES
 +
|MUL
 +
|
 +
|FK to ([[#Table:_soilph | table:soilph]])
 +
|-
 +
|FK_HabitatCode
 +
|int(10) unsigned
 +
|YES
 +
|MUL
 +
|
 +
|FK to ([[#Table:_habitatcode | table:habitatcode]])
 +
|-
 +
|FK_LandUseCode
 +
|int(10) unsigned
 +
|YES
 +
|MUL
 +
|
 +
|FK to ([[#Table:_landusecode | table:landusecode]])
 +
|-
 +
|FK_Slope
 +
|int(10) unsigned
 +
|YES
 +
|MUL
 +
|
 +
|FK to ([[#Table:_slope | table:slope]])
 +
|-
 +
|FK_Aspect
 +
|int(10) unsigned
 +
|YES
 +
|MUL
 +
|
 +
|FK ([[#Table:_prevalentaspect | 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 | table:geocodemethod]])
 +
|-
 +
|FK_Sex
 +
|int(10) unsigned
 +
|YES
 +
|MUL
 +
|
 +
|FK to ([[#Table:_sex | 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 | table:cachecollection]]), if used for DNA too
 +
|-
 +
|FK_Type
 +
|int(10) unsigned
 +
|YES
 +
|MUL
 +
|
 +
|FK to ([[#Table:_specimentype | 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 | table:stratigraphy_series]])
 +
|-
 +
|Qualifier_Series
 +
|varchar(50)
 +
|YES
 +
|
 +
|
 +
|Qualifier of Series
 +
|-
 +
|FK_Strat_Stage
 +
|int(10) unsigned
 +
|YES
 +
|MUL
 +
|
 +
|FK to ([[#Table:_stratigraphy_stage | 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 | table:sptoolcollection]])
 +
|-
 +
|FK_RelationToParent
 +
|int(10) unsigned
 +
|YES
 +
|MUL
 +
|
 +
|FK to ([[#Table:_relation | 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 | 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%"
 +
!width="28%" | Field
 +
!width="12%" | Type
 +
!width="6%" | Null
 +
!width="6%" | Key
 +
!width="20%" | 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 | table:sptoolcollection]])
 +
|-
 +
|FK_GeneticLocus
 +
|int(10) unsigned
 +
|NO
 +
|MUL
 +
|0
 +
|FK to ([[#Table:_geneticlocus | 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 | Specimen Tool]]
 +
{| border="1" width="95%"
 +
!width="28%" | Field
 +
!width="12%" | Type
 +
!width="6%" | Null
 +
!width="6%" | Key
 +
!width="20%" | 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 | 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 | 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
 +
|
 
|
 
|
−
|No default
 
 
|md5 encoded password
 
|md5 encoded password
−
|-
+
|-
−
|Signature
+
|Signature
−
|varchar(50)
+
|varchar(50)
−
|No
+
|NO
−
|
+
|
−
|No default
+
|
−
|real name of individual user
+
|real name of individual user
−
|-
+
|-
−
|Email
+
|Email
−
|varchar(100)
+
|varchar(100)
−
|No
+
|NO
−
|
+
|
−
|No default
+
|
−
|user email
+
|user email
−
|-
+
|-
−
|ID_Group
+
|FK_Group
−
|tinyint
+
|int(10) unsigned
−
|Yes
+
|YES
−
|
+
|MUL
−
|NULL
+
|
−
|FK to [[#Table:_usergroups | table:usergroups]]
+
|FK to [[#Table:_usergroups | table:usergroups]]
−
|}
+
|-
−
 
+
|Created_Who
−
===Table: usergroups===
+
|varchar(50)
−
Belongs to: [[#User_management | User management]]
+
|NO
−
{| border="1" width="95%"
+
|
−
!width="15%" | Column
+
|unknown
−
!width="9%" | Type  
+
|
−
!width="4%" | Null
+
|-
−
!width="4%" | Key
+
|Created_When
−
!width="20%" | Default
+
|timestamp
−
!Description
+
|YES
−
|-
+
|
−
|ID_Groups
+
|CURRENT_TIMESTAMP
−
|int
+
|
−
|No
+
|}
−
|PRI
+
 
−
|AUTO_INCREMENT
+
===Table: usergroups===
−
|PK of all user groups
+
Belongs to: [[#User_management | User management]]
−
|-
+
{| border="1" width="95%"
−
|Group
+
!width="28%" | Field
−
|varchar(50)
+
!width="12%" | Type
−
|Yes
+
!width="6%" | Null
−
|
+
!width="6%" | Key
−
|NULL
+
!width="20%" | Default
−
|Group name
+
!Comment
−
|-
+
|-
−
|ConfigTool
+
|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)
 
|varchar(50)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
 
|
 
|
 +
|Sorting of hitlist (Specimen Tool)
 
|-
 
|-
−
|CreatedOther
+
|ShowUnitIDSP
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|YES
−
|
 
−
|NULL
 
 
|
 
|
−
|-
 
−
|CreatedSelf
 
−
|varchar(50)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
 
|
 
|
 
|-
 
|-
−
|BlockGeneral
+
|ShowCollCodeSP
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
 
|
 
|
−
|-
 
−
|BlockTemp
 
−
|varchar(50)
 
−
|Yes
 
−
|
 
−
|NULL
 
 
|
 
|
 
|-
 
|-
−
|AvailableDNA
+
|ShowInstCodeSP
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|YES
 
|
 
|
−
|NULL
 
 
|
 
|
−
|-
 
−
|NotesInternal
 
−
|varchar(50)
 
−
|Yes
 
−
|
 
−
|NULL
 
 
|
 
|
 
|-
 
|-
−
|NotesExternal
+
|Created_Who
 
|varchar(50)
 
|varchar(50)
−
|Yes
+
|NO
−
|
 
−
|NULL
 
−
|
 
−
|}
 
−
 
 
−
===Table: usersettings===
 
−
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_Setting
 
−
|int
 
−
|No
 
−
|PRI
 
−
|AUTO_INCREMENT
 
−
|PK of all user settings
 
−
|-
 
−
|ID_User
 
−
|int
 
−
|No
 
−
|Key
 
−
|No default
 
−
|FK to [[#Table:_user | table:user]]
 
−
|-
 
−
|RecordsPerPage
 
−
|float
 
−
|Yes
 
 
|
 
|
−
|NULL
+
|unknown
−
|records per page hitlist (Search Tool)
 
−
|-
 
−
|Sorting
 
−
|varchar(50)
 
−
|Yes
 
 
|
 
|
−
|NULL
 
−
|sorting of hitlist (Search Tool)
 
 
|-
 
|-
−
|RecordsPerpageSP
+
|Created_When
−
|float
+
|timestamp
−
|Yes
+
|NO
 
|
 
|
−
|NULL
+
|CURRENT_TIMESTAMP
−
|records per page hitlist (Specimen Tool)
 
−
|-
 
−
|SortingSP
 
−
|varchar(50)
 
−
|Yes
 
 
|
 
|
−
|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

Contents

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

ER diagram blank.jpg

DNA extractions

ER diagram Package "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

ER diagram package “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

ER diagram package “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

ER diagram package “Location 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

ER diagram package “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

ER diagram package “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

ER diagram package “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

All of the following information is stored directly in the tables, including the description and comments. Wiki-syntax is generated by running a php script. Last update: 2015-02-08

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

Retrieved from "http://wiki.ggbn.org/ggbnwiki/index.php?title=ER_diagram&oldid=2424"