diff flys-backend/doc/schema/postgresql.sql @ 1209:c12b5fbd33e8

schema: Added structures for HYKs "Hydraulische Kenngroessen" flys-backend/trunk@2335 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Fri, 15 Jul 2011 09:36:23 +0000
parents 5f1506fc7636
children dd948530fd57
line wrap: on
line diff
--- a/flys-backend/doc/schema/postgresql.sql	Wed Jul 13 12:38:11 2011 +0000
+++ b/flys-backend/doc/schema/postgresql.sql	Fri Jul 15 09:36:23 2011 +0000
@@ -291,4 +291,53 @@
     UNIQUE (cross_section_line_id, col_pos)
 );
 
+-- Hydraulische Kenngroessen
+
+CREATE SEQUENCE HYKS_ID_SEQ;
+
+CREATE TABLE hyks (
+    id          int PRIMARY KEY NOT NULL,
+    river_id    int             NOT NULL,
+    description VARCHAR(256)    NOT NULL
+);
+
+CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
+
+CREATE TABLE hyk_entries (
+    id     int PRIMARY KEY NOT NULL,
+    hyk_id int             NOT NULL REFERENCES hyks(id),
+    km     NUMERIC         NOT NULL,
+    UNIQUE (hyk_id, km)
+);
+
+CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;
+
+CREATE TABLE hyk_formations (
+    id            int PRIMARY KEY NOT NULL,
+    formation_num int             NOT NULL DEFAULT 0,
+    hyk_entry_id  int             NOT NULL REFERENCES hyk_entries(id),
+    top           NUMERIC         NOT NULL,
+    bottom        NUMERIC         NOT NULL,
+    UNIQUE (hyk_entry_id, formation_num)
+);
+
+CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ;
+
+CREATE TABLE hyk_flow_zone_types (
+    id          int PRIMARY KEY NOT NULL,
+    name        VARCHAR(50)     NOT NULL UNIQUE,
+    description VARCHAR(256)
+);
+
+CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ;
+
+CREATE TABLE hyk_flow_zones (
+    id           int PRIMARY KEY NOT NULL,
+    formation_id int             NOT NULL REFERENCES hyk_formations(id),
+    type_id      int             NOT NULL REFERENCES hyk_flow_zone_types(id),
+    a            NUMERIC         NOT NULL,
+    b            NUMERIC         NOT NULL,
+    CHECK (a <= b)
+);
+
 COMMIT;

http://dive4elements.wald.intevation.org