Revision as of 15:45, 7 February 2015 (view source) WikiSysop (talk | contribs) (→‎Table: cachecollection) ← Older edit		Revision as of 15:46, 7 February 2015 (view source) WikiSysop (talk | contribs) (→‎Table: cachecollection) Newer edit →
Line 728:		Line 728:
	|MUL		|MUL
	|		|
−	|FK to table:dataset	+	|FK to [[#Table:_dataset | table:dataset]]
	|-		|-
	|UnitID_Specimen		|UnitID_Specimen

---

Revision as of 15:46, 7 February 2015

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

See Metadata for DNA data

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

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