ER diagram
From GGBN Wiki
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: extractionmethod
- 1.2.23 Table: geneticlocus
- 1.2.24 Table: geocodemethod
- 1.2.25 Table: habitatcode
- 1.2.26 Table: images
- 1.2.27 Table: journals
- 1.2.28 Table: landusecode
- 1.2.29 Table: location_stock
- 1.2.30 Table: people
- 1.2.31 Table: preservation
- 1.2.32 Table: prevalentaspect
- 1.2.33 Table: primer
- 1.2.34 Table: provider
- 1.2.35 Table: publications
- 1.2.36 Table: publications_amplifications
- 1.2.37 Table: publications_authors
- 1.2.38 Table: purification
- 1.2.39 Table: relationdna_voucher
- 1.2.40 Table: request
- 1.2.41 Table: schemata
- 1.2.42 Table: seasandoceans
- 1.2.43 Table: sequencing
- 1.2.44 Table: sequencingmethod
- 1.2.45 Table: sex
- 1.2.46 Table: singlesequencing
- 1.2.47 Table: singlesequencingchromatograms
- 1.2.48 Table: singlesequencing_primers
- 1.2.49 Table: slope
- 1.2.50 Table: soilph
- 1.2.51 Table: soiltexture
- 1.2.52 Table: specimentype
- 1.2.53 Table: sptoolassociatedunits
- 1.2.54 Table: sptoolcollection
- 1.2.55 Table: sptoolecology
- 1.2.56 Table: sptoolhighertaxon
- 1.2.57 Table: sptoolmultimedia
- 1.2.58 Table: sptooltaxa
- 1.2.59 Table: stock_box
- 1.2.60 Table: stock_fridge
- 1.2.61 Table: stock_rack
- 1.2.62 Table: tissue
- 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
February 2014: We are currently updating this information!
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
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 | ||
| CollectionInfo_Other | varchar(500) | YES | deprecated | ||
| 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 | |||
| Other | varchar(500) | YES | Deprecated | ||
| 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!
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Country | int | No | PRI | AUTO_INCREMENT | PK of all Countries |
| IsoCode | varchar(50) | No | No default | ISO 3611-2 code of the country | |
| Country | varchar(50) | No | No default | English name of the country |
Table: dataset
Belongs to: Specimen data providers, Specimen Tool
Create statement filled with standardised first row. Please don't make any changes at first row manually!
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Dataset | int | No | PRI | AUTO_INCREMENT | PK of all connected specimen datasets (GBIF compliant databases, first row contains the Specimen Tool) |
| FK_Provider | int | No | Key | No default | FK to table:provider |
| Digir_Resource | varchar(255) | Yes | NULL | Parameter important for Digir only | |
| Digir_Source | varchar(255) | Yes | NULL | Parameter important for Digir only, mostly an URL | |
| Display | varchar(255) | Yes | NULL | name of this dataset given by DNA Module-user | |
| Internal_External | varchar(10) | Yes | NULL | DNA Module-user decide if this dataset appears in the external or internal dataset list | |
| DatasetKey_GBIF | varchar(50) | Yes | NULL | dataset guid (key) provided by GBIF via web services | |
| Protocol | varchar(50) | Yes | NULL | protocol name provided by GBIF via web services (e.g. BIOCASE) | |
| InstitutionCode | varchar(50) | Yes | NULL | TODO: Check if still needed! |
Table: dnabanknumbers
Belongs to: DNA extractions, Specimen cache, Location stock/aliquots, Sequence data, Molecular publications
| Column | Type | Allow Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_DNA | int | No | PRI | AUTO_INCREMENT | PK of all DNA extraction numbers |
| FK_Cache | int | No | Key | No default | FK to table:cachecollection, underlying specimen |
| FK_AcquiredFrom | int | Yes | Key | NULL | FK to table:people, Person who provided the sample (used for donations) |
| FK_Type | int | Yes | Key | NULL | FK to table:dnatype, Type of the DNA |
| FK_Location_Stock | int | Yes | Key | NULL | FK to table:location_stock |
| DNA_Bank_Number | varchar(50) | Yes | NULL | DNA extraction number | |
| FK_Relation | int | Yes | Key | NULL | FK to table:relation |
| FK_Tissue | int | Yes | Key | NULL | FK to table:tissue, type of tissue used for extraction |
| FK_Preservation | int | Yes | Key | NULL | FK to table:preservation, preservation of underyling tissue/specimen |
| Extraction_Date | datetime | Yes | NULL | date of extraction (format YYYY-MM-DD) if known | |
| NoExtractDate | tinyint | Yes | NULL | = '1' if date of extraction is not known | |
| FK_Extraction_Method | int | Yes | Key | NULL | FK to table:methods |
| FK_Purification_Method | int | Yes | Key | NULL | FK to table:methods, method of purification |
| FK_Extraction_Staff | int | Yes | Key | NULL | FK to table:people, person who extracted the DNA |
| Quality_Check_Date | datetime | Yes | NULL | date of quality check (format YYYY-MM-DD) | |
| FK_Concentration_Method | int | Yes | Key | NULL | FK to table:method, method to determine concentration |
| FK_GelImage | int | Yes | Key | NULL | FK to table:gelimages |
| Concentration | varchar(10) | Yes | NULL | Concentration value of DNA sample in ng/µl | |
| Absorbance280 | varchar(10) | Yes | NULL | Ratio of absorbance (value, 260/280) | |
| Absorbance230 | varchar(10) | Yes | NULL | Ratio of absorbance (value, 260/230) | |
| Weight | varchar(10) | Yes | NULL | DNA weight | |
| WeightUnit | varchar(10) | Yes | NULL | unit of measuring DNA weight | |
| FK_Weight_Method | varchar | Yes | NULL | FK to table:method, method to measure weight | |
| Stock_Gone | tinyint | Yes | NULL | ='1' if stock is gone | |
| Source_Gone | tinyint | Yes | NULL | ='1' of source (tissue/specimen) is gone | |
| Notes | varchar(500) | Yes | NULL | Notes related to DNA sample, visible for all user groups | |
| Notes_Intern | varchar(500) | Yes | NULL | Notes related to DNA sample, not visible for guests (user group) | |
| Blocked_Until | datetime | Yes | NULL | date until sample is blocked for delivery (format YYYY-MM-DD) | |
| Blocked_For_Loaning | int | Yes | NULL | ='1' if DNA sample is blocked for delivery only; this record will be visible in the Network's data portal | |
| Blocked_For_Publishing | int | Yes | NULL | ='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 | NULL | certain conditions for loaning the DNA sample | |
| Backup_Aliquot | tinyint | Yes | NULL | ='1' if a backup aliquot is deposited in an external institution | |
| GUID | varchar(50) | Yes | NULL | GUID of the DNA sample |
Table: dnatype
Belongs to: DNA extractions
Create statement filled with standardised content. Existing content should not be changed, but you can add further entries.
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Type | int | No | PRI | AUTO_INCREMENT | PK of all Types |
| Type | varchar(50) | No | No default | List of all DNA types |
Table: extractionmethod
Belongs to: DNA extractions
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Method | int | No | PRI | AUTO_INCREMENT | PK of all Extraction methods |
| Method | varchar(50) | No | No default | List of all Extraction Methods (Kits) | |
| Company | varchar(50) | Yes | NULL | List of all Companies |
Table: geneticlocus
Belongs to: DNA extractions
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_GeneticLocus | int | No | PRI | AUTO_INCREMENT | PK of all Genetic Loci/Fragments |
| GeneticLocus | varchar(150) | No | No default | List of all Genetic Loci/Fragments |
Table: geocodemethod
Belongs to: Specimen Tool
Create statement filled with standardised content. Existing content should not be changed, but you can add further entries.
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Geocode | int | No | PRI | AUTO_INCREMENT | PK of all geocode methods |
| Geocode | varchar(50) | Yes | NULL | List of all geocode methods |
Table: habitatcode
Belongs to: Specimen Tool
Create statement filled with standardised content. Existing table contains European categories (EUNIS). Of course you can use other standardised lists.
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_HabitatCode | int | No | PRI | AUTO_INCREMENT | PK of all habitat code categories |
| HabitatCode | varchar(50) | Yes | NULL | List of all habitat code categories |
Table: images
Belongs to: DNA extractions
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Images | int | No | PRI | AUTO_INCREMENT | PK of all Gel images |
| ID_DNA | int | Yes | Key | NULL | FK to parent DNA sample ( table:dnabanknumbers) |
| ImageUrl | varchar(500) | Yes | NULL | List of all Gel image URLs | |
| ImageRemarks | varchar(150) | Yes | NULL | Remarks to individual Gel images |
Table: journals
Belongs to: Molecular publications
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Journal | int | No | PRI | AUTO_INCREMENT | PK of all Journals |
| Journal_Display | varchar(255) | No | No default | List of all Journal abbreviations; this column is used for display purposes | |
| Journal_All | varchar(500) | No | No default | List of all Journals; this column is used for search purposes |
Table: landusecode
Belongs to: Specimen Tool
Create statement filled with standardised content. Existing table contains European categories. Of course you can use other standardised lists.
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_LandUseCode | int | No | PRI | AUTO_INCREMENT | PK of all land use codes |
| LandUseCode | varchar(50) | Yes | NULL | List of all land use codes |
Table: location_stock
Belongs to: DNA extractions, Locations stock/aliquots
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Location_Stock | int | No | PRI | AUTO_INCREMENT | PK of all stock locations |
| ID_Stock_Box | int | Yes | Key | NULL | FK to table:stock_box |
| ID_Stock_Rack | int | Yes | Key | NULL | FK to table:stock_rack |
| ID_Stock_Fridge | int | Yes | Key | NULL | FK to table:stock_fridge |
| Stock_Position | varchar(50) | Yes | NULL | position of the stock tube on a plate | |
| Stock_Barcode | varchar(200) | Yes | NULL | barcode of the stock tube on a plate | |
| Origin_Quantity | float | Yes | NULL | 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, Customer requests, Sequence data
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_People | int | No | PRI | AUTO_INCREMENT | PK of all Persons |
| FormOfAddress | varchar(50) | Yes | NULL | ||
| Title | varchar(100) | Yes | NULL | ||
| Forename | varchar(50) | Yes | NULL | ||
| Surname | varchar(50) | Yes | NULL | ||
| Phone | varchar(50) | Yes | NULL | ||
| varchar(100) | Yes | NULL | |||
| Remarks | varchar(500) | Yes | NULL | ||
| Name_All | varchar(100) | Yes | NULL | name string (Surname, Forename) | |
| Institut | varchar(150) | Yes | NULL | ||
| Division | varchar(150) | Yes | NULL | ||
| Street | varchar(150) | Yes | NULL | ||
| City | varchar(150) | Yes | NULL | ||
| Postal_Code | varchar(150) | Yes | NULL | ||
| Country | varchar(150) | Yes | NULL | ||
| Institut_Delivery | varchar(150) | Yes | NULL | if shipping/delivery address differs from billing address | |
| Division_Delivery | varchar(150) | Yes | NULL | ||
| Street_Delivery | varchar(150) | Yes | NULL | ||
| City_Delivery | varchar(150) | Yes | NULL | ||
| Postal_Code_Delivery | varchar(150) | Yes | NULL | ||
| Country_Delivery | varchar(150) | Yes | NULL | ||
| ExtractionStaff | varchar(10) | Yes | NULL | ='Yes' if person has extracted DNA samples | |
| Provider | varchar(10) | Yes | NULL | ='Yes' if person has provided DNA samples | |
| Customer | varchar(10) | Yes | NULL | ='Yes' if person has ordered DNA samples |
Table: preservation
Belongs to: DNA extractions
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Preservation | int | No | PRI | AUTO_INCREMENT | PK of all Preservation types (preservation of Tissue/Specimen) |
| Preservation | varchar(150) | No | No default | List of all Preservations |
Table: prevalentaspect
Belongs to: Specimen Tool
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Aspect | int | No | PRI | AUTO_INCREMENT | PK of all aspect categories |
| Stock_Rack | varchar(50) | Yes | NULL | List of all aspect categories |
Table: primer
Belongs to: Sequence data
At present not in use, coming soon.
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Primer | int | No | PRI | AUTO_INCREMENT | PK of all Primers |
| Name | varchar(100) | Yes | NULL | List of all Primer names | |
| Sequence | varchar(5000) | Yes | NULL | List of all Primer sequences | |
| ReferenceCitation | varchar(500) | Yes | NULL | List of all Primer references | |
| ReferenceLink | varchar(500) | Yes | NULL | List of all links to Primer references |
Table: provider
Belongs to: Specimen data providers
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Provider | int | No | 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: publications
Belongs to: Molecular publications
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Publications | int | No | PRI | AUTO_INCREMENT | PK of all Molecular Publications |
| Editors | varchar(10) | Yes | NULL | Editor(s) of publications | |
| Year | varchar(20) | Yes | NULL | Year of publication | |
| Title | varchar(1000) | Yes | NULL | Title of publication | |
| ID_Journal | int | No | Key | No default | FK to table:journals |
| Volume | varchar(10) | Yes | NULL | ||
| Pages | varchar(20) | Yes | NULL | ||
| Book | varchar(100) | Yes | NULL | Book authors and title | |
| Publisher | varchar(250) | Yes | NULL | Book publisher | |
| Link | varchar(400) | Yes | NULL | url to online version if available |
Table: publications_amplifications
Belongs to: Molecular publications, Sequence data, DNA extractions
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Pub_Amp | bigint | No | PRI | AUTO_INCREMENT | PK of all references between amplifications/DNA samples and publications |
| ID_Publications | bigint | No | Key | No default | FK to table:publications |
| ID_Amplification | bigint | No | Key | No default | FK to table:amplifications |
| ID_DNA | bigint | No | Key | No default | FK to table:dnabanknumbers |
| Paper_Cache | varchar(500) | Yes | NULL | Citation string of publication | |
| GeneticLocus | varchar(50) | Yes | NULL | Genetic Locus/Fragment used for underlying analysis |
Table: publications_authors
Belongs to: Molecular publications
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| Order | bigint | No | PRI | AUTO_INCREMENT | Sort sequence of authors of an individual publication |
| ID_Publications | bigint | No | Key | No default | FK to table:publications |
| ID_Author | bigint | No | Key | No default | FK to table:authors |
Table: purification
Belongs to: DNA extractions
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Purification | bigint | No | PRI | AUTO_INCREMENT | PK of all purification methods |
| Method | varchar(150) | Yes | No default | List of all purification methods | |
| Company | varchar(150) | Yes | No default | List of all companies |
Table: relationdna_voucher
Belongs to: DNA extractions
Create statement filled with standardised content. You can change or add relationships if needed. Ideally you should use the existing relationships.
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_RelationDNA_Voucher | bigint | No | PRI | AUTO_INCREMENT | PK of all relationships |
| RelationDNA_Voucher | varchar(250) | Yes | NULL | List of all relationships |
Table: request
Belongs to: Customer requests, Locations stock/aliquots, DNA extractions
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Request | bigint | No | PRI | AUTO_INCREMENT | PK of all customer requests |
| ID_People | bigint | No | Key | No default | FK to customer ( table:people) |
| ID_DNA | bigint | No | Key | No default | FK to parent DNA sample ( table:dnabanknumbers) |
| ID_Aliquots | bigint | No | Key | No default | FK to table:aliquots |
| Request_Date | datetime | 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 | varchar(500) | Yes | NULL | Notes regarding the request | |
| Shipping_Date | datetime | Yes | NULL | Date of shipping | |
| PriceAndCurrency | varchar(50) | Yes | NULL | Price and Currency of requested aliquot | |
| Agreement | varchar(10) | Yes | NULL | ='Yes' if Material transfer agreement has been signed by customer |
Table: schemata
Belongs to: Specimen data providers
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Schema | int | No | PRI | AUTO_INCREMENT | PK of all schema queries |
| Schemata | varchar(50 | Yes | NULL | Name of the Schema | |
| QueryPart1 | varchar(2000) | Yes | NULL | Part one of required query | |
| QueryPart1_1 | varchar(200) | Yes | NULL | special part one of required query | |
| QueryPart1_2 | varchar(2000) | Yes | NULL | special part two of required query | |
| QueryPart1_2b | varchar(2000) | Yes | NULL | special part three of required query | |
| QueryPart1b | varchar(2000) | Yes | NULL | Part one B of required query | |
| QueryPart2 | varchar(2000) | Yes | NULL | Part two of required query | |
| QueryPart2a | varchar(2000) | Yes | NULL | Part two A of required query | |
| QueryPart3 | varchar(2000) | Yes | NULL | Part three of required query | |
| QueryPart4 | varchar(2000) | Yes | NULL | Part four of required query | |
| QueryPart5 | varchar(2000) | Yes | NULL | Part five of required query |
Table: seasandoceans
Belongs to: Specimen Tool
Create statement filled with standardised content. Content should not be changed in general, especially when providing data to the DNA Bank Network!
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_SeasAndOceans | int | No | PRI | AUTO_INCREMENT | PK of all seas |
| SeasAndOceans | varchar(255) | No | No default | List of all seas | |
| Ocean | varchar(255) | Yes | No default | the larger ocean the sea belongs to |
Table: sequencing
Belongs to: Sequence data, DNA extractions
At present not in use, coming soon.
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Sequencing | int | No | PRI | AUTO_INCREMENT | PK of all sequencings |
| ID_Amplification | int | No | Key | No default | FK to parent amplification event ( table:amplifications) |
| CloningDate | datetime | Yes | NULL | Date of cloning (format: YYYY-MM-DD) | |
| ID_CloningStaff | int | Yes | Key | NULL | FK to person or company permorming DNA cloning table:people |
| ID_CloningMethod | int | Yes | Key | NULL | FK to used method or protocol table:cloningmethod |
| CloneStrain | varchar(50 | Yes | NULL | Name of the individual DNA clone | |
| ConsensusSequence | varchar(1000) | 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 | varchar(1000) | 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
Belongs to: Sequence data
At present not in use, coming soon.
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_SequencingMethod | int | No | PRI | AUTO_INCREMENT | PK of all sequencing methods |
| SequencingMethod | varchar(50) | No | No default | List of all sequencing methods |
Table: sex
Belongs to: Specimen Tool
Create statement filled with standardised content. Content should not be changed in general, especially when providing data to the DNA Bank Network!
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Sex | int | No | PRI | AUTO_INCREMENT | PK of all sex types |
| Sex | varchar(50) | No | No default | List of all sex types |
Table: singlesequencing
Belongs to: Sequence data, DNA extractions
At present not in use, coming soon.
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_SingleSequencing | bigint | No | PRI | AUTO_INCREMENT | PK of all single sequencings |
| ID_Sequencing | bigint | No | Key | No default | FK to parent sequencing event ( table:sequencings) |
| ID_Amplification | bigint | No | Key | No default | FK to parent amplification event ( table:amplifications) |
| SequencingDate | datetime | Yes | NULL | Date of sequencing (format: YYYY-MM-DD) | |
| ID_SequencingStaff | int | Yes | Key | NULL | FK to person or company permorming DNA cloning table:people |
| ID_SequencingMethod | int | Yes | Key | NULL | FK to used method or protocol table:sequencingmethod |
| SingleSequence | varchar(1000) | Yes | NULL | Sequence of the individual DNA sequence (A,T,G,C; 5' to 3') |
Table: singlesequencingchromatograms
Belongs to: Sequence data
At present not in use, coming soon.
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Chromatograms | bigint | No | PRI | AUTO_INCREMENT | PK of all Chromatograms |
| ID_SingleSequencing | bigint | No | Key | No default | FK to parent single sequencing event ( table:singlesequencings) |
| ID_Sequencing | bigint | No | Key | No default | FK to parent sequencing event ( table:sequencings) |
| ID_Amplification | bigint | No | Key | No default | FK to parent amplification event ( table:amplifications) |
| FileURI | varchar(500) | Yes | NULL | Link to individual chromatogram | |
| Remarks | varchar(500) | Yes | NULL | Notes and remarks regarding individual chromatogram |
Table: singlesequencing_primers
Belongs to: Sequence data
At present not in use, coming soon.
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_SingleSequencing | bigint | No | No default | FK to Single sequencing event ( table:singlesequencing) | |
| ID_Primer | bigint | No | No default | FK to used primer ( table:primer) |
Table: slope
Belongs to: Specimen Tool
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Slope | int | No | PRI | AUTO_INCREMENT | PK of all slope categories |
| Slope | varchar(50) | Yes | NULL | List of slope categories |
Table: soilph
Belongs to: Specimen Tool
Create statement filled with standardised content. Existing content should not be changed, but you can add further entries.
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_SoilpH | int | No | PRI | AUTO_INCREMENT | PK of all soil pH categories |
| SoilpH | varchar(50) | Yes | NULL | List of all soil pH categories |
Table: soiltexture
Belongs to: Specimen Tool
Create statement filled with standardised content. Existing content should not be changed, but you can add further entries.
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_SoilTexture | int | No | PRI | AUTO_INCREMENT | PK of all soil texture categories |
| SoilTexture | varchar(50) | Yes | NULL | List of all soil texture categories |
Table: specimentype
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!
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_SpecimenType | int | No | PRI | AUTO_INCREMENT | PK of all sort of types |
| SpecimenType | varchar(50) | No | No default | List of all sort of types |
Table: sptoolassociatedunits
Belongs to: Specimen Tool, Specimen data providers
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Association | bigint | 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) |
| UnitID_Main | varchar(100) | Yes | NULL | GBIF-Identifier of main unit, mostly Barcode or Catalogue Number (part of triple ID), same as in 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 | |
| InstitutionCode_Main | varchar(100) | Yes | NULL | GBIF-Identifier of main unit, Institution of specimen deposit (part of triple ID), same as in table:sptoolcollection | |
| RelationToMainUnit | varchar(100) | Yes | NULL | ||
| UnitID | varchar(100) | 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) | |
| KindOfUnit | varchar(100) | Yes | NULL | description of preservation or type of object | |
| FormerCollection | varchar(100) | Yes | NULL | former collection of the specimen | |
| ID_BasisOfRecord | bigint | Yes | Key | NULL | FK to record basis of individual associated specimen/observation ( table:basisofrecordspecimen) |
| ID_Dataset | bigint | Yes | Key | NULL | FK to specimen data provider, if record is avalaibla via GBIF compliant database ( table:dataset) |
| Notes | varchar(200) | Yes | NULL | Notes/remarkes regarding the individual associated specimen/observation |
Table: sptoolcollection
Belongs to: Specimen Tool
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Collection | bigint | No | PRI | AUTO_INCREMENT | PK of all specimens and observations |
| UnitID_Specimen | varchar(100) | No | Key | No default | GBIF-Identifier of main unit, mostly Barcode or Catalogue Number (part of triple ID) |
| CollectionCode_Specimen | varchar(100) | No | 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 | varchar(100) | Yes | NULL | former collection of the specimen | |
| ID_BiogeographicRegion | bigint | Yes | Key | NULL | FK to table:biogeographicregion |
| ID_Country | bigint | Yes | Key | NULL | FK to table:countryisocode |
| ID_SeasAndOceans | bigint | Yes | Key | NULL | FK to table:seasandoceans |
| Collectors | varchar(200) | Yes | NULL | Collection Team | |
| CollectionDateMin | datetime | Yes | NULL | Collection Date from (format: YYYY-MM-DD) | |
| CollectionDateMax | datetime | 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) | |
| CollectionNotes | varchar(400) | Yes | NULL | Notes and remarks regarding the collection event itself | |
| CollectionMethod | varchar(400) | Yes | NULL | Method of collecting | |
| Locality_ID | varchar(150) | Yes | NULL | number/name of collection site, e.g. in a river or a plot name/number | |
| ExpeditionDetails | varchar(300) | Yes | NULL | Project or Expedition details concerning collection event | |
| Region | varchar(500) | Yes | NULL | district or island name | |
| Locality | varchar(500) | Yes | NULL | free text of locality information (below district level) | |
| Habitat | varchar(100) | Yes | NULL | free text of habitat information concerning collection site | |
| ID_SoilTexture | bigint | Yes | Key | NULL | FK to table:soiltexture |
| ID_SoilpH | bigint | Yes | Key | NULL | FK to table:soilph |
| ID_HabitatCode | bigint | Yes | Key | NULL | FK to table:habitatcode |
| ID_LandUseCode | bigint | Yes | Key | NULL | FK to table:landusecode |
| ID_Slope | bigint | Yes | Key | NULL | FK to table:slope |
| ID_Aspect | bigint | Yes | Key | NULL | FK to table:prevalentaspect |
| DepthAccuracyStatement | varchar(50) | Yes | NULL | Statement of accuracy of measurement | |
| DepthMin | varchar(50) | 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 | |
| DepthMethod | varchar(50) | Yes | NULL | method of measurement | |
| DepthRaw | varchar(50) | Yes | NULL | original label data | |
| AltitudeAccuracyStatement | varchar(50) | Yes | NULL | Statement of accuracy of measurement | |
| AltitudeMin | varchar(50) | Yes | NULL | lower or only value of measurement | |
| AltitudeMax | varchar(50) | Yes | NULL | upper value of measurement | |
| AltitudeUnit | varchar(50) | Yes | NULL | unit of measurement | |
| AltitudeMethod | varchar(50) | Yes | NULL | method of measurement | |
| AltitudeRaw | varchar(50) | Yes | NULL | original label data | |
| 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 | varchar(50) | 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 | varchar(50) | Yes | NULL | upper value expressed in decimal degrees | |
| LongitudeRaw | varchar(50) | Yes | NULL | original label data | |
| LatitudeRaw | varchar(50) | Yes | NULL | original label data | |
| GeodeticDatum | varchar(50) | Yes | NULL | e.g. "WGS84" | |
| ID_GeocodeMethod | int | Yes | Key | NULL | FK to table:geocodemethod |
| ID_Sex | bigint | Yes | Key | NULL | FK to table:sex |
| Protected | varchar(20) | No | No | values "No", "Yes", "Unknown" | |
| Permission | varchar(20) | No | NotRequired | values "No", "Yes", "Unknown", "NotRequired" | |
| Blocked | varchar(20) | No | No | values "No", "Yes", "Unknown" | |
| ID_BasisOfRecord | bigint | Yes | Key | NULL | FK to table:basisofrecordspecimen |
| KindOfUnit | varchar(150) | Yes | NULL | description of preservation or type of object | |
| ID_Cache | bigint | Yes | Key | NULL | FK to table:cachecollection |
| ID_Type | bigint | Yes | Key | NULL | FK to table:specimentype |
| Notes | varchar(400) | Yes | NULL | Notes/remarks concerning specimen/observation in general | |
| AccessionNo | varchar(50) | 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 | |
| PrepType | varchar(50) | Yes | NULL | Free text describing preservation method | |
| PrepDate | datetime | Yes | NULL | Date of preparation, (format: YYYY-MM-DD) | |
| PrepStaff | varchar(50) | Yes | NULL | Person who did preparation | |
| SampleGroup | varchar(15) | No | 'Specimen' | either 'Specimen' or 'MixedSample' | |
| GUID | varchar(50) | Yes | NULL | GUID generated with PHP, if UnitID is empty UnitID = GUID | |
| Multimedia | varchar(10) | Yes | NULL | = 'Yes' when table:sptoolmultimedia filled with content | |
| VoucherRefText | varchar(1000) | Yes | NULL | Citation format of publication where voucher is cited | |
| VoucherRefUrl | varchar(1000) | Yes | NULL | Url of publication (if available) | |
| VoucherRefDoi | varchar(200) | Yes | NULL | DOI of publication (format e.g. "10.1000/182" without "doi:" or "http://...") |
Table: sptoolecology
Belongs to: Specimen Tool
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Ecology | bigint | No | PRI | AUTO_INCREMENT | PK of all ecological facts |
| ID_Collection | bigint | Yes | Key | NULL | FK to parent Collection event ( table:sptoolcollection) |
| Value | varchar(20) | Yes | NULL | value of measurement | |
| Unit | varchar(20) | Yes | NULL | Unit of measurement | |
| Parameter | varchar(20) | Yes | NULL | Name of measured parameter | |
| Method | varchar(20) | Yes | NULL | method used for measurement |
Table: sptoolhighertaxon
Belongs to: Specimen Tool
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_HigherTaxon | bigint | No | PRI | AUTO_INCREMENT | PK of all higher taxa |
| ID_Taxa | bigint | Yes | Key | NULL | FK to parent determination ( table:sptooltaxa) |
| HigherTaxon | varchar(255) | Yes | NULL | Name of higher taxon | |
| TaxonRank | varchar(20) | Yes | NULL | Rank of higher taxon | |
| Synecology | varchar(20) | Yes | NULL | ='host' if taxon ist host species of specimen |
Table: sptoolmultimedia
Belongs to: Specimen Tool
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Multimedia | bigint | No | PRI | AUTO_INCREMENT | PK of all multimedia items |
| ID_Collection | bigint | Yes | Key | NULL | FK to parent collection event ( table:sptoolcollection) |
| File_Path | varchar(500) | Yes | NULL | path/url of multimedia file | |
| File_Type | varchar(20) | Yes | NULL | type of multimedia item (video, sound, image etc.) | |
| File_Created_When | datetime | Yes | NULL | date and time when multimedia file has been created (format YYYY-MM-DD hh:mm:ss) | |
| File_Created_Who | varchar(50) | Yes | NULL | creator of multimedia file | |
| File_Context | varchar(500) | Yes | NULL | comments/notes concerning individual multimedia file | |
| File_Comment | varchar(500) | Yes | 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 | varchar(50) | Yes | NULL | ='specimen' if multimedia item belongs directly to the specimen; ='site' if multimedia item belongs to collection site |
Table: sptooltaxa
Belongs to: Specimen Tool
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_TaxonIdentified | bigint | No | PRI | AUTO_INCREMENT | PK of all determinations |
| ID_Collection | bigint | Yes | Key | NULL | FK to parent collection event ( table:sptoolcollection) |
| Genus | varchar(255) | Yes | NULL | ||
| Subgenus | varchar(255) | Yes | NULL | ||
| SpecificEpithet | varchar(255) | Yes | NULL | ||
| InfraspecificEpithet | varchar(155) | Yes | NULL | ||
| Rank | varchar(50) | Yes | NULL | ||
| AuthorParenth | varchar(100) | Yes | NULL | basionym author team | |
| YearParenth | varchar(50) | Yes | NULL | basionym year | |
| Author | varchar(100) | Yes | NULL | ||
| Year | varchar(50) | Yes | NULL | ||
| NameAuthorYear | varchar(500) | Yes | NULL | Full Scientific Name String including Author teams and years | |
| KindOfIdentification | varchar(20) | 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 | varchar(50) | 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
Belongs to: Locations stock/aliquot
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Stock_Box | int | No | PRI | AUTO_INCREMENT | PK of listed stock boxes |
| Stock_Box | varchar(50) | Yes | NULL | List of boxes used for stock ( table:location_stock) |
Table: stock_fridge
Belongs to: Locations stock/aliquot
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Stock_Fridge | int | No | PRI | AUTO_INCREMENT | PK of listed stock fridges |
| Stock_Fridge | varchar(50) | Yes | NULL | List of fridges used for stock ( table:location_stock) |
Table: stock_rack
Belongs to: Locations stock/aliquot
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Stock_Rack | int | No | PRI | AUTO_INCREMENT | PK of listed stock racks |
| Stock_Rack | varchar(50) | Yes | NULL | List of racks used for stock ( table:location_stock) |
Table: tissue
Belongs to: DNA extractions
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Tissue | int | No | PRI | AUTO_INCREMENT | PK of all tissue types |
| Tissue | varchar(255) | Yes | NULL | List of all tissue types |
Table: user
Belongs to: User management
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_User | int | No | PRI | AUTO_INCREMENT | PK of all users |
| Login | varchar(50) | No | UNIQUE | No default | List of login names |
| password | varchar(50) | No | No default | md5 encoded password | |
| Signature | varchar(50) | No | No default | real name of individual user | |
| varchar(100) | No | No default | user email | ||
| ID_Group | tinyint | Yes | NULL | FK to table:usergroups |
Table: usergroups
Belongs to: User management
| Column | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| ID_Groups | int | No | PRI | AUTO_INCREMENT | PK of all user groups |
| Group | varchar(50) | Yes | NULL | Group name | |
| ConfigTool | varchar(50) | Yes | NULL | ||
| CreatedOther | varchar(50) | Yes | NULL | ||
| CreatedSelf | varchar(50) | Yes | NULL | ||
| BlockGeneral | varchar(50) | Yes | NULL | ||
| BlockTemp | varchar(50) | Yes | NULL | ||
| AvailableDNA | varchar(50) | Yes | NULL | ||
| NotesInternal | varchar(50) | Yes | NULL | ||
| NotesExternal | varchar(50) | Yes | NULL |
Table: usersettings
Belongs to: User management
| Column | Type | Null | Key | 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 |
| RecordsPerPage | float | Yes | NULL | records per page hitlist (Search Tool) | |
| Sorting | varchar(50) | Yes | NULL | sorting of hitlist (Search Tool) | |
| RecordsPerpageSP | float | Yes | NULL | records per page hitlist (Specimen Tool) | |
| SortingSP | varchar(50) | Yes | NULL | sorting of hitlist (Specimen Tool) |
