Schema of database
You can download this file here.
Database table in detail
This is a guide to understand the 16 tables of DataFlowS - EcoFlowS. The version of this document is : v.2.3.
Dataset
Represent a sampling project.
This table have 10 fields.
id dataset (
IntegerField)
lb dataset (
CharField) is default id_dataset + “dataset”
dataset owner (
ForeignKey) is the person and/or company who owns the data with associated rights
dataset institute (
CharField) is institute (or representative one) associated with the dataset
dataset country (
CharField) is country in which the location is located (full name in english)
id template (
ForeignKey) is id_format_template de table format_template existant
default availability (
BooleanField) is set True if all data is public access (login remain mandatory)
dataset status (
CharField) is Set data status among : dataset-only, inventory-only, full-data
DOI (
CharField) is DOI if possible
creation date (
DateField) is dataset creation date DatasetInfo
This table have 4 fields.
ID (
BigAutoField)
id dataset (
ForeignKey) is id_dataset from Dataset table
id tableField (
ForeignKey) is id_tableField from Table_field
valeur (
CharField) is free value but possibly to be check if tagged as such in table_Field table and/or mandatory if tagged as such in tamplate_field_list table DatasetSite
This table have 3 fields.
id site dataset (
AutoField)
id dataset (
ForeignKey) is id_dataset from dataset table
id site (
ForeignKey) is id_site from Site table FormatTemplate
This table have 5 fields.
id template (
AutoField)
lb template (
CharField) is template format name
short summary (
CharField) is short template description
version (
IntegerField) is version number (if necessary)
creation date (
DateField) is creation date RefTaxonCateg
Table of taxon
This table have 8 fields.
id taxon (
IntegerField) is identifiant du taxon
lb taxon (
CharField) is Appelation du taxon
thematique taxon (
CharField) is thématique (macoinvertébrés, poisson, diatomée, macrophyte, phyrtoplancton…)
niveau hierarchique (
IntegerField) is niveau hierarchique du taxon RECOFGE (version numerique)
version (
DateField) is date de la version
taxon parent (
IntegerField) is id_taxon du parent hierarchique, NULL possible seulement pour la racine
id taxon old (
IntegerField) is id_taxon de la version antérieure
statut taxon (
BooleanField) is TRUE pour appelation active et à jour, FALSE pour ancienne appellation devenue périmée
Exemple :
Cyphon (62493)
Cyphon (62493)
Sample
Sample
This table have 4 fields.
id sample (
AutoField)
lb sample (
CharField) is explicit labeling possible
id survey (
ForeignKey)
time (
TimeField) is hh:mm:ss SampleData
Sample with taxon counts
This table have 5 fields.
id sampleData (
AutoField)
id sample (
ForeignKey) is id_sample from Sample table
id taxon ori (
ForeignKey) is id_taxon from ref_taxa_categ as it was when added to base.
id taxon (
ForeignKey) is id_taxon from ref_taxa_categ up to date.
value (
FloatField) is counted or mesured value SampleDataInfo
This table have 4 fields.
ID (
BigAutoField)
id sampleData (
ForeignKey) is id_surveyData from sampleData table
id tableField (
ForeignKey) is id_tableField from Table_field
valeur (
CharField) is free value but possibly to be check if tagged as such in table_Field table and/or mandatory if tagged as such in tamplate_field_list table SampleInfo
This table have 4 fields.
ID (
BigAutoField)
id sample (
ForeignKey) is id_sample from Sample table
id tableField (
ForeignKey) is id_tableField from table_field
valeur (
CharField) is free value but possibly to be check if tagged as such in table_Field table and/or mandatory if tagged as such in tamplate_field_list table Site
Table for define a station also know as site sampling
This table have 8 fields.
id site (
AutoField) is Auto increment
cd site (
CharField) is site code in case of existing nomenclature (eg water agency station coding)
x (
FloatField) is The latitude of the location (datum : WGS84)
y (
FloatField) is The longitude of the location (datum : WGS84)
z (
FloatField) is Mean altitude of the site or station above sea level (metrique)
GPS accuracy (
CharField) is accuracy in log10 scale : 1m, 10m, 100m, 1km, unknown
river (
CharField) is full name of the river, including the watercourse type
country (
CharField) is country in which the location is located (full name in english or ISO3166 two letter code)
Exemple :
Station ALB_123 45.92842, 5.3858 Albarine
Station ALB_123 45.92842, 5.3858 Albarine
SiteInfo
This table have 4 fields.
ID (
BigAutoField)
id site dataset (
ForeignKey) is id_site from Site table
id tableField (
ForeignKey) is id_tableField from Table_field
valeur (
CharField) is free value but possibly to be check if tagged as such in table_Field table and/or mandatory if tagged as such in tamplate_field_list table Survey
Table of survey (sample × time)
This table have 6 fields.
id survey (
AutoField)
lb survey (
CharField) is explicit labeling possible
id site dataset (
ForeignKey) is id_site_meta de table Dataset_site existant
date (
DateField) is yyyy-mm-dd
ecological theme (
CharField) is ecological theme between (invertebrate, fish, PC, T° etc…) : each theme tables (context and dataSample + corr. side tables) will have its own schemas
classe (
CharField) is type de mesure : directe ; indirecte (DNA) ; modélisée SurveyInfo
This table have 4 fields.
ID (
BigAutoField)
id survey (
ForeignKey) is id_survey from Survey table
id tableField (
ForeignKey) is id_tableField from table_field
valeur (
CharField) is free value but possibly to be check if tagged as such in table_Field table and/or mandatory if tagged as such in tamplate_field_list table TableField
This table have 7 fields.
id tableField (
IntegerField)
lb tableField (
CharField) is field name
to check (
BooleanField) is wether to check or not in the API (eg range or exsting in a ref table)
check rule (
CharField) is if to_check is True, definition of the rule
var type (
CharField) is variable class (char, int, float, boolean, date)
id unit (
ForeignKey) is id_unit from Unit table
definition (
TextField) is field definition TemplateFieldList
This table have 6 fields.
id template field list (
AutoField)
id template (
ForeignKey) is id_template from Format_template table
id tableField (
ForeignKey) is field name
lb schema (
CharField) is this postgreSQL database schema where to locate this field
lb table (
CharField) is this postgreSQL database table where to locate this field
mandatory (
BooleanField) is is the given field mandatory in the correponding template Unit
This table have 3 fields.
id unit (
IntegerField)
short lb unit (
CharField) is short name
long lb unit (
CharField) is long name