DB Schema

A proposed database design based on Vannmiljø Data

The page shows the database schema diagram along with table definitions based on the Vannmiljø dataset.

DB Schema Diagram

The proposed database design shown in the ER (Entity Relationship) diagram below contains 10 tables including three meta (entity) tables, four variable (look-up or reference) tables, one fact (measurement) table, and two mixed tables.

The columns with PK in the diagram indicate primary keys of the table, which guarantees unique identifications.

Activity Table

The activity table contains the monitoring programs or projects associated with the samples.

Table Columns

Name Data Type PK NA Allowed Description
activity_id TEXT ✓ FALSE Primary Key. Unique identifier for the activity/monitoring program.
activity_name TEXT FALSE Name of the monitoring program or project.

Client Table

The client table stores information about the entities (e.g., municipalities, environmental agencies) that commissioned the data collection.

Table Columns

Name Data Type PK NA Allowed Description
client_id INTEGER ✓ FALSE Primary Key. Unique identifier for the client.
client TEXT FALSE Name of the client or commissioner.
archive BOOLEAN FALSE Flag indicating if the record is archived.

Contractor Table

The contractor table identifies the laboratories or institutes that performed the sampling or analysis.

Table Columns

Name Data Type PK NA Allowed Description
contractor_id INTEGER ✓ FALSE Primary Key. Unique identifier for the contractor.
contractor TEXT FALSE Name of the contractor (e.g., NIVA, Analycen).

Site Table

The site table holds geographical and administrative details about the sampling locations.

Table Columns

Name Data Type PK NA Allowed Description
site_code TEXT ✓ FALSE Primary Key. Unique code for the sampling site.
site_name TEXT TRUE Name of the sampling location.
label TEXT TRUE Additional designation or label for the site.
lon REAL FALSE Longitude coordinate.
lat REAL FALSE Latitude coordinate.
dist_to_coast REAL TRUE Distance to the coast.
country TEXT TRUE Country name.
country_code TEXT TRUE Country code (e.g., NO).
municipality TEXT TRUE Municipality name.
sea_name TEXT TRUE Name of the sea or coastal water body.

Sample Method Table

The sample_method table catalogs the different techniques and equipment used to collect samples.

Table Columns

Name Data Type PK NA Allowed Description
method_id INTEGER ✓ FALSE Primary Key. Unique identifier for the sampling method.
method TEXT FALSE Description or standard code for the sampling method.

Analysis Method Table

The analysis_method table contains the laboratory procedures and units used to analyze the parameters.

Table Columns

Name Data Type PK NA Allowed Description
analysis_id INTEGER ✓ FALSE Primary Key. Unique identifier for the analysis method.
analysis TEXT FALSE Description or standard code of the analysis method.
unit TEXT FALSE Unit of measurement for the analysis results.

Parameter Table

The parameter table defines the chemicals or properties being measured.

Table Columns

Name Data Type PK NA Allowed Description
param_id TEXT ✓ FALSE Primary Key. Unique code for the parameter.
param_name TEXT FALSE Name of the chemical or measured property.
cas_no TEXT TRUE Chemical Abstracts Service (CAS) registry number.

Sample Table

The sample table records the core metadata for each physical sample collected, tying together locations, methods, and actors.

Table Columns

Name Data Type PK NA Allowed Description
sample_id TEXT ✓ FALSE Primary Key. Unique identifier for the sample.
activity_id TEXT FALSE Foreign Key to activity table.
site_code TEXT FALSE Foreign Key to site table.
client_id INTEGER FALSE Foreign Key to client table.
contractor_id INTEGER FALSE Foreign Key to contractor table.
method_id INTEGER FALSE Foreign Key to sample_method table.
upper_depth REAL FALSE Upper depth of the sample.
lower_depth REAL FALSE Lower depth of the sample.
sample_time TEXT TRUE Timestamp of when the sample was collected.
filtered BOOLEAN TRUE Indicates whether the sample was filtered.

Sediment Table

The sediment table stores the actual measurement values for the parameters analyzed in each sample.

Table Columns

Name Data Type PK NA Allowed Description
sample_id TEXT ✓ FALSE Primary Key. Foreign Key to sample table.
param_id TEXT ✓ FALSE Primary Key. Foreign Key to parameter table.
sediment_no INTEGER ✓ FALSE Primary Key. Sequential index for multiple records per sample-parameter combination.
analysis_id INTEGER FALSE Foreign Key to analysis_method table.
value REAL FALSE The measured concentration or numerical result.
operator TEXT TRUE Mathematical operator (e.g., <, =, >), indicating LLD.
sample_no TEXT TRUE Physical sample identification number.
n_values INTEGER TRUE Number of values/replicates used to derive the measurement.

LLD (Limits of Detection) Table

The lld table contains Limit of Detection (LOD) and Limit of Quantification (LOQ) data corresponding to specific measurements.

Table Columns

Name Data Type PK NA Allowed Description
sample_id TEXT ✓ FALSE Primary Key. Foreign Key to sediment table.
param_id TEXT ✓ FALSE Primary Key. Foreign Key to sediment table.
sediment_no INTEGER ✓ FALSE Primary Key. Foreign Key to sediment table.
type TEXT ✓ FALSE Primary Key. Type of limit (e.g., LOD or LOQ).
value REAL FALSE Numerical value of the detection/quantification limit.