MARIS database schema
The MARIS master database is a SQL Server database. It stores every curated measurement. marisco writes NetCDF4 files for dissemination and CSV files for import into this database. The CSV columns mirror the database columns, so this page is the contract the CSV export must satisfy.
This page documents the two central tables, sample and measure. It is written for developers and curators who work with the CSV export or the database directly. The mapping between MARISCO keys, NetCDF variables, and CSV (database) columns is in field definitions.
The two central tables
sample holds one row per sample: a water sample, an organism, a sediment slice, a sediment trap content. measure holds one row per measurement on a sample: one nuclide, one value, one uncertainty. A sample has many measures. The link is measure.sample_id, which references sample.sample_id.
A column is mandatory when the database declares it NOT NULL. Mandatory columns are marked with a bold dot in the tables below. Handlers must provide these columns before encoding.
sample
| Column | Type | Mandatory | Notes |
|---|---|---|---|
| sample_id | bigint | • | Primary key |
| sampquality | varchar(1) | • | F, C, G |
| ref_id | int | • | references ref |
| osamcod | int | ||
| samplabcode | varchar(100) | ||
| samptype_id | tinyint | • | references samptype |
| lab_id | smallint | • | references lab |
| station | varchar(100) | ||
| aqcs | varchar(2) | NA, Y, N | |
| sedtrap | varchar(2) | NA, Y, N | |
| aqcsyear | int | ||
| aqcsmonth | int | ||
| totdepth | float | ||
| sampdepth | float | ||
| volume | float | ||
| salinity | float | ||
| temperatur | float | ||
| filtered | varchar(2) | NA, Y, N | |
| acid | varchar(2) | NA, Y, N | |
| oxygen | float | ||
| samparea | float | ||
| sliceup | float | ||
| slicedown | float | ||
| sieved | varchar(2) | NA, Y, N | |
| organic | float | ||
| oxic | varchar(2) | NA, Y, N | |
| drytemp | smallint | ||
| drywt | float | ||
| wetwt | float | ||
| percentwt | float | ||
| filtpore | varchar(20) | ||
| sampnote | varchar(2000) | ||
| begperiod | datetime | • | |
| endperiod | datetime | ||
| sedtype_id | tinyint | • | references sedtype |
| drymet_id | tinyint | • | references drymet |
| species_id | smallint | • | references species |
| bodypar_id | smallint | • | references bodypar |
| sampmet_id | tinyint | • | references sampmet |
| prepmet_id | smallint | • | references prepmet |
| gfe | tinyint | ||
| Commonname | varchar(50) | ||
| TaxonRepName | varchar(100) | ||
| SedRepName | varchar(100) | ||
| shiftedcoordinates | geography | ||
| shiftedLong | float | ||
| shiftedLat | float | ||
| longitude | float | ||
| latitude | float |
measure
| Column | Type | Mandatory | Notes |
|---|---|---|---|
| sample_id | bigint | • | references sample |
| measquality | varchar(1) | • | F, C, G |
| nuclide_id | smallint | • | references nuclide |
| decayedto | datetime | ||
| unit_id | tinyint | • | references unit |
| vartype | varchar(5) | ||
| activity | float | ||
| uncertaint | float | ||
| detection | varchar(2) | • | ND, -, <, =, DE |
| rl_detection | varchar(2) | ||
| ru_detection | varchar(2) | ||
| rangelow | float | ||
| rangeupp | float | ||
| freq | int | ||
| counmet_id | smallint | • | references counmet |
| note | varchar(2000) | ||
| actsed | varchar(5) | ||
| wtsed | varchar(5) | ||
| valsed | float | ||
| eased | float | ||
| epsed | float | ||
| seddet | varchar(5) | ||
| gfe | tinyint | ||
| IODE_QualityFlag | tinyint | 1, 2, 3, 4, 9 | |
| profile_id | varchar(50) | ||
| transect_id | varchar(50) | ||
| dl | float | ||
| dt | float | ||
| cl_type | smallint |
Constraints
The sample table: - Primary key on sample_id. - Foreign keys to ref, samptype, lab, sedtype, drymet, species, bodypar, sampmet, prepmet. - Checks: sampquality in (F, C, G); acid, aqcs, filtered, oxic, sedtrap, sieved each in (NA, Y, N).
The measure table: - Foreign keys to sample, nuclide, unit, counmet. - Checks: measquality in (F, C, G); detection in (ND, -, <, =, DE); IODE_QualityFlag in (1, 2, 3, 4, 9).
Required columns for handlers
A handler must produce, before encoding, at least the mandatory columns of the group it writes. For a seawater group, that is at least sample_id, sampquality, ref_id, samptype_id, lab_id, begperiod, sedtype_id, drymet_id, species_id, bodypar_id, sampmet_id, prepmet_id from sample, plus sample_id, measquality, nuclide_id, unit_id, detection, counmet_id from measure.
The NetCDFEncoder itself writes only the variables present in the template, so a missing required column is not caught at encode time. The check belongs in the handler, before encoding. A helper that compares the handler’s DataFrame columns against the mandatory set, and raises before any partial output is written, is a good addition to a shared module.
The lookup tables
The foreign key targets are the nomenclatures: bodypar, drymet, lab, ref, sampmet, samptype, sedtype, species, counmet, nuclide, unit. MARISCO ships their Excel exports as lookup tables in marisco/files/lut, with files named dbo_<table>.xlsx, readable with get_lut from marisco.configs. See reporting template.
Relationship to the CSV export
The CSV export mirrors these tables. Each CSV variable name is a database column name: activity, uncertaint, detection, begperiod, and so on. The mapping is defined in marisco.configs under NC_CSV, which pairs every MARISCO key with its NetCDF variable and its CSV (database) column. The field definitions page renders that mapping.
The NetCDF4 dissemination files carry a subset of these columns. Database columns such as osamcod, aqcs, gfe, shiftedcoordinates, and the sed-* group do not appear in NetCDF. That is by design: NetCDF is for dissemination, the database is for storage.