ER diagram

From GGBN Wiki
Revision as of 10:38, 8 February 2015 by WikiSysop (talk | contribs) (→‎Table descriptions)
Jump to: navigation, search

Contents

Entity-relation diagram of the DNA Module V2.0

February 2014: We are currently updating this information!

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

All of the following information is stored directly in the tables, including the description and comments. Wiki-syntax is generated by running a php script. Last update: 2015-02-08

Table: abcdmetadata

See Metadata for DNA data

Table: abcdmetadataspecimens

See Metadata for Specimen Tool; will be removed and merged with abcdmetadata soon

Table: aliquots

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

Field Type Null Key Default Comment
ID_Aliquots int(10) unsigned NO PRI PK of all aliquots
FK_DNA int(10) unsigned NO MUL FK to parent DNA sample ( table:dnabanknumbers)
FK_Aliquots_Box int(10) unsigned YES MUL FK to table:aliquots_box
FK_Aliquots_Rack int(10) unsigned YES MUL FK to table:aliquots_rack
FK_Aliquots_Fridge int(10) unsigned YES MUL FK to table:aliquots_fridge
Aliquot_Number varchar(100) YES The number/name of the individual aliquot, mostly the DNA extraction no combined with '-A', '-B' et cetera
Origin_Quantity float YES quantity in µl when first prepared
Rest_Quantity float YES quantity in µl after ordering
Aliquot_Position varchar(50) YES position of an individual aliquot on a plate
Aliquot_Barcode varchar(255) YES barcode of an individual aliquot on a plate
Order_All varchar(50) YES ='yes' when whole aliquot has been ordered
Order_Partial varchar(50) YES ='yes' when only a part of the aliquot has been ordered
Order_Rest varchar(50) YES ='yes' when a part of the aliquot has been shipped earlier and now the rest has been ordered
Shipping_All varchar(50) YES ='yes' when only a part of the aliquot has been shipped
Shipping_Partial varchar(50) YES ='yes' when only a part of the aliquot has been shipped
Shipping_Rest varchar(50) YES ='yes' when a part of the aliquot has been shipped earlier and now the rest has been shipped
Price varchar(50) YES price of the individual aliquot (defined via General Settings)
Currency varchar(50) YES currency of the price (defined via General Settings)
Created_When timestamp NO CURRENT_TIMESTAMP
Created_Who varchar(50) NO unknown
Update_Who varchar(50) YES
Update_When timestamp YES

Table: aliquots_box

Belongs to: Locations stock/aliquot

Field Type Null Key Default Comment
ID_Aliquots_Box int(10) unsigned NO PRI PK of listed aliquot boxes
Aliquots_Box varchar(50) NO List of boxes used for aliquots ( table:aliquots)
Created_When timestamp NO CURRENT_TIMESTAMP
Created_Who varchar(50) NO unknown

Table: aliquots_fridge

Belongs to: Locations stock/aliquot

Field Type Null Key Default Comment
ID_Aliquots_Fridge int(10) unsigned NO PRI PK of listed aliquot fridges
Aliquots_Fridge varchar(50) NO List of fridges/freezers used for aliquots ( table:aliquots)
Created_When timestamp NO CURRENT_TIMESTAMP
Created_Who varchar(50) NO unknown

Table: aliquots_rack

Belongs to: Locations stock/aliquot

Field Type Null Key Default Comment
ID_Aliquots_Rack int(10) unsigned NO PRI PK of listed aliquot racks
Aliquots_Rack varchar(50) NO List of racks used for aliquots ( table:aliquots)
Created_When timestamp NO CURRENT_TIMESTAMP
Created_Who varchar(50) NO unknown

Table: amplifications

UNDER CONSTRUCTION! Belongs to: Sequence data, DNA extractions

Field Type Null Key Default Comment
ID_Amplification int(10) unsigned NO PRI PK of all amplifications
FK_DNA int(10) unsigned NO MUL FK to parent DNA sample ( table:dnabanknumbers)
FK_GeneticLocus int(10) unsigned NO MUL FK to used Genetic locus ( table:geneticlocus)
FK_Subfragment int(10) unsigned YES MUL FK to used Genetic locus ( table:subfragment)
Amplification_Date datetime YES Date of an individual amplification
FK_Amplification_Staff int(20) unsigned YES MUL FK to table:people contains person who made the amplification
Success varchar(50) YES contains "Yes" or "No"
SuccessDetails varchar(500) YES remarks on amplification, e.g. why it has been failed
FK_Amplification_Method int(10) unsigned YES MUL FK to table:methods
FK_Purification_Method int(10) unsigned YES MUL FK to table:methods
FK_PrimerForward int(10) unsigned YES MUL FK to table:primer
FK_PrimerReverse int(10) unsigned YES MUL FK to table:primer
Created_When timestamp NO CURRENT_TIMESTAMP
Created_Who varchar(50) NO unknown
Update_Who varchar(50) YES
Update_When timestamp YES

Table: basisofrecordspecimen

Belongs to: Specimen Tool

SQL create statement filled with standardised content. Content should not be changed in general, especially when providing data to GBIF or GGBN!

Field Type Null Key Default Comment
ID_BasisOfRecord_Specimen int(10) unsigned NO PRI PK of listed record bases in accordance with GBIF and GGBN
BasisOfRecord_Specimen varchar(50) NO List of record bases used by package Specimen Tool; important for providing data to GBIF or GGBN
Description varchar(255) YES
Created_Who varchar(50) NO unknown
Created_When timestamp NO CURRENT_TIMESTAMP

Table: biogeographicregion

Belongs to: Specimen Tool

SQL create statement filled with standardised content. Existing table contains European categories. Of course you can use other standardised lists.

Field Type Null Key Default Comment
ID_BiogeographicRegion int(10) unsigned NO PRI PK of all biogeographic categories
BiogeographicRegion varchar(50) NO List of all biogeographic categories
Created_Who varchar(50) NO unknown
Created_When timestamp NO CURRENT_TIMESTAMP

Table: cacheassociatedunits

Belongs to: Specimen Cache

Field Type Null Key Default Comment
ID_Association int(10) unsigned NO PRI PK of all associated records for specimens or tissues; e.g. the "grandparent" record of a DNA sample
FK_Cache int(10) unsigned NO MUL FK to table:cachecollection
AssociatedUnitID varchar(100) NO UnitID/Catalogue Number of associated record
AssociatedCollectionCode varchar(100) NO SourceID/CollectionCode of associated record
AssociatedInstitutionCode varchar(100) NO SourceInstitutionID/InstitutionCode of associated record
AssociationType varchar(100) YES Description of relation between associated records
WrapperUrl varchar(200) YES access point, for BioCASe records only
Created_Who varchar(50) NO unknown
Created_When timestamp NO CURRENT_TIMESTAMP
Updated_Who varchar(50) YES
Updated_When timestamp YES

Table: cachecollection

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

Field Type Null Key Default Comment
ID_Cache int(10) unsigned NO PRI PK of all DNA voucher specimens or tissues referenced via BioCASE or GBIF (parent record of a DNA sample)
FK_Dataset_Specimen int(10) unsigned NO MUL FK to table:dataset
UnitID_Specimen varchar(255) NO GBIF-Identifier, UnitID or Catalogue Number (part of triple ID)
CollectionCode_Specimen varchar(255) NO GBIF-Identifier, CollectionCode or SourceID (part of triple ID)
InstitutionCode_Specimen varchar(255) NO GBIF-Identifier, InstitutionCode or SourceInstitutionID (part of triple ID)
ContinentOrOcean varchar(255) YES Continent or Ocean (collection site)
CountryName varchar(255) YES English Name of the Country (collection site)
CountryISO2 varchar(50) YES ISO 3166-2 (collection site)
Collectors varchar(200) YES Collection Team
CollectionDate varchar(200) YES Collection Date (free text)
CollectionDateMin varchar(200) YES Collection Date from (free text or ISO, depends on original data provider)
CollectionDateMax varchar(200) YES Collection Date to (free text or ISO, depends on original data provider)
CollectorsNo varchar(100) YES field number of a specimen given by collector (in the field)
Locality varchar(300) YES free text of locality information (below country level)
Altitude varchar(50) YES free text of Altitude measurement (from - to - unit)
Longitude varchar(50) YES decimal value of Longitude
Latitude varchar(50) YES decimal value of Latitude
CollectionInfo_Other varchar(500) YES deprecated
TypeStatus varchar(150) YES type status of the specimen
GUID_Specimen varchar(50) YES GUID of the record if provided
Created_When timestamp NO CURRENT_TIMESTAMP
Created_Who varchar(50) NO unknown
Updated_When timestamp YES
Updated_Who varchar(50) YES

Table: cachehighertaxa

Belongs to: Specimen Cache

Field Type Null Key Default Comment
ID_HigherTaxon int(10) unsigned NO PRI PK of all Higher Taxa, sent by original data provider (wrappers)
FK_CacheTaxonIdentified int(10) unsigned NO MUL FK to table:cachetaxonidentified
HigherTaxon varchar(255) NO Name of the HigherTaxon
TaxonRank varchar(255) YES Rank of the Higher Taxon
Created_When timestamp NO CURRENT_TIMESTAMP
Created_Who varchar(50) NO unknown
Updated_When timestamp YES
Updated_Who varchar(50) YES

Table: cachemultimedia

Belongs to: Specimen Cache

Field Type Null Key Default Comment
ID_Multimedia int(10) unsigned NO PRI PK of all Multimedia URLs, sent by original data provider (wrappers)
FK_Cache int(10) unsigned NO MUL FK to table:cache
Multimedia varchar(500) NO List of all Multimedia URLs
Created_Who varchar(50) NO unknown
Created_When timestamp NO CURRENT_TIMESTAMP
Updated_When timestamp YES
Updated�_Who varchar(50) YES

Table: cachetaxonidentified

Belongs to: Specimen Cache

Field Type Null Key Default Comment
ID_TaxonIdentified int(10) unsigned NO PRI PK of all specimen determinations, sent by original data provider (wrappers)
FK_Cache int(10) unsigned NO MUL FK to table:cache
Genus varchar(255) YES MUL
Subgenus varchar(255) YES
FirstEpithet varchar(255) YES MUL
SecondEpithet varchar(255) YES MUL
Rank varchar(50) YES Rank of the taxon
HybridFlag varchar(50) YES
Other varchar(500) YES Deprecated
NameAuthorYear varchar(500) YES Full Scientific Name String including Author teams and years
PreferredFlag varchar(50) YES mostly provided as "1" or "true" ; if empty filled with "1"
Created_When timestamp NO CURRENT_TIMESTAMP
Created_Who varchar(50) NO unknown
Updated_When timestamp YES
Updated_Who varchar(50) YES

Table: cloningmethod

Belongs to: Sequence data

At present not in use, coming soon.

Table: countrorcontinent

Belongs to Specimen Tool SQL create statement filled with standardised content. Content should not be changed in general, especially when providing data to GBIF or the DNA Bank Network!

Field Type Null Key Default Comment
ID_Continent int(10) unsigned NO PRI PK of all continents and oceans
ContinentOrOcean varchar(50) NO List of all continents and oceans; no FK

Table: country_continent

Belongs to: Specimen Tool

SQL create statement filled with standardised content. Content should not be changed in general, especially when providing data to GBIF or the DNA Bank Network!

Field Type Null Key Default Comment
FK_Country int(10) unsigned NO MUL FK of all Countries
Continent varchar(50) NO MUL Contintens belonging to Country (e.g. Turkey belongs to Asia and Europe)

Table: countryisocode

Belongs to: Specimen Tool

Create statement filled with standardised content. Content should not be changed in general, especially when providing data to GBIF or the DNA Bank Network!

Field Type Null Key Default Comment
ID_Country int(10) unsigned NO PRI PK of all Countries
IsoCode varchar(50) NO ISO 3611-2 code of the country
Country varchar(150) NO English name of the country

Table: dataset

Belongs to: Specimen data providers, Specimen Tool

SQL create statement filled with standardised first row. Please don't make any changes at first row manually!

Field Type Null Key Default Comment
ID_Dataset int(10) unsigned NO PRI PK of all connected specimen datasets (GBIF compliant databases, first row contains the Specimen Tool)
FK_Provider int(10) unsigned YES MUL FK to table:provider
Digir_Resource varchar(255) YES Parameter important for Digir only
Digir_Source varchar(255) YES Parameter important for Digir only, mostly an URL
Display varchar(255) YES Name of this dataset given by DNA Module-user
Internal_External varchar(10) YES DNA Module-user decide if this dataset appears in the external or internal dataset list
DatasetKey_GBIF varchar(50) YES Dataset guid (key) provided by GBIF via web services
Protocol varchar(50) YES Protocol name provided by GBIF via web services (e.g. BIOCASE)
InstitutionCode varchar(50) YES Deprecated?
Created_When timestamp NO CURRENT_TIMESTAMP
Created_Who varchar(50) NO unknown

Table: dnabanknumbers

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

Field Type Null Key Default Comment
ID_DNA int(10) unsigned NO PRI PK of all DNA extraction numbers
FK_Cache int(10) unsigned YES MUL FK to table:cachecollection, underlying specimen
FK_AcquiredFrom int(10) unsigned YES MUL FK to table:people, Person who provided the sample (used for donations)
FK_Type int(10) unsigned YES MUL FK to table:dnatype, Type of the DNA
FK_Location_Stock int(10) unsigned YES MUL FK to table:location_stock
DNA_Bank_Number varchar(50) NO DNA extraction number; Must be unique
FK_Relation int(10) unsigned NO MUL FK to table:relation
FK_Tissue int(10) unsigned YES MUL FK to table:tissue, type of tissue used for extraction
FK_Preservation int(10) unsigned YES MUL FK to table:preservation, preservation of underyling tissue/specimen
Extraction_Date datetime YES date of extraction (format YYYY-MM-DD) if known
NoExtractDate int(11) NO = '1' if date of extraction is not known
FK_Extraction_Method int(10) unsigned YES MUL FK to table:methods, extraction kit
FK_Purification_Method int(10) unsigned YES MUL FK to table:methods, method of purification
FK_Extraction_Staff int(10) unsigned YES MUL FK to table:people, person who extracted the DNA
Quality_Check_Date datetime YES date of quality check (format YYYY-MM-DD)
FK_Concentration_Method int(10) unsigned YES MUL FK to table:methods, method to determine concentration
FK_GelImage int(11) unsigned YES MUL FK to table:gelimages
Concentration varchar(10) YES Concentration value of DNA sample in ng/µl
Absorbance280 varchar(10) YES Ratio of absorbance (value, 260/280)
Absorbance230 varchar(10) YES Ratio of absorbance (value, 260/230)
Weight varchar(10) YES DNA weight
WeightUnit varchar(10) YES Unit of measuring DNA weight
FK_Weight_Method int(10) unsigned YES MUL FK to table:methods, method to measure weight
Stock_Gone int(10) YES ='1' if stock is gone
Source_Gone int(10) YES ='1' of source (tissue/specimen) is gone
Notes varchar(500) YES Notes related to DNA sample, visible for all user groups
Notes_Intern varchar(500) YES Notes related to DNA sample, not visible for guests (user group)
Blocked_Until datetime YES date until sample is blocked for delivery (format YYYY-MM-DD)
Blocked_For_Loaning int(10) YES ='1' if DNA sample is blocked for delivery only; this record will be visible in the Network's data portal
Blocked_For_Publishing int(10) YES ='1' if DNA sample is blocked in general; this record won't be visible in the Network's data portal
Loan_Conditions varchar(500) YES certain conditions for loaning the DNA sample
Backup_Aliquot int(10) YES ='1' if a backup aliquot is deposited in an external institution
GUID varchar(50) YES GUID of the DNA sample
Created_Who varchar(50) NO unknown
Created_When timestamp NO CURRENT_TIMESTAMP
Update_Who varchar(50) YES
Update_When timestamp YES
Lock_Who varchar(50) YES
Lock_When timestamp YES

Table: dnatype

Belongs to: DNA extractions

SQL create statement filled with standardised content. Existing content should not be changed, but you can add further entries.

Field Type Null Key Default Comment
ID_Type int(10) unsigned NO PRI PK of all DNA Types
Type varchar(50) NO List of all DNA types
Created_When timestamp NO CURRENT_TIMESTAMP
Created_Who varchar(50) NO unknown

Table: gelimages

Belongs to: DNA extractions

Field Type Null Key Default Comment
ID_Images int(10) unsigned NO PRI PK of all gel images
FileURI varchar(500) NO URL of gel image
GelRemarks varchar(150) YES Remarks on a gel
GelVoltage varchar(50) YES Gel voltage
GelConcentration varchar(50) YES Gel concentration
GelDuration varchar(50) YES Gel duration
GelLadder varchar(100) YES Gel ladder
GelStain varchar(100) YES Gel stain
Created_Who varchar(50) NO unknown
Created_When timestamp NO CURRENT_TIMESTAMP
Updated_Who varchar(50) YES
Updated_When timestamp YES

Table: geneticlocus

Belongs to: DNA extractions

Field Type Null Key Default Comment
ID_GeneticLocus int(10) unsigned NO PRI PK of all Genetic Loci/Fragments
GeneticLocus varchar(150) NO List of all Genetic Loci/Fragments
GeneticLocusRemarks varchar(255) NO Remarks on Genetic Locus/Fragment
Created_When timestamp NO CURRENT_TIMESTAMP
Created_Who varchar(50) NO unknown
Updated_Who varchar(50) YES
Updated_When timestamp YES

Table: geocodemethod

Belongs to: Specimen Tool Include into methods table?

SQL create statement filled with standardised content. Existing content should not be changed, but you can add further entries.

Field Type Null Key Default Comment
ID_Geocode int(10) unsigned NO PRI PK of all geocode methods
Geocode varchar(50) NO List of all geocode methods
Created_Who varchar(50) NO unknown
Created_When timestamp NO CURRENT_TIMESTAMP

Table: habitatcode

Belongs to: Specimen Tool

SQL create statement filled with standardised content. Existing table contains European categories (EUNIS). Of course you can use other standardised lists.

Field Type Null Key Default Comment
ID_HabitatCode int(10) unsigned NO PRI PK of all habitat code categories
HabitatCode varchar(50) NO List of all habitat code categories
Created_Who varchar(50) NO unknown
Created_When timestamp NO CURRENT_TIMESTAMP

Table: kindofunit

Belongs to: Specimen Tool

Field Type Null Key Default Comment
ID_KindOfUnit int(10) unsigned NO PRI PK of all Kind of Units (materialSampleType)
KindOfUnit varchar(100) NO List of all Kind of Units (materialSampleType)
KindOfUnitRemarks varchar(500) YES Remarks on a Kind of Unit
Created_Who varchar(50) NO unknown
Created_When timestamp NO CURRENT_TIMESTAMP
Updated_Who varchar(50) YES
Updated_When timestamp YES

Table: landusecode

Belongs to: Specimen Tool

SQL create statement filled with standardised content. Existing table contains European categories. Of course you can use other standardised lists.

Field Type Null Key Default Comment
ID_LandUseCode int(10) unsigned NO PRI PK of all land use codes
LandUseCode varchar(50) NO List of all land use codes
Created_Who varchar(50) NO unknown
Created_When timestamp NO CURRENT_TIMESTAMP

Table: location_stock

Belongs to: DNA extractions, Locations stock/aliquots

Field Type Null Key Default Comment
ID_Location_Stock int(10) unsigned NO PRI PK of all stock locations
FK_Stock_Box int(10) unsigned YES MUL FK to table:stock_box
FK_Stock_Rack int(10) unsigned YES MUL FK to table:stock_rack
FK_Stock_Fridge int(10) unsigned YES MUL FK to table:stock_fridge
Stock_Position varchar(50) YES position of the stock tube on a plate
Stock_Barcode varchar(200) YES barcode of the stock tube on a plate
Origin_Quantity float YES quantity in µl when first prepared
Rest_Quantity float YES quantity in µl after using stock partly for an aliquot
Created_When timestamp YES CURRENT_TIMESTAMP
Created_Who varchar(50) NO unknown
Update_Who varchar(50) YES
Update_When timestamp YES

Table: methods

Belongs to: DNA extractions, Specimen Tool, Sequence data

Field Type Null Key Default Comment
ID_Method int(10) unsigned NO PRI PK of all methods
Method varchar(200) NO List of all methods
MethodGroup varchar(50) NO tbd; group for drop-down lists
MethodRemarks varchar(500) YES remarks on a certain method
FK_Reference int(10) unsigned YES MUL FK to table:reference
Created_When timestamp NO CURRENT_TIMESTAMP
Created_Who varchar(50) NO unknown
Updated_Who varchar(50) YES
Updated_When timestamp YES

Table: people

Belongs to: DNA extractions, Customer requests, Sequence data, Specimen Tool

Field Type Null Key Default Comment
ID_People int(20) unsigned NO PRI PK of all Persons
FormOfAddress varchar(50) YES
Title varchar(100) YES
Forename varchar(50) YES
Surname varchar(50) YES
Phone varchar(100) YES
Email varchar(100) YES
Remarks varchar(500) YES
Name_All varchar(50) YES name string (Surname, Forename)
Institut varchar(150) YES
Division varchar(150) YES
Street varchar(150) YES
City varchar(150) YES
Postal_Code varchar(150) YES
Country varchar(150) YES
Institut_Delivery varchar(150) YES if shipping/delivery address differs from billing address
Division_Delivery varchar(150) YES
Street_Delivery varchar(150) YES
City_Delivery varchar(150) YES
Postal_Code_Delivery varchar(150) YES
Country_Delivery varchar(150) YES
Staff varchar(10) YES ='Yes' if person is part of the lab, also externals that have done the extractions
Provider varchar(10) YES ='Yes' if person has provided samples
Customer varchar(10) YES ='Yes' if person has ordered DNA sample
Created_When timestamp NO CURRENT_TIMESTAMP
Created_Who varchar(50) NO unknown
Updated_When timestamp YES
Updated_Who varchar(50) YES

Table: preservations

Belongs to: DNA extractions, Specimen Tool

Field Type Null Key Default Comment
ID_Preservation int(10) unsigned NO PRI PK of all Preservation types (DNA,tissue,specimen)
Preservation varchar(200) NO List of all preservation types (DNA,tissue,specimen)
PreservationRemarks varchar(500) NO Remarks on a certain preservation
FK_Reference int(11) unsigned NO MUL FK to table:reference
Created_When timestamp NO CURRENT_TIMESTAMP
Created_Who varchar(50) NO unknown
Updated_When timestamp YES
Updated_Who varchar(50) YES

Table: prevalentaspect

Belongs to: Specimen Tool

Field Type Null Key Default Comment
ID_Aspect int(10) unsigned NO PRI PK of all aspect categories
Aspect varchar(50) NO List of all aspect categories
Created_Who varchar(50) NO unknown
Created_When timestamp NO CURRENT_TIMESTAMP

Table: primer

Belongs to: Sequence data

At present not in use, coming soon.

Field Type Null Key Default Comment
ID_Primer int(10) unsigned NO PRI PK of all primers
Primer varchar(100) NO unknown List of all primer names
PrimerSequence varchar(5000) YES List of all primer sequences
PrimerRemarks varchar(500) YES Remarks on a certain primer
FK_Reference int(10) unsigned YES MUL FK to table:reference
adapters varchar(100) YES MIxS term
multiplexIdentifiers varchar(100) YES MIxS term
Created_Who varchar(50) NO unknown
Created_When timestamp NO CURRENT_TIMESTAMP
Updated_Who varchar(50) NO unknown
Updated_When timestamp YES

Table: provider

Belongs to: Specimen data providers

Field Type Null Key Default Comment
ID_Provider int(10) unsigned NO PRI PK of all specimen data provider urls
Provider varchar(500) NO List of all specimen data providers urls (access points)
FK_Schema int(10) unsigned NO MUL FK to required Schema ( table:schemata)
Created_When timestamp NO CURRENT_TIMESTAMP
Created_Who varchar(50) NO unknown

Table: reference

Belongs to: Sequence data, DNA extractions, Specimen Tool

Field Type Null Key Default Comment
ID_References int(10) unsigned NO PRI PK of all references, protocols etc.
ReferenceText varchar(1000) YES MUL Citation format of all references
ReferenceShort varchar(100) YES Abbreviation for a reference, user-defined
ReferenceUrl varchar(1000) YES URL to online version of reference
Created_Who varchar(50) NO unknown
Created_When timestamp NO CURRENT_TIMESTAMP
Update_When timestamp YES
Update_Who varchar(50) YES

Table: relation

Belongs to: DNA extractions, [[#Specimen_Tool | Specimen_Tool]

SQL create statement filled with standardised content. You can change or add relationships if needed. Ideally you should use the existing relationships.

Field Type Null Key Default Comment
ID_Relation int(10) unsigned NO PRI PK of all relationships
Relation varchar(250) NO List of all relationships
RelationRemarks varchar(250) NO Remarks on a certain relation
FK_Reference int(10) unsigned YES MUL FK to ( table:reference)
Created_When timestamp NO CURRENT_TIMESTAMP
Created_Who varchar(50) NO unknown
Updated_Who varchar(50) YES unknown
Updated_When timestamp YES

Table: request

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

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=1802"