changeset 7461:5b5a2cc8210f

Schema change: add lookup table for sediment yield kinds to have its values documented.
author Tom Gottfried <tom@intevation.de>
date Tue, 29 Oct 2013 19:59:59 +0100 (2013-10-29)
parents 2733b4aae74b
children 5cddf115b27b
files backend/doc/schema/oracle-minfo.sql backend/doc/schema/postgresql-minfo.sql
diffstat 2 files changed, 17 insertions(+), 0 deletions(-) [+]
line wrap: on
line diff
--- a/backend/doc/schema/oracle-minfo.sql	Tue Oct 29 19:46:41 2013 +0100
+++ b/backend/doc/schema/oracle-minfo.sql	Tue Oct 29 19:59:59 2013 +0100
@@ -250,6 +250,13 @@
     PRIMARY KEY (id)
 );
 
+--lookup table for sediment yield kinds
+CREATE TABLE sediment_yield_kinds (
+    id 	     int PRIMARY KEY NOT NULL,
+    kind     VARCHAR(64) NOT NULL
+);
+INSERT INTO sediment_yield_kinds (id, kind) VALUES (0, 'non-official');
+INSERT INTO sediment_yield_kinds (id, kind) VALUES (1, 'official');
 
 CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ;
 
@@ -263,6 +270,7 @@
     kind                NUMBER(38,0),
     PRIMARY KEY (id),
     CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
+    CONSTRAINT fk_sy_kind_id FOREIGN KEY (kind) REFERENCES sediment_yield_kinds(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)
--- a/backend/doc/schema/postgresql-minfo.sql	Tue Oct 29 19:46:41 2013 +0100
+++ b/backend/doc/schema/postgresql-minfo.sql	Tue Oct 29 19:59:59 2013 +0100
@@ -251,6 +251,14 @@
 );
 
 
+--lookup table for sediment yield kinds
+CREATE TABLE sediment_yield_kinds (
+    id 	     int PRIMARY KEY NOT NULL,
+    kind     VARCHAR(64) NOT NULL
+);
+INSERT INTO sediment_yield_kinds (id, kind) VALUES (0, 'non-official');
+INSERT INTO sediment_yield_kinds (id, kind) VALUES (1, 'official');
+
 CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ;
 
 CREATE TABLE sediment_yield (
@@ -263,6 +271,7 @@
     kind                int,
     PRIMARY KEY (id),
     CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
+    CONSTRAINT fk_sy_kind_id FOREIGN KEY (kind) REFERENCES sediment_yield_kinds(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)

http://dive4elements.wald.intevation.org