# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1299663661 0
# Node ID 4a3b2912a0cd0d1e3ba1af556d542f71700bb798
# Parent  80669241956c1a8184d8c2d086e192949bdfd1b2
Factorized time intervals out into a separated table.


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

diff -r 80669241956c -r 4a3b2912a0cd flys-backend/ChangeLog
--- a/flys-backend/ChangeLog	Tue Feb 22 10:34:53 2011 +0000
+++ b/flys-backend/ChangeLog	Wed Mar 09 09:41:01 2011 +0000
@@ -1,3 +1,8 @@
+2011-03-09	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
+
+	* doc/schema/sqlite.sql: Factorized time intervals out into
+	  a separated table.
+
 2011-01-22	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
 
 	* contrib/import-kms.py, contrib/import-gew.py: Initial scripts
diff -r 80669241956c -r 4a3b2912a0cd flys-backend/doc/schema/sqlite.sql
--- 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)
 );