ER diagram

From GGBN Wiki
Revision as of 14:04, 7 February 2015 by WikiSysop (talk | contribs) (→‎Table: cachetaxonidentified)
Jump to: navigation, search

Contents

Entity-relation diagram of the DNA Module V2.0

Overview

ER diagram blank.jpg

DNA extractions

ER diagram Package "DNA extractions"

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

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

Specimen data providers

ER diagram package “Specimen data providers”

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

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

Specimen cache

ER diagram package “Specimen cache”

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

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

Locations stock/aliquots

ER diagram package “Location stock/aliquots”

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

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

Publications

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

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

Sequence data

ER diagram package “Sequence data”

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

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

Customer requests

ER diagram package “Customer requests”

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

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

Specimen Tool

ER diagram package “Specimen Tool”

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

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

Table descriptions

Table: abcdmetadata

See Metadata for DNA data

Table: abcdmetadataspecimens

See Metadata for Specimen Tool

Table: aliquots

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

Column Type Allow Null Indexes Default Description
ID_Aliquots int No PRI AUTO_INCREMENT ID of the aliquots (Primary Key)
FK_DNA int No Key No default FK to parent DNA sample ( table:dnabanknumbers)
FK_Aliquots_Box int Yes Key NULL FK to table:aliquots_box
FK_Aliquots_Rack int Yes Key NULL FK to table:aliquots_rack
FK_Aliquots_fridge int Yes Key NULL FK to table:aliquots_fridge
Aliquot_Number varchar(100) Yes NULL The number/name of the individual aliquot, mostly the DNA extraction no combined with '-A', '-B' et cetera
Origin_Quantity float Yes NULL quantity in µl when first prepared
Rest_Quantity float Yes NULL quantity in µl after ordering
Alquot_Position varchar(50) Yes NULL position of an individual aliquot on a plate
Aliquot_Barcode varchar(255) Yes NULL barcode of an individual aliquot on a plate
Order_All varchar(50) Yes NULL ='yes' when whole aliquot has been ordered
Order_Partial varchar(50) Yes NULL ='yes' when only a part of the aliquot has been ordered
Order_Rest varchar(50) Yes NULL ='yes' when a part of the aliquot has been shipped earlier and now the rest has been ordered
Shipping_All varchar(50) Yes NULL ='yes' when only a part of the aliquot has been shipped
Shipping_Partial varchar(50) Yes NULL ='yes' when only a part of the aliquot has been shipped
Shipping_Rest varchar(50) Yes NULL ='yes' when a part of the aliquot has been shipped earlier and now the rest has been shipped
Price varchar(50) Yes NULL price of the individual aliquot (defined via General Settings)
Currency varchar(50) Yes NULL currency of the price (defined via General Settings)

Table: aliquots_box

Belongs to: Locations stock/aliquot

Column Type Null Key Default Description
ID_Aliquots_Box int No PRI AUTO_INCREMENT PK of listed aliquot boxes
Aliquots_Box varchar(50) Yes NULL List of boxes used for aliquots ( table:aliquots)

Table: aliquots_fridge

Belongs to: Locations stock/aliquot

Column Type Null Key Default Description
ID_Aliquots_Fridge int No PRI AUTO_INCREMENT PK of listed aliquot fridges
Aliquots_Fridge varchar(50) Yes NULL List of fridges/freezers used for aliquots ( table:aliquots)

Table: aliquots_rack

Belongs to: Locations stock/aliquot

Column Type Null Key Default Description
ID_Aliquots_Rack int No PRI AUTO_INCREMENT PK of listed aliquot racks
Aliquots_Rack varchar(50) Yes NULL List of racks used for aliquots ( table:aliquots)

Table: amplifications

UNDER CONSTRUCTION! Belongs to: Sequence data, DNA extractions

Column Type Null Key Default Description
ID_Amplification int No PRI AUTO_INCREMENT PK of all amplifications
ID_DNA int No Key No default FK to parent DNA sample ( table:dnabanknumbers)
ID_GeneticLocus int No Key No default FK to used Genetic locus ( table:geneticlocus)
GenBankNumber varchar(150) Yes NULL At present in use, will be substituted by table:sequencing: GenBankNumber; contains the GenBank Accession number or BOLD process ID
Link varchar(500) Yes NULL At present in use, will be substituted by table:sequencing: GenBankNumber-URI; contains the Link to GenBank or BOLD
Amplification_Date datetime Yes NULL Date of an individual amplification
ID_Amplification_Staff int Yes Key NULL FK to table:people contains person who made the amplification
Amplification_Success varchar(10) Yes NULL contains "Yes" or "No"
ID_Purification int Yes Key NULL FK to table:purification at present not in use
ID_PrimerForward int Yes Key NULL FK to table:primer at present not in use
ID_PrimerReverse int Yes Key NULL FK to table:primer at present not in use

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!

Column Type Null Key Default Description
ID_BasisOfRecord_Specimen bigint No PRI AUTO_INCREMENT PK of listed record bases
BasisOfRecord_Specimen varchar(150) Yes NULL List of record bases used by package Specimen Tool; important for providing data to GBIF or GGBN

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.

Column Type Null Key Default Description
ID_BiogeographicRegion int No PRI AUTO_INCREMENT PK of all biogeographic categories
BiogeographicRegion varchar(50) Yes NULL List of all biogeographic categories

Table: cacheassociatedunits

Belongs to: Specimen Cache

Column Type Allow Null Key Default Description
ID_Association int No PRI AUTO_INCREMENT PK of all associated records for specimens or tissues; e.g. the "grandparent" record of a DNA sample
FK_Cache int No Key No default FK to table:cachecollection
AssociatedUnitID varchar(100) No No default UnitID/Catalogue Number of associated record
AssociatedCollectionCode varchar(100) No No default SourceID/CollectionCode of associated record
AssociatedInstitutionCode varchar(100) No No default SourceInstitutionID/InstitutionCode of associated record
AssociationType varchar(100) Yes NULL Description of relation between associated records
WrapperUrl varchar(200) Yes NULL access point, for BioCASe records only

Table: cachecollection

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

Column Type Allow Null Key Default Description
ID_Cache int No PRI AUTO_INCREMENT PK of all DNA voucher specimens or tissues referenced via BioCASE or GBIF (parent record of a DNA sample)
FK_Dataset_Specimen int No Key No default FK to table:dataset
UnitID_Specimen varchar(255) No No default GBIF-Identifier, mostly Barcode or Catalogue Number (part of triple ID)
CollectionCode_Specimen varchar(255) No No default GBIF-Identifier, Collection of specimen deposit (part of triple ID)
InstitutionCode_Specimen varchar(255) No No default GBIF-Identifier, Institution of specimen deposit (part of triple ID)
ContinentOrOcean varchar(255) Yes NULL Continent or Ocean (collection site)
CountryName varchar(255) Yes Null English Name of the Country (collection site)
CountryISO2 varchar(50) Yes NULL ISO 3166-2 (collection site)
Collectors varchar(200) Yes NULL Collection Team
CollectionDate varchar(200) Yes NULL Collection Date (free text)
CollectionDateMin varchar(200) Yes NULL Collection Date from (free text or ISO, depends on original data provider)
CollectionDateMax varcahr(200) Yes NULL Collection Date to (free text or ISO, depends on original data provider)
CollectorsNo varchar(100) Yes NULL field number of a specimen given by collector (in the field)
Locality varchar(300) Yes NULL free text of locality information (below country level)
Altitude varchar(50) Yes NULL free text of Altitude measurement (from - to - unit)
Longitude varchar(50) Yes NULL decimal value of Longitude
Latitude varchar(50) Yes NULL decimal value of Latitude
CollectionInfo_Other varchar(500) Yes NULL no longer in use
TypeStatus varchar(150) Yes NULL type status of the specimen
GUID_Specimen varchar(50) Yes NULL GUID of the record if provided

Table: cachehighertaxa

Belongs to: Specimen Cache

Column Type Null Key Default Description
ID_HigherTaxon int No PRI AUTO_INCREMENT PK of all Higher Taxa, sent by original data provider (wrappers)
FK_CacheTaxonIdentified int No Key No default FK to table:cachetaxonidentified
HigherTaxon varchar(255) Yes NULL Name of the HigherTaxon
TaxonRank varchar(255) Yes NULL Rank of the Higher Taxon

Table: cachemultimedia

Belongs to: Specimen Cache

Column Type Null Key Default Description
ID_Multimedia int No PRI AUTO_INCREMENT PK of all Multimedia URLs, sent by original data provider (wrappers)
FK_Cache int No Key No default FK to table:cache
Images varchar(500) Yes NULL List of all Multimedia URLs

Table: cachetaxonidentified

Belongs to: Specimen Cache

Column Type Null Key Default Description
ID_TaxonIdentified int No PRI AUTO_INCREMENT PK of all specimen determinations, sent by original data provider (wrappers)
FK_Cache int No Key No default FK to table:cache
Genus varchar(255) Yes Key NULL
Subgenus varchar(255) Yes NULL
FirstEpithet varchar(255) Yes Key NULL
Genus varchar(255) Yes Key NULL
SecondEpithet varchar(255) Yes Key NULL
Rank varchar(50) Yes NULL Rank of the taxon
HybridFlag varchar(50) Yes NULL
Other varchar(500) Yes NULL No longer in use
NameAuthorYear varchar(500) Yes NULL Full Scientific Name String including Author teams and years
PreferredFlag varchar(50) Yes NULL mostly provided as "1" or "true"

Table: cloningmethod

Belongs to: Sequence data

At present not in use, coming soon.

Column Type Null Key Default Description
ID_CloningMethod int No PRI AUTO_INCREMENT PK of all Cloning Methos
CloningMethod varchar(50) No No default List of all cloning methods

Table: country_continent

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 Countrys
Continent varchar(50) No Key No default 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)
ID_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

Table: degradation

Belongs to: DNA extractions

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_Degradation int No PRI AUTO_INCREMENT PK of all Degradations
Degradation varchar(50) No No default List of all degradation levels

Table: dnabanknumbers

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

Column Type Null Key Default Description
ID_DNA bigint No PRI AUTO_INCREMENT PK of all DNA extraction numbers
ID_Cache bigint No Key No default FK to table:cachecollection, underlying specimen
ID_Provided_By_People bigint Yes Key NULL FK to table:people, Person who provided the sample (used for donations)
ID_Type bigint Yes Key NULL FK to table:dnatype, Type of the DNA
ID_Location_Stock bigint Yes Key NULL FK to table:location_stock
DNA_Bank_Number varchar(50) Yes NULL DNA extraction number
ID_RelationDNA_Voucher bigint Yes Key NULL FK to table:relationdna_voucher
ID_Tissue bigint Yes Key NULL FK to table:tissue, type of tissue used for extraction
ID_Preservation bigint 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
ID_Extraction_Method bigint Yes Key NULL FK to table:extractionmethod
ID_Purification bigint Yes Key NULL FK to table:purification, method of purification
ID_Extraction_Staff bigint Yes Key NULL FK to table:people, person who extracted the DNA
ID_Degradation bigint Yes Key NULL FK to table:degradation
Check_Date datetime Yes NULL date of quality check (format YYYY-MM-DD)
Concentration varchar(5) Yes NULL Concentration value of DNA sample in ng/µl
Absorbance280 varchar(5) Yes NULL Ratio of absorbance (value, 260/280)
Absorbance230 varchar(5) Yes NULL Ratio of absorbance (value, 260/230)
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)
Block_Until datetime Yes NULL date until sample is blocked for delivery (format YYYY-MM-DD)
Block_General tinyint Yes NULL ='1' if DNA sample is blocked for delivery in general; this record won't be visible in the Network's webportal
Backup_Aliquot tinyint Yes NULL ='1' if a backup aliquot is deposited in an external institution

Table: dnatype

Belongs to: DNA extractions

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
Email 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
Email 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)

View descriptions

View: wrapper_view_amplifications

View: wrapper_view_dna

View: wrapper_view_providers

View: wrapper_view_publications_dna

View: wrapper_view_specimens

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