ER diagram

Entity-relation diagram of the DNA Module V2.0

Overview

Bild einbauen

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

Molecular publications

ER diagram package “Molecular publications”

This package allows to associate molecular publications to DNA samples and sequence data. It should not be a complete substitute for a literature management software.

• Main table:
  • publications
• Related packages:
  • DNA extractions
  • 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

Log tables

Kurze Einführung, worum gehts, Allgemeiner Aufbau

Table descriptions

Table: aliquots

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

Column	Type	Null	Key	Default	Description
ID_Aliquots	int	No	PRI	AUTO_INCREMENT	ID of the aliquots (Primary Key)
ID_DNA	int	No	Key	No default	FK to parent DNA sample ( table:dnabanknumbers)
ID_Aliquots_Box	int	Yes	Key	NULL	FK to table:aliquots_box
ID_Aliquots_Rack	int	Yes	Key	NULL	FK to table:aliquots_rack
ID_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

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	DATE	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_Method	int	Yes	Key	NULL	FK to table:purificationmethod 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: authors

Belongs to: Molecular publications

Column	Type	Null	Key	Default	Description
ID_Author	bigint	No	PRI	AUTO_INCREMENT	PK of listed authors
Author	varchar(150)	Yes		NULL	List of authors used by package Molecular Publications

Table: basisofrecordspecimen

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_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 the DNA Bank Network

Table: cachecollection

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

Column	Type	Null	Key	Default	Description
ID_Cache	int	No	PRI	AUTO_INCREMENT	PK of all DNA voucher specimens referenced via BioCASe or DiGIR (wrappers)
ID_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

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)
ID_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: cacheimages

Belongs to: Specimen Cache

Column	Type	Null	Key	Default	Description
ID_Images	int	No	PRI	AUTO_INCREMENT	PK of all Image URLs, sent by original data provider (wrappers)
ID_Cache	int	No	Key	No default	FK to table:cache
Images	varchar(500)	Yes		NULL	List of all Image 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)
ID_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: continentorocean

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_Continent	int	No	PRI	AUTO_INCREMENT	PK of all Continents and Oceans
ContinentOrOcean	varchar(50)	No		No default	List of all Continents and Oceans

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
Intern_Extern	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_Location_Stock	bigint	Yes	Key	NULL	FK to table:location_stock
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	varchar(50)	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	varchar(50)	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	varchar(50)	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: 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: 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	date	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	date	Yes		NULL	Date of shipping
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	date	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	date	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: 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_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	varchar(200)	Yes		NULL	Collection Date from (format: YYYY-MM-DD)
CollectionDateMax	varcahr(200)	Yes		NULL	Collection Date to (format: YYYY-MM-DD)
CollectionYear	varchar(50)	Yes		NULL	Collection Year (generated when record has been saved)
CollectorsNo	varchar(100)	Yes		NULL	field number of a specimen given by collector (in the field)
Locality_ID	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_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
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
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
LongitudeOther	varchar(50)	Yes		NULL	original label data
LatitudeOther	varchar(50)	Yes		NULL	original label data
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

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	date	Yes		NULL	date when multimedia file has been created
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: Locations stock/aliquot

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