--- a/wamos.sql	Thu May 24 11:51:14 2018 +0200
+++ b/wamos.sql	Thu May 24 14:38:27 2018 +0200
@@ -149,6 +149,10 @@
        level_of_service smallint PRIMARY KEY
        );
 
+CREATE TABLE measure_types (
+       measure_type varchar PRIMARY KEY
+       );
+
 CREATE TABLE fairway_availability (
        bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
        surdat date NOT NULL,
@@ -190,8 +194,22 @@
 CREATE TABLE effective_fairway_availability (
        bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
        surdat date NOT NULL,
-       FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability
-       -- TODO
+       FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability,
+       measure_date timestamp NOT NULL,
+       level_of_service smallint NOT NULL REFERENCES levels_of_service,
+       PRIMARY KEY (bottleneck_id, surdat, measure_date, level_of_service),
+       available_depth_value smallint,
+       available_width_value smallint,
+       water_level_value smallint,
+       CHECK(COALESCE(available_depth_value, available_width_value,
+           water_level_value) IS NOT NULL),
+       measure_type varchar NOT NULL REFERENCES measure_types,
+       source_organization varchar NOT NULL,
+       forecast_generation_time timestamp,
+       CHECK(measure_type <> 'forecasted'
+             OR forecast_generation_time IS NOT NULL),
+       value_lifetime timestamp,
+       CHECK(measure_type = 'minimum guaranteed' OR value_lifetime IS NOT NULL)
        );
 
 COMMIT;