diff flys-backend/doc/schema/sqlite.sql @ 163:4a3b2912a0cd

Factorized time intervals out into a separated table. flys-backend/trunk@1432 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 09 Mar 2011 09:41:01 +0000
parents 119048655872
children 324e12ab7a05
line wrap: on
line diff
--- a/flys-backend/doc/schema/sqlite.sql	Tue Feb 22 10:34:53 2011 +0000
+++ b/flys-backend/doc/schema/sqlite.sql	Wed Mar 09 09:41:01 2011 +0000
@@ -66,32 +66,36 @@
     UNIQUE (name, type_id)
 );
 
+-- Table for time intervals
+CREATE TABLE time_intervals (
+    id         INTEGER PRIMARY KEY NOT NULL,
+    start_time TIMESTAMP NOT NULL,
+    stop_time  TIMESTAMP,
+    CHECK (start_time <= stop_time)
+);
+
 -- Stammdaten
 CREATE TABLE main_values (
-    id             INTEGER PRIMARY KEY NOT NULL,
-    gauge_id       INTEGER NOT NULL REFERENCES gauges(id),
-    named_value_id INTEGER NOT NULL REFERENCES named_main_values(id),
-    value          NUMERIC NOT NULL,
+    id               INTEGER PRIMARY KEY NOT NULL,
+    gauge_id         INTEGER NOT NULL REFERENCES gauges(id),
+    named_value_id   INTEGER NOT NULL REFERENCES named_main_values(id),
+    value            NUMERIC NOT NULL,
 
-    start_time     TIMESTAMP DEFAULT NULL,
-    stop_time      TIMESTAMP DEFAULT NULL,
+    time_interval_id INTEGER REFERENCES time_intervals(id),
 
     -- TODO: better checks
-    CHECK (start_time <= stop_time),
-    UNIQUE (gauge_id, named_value_id, start_time, stop_time)
+    UNIQUE (gauge_id, named_value_id, time_interval_id)
 );
 
 -- Abflusstafeln
 CREATE TABLE discharge_table (
-    id             INTEGER PRIMARY KEY NOT NULL,
-    gauge_id       INTEGER NOT NULL REFERENCES gauges(id),
+    id               INTEGER PRIMARY KEY NOT NULL,
+    gauge_id         INTEGER NOT NULL REFERENCES gauges(id),
 
-    start_time     TIMESTAMP DEFAULT NULL,
-    stop_time      TIMESTAMP DEFAULT NULL,
+    time_interval_id INTEGER REFERENCES time_intervals(id),
 
     -- TODO: better checks
-    CHECK (start_time <= stop_time),
-    UNIQUE (gauge_id, start_time, stop_time)
+    UNIQUE (gauge_id, time_interval_id)
 );
 
 -- Values of the Abflusstafeln
@@ -120,10 +124,8 @@
     name           VARCHAR(256) NOT NULL,
     description    VARCHAR,
 
-    start_time     TIMESTAMP DEFAULT NULL,
-    stop_time      TIMESTAMP DEFAULT NULL,
+    time_interval_id INTEGER REFERENCES time_intervals(id),
 
-    CHECK (start_time <= stop_time),
     UNIQUE (wst_id, name)
 );
 

http://dive4elements.wald.intevation.org