--- a/flys-backend/doc/schema/postgresql-minfo.sql	Wed Mar 06 14:14:15 2013 +0100
+++ b/flys-backend/doc/schema/postgresql-minfo.sql	Fri Mar 22 11:25:54 2013 +0100
@@ -20,15 +20,19 @@
     CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id)
 );
 
-CREATE SEQUENCE BED_HEIGHT_TYPE_SEQ;
 
+-- lookup table for bedheight types
 CREATE TABLE bed_height_type (
     id          int NOT NULL,
-    name        VARCHAR(16)  NOT NULL,
-    description VARCHAR(255),
+    name        VARCHAR(64) NOT NULL,
     PRIMARY KEY(id)
 );
-
+INSERT INTO bed_height_type VALUES (1, 'Querprofile');
+INSERT INTO bed_height_type VALUES (2, 'Flächenpeilung');
+INSERT INTO bed_height_type VALUES (3, 'Flächen- u. Querprofilpeilungen');
+INSERT INTO bed_height_type VALUES (4, 'DGM');
+INSERT INTO bed_height_type VALUES (5, 'TIN');
+INSERT INTO bed_height_type VALUES (6, 'Modell');
 
 
 CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;
@@ -46,12 +50,12 @@
     evaluation_by           VARCHAR(255),
     description             VARCHAR(255),
     PRIMARY KEY(id),
-    CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
+    CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
     CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id),
     CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id),
     CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
     CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
-    CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id)
+    CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE
 );
 
 
@@ -72,7 +76,7 @@
     CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id),
     CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
     CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
-    CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id)
+    CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE
 );
 
 
@@ -88,7 +92,7 @@
     sounding_width          NUMERIC,
     width                   NUMERIC,
     PRIMARY KEY(id),
-    CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id)
+    CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id) ON DELETE CASCADE
 );
 
 
@@ -100,7 +104,7 @@
     station                 NUMERIC NOT NULL,
     height                  NUMERIC,
     PRIMARY KEY(id),
-    CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id)
+    CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id) ON DELETE CASCADE
 );
 
 
@@ -125,7 +129,7 @@
     unit_id     int NOT NULL,
     description VARCHAR(256),
     PRIMARY KEY(id),
-    CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
+    CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
     CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id),
     CONSTRAINT fk_sd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
 );
@@ -141,7 +145,7 @@
     description         VARCHAR(256),
     year                int,
     PRIMARY KEY(id),
-    CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id)
+    CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) ON DELETE CASCADE
 );
 
 
@@ -152,7 +156,7 @@
     river_id    int NOT NULL,
     unit_id     int NOT NULL,
     PRIMARY KEY(id),
-    CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id),
+    CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id) ON DELETE CASCADE,
     CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id)
 );
 
@@ -166,7 +170,7 @@
     width                   NUMERIC NOT NULL,
     description             VARCHAR(256),
     PRIMARY KEY(id),
-    CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id)
+    CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) ON DELETE CASCADE
 );
 
 
@@ -180,7 +184,7 @@
     lower_discharge         VARCHAR(16)  NOT NULL,
     upper_discharge         VARCHAR(16),
     PRIMARY KEY(id),
-    CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id)
+    CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE
 );
 
 
@@ -188,12 +192,10 @@
 
 CREATE TABLE flow_velocity_model (
     id                  int NOT NULL,
-    river_id            int NOT NULL,
     discharge_zone_id   int NOT NULL,
     description         VARCHAR(256),
     PRIMARY KEY (id),
-    CONSTRAINT fk_fvm_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
-    CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id)
+    CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id) ON DELETE CASCADE
 );
 
 
@@ -208,7 +210,7 @@
     main_channel            NUMERIC NOT NULL,
     shear_stress            NUMERIC NOT NULL,
     PRIMARY KEY(id),
-    CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id)
+    CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id) ON DELETE CASCADE
 );
 
 
@@ -220,7 +222,7 @@
     river_id    int NOT NULL,
     description VARCHAR(256),
     PRIMARY KEY (id),
-    CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id)
+    CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE
 );
 
 CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ;
@@ -235,7 +237,7 @@
     v               NUMERIC NOT NULL,
     description     VARCHAR(256),
     PRIMARY KEY (id),
-    CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id)
+    CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) ON DELETE CASCADE
 );
 
 
@@ -246,9 +248,7 @@
     name    VARCHAR(64)    NOT NULL,
     lower   NUMERIC,
     upper   NUMERIC,
-    unit_id int,
     PRIMARY KEY (id),
-    CONSTRAINT fk_gf_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
 );
 
 
@@ -262,7 +262,7 @@
     time_interval_id    int NOT NULL,
     description         VARCHAR(256),
     PRIMARY KEY (id),
-    CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
+    CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
     CONSTRAINT fk_sy_grain_fraction_id FOREIGN KEY (grain_fraction_id) REFERENCES grain_fraction(id),
     CONSTRAINT fk_sy_unit_id FOREIGN KEY (unit_id) REFERENCES units(id),
     CONSTRAINT fk_sy_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
@@ -277,101 +277,29 @@
     station             NUMERIC NOT NULL,
     value               NUMERIC NOT NULL,
     PRIMARY KEY (id),
-    CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id)
-);
-
-
-CREATE SEQUENCE WATERLEVEL_ID_SEQ;
-
-CREATE TABLE waterlevel (
-    id          int NOT NULL,
-    river_id    int NOT NULL,
-    unit_id     int NOT NULL,
-    description VARCHAR(256),
-    PRIMARY KEY (id),
-    CONSTRAINT fk_w_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
-    CONSTRAINT fk_w_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
-);
-
-
-CREATE SEQUENCE WATERLEVEL_Q_RANGES_ID_SEQ;
-
-CREATE TABLE waterlevel_q_range (
-    id              int NOT NULL,
-    waterlevel_id   int NOT NULL,
-    q               NUMERIC NOT NULL,
-    PRIMARY KEY (id),
-    CONSTRAINT fk_wqr_waterlevel_id FOREIGN KEY (waterlevel_id) REFERENCES waterlevel(id)
-);
-
-
-CREATE SEQUENCE WATERLEVEL_VALUES_ID_SEQ;
-
-CREATE TABLE waterlevel_values (
-    id                      int NOT NULL,
-    waterlevel_q_range_id   int NOT NULL,
-    station                 NUMERIC NOT NULL,
-    w                       NUMERIC NOT NULL,
-    PRIMARY KEY (id),
-    CONSTRAINT fk_wv_waterlevel_q_range_id FOREIGN KEY (waterlevel_q_range_id) REFERENCES waterlevel_q_range(id)
-);
-
-
-CREATE SEQUENCE WATERLEVEL_DIFFERENCE_ID_SEQ;
-
-CREATE TABLE waterlevel_difference (
-    id          int NOT NULL,
-    river_id    int NOT NULL,
-    unit_id     int NOT NULL,
-    description VARCHAR(256),
-    PRIMARY KEY (id),
-    CONSTRAINT fk_wd_river_id FOREIGN KEY (river_id) REFERENCES rivers (id),
-    CONSTRAINT fk_wd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
-);
-
-
-CREATE SEQUENCE WATERLEVEL_DIFF_COLUMN_ID_SEQ;
-
-CREATE TABLE waterlevel_difference_column (
-    id              int NOT NULL,
-    difference_id   int NOT NULL,
-    description     VARCHAR(256),
-    PRIMARY KEY (id),
-    CONSTRAINT fk_wdc_difference_id FOREIGN KEY (difference_id) REFERENCES waterlevel_difference (id)
-);
-
-
-CREATE SEQUENCE WATERLEVEL_DIFF_VALUES_ID_SEQ;
-
-CREATE TABLE waterlevel_difference_values (
-    id          int NOT NULL,
-    column_id   int NOT NULL,
-    station     NUMERIC NOT NULL,
-    value       NUMERIC NOT NULL,
-    PRIMARY KEY (id),
-    CONSTRAINT fk_wdv_column_id FOREIGN KEY (column_id) REFERENCES waterlevel_difference_column (id)
+    CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id) ON DELETE CASCADE
 );
 
 
 CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ;
 CREATE TABLE measurement_station (
-	id			             int          NOT NULL,
-	name		             VARCHAR(256) NOT NULL,
-	river_id                 int          NOT NULL,
-	station                  NUMERIC      NOT NULL,
-	range_id                 int          NOT NULL,
-	measurement_type         VARCHAR(64)  NOT NULL,
-	riverside                VARCHAR(16),
-	reference_gauge_id       int,
-	observation_timerange_id int,
-	operator				 VARCHAR(64),
-	comment					 VARCHAR(512),
-	PRIMARY KEY (id),
-	CONSTRAINT fk_ms_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
-	CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id) REFERENCES ranges(id),
-	CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) REFERENCES gauges(id),
-	CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id),
-	UNIQUE (river_id, station)
+    id                       int          NOT NULL,
+    name                     VARCHAR(256) NOT NULL,
+    river_id                 int          NOT NULL,
+    station                  NUMERIC      NOT NULL,
+    range_id                 int          NOT NULL,
+    measurement_type         VARCHAR(64)  NOT NULL,
+    riverside                VARCHAR(16),
+    reference_gauge_id       int,
+    observation_timerange_id int,
+    operator                 VARCHAR(64),
+    description              VARCHAR(512),
+    PRIMARY KEY (id),
+    CONSTRAINT fk_ms_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
+    CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE,
+    CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) REFERENCES gauges(id) ON DELETE CASCADE,
+    CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id),
+    UNIQUE (river_id, station)
 );
 
 
@@ -383,7 +311,7 @@
     time_interval_id int NOT NULL,
     description      VARCHAR(256),
     PRIMARY KEY (id),
-    CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
+    CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
     CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
 );
 
@@ -400,6 +328,6 @@
     a              NUMERIC NOT NULL,
     b              NUMERIC NOT NULL,
     PRIMARY KEY (id),
-    CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id)
+    CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id) ON DELETE CASCADE
 );
 COMMIT;