# HG changeset patch
# User Ingo Weinzierl <ingo.weinzierl@intevation.de>
# Date 1334751142 0
# Node ID daba3f895eb3503dfe1916dad0c2a383ab260275
# Parent  5b54a648f7028452d73ee8893b759cdeae548d33
Improved the db schema to store sediment yield values.

flys-backend/trunk@4266 c6561f87-3c4e-4783-a992-168aeb5c3f6f

diff -r 5b54a648f702 -r daba3f895eb3 flys-backend/ChangeLog
--- a/flys-backend/ChangeLog	Wed Apr 18 08:54:55 2012 +0000
+++ b/flys-backend/ChangeLog	Wed Apr 18 12:12:22 2012 +0000
@@ -1,3 +1,9 @@
+2012-04-18  Ingo Weinzierl <ingo@intevation.de>
+
+	* doc/schema/oracle-minfo.sql,
+	  doc/schema/oracle-drop-minfo.sql: Added relations for storing sediment
+	  yield values.
+
 2012-04-18  Ingo Weinzierl <ingo@intevation.de>
 
 	* doc/schema/oracle-minfo.sql,
diff -r 5b54a648f702 -r daba3f895eb3 flys-backend/doc/schema/oracle-drop-minfo.sql
--- a/flys-backend/doc/schema/oracle-drop-minfo.sql	Wed Apr 18 08:54:55 2012 +0000
+++ b/flys-backend/doc/schema/oracle-drop-minfo.sql	Wed Apr 18 12:12:22 2012 +0000
@@ -25,6 +25,11 @@
 ALTER TABLE discharge_zone DROP CONSTRAINT fk_dz_river_id;
 ALTER TABLE flow_velocity_measurements DROP CONSTRAINT fk_fvm_rivers_id;
 ALTER TABLE flow_velocity_measure_values DROP CONSTRAINT fk_fvmv_measurements_id;
+ALTER TABLE grain_fraction DROP CONSTRAINT fk_gf_unit_id;
+ALTER TABLE sediment_yield DROP CONSTRAINT fk_sy_grain_fraction_id;
+ALTER TABLE sediment_yield DROP CONSTRAINT fk_sy_unit_id;
+ALTER TABLE sediment_yield DROP CONSTRAINT fk_sy_time_interval_id;
+ALTER TABLE sediment_yield_values DROP CONSTRAINT fk_syv_sediment_yield_id;
 
 DROP TABLE bed_height_type;
 DROP TABLE location_system;
@@ -43,6 +48,9 @@
 DROP TABLE flow_velocity_model_values;
 DROP TABLE flow_velocity_measurements;
 DROP TABLE flow_velocity_measure_values;
+DROP TABLE grain_fraction;
+DROP TABLE sediment_yield;
+DROP TABLE sediment_yield_values;
 
 DROP SEQUENCE BED_HEIGHT_TYPE_SEQ;
 DROP SEQUENCE LOCATION_SYSTEM_SEQ;
@@ -61,4 +69,7 @@
 DROP SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ;
 DROP SEQUENCE FV_MEASURE_ID_SEQ;
 DROP SEQUENCE FV_MEASURE_VALUES_ID_SEQ;
+DROP SEQUENCE GRAIN_FRACTION_ID_SEQ;
+DROP SEQUENCE SEDIMENT_YIELD_ID_SEQ;
+DROP SEQUENCE SEDIMENT_YIELD_VALUES_ID_SEQ;
 
diff -r 5b54a648f702 -r daba3f895eb3 flys-backend/doc/schema/oracle-minfo.sql
--- a/flys-backend/doc/schema/oracle-minfo.sql	Wed Apr 18 08:54:55 2012 +0000
+++ b/flys-backend/doc/schema/oracle-minfo.sql	Wed Apr 18 12:12:22 2012 +0000
@@ -236,3 +236,42 @@
     PRIMARY KEY (id),
     CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id)
 );
+
+
+CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ;
+
+CREATE TABLE grain_fraction (
+    id      NUMBER(38,0)   NOT NULL,
+    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)
+);
+
+
+CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ;
+
+CREATE TABLE sediment_yield (
+    id                  NUMBER(38,0) NOT NULL,
+    grain_fraction_id   NUMBER(38,0),
+    unit_id             NUMBER(38,0) NOT NULL,
+    time_interval_id    NUMBER(38,0) NOT NULL,
+    PRIMARY KEY (id),
+    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)
+);
+
+
+CREATE SEQUENCE SEDIMENT_YIELD_VALUES_ID_SEQ;
+
+CREATE TABLE sediment_yield_values (
+    id                  NUMBER(38,0) NOT NULL,
+    sediment_yield_id   NUMBER(38,0) NOT NULL,
+    station             NUMBER(38,3) NOT NULL,
+    value               NUMBER(38,3) NOT NULL,
+    PRIMARY KEY (id),
+    CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id)
+);