CREATE TABLE `waters_analysis` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Interne Datensatznummer', `timestamp` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Zeit der letzten Änderung', `checked` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Kennzeichnung für geprüften Datensatz', `name` varchar(255) NOT NULL COMMENT 'Name|Bezeichnung der Analyse - empfohlen wird der Name der Ortschaft, des Ortsteils oder Wasserwerks', `country` set('Baden-Württemberg','Bayern','Berlin','Brandenburg','Bremen','Hamburg','Hessen','Mecklenburg-Vorpommern','Niedersachsen','Nordrhein-Westfalen','Rheinland-Pfalz','Saarland','Sachsen','Sachsen-Anhalt','Schleswig-Holstein','Thüringen','Österreich/Oberösterreich','Österreich/Niederösterreich','Österreich/Wien','Österreich/Burgenland','Österreich/Steiermark','Österreich/Kärnten','Österreich/Salzburg','Österreich/Tirol','Österreich/Vorarlberg','Schweiz/Zürich','Schweiz/Bern','Schweiz/Luzern','Schweiz/Uri','Schweiz/Schwyz','Schweiz/Obwalden','Schweiz/Nidwalden','Schweiz/Glarus','Schweiz/Zug','Schweiz/Freiburg','Schweiz/Solothurn','Schweiz/Basel-Stadt','Schweiz/Basel-Landschaft','Schweiz/Schaffhausen','Schweiz/Appenzell Ausserrhoden','Schweiz/Appenzell Innerrhoden','Schweiz/St. Gallen','Schweiz/Graubünden','Schweiz/Aargau','Schweiz/Thurgau','Schweiz/Tessin','Schweiz/Waadt','Schweiz/Wallis','Schweiz/Neuenburg','Schweiz/Genf','Schweiz/Jura','UK','Irland') DEFAULT NULL COMMENT 'Bundesland|Bundesland, in dem der Ort liegt', `location` varchar(255) NOT NULL COMMENT 'Ort|Gebiet, für das die Analyse gilt', `date` date NOT NULL COMMENT 'Datum|Datum der Analyse', `comment` text DEFAULT NULL COMMENT 'Kommentar|Kommentar zur Analyse', `link` varchar(255) DEFAULT NULL COMMENT 'Link|Link zur Anylyse', `hardness_level` set('1 (weich)','2 (mittelhart)','3 (hart)','4 (sehr hart)') DEFAULT NULL COMMENT 'Härtebereich|Härtebereich 1 (weich, bis 7,3°dH), 2 (mittelhart, 7,3 bis 14°dH), 3 (hart, 14-21,3°dH) oder 4 (sehr hart, über 21,3°dH)', `total_hardness_min` decimal(5,2) unsigned NOT NULL COMMENT 'Gesamthärte|Summe aller gelösten Erdalkalimetalle', `total_hardness_max` decimal(5,2) unsigned DEFAULT NULL, `total_hardness` decimal(5,2) unsigned GENERATED ALWAYS AS (case when `total_hardness_max` is null then `total_hardness_min` else round((`total_hardness_min` + `total_hardness_max`) / 2,1) end) STORED, `total_hardness_unit` set('°dH','°fH','mol/m³','mmol/l','mval/l','mg/l','ppm') NOT NULL DEFAULT '°dH', `carbonate_hardness_min` decimal(5,2) unsigned DEFAULT NULL COMMENT 'Karbonathärte|Karbonathärte oder temporäre Härte', `carbonate_hardness_max` decimal(5,2) unsigned DEFAULT NULL, `carbonate_hardness` decimal(5,2) unsigned GENERATED ALWAYS AS (case when `carbonate_hardness_max` is null then `carbonate_hardness_min` else round((`carbonate_hardness_min` + `carbonate_hardness_max`) / 2,1) end) STORED, `carbonate_hardness_unit` set('°dH','°fH','mol/m³','mmol/l','mval/l','mg/l','ppm') NOT NULL DEFAULT '°dH', `residual_alkalinity_min` decimal(5,2) DEFAULT NULL COMMENT 'Restalkalität|Restalkalität', `residual_alkalinity_max` decimal(5,2) DEFAULT NULL, `residual_alkalinity` decimal(5,2) GENERATED ALWAYS AS (case when `residual_alkalinity_max` is null then `residual_alkalinity_min` else round((`residual_alkalinity_min` + `residual_alkalinity_max`) / 2,2) end) STORED, `residual_alkalinity_unit` set('°dH','°fH','mol/m³','mmol/l','mval/l','mg/l','ppm') NOT NULL DEFAULT '°dH', `conductivity_min` decimal(4,0) unsigned DEFAULT NULL COMMENT 'Leitfähigkeit|Elektrische Leitfähigkeit bei 25°C', `conductivity_max` decimal(4,0) unsigned DEFAULT NULL, `conductivity` decimal(4,0) unsigned GENERATED ALWAYS AS (case when `conductivity_max` is null then `conductivity_min` else round((`conductivity_min` + `conductivity_max`) / 2,2) end) STORED, `conductivity_unit` set('µS/cm') NOT NULL DEFAULT 'µS/cm', `acid_capacity_min` decimal(5,2) unsigned DEFAULT NULL COMMENT 'Säurekapazität|Säurekapazität bis pH 4,3', `acid_capacity_max` decimal(5,2) unsigned DEFAULT NULL, `acid_capacity` decimal(5,2) unsigned GENERATED ALWAYS AS (case when `acid_capacity_max` is null then `acid_capacity_min` else round((`acid_capacity_min` + `acid_capacity_max`) / 2,2) end) STORED, `acid_capacity_unit` set('mol/m³','mmol/l','mval/l','mg/l','ppm') NOT NULL DEFAULT 'mmol/l', `calcium_min` decimal(5,2) unsigned DEFAULT NULL COMMENT 'Kalziumgehalt |Kalziumgehalt', `calcium_max` decimal(5,2) unsigned DEFAULT NULL, `calcium` decimal(5,2) unsigned GENERATED ALWAYS AS (case when `calcium_max` is null then `calcium_min` else round((`calcium_min` + `calcium_max`) / 2,2) end) STORED, `calcium_unit` set('mol/m³','mmol/l','mval/l','mg/l','ppm') NOT NULL DEFAULT 'mg/l', `potassium_min` decimal(5,2) unsigned DEFAULT NULL COMMENT 'Kaliumgehalt|Kaliumgehalt', `potassium_max` decimal(5,2) unsigned DEFAULT NULL, `potassium` decimal(5,2) unsigned GENERATED ALWAYS AS (case when `potassium_max` is null then `potassium_min` else round((`potassium_min` + `potassium_max`) / 2,2) end) STORED, `potassium_unit` set('mol/m³','mmol/l','mval/l','mg/l','ppm') NOT NULL DEFAULT 'mg/l', `magnesium_min` decimal(5,2) unsigned DEFAULT NULL COMMENT 'Magnesiumgehalt|Magnesiumgehalt', `magnesium_max` decimal(5,2) unsigned DEFAULT NULL, `magnesium` decimal(5,2) unsigned GENERATED ALWAYS AS (case when `magnesium_max` is null then `magnesium_min` else round((`magnesium_min` + `magnesium_max`) / 2,2) end) STORED, `magnesium_unit` set('mol/m³','mmol/l','mval/l','mg/l','ppm') NOT NULL DEFAULT 'mg/l', `sodium_min` decimal(5,2) unsigned DEFAULT NULL COMMENT 'Natriumgehalt|Natriumgehalt (Grenzwert: 200 mg/l)', `sodium_max` decimal(5,2) unsigned DEFAULT NULL, `sodium` decimal(5,2) unsigned GENERATED ALWAYS AS (case when `sodium_max` is null then `sodium_min` else round((`sodium_min` + `sodium_max`) / 2,2) end) STORED, `sodium_unit` set('mol/m³','mmol/l','mval/l','mg/l','ppm') NOT NULL DEFAULT 'mg/l', `nitrate_min` decimal(5,2) unsigned DEFAULT NULL COMMENT 'Nitratgehalt|Nitratgehalt (Grenzwert: 50 mg/l)', `nitrate_max` decimal(5,2) unsigned DEFAULT NULL, `nitrate` decimal(5,2) unsigned GENERATED ALWAYS AS (case when `nitrate_max` is null then `nitrate_min` else round((`nitrate_min` + `nitrate_max`) / 2,2) end) STORED, `nitrate_unit` set('mol/m³','mmol/l','mval/l','mg/l','ppm') NOT NULL DEFAULT 'mg/l', `chloride_min` decimal(5,2) unsigned DEFAULT NULL COMMENT 'Chloridgehalt|Chloridgehalt (Grenzwert: 250 mg/l)', `chloride_max` decimal(5,2) unsigned DEFAULT NULL, `chloride` decimal(5,2) unsigned GENERATED ALWAYS AS (case when `chloride_max` is null then `chloride_min` else round((`chloride_min` + `chloride_max`) / 2,2) end) STORED, `chloride_unit` set('mol/m³','mmol/l','mval/l','mg/l','ppm') NOT NULL DEFAULT 'mg/l', `sulfate_min` decimal(5,2) unsigned DEFAULT NULL COMMENT 'Sulfatgehalt|Sulfatgehalt (Grenzwert: 250 mg/l)', `sulfate_max` decimal(5,2) unsigned DEFAULT NULL, `sulfate` decimal(5,2) unsigned GENERATED ALWAYS AS (case when `sulfate_max` is null then `sulfate_min` else round((`sulfate_min` + `sulfate_max`) / 2,2) end) STORED, `sulfate_unit` set('mol/m³','mmol/l','mval/l','mg/l','ppm') NOT NULL DEFAULT 'mg/l', `sulfate_chloride_ratio` decimal(3,1) unsigned GENERATED ALWAYS AS (case when `sulfate_max` is null then `sulfate_min` else round((`sulfate_min` + `sulfate_max`) / 2,2) end / case when `chloride_max` is null then `chloride_min` else round((`chloride_min` + `chloride_max`) / 2,2) end) STORED COMMENT 'Sulfat-Chlorid-Verhältnis|Verhältnis von Sulfat zu Chlorid-Gehalt', `cation_min` decimal(5,2) unsigned DEFAULT NULL COMMENT 'Kationen|Summe der Kationen', `cation_max` decimal(5,2) unsigned DEFAULT NULL, `cation` decimal(5,2) unsigned GENERATED ALWAYS AS (case when `cation_max` is null then `cation_min` else round((`cation_min` + `cation_max`) / 2,2) end) STORED, `cation_unit` set('meq/l') DEFAULT 'meq/l', `anion_min` decimal(5,2) unsigned DEFAULT NULL COMMENT 'Anionen|Summe der Anionen', `anion_max` decimal(5,2) unsigned DEFAULT NULL, `anion` decimal(5,2) unsigned GENERATED ALWAYS AS (case when `anion_max` is null then `anion_min` else round((`anion_min` + `anion_max`) / 2,2) end) STORED, `anion_unit` set('meq/l') DEFAULT 'meq/l', `bicarbonate_min` decimal(5,2) DEFAULT NULL COMMENT 'Bikarbonate|Bikarbonatgehalt', `bicarbonate_max` decimal(5,2) DEFAULT NULL, `bicarbonate` decimal(5,2) GENERATED ALWAYS AS (case when `bicarbonate_max` is null then `bicarbonate_min` else round((`bicarbonate_min` + `bicarbonate_max`) / 2,1) end) STORED, `bicarbonate_unit` set('°dH','°fH','mol/m³','mmol/l','mval/l','mg/l','ppm') NOT NULL DEFAULT 'mg/l', PRIMARY KEY (`id`), KEY `index_checked` (`checked`) ) ENGINE=InnoDB AUTO_INCREMENT=155 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci COMMENT='Wasseranalysen'