--- a/flys-backend/doc/schema/oracle-minfo.sql	Wed Apr 03 15:59:01 2013 +0200
+++ b/flys-backend/doc/schema/oracle-minfo.sql	Wed Apr 03 16:00:21 2013 +0200
@@ -40,13 +40,13 @@
 CREATE TABLE bed_height_single (
     id                      NUMBER(38,0) NOT NULL,
     river_id                NUMBER(38,0) NOT NULL,
-    year                    NUMBER(38,0) NOT NULL,
-    sounding_width          NUMBER(38,0) NOT NULL,
+    year                    NUMBER(38,0),
+    sounding_width          NUMBER(38,0),
     type_id                 NUMBER(38,0) NOT NULL,
     location_system_id      NUMBER(38,0) NOT NULL,
     cur_elevation_model_id  NUMBER(38,0) NOT NULL,
     old_elevation_model_id  NUMBER(38,0),
-    range_id                NUMBER(38,0) NOT NULL,
+    range_id                NUMBER(38,0),
     evaluation_by           VARCHAR(255),
     description             VARCHAR(255),
     PRIMARY KEY(id),
@@ -69,7 +69,7 @@
     -- type_id                 NUMBER(38,0) NOT NULL,
     cur_elevation_model_id  NUMBER(38,0) NOT NULL,
     old_elevation_model_id  NUMBER(38,0),
-    range_id                NUMBER(38,0) NOT NULL,
+    range_id                NUMBER(38,0),
     evaluation_by           VARCHAR(255),
     description             VARCHAR(255),
     PRIMARY KEY(id),
@@ -114,9 +114,7 @@
     id      NUMBER(38,0) NOT NULL,
     lower   NUMBER(38,2) NOT NULL,
     upper   NUMBER(38,2) NOT NULL,
-    unit_id NUMBER(38,0) NOT NULL,
-    PRIMARY KEY(id),
-    CONSTRAINT fk_depths_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
+    PRIMARY KEY(id)
 );
 
 
@@ -126,12 +124,10 @@
     id          NUMBER(38,0) NOT NULL,
     river_id    NUMBER(38,0) NOT NULL,
     depth_id    NUMBER(38,0) NOT NULL,
-    unit_id     NUMBER(38,0) NOT NULL,
     description VARCHAR(256),
     PRIMARY KEY(id),
     CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
-    CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id),
-    CONSTRAINT fk_sd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
+    CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id)
 );
 
 
@@ -141,6 +137,7 @@
     id                  NUMBER(38,0) NOT NULL,
     sediment_density_id NUMBER(38,0) NOT NULL,
     station             NUMBER(38,2) NOT NULL,
+    shore_offset	NUMBER(38,2),
     density             NUMBER(38,2) NOT NULL,
     description         VARCHAR(256),
     year                NUMBER(38,0),
@@ -287,10 +284,12 @@
     name                     VARCHAR(256) NOT NULL,
     river_id                 NUMBER(38)   NOT NULL,
     station                  NUMBER(38,3) NOT NULL,
-    range_id                 NUMBER(38)   NOT NULL,
+    range_id                 NUMBER(38),
     measurement_type         VARCHAR(64)  NOT NULL,
     riverside                VARCHAR(16),
     reference_gauge_id       NUMBER(38),
+    -- store name of reference gauges here too, as not all are in gauges
+    reference_gauge_name     VARCHAR(64),
     observation_timerange_id NUMBER(38),
     operator                 VARCHAR(64),
     description              VARCHAR(512),
@@ -298,8 +297,7 @@
     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)
+    CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id)
 );
 
 
@@ -319,14 +317,19 @@
 CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ;
 
 CREATE TABLE sq_relation_value (
-    id             NUMBER(38,0) NOT NULL,
-    sq_relation_id NUMBER(38,0) NOT NULL,
-    parameter      VARCHAR(16)  NOT NULL,
-    fraction       VARCHAR(32)  NOT NULL,
-    function       VARCHAR(32)  NOT NULL,
-    km             NUMBER(38,3) NOT NULL,
-    a              NUMBER(38, 3) NOT NULL,
-    b              NUMBER(38,3) NOT NULL,
+    id                       NUMBER(38,0) NOT NULL,
+    sq_relation_id           NUMBER(38,0) NOT NULL,
+    measurement_station_id   NUMBER(38,0) NOT NULL,
+    parameter                VARCHAR(1) NOT NULL,
+    a                        NUMBER(38,20) NOT NULL,
+    b                        NUMBER(38,20) NOT NULL,
+    qmax                     NUMBER(38,20) NOT NULL,
+    rsq                      NUMBER(38,3),
+    ntot                     NUMBER(38,0),
+    noutl                    NUMBER(38,0),
+    cferguson                NUMBER(38,20),
+    cduan                    NUMBER(38,20),
     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),
+    CONSTRAINT fk_mstation_id FOREIGN KEY (measurement_station_id) REFERENCES measurement_station(id)
 );