Difference between revisions of "ER diagram"
(→Table: tissue) |
(→Entity-relation diagram of the DNA Module V2.0) |
||
(13 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== | ||
Line 836: | Line 836: | ||
| | | | ||
|decimal value of Latitude | |decimal value of Latitude | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
|- | |- | ||
|TypeStatus | |TypeStatus | ||
Line 1,079: | Line 1,072: | ||
| | | | ||
| | | | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
|- | |- | ||
|NameAuthorYear | |NameAuthorYear | ||
Line 1,935: | Line 1,921: | ||
| | | | ||
|} | |} | ||
+ | |||
+ | ===Table: labnumber=== | ||
+ | |||
+ | tbd | ||
===Table: landusecode=== | ===Table: landusecode=== | ||
Line 2,940: | Line 2,930: | ||
|List of all schemata (ABCD, DwC) | |List of all schemata (ABCD, DwC) | ||
|- | |- | ||
− | | | + | |Created_When |
− | | | + | |timestamp |
|YES | |YES | ||
| | | | ||
+ | |CURRENT_TIMESTAMP | ||
| | | | ||
− | |||
|- | |- | ||
− | | | + | |Created_Who |
− | |varchar( | + | |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( | + | |varchar(255) |
− | | | + | |NO |
| | | | ||
| | | | ||
− | | | + | |List of all seas |
|- | |- | ||
− | | | + | |Ocean |
− | |varchar( | + | |varchar(255) |
− | | | + | |NO |
| | | | ||
| | | | ||
− | | | + | |the larger ocean the sea belongs to |
|- | |- | ||
− | | | + | |Created_Who |
− | |varchar( | + | |varchar(50) |
− | | | + | |NO |
| | | | ||
+ | |unknown | ||
| | | | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
|- | |- | ||
|Created_When | |Created_When | ||
|timestamp | |timestamp | ||
− | | | + | |NO |
| | | | ||
|CURRENT_TIMESTAMP | |CURRENT_TIMESTAMP | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
| | | | ||
|} | |} | ||
− | ===Table: | + | ===Table: sequencing=== |
− | Belongs to: [[# | + | Belongs to: [[#Sequence_data | Sequence data]], [[#DNA_extractions | DNA extractions]] |
− | ''' | + | '''At present not in use, coming soon.''' |
{| border="1" width="95%" | {| border="1" width="95%" | ||
!width="28%" | Field | !width="28%" | Field | ||
Line 3,044: | Line 3,005: | ||
!Comment | !Comment | ||
|- | |- | ||
− | | | + | |ID_Sequencing |
|int(10) unsigned | |int(10) unsigned | ||
|NO | |NO | ||
|PRI | |PRI | ||
| | | | ||
− | |PK of all | + | |PK of all sequencings |
|- | |- | ||
− | | | + | |FK_Amplification |
− | | | + | |int(10) unsigned |
|NO | |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 | |int(10) unsigned | ||
− | | | + | |YES |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
|MUL | |MUL | ||
| | | | ||
− | |FK to | + | |FK to used reverse primer [[#Table:_primer | table:primer]] |
|- | |- | ||
− | | | + | |CloneStrain |
− | | | + | |varchar(50) |
|YES | |YES | ||
| | | | ||
| | | | ||
− | | | + | |Name of the individual DNA clone |
|- | |- | ||
− | | | + | |ConsensusSequence |
− | | | + | |varchar(1000) |
|YES | |YES | ||
− | |||
| | | | ||
− | | | + | | |
+ | |Consensus sequence derived from all individual sequences | ||
|- | |- | ||
− | + | |ConsensusSequenceLength | |
− | + | |varchar(10) | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |ConsensusSequenceLength | ||
− | |varchar(10) | ||
|YES | |YES | ||
| | | | ||
Line 5,036: | Line 4,949: | ||
===Table: stratigraphy_stage=== | ===Table: stratigraphy_stage=== | ||
Belongs to: [[#Specimen_Tool | Specimen Tool]] | Belongs to: [[#Specimen_Tool | Specimen Tool]] | ||
− | |||
− | |||
− | |||
− | |||
{| border="1" width="95%" | {| border="1" width="95%" | ||
− | !width=" | + | !width="28%" | Field |
− | !width=" | + | !width="12%" | Type |
− | !width=" | + | !width="6%" | Null |
− | !width=" | + | !width="6%" | Key |
!width="20%" | Default | !width="20%" | Default | ||
− | ! | + | !Comment |
|- | |- | ||
− | | | + | |ID_Strat_Stage |
− | |int | + | |int(10) unsigned |
− | | | + | |NO |
|PRI | |PRI | ||
− | | | + | | |
− | |PK of all | + | |PK of all stages (Stratigraphy) |
|- | |- | ||
− | | | + | |Stage |
− | |varchar( | + | |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: | + | ===Table: subfragment=== |
− | Belongs to: [[# | + | Belongs to: [[#Sequence_data | Sequence data]], [[#DNA_extractions | DNA extractions]] |
{| border="1" width="95%" | {| border="1" width="95%" | ||
− | !width=" | + | !width="28%" | Field |
− | !width=" | + | !width="12%" | Type |
− | !width=" | + | !width="6%" | Null |
− | !width=" | + | !width="6%" | Key |
!width="20%" | Default | !width="20%" | Default | ||
− | ! | + | !Comment |
|- | |- | ||
− | | | + | |ID_Subfragment |
− | |int | + | |int(10) unsigned |
− | | | + | |NO |
|PRI | |PRI | ||
− | | | + | | |
− | |PK of all | + | |PK of all subfragments |
|- | |- | ||
− | | | + | |Subfragment |
− | |varchar( | + | |varchar(100) |
− | | | + | |YES |
− | | | + | | |
− | | | + | | |
− | |List of | + | |List of all subfragments |
|- | |- | ||
− | | | + | |SubfragmentRemarks |
− | |varchar( | + | |varchar(500) |
− | | | + | |YES |
| | | | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
| | | | ||
− | | | + | |Remarks on a certain subfragment |
− | |||
|- | |- | ||
− | | | + | |Created_Who |
|varchar(100) | |varchar(100) | ||
− | | | + | |NO |
+ | | | ||
+ | |unknown | ||
+ | | | ||
+ | |- | ||
+ | |Created_When | ||
+ | |timestamp | ||
+ | |NO | ||
+ | | | ||
+ | |CURRENT_TIMESTAMP | ||
+ | | | ||
+ | |- | ||
+ | |Updated_Who | ||
+ | |varchar(50) | ||
+ | |YES | ||
+ | | | ||
+ | | | ||
| | | | ||
− | |||
− | |||
|- | |- | ||
− | | | + | |Updated_When |
− | | | + | |timestamp |
− | | | + | |YES |
+ | | | ||
+ | | | ||
| | | | ||
− | |||
− | |||
|} | |} | ||
− | ===Table: | + | ===Table: user=== |
Belongs to: [[#User_management | User management]] | Belongs to: [[#User_management | User management]] | ||
{| border="1" width="95%" | {| border="1" width="95%" | ||
− | !width=" | + | !width="28%" | Field |
− | !width=" | + | !width="12%" | Type |
− | !width=" | + | !width="6%" | Null |
− | !width=" | + | !width="6%" | Key |
!width="20%" | Default | !width="20%" | Default | ||
− | ! | + | !Comment |
|- | |- | ||
− | | | + | |ID_User |
− | |int | + | |int(10) unsigned |
− | | | + | |NO |
|PRI | |PRI | ||
− | | | + | | |
− | |PK of all | + | |PK of all users |
|- | |- | ||
− | | | + | |Login |
|varchar(50) | |varchar(50) | ||
− | | | + | |NO |
+ | |UNI | ||
| | | | ||
− | | | + | |List of login names |
− | |||
|- | |- | ||
− | | | + | |Password |
|varchar(50) | |varchar(50) | ||
− | | | + | |NO |
| | | | ||
− | |||
| | | | ||
+ | |md5 encoded password | ||
|- | |- | ||
− | | | + | |Signature |
|varchar(50) | |varchar(50) | ||
− | | | + | |NO |
| | | | ||
− | |||
| | | | ||
+ | |real name of individual user | ||
|- | |- | ||
− | | | + | |Email |
− | |varchar( | + | |varchar(100) |
− | | | + | |NO |
| | | | ||
− | |||
| | | | ||
+ | |user email | ||
|- | |- | ||
− | | | + | |FK_Group |
− | | | + | |int(10) unsigned |
− | | | + | |YES |
− | | | + | |MUL |
− | |||
| | | | ||
+ | |FK to [[#Table:_usergroups | table:usergroups]] | ||
|- | |- | ||
− | | | + | |Created_Who |
|varchar(50) | |varchar(50) | ||
− | | | + | |NO |
| | | | ||
− | | | + | |unknown |
| | | | ||
|- | |- | ||
− | | | + | |Created_When |
− | | | + | |timestamp |
− | | | + | |YES |
| | | | ||
− | | | + | |CURRENT_TIMESTAMP |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
| | | | ||
|} | |} | ||
− | ===Table: | + | ===Table: usergroups=== |
Belongs to: [[#User_management | User management]] | Belongs to: [[#User_management | User management]] | ||
{| border="1" width="95%" | {| border="1" width="95%" | ||
− | !width=" | + | !width="28%" | Field |
− | !width=" | + | !width="12%" | Type |
− | !width=" | + | !width="6%" | Null |
− | !width=" | + | !width="6%" | Key |
!width="20%" | Default | !width="20%" | Default | ||
− | ! | + | !Comment |
|- | |- | ||
− | | | + | |ID_Group |
− | |int | + | |int(10) unsigned |
− | | | + | |NO |
|PRI | |PRI | ||
− | | | + | | |
− | |PK of all user | + | |PK of all user groups |
|- | |- | ||
− | | | + | |Group |
− | |int | + | |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]] | |FK to [[#Table:_user | table:user]] | ||
|- | |- | ||
|RecordsPerPage | |RecordsPerPage | ||
|float | |float | ||
− | | | + | |YES |
| | | | ||
− | | | + | | |
− | | | + | |Records per page hitlist (Search Tool) |
|- | |- | ||
|Sorting | |Sorting | ||
|varchar(50) | |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 | |float | ||
− | | | + | |YES |
+ | | | ||
| | | | ||
− | | | + | |Records per page hitlist (Specimen Tool) |
− | |||
|- | |- | ||
|SortingSP | |SortingSP | ||
|varchar(50) | |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== | ==View descriptions== | ||
− | + | coming soon, required for BioCASe mapping | |
− | |||
− | |||
− | |||
− | |||
[[Category:DNA_Module]] | [[Category:DNA_Module]] |
Latest revision as of 15:46, 28 September 2016
Contents
- 1 Entity-relation diagram of the DNA Module V2.0
- 1.1 Overview
- 1.2 Table descriptions
- 1.2.1 Table: abcdmetadata
- 1.2.2 Table: abcdmetadataspecimens
- 1.2.3 Table: aliquots
- 1.2.4 Table: aliquots_box
- 1.2.5 Table: aliquots_fridge
- 1.2.6 Table: aliquots_rack
- 1.2.7 Table: amplifications
- 1.2.8 Table: basisofrecordspecimen
- 1.2.9 Table: biogeographicregion
- 1.2.10 Table: cacheassociatedunits
- 1.2.11 Table: cachecollection
- 1.2.12 Table: cachehighertaxa
- 1.2.13 Table: cachemultimedia
- 1.2.14 Table: cachetaxonidentified
- 1.2.15 Table: cloningmethod
- 1.2.16 Table: countrorcontinent
- 1.2.17 Table: country_continent
- 1.2.18 Table: countryisocode
- 1.2.19 Table: dataset
- 1.2.20 Table: dnabanknumbers
- 1.2.21 Table: dnatype
- 1.2.22 Table: gelimages
- 1.2.23 Table: geneticlocus
- 1.2.24 Table: geocodemethod
- 1.2.25 Table: habitatcode
- 1.2.26 Table: kindofunit
- 1.2.27 Table: labnumber
- 1.2.28 Table: landusecode
- 1.2.29 Table: location_stock
- 1.2.30 Table: methods
- 1.2.31 Table: people
- 1.2.32 Table: preservations
- 1.2.33 Table: prevalentaspect
- 1.2.34 Table: primer
- 1.2.35 Table: provider
- 1.2.36 Table: reference
- 1.2.37 Table: relation
- 1.2.38 Table: request
- 1.2.39 Table: samples_preservations
- 1.2.40 Table: schemata
- 1.2.41 Table: seasandoceans
- 1.2.42 Table: sequencing
- 1.2.43 Table: sex
- 1.2.44 Table: singlesequencing
- 1.2.45 Table: slope
- 1.2.46 Table: soilph
- 1.2.47 Table: soiltexture
- 1.2.48 Table: specimentype
- 1.2.49 Table: sptoolassociatedunits
- 1.2.50 Table: sptoolcollection
- 1.2.51 Table: sptoolgeneticaccessions
- 1.2.52 Table: sptoolhighertaxon
- 1.2.53 Table: sptoolmeasurementorfact
- 1.2.54 Table: sptoolmultimedia
- 1.2.55 Table: sptoolreferences
- 1.2.56 Table: sptooltaxa
- 1.2.57 Table: stock_box
- 1.2.58 Table: stock_fridge
- 1.2.59 Table: stock_rack
- 1.2.60 Table: stratigraphy_series
- 1.2.61 Table: stratigraphy_stage
- 1.2.62 Table: subfragment
- 1.2.63 Table: user
- 1.2.64 Table: usergroups
- 1.2.65 Table: usersettings
- 1.3 View descriptions
Entity-relation diagram of the DNA Module V2.0
September 2016: The new version will be called SaM (Sample Management). The empty sql script can be found at http://www.ggbn.org/ggbn_portal/documents/sam_dev.sql
Overview
DNA extractions
Main package of the DNA Module, related to many other packages. The main table is dnabanknumbers with several related list tables. The table people is connected to the dna extractions via three IDs: ExtractionStaff, Provided_By and AmplificationStaff.
- Main tables:
- dnabanknumbers
- people
- List tables:
- dnatype
- relationdna_voucher
- tissue
- preservation
- extractionmethod
- purification
- degradation
- images
- Related packages:
- Amplifications
- Locations Aliquots/stock
- Specimen cache
- Requests
- Molecular publications
- Log tables
Specimen data providers
List of all connected specimen data providers (GBIF compliant databases). More than one dataset per provider is possible. The required schema and provider software is recorded by ID_Schema. The Specimen Tool is predefined with ID_Dataset = '1'. The Wrapper urls of both the Specimen Tool and the DNA data are left empty until definition via the Configuration Tool.
- Main table:
- dataset
- Related packages:
- Specimen cache
- Specimen Tool
Specimen cache
A few specimen parameters have to be recorded within the DNA Module database to enable search for exisiting taxa in the DNA database et cetera. The specimen data are recorded in four tables to get all available determinations and higher taxa. One specimen can be connected to several DNA extractions.
- Main table:
- cachecollection
- Related packages:
- Specimen data providers
- DNA extractions
Locations stock/aliquots
This package records the location and volumes of all aliquot and stock samples in the freezers. The information in this package is completely internal and help to find out easily which samples can be send to customers and which have to be extracted.
- Main tables:
- aliquots
- location_stock
- List tables:
- aliquots_box
- aliquots_fridge
- aliquots_rack
- stock_box
- stock_fridge
- stock_rack
- Related packages:
- DNA extraction
- Customer requests
Publications
UPDATE of docu required. This package allows to associate any publications to DNA or samples and specimen data. It should not be a complete substitute for a literature management software.
- Main table:
- reference
- Related packages:
- DNA extractions
- Specimen Tool
- Sequence data
Sequence data
This package is for management of GenBank Accession Numbers, BOLD process IDs and Amplifications in general. More tables have been modelled for future management of raw sequence data, including primers, references, cloning etc. The frontend for this tables will be ready for use until end of 2011. For now only GenBank/BOLD accessions and Amplifications can be administrated.
- Main tables:
- amplifications
- sequencing
- List tables:
- geneticlocus
- primer
- cloningmethod
- sequencingmethod
- Related packages:
- DNA extractions
- Molecular publications
Customer requests
This package includes management of all customer requests respectively orders. Since way of administer payments depends on institution this package is for the lab site only.
- Main table:
- request
- Related packages:
- Location stock/aliquots
- DNA extractions
Specimen Tool
This is a stand-alone package that interact with the DNA Module via wrapper. It has been developed for specimen data that are not available via GBIF. Furthermore external duplicates or similiar relevant objects can be associated.
- Main table:
- sptoolcollection
- List tables:
- basisofrecordspecimen
- countryisocode
- prevalentaspect
- seasandoceans
- sex
- slope
- specimentype
- Related packages:
- Specimen data providers
Table descriptions
Table: abcdmetadata
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 | |||
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 | ||
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