--- a/flys-backend/doc/schema/oracle-minfo.sql	Wed Mar 06 14:14:15 2013 +0100
+++ b/flys-backend/doc/schema/oracle-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          NUMBER(38,0) NOT NULL,
-    name        VARCHAR(16)  NOT NULL,
-    description VARCHAR(255),
+    name        VARCHAR(65)  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;
@@ -188,11 +192,9 @@
 
 CREATE TABLE flow_velocity_model (
     id                  NUMBER(38,0) NOT NULL,
-    river_id            NUMBER(38,0) NOT NULL,
     discharge_zone_id   NUMBER(38,0) 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)
 );
 
@@ -246,9 +248,7 @@
     name    VARCHAR(64)    NOT NULL,
     lower   NUMBER(38,3),
     upper   NUMBER(38,3),
-    unit_id NUMBER(38,0),
     PRIMARY KEY (id),
-    CONSTRAINT fk_gf_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
 );
 
 
@@ -281,75 +281,25 @@
 );
 
 
-CREATE SEQUENCE WATERLEVEL_ID_SEQ;
-
-CREATE TABLE waterlevel (
-    id          NUMBER(38,0) NOT NULL,
-    river_id    NUMBER(38,0) NOT NULL,
-    unit_id     NUMBER(38,0) 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              NUMBER(38,0) NOT NULL,
-    waterlevel_id   NUMBER(38,0) NOT NULL,
-    q               NUMBER(38,2) 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                      NUMBER(38,0) NOT NULL,
-    waterlevel_q_range_id   NUMBER(38,0) NOT NULL,
-    station                 NUMBER(38,3) NOT NULL,
-    w                       NUMBER(38,2) 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          NUMBER(38,0) NOT NULL,
-    river_id    NUMBER(38,0) NOT NULL,
-    unit_id     NUMBER(38,0) 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              NUMBER(38,0) NOT NULL,
-    difference_id   NUMBER(38,0) 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          NUMBER(38,0) NOT NULL,
-    column_id   NUMBER(38,0) NOT NULL,
-    station     NUMBER(38,3) NOT NULL,
-    value       NUMBER(38,2) NOT NULL,
-    PRIMARY KEY (id),
-    CONSTRAINT fk_wdv_column_id FOREIGN KEY (column_id) REFERENCES waterlevel_difference_column (id)
+CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ;
+CREATE TABLE measurement_station (
+    id                       NUMBER(38)   NOT NULL,
+    name                     VARCHAR(256) NOT NULL,
+    river_id                 NUMBER(38)   NOT NULL,
+    station                  NUMBER(38,3) NOT NULL,
+    range_id                 NUMBER(38)   NOT NULL,
+    measurement_type         VARCHAR(64)  NOT NULL,
+    riverside                VARCHAR(16),
+    reference_gauge_id       NUMBER(38),
+    observation_timerange_id NUMBER(38),
+    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)
 );