--- a/backend/doc/schema/postgresql.sql	Wed Mar 07 17:36:04 2018 +0100
+++ b/backend/doc/schema/postgresql.sql	Tue Mar 13 09:42:41 2018 +0100
@@ -3,7 +3,7 @@
 CREATE SEQUENCE UNITS_ID_SEQ;
 
 CREATE TABLE units (
-    id   int PRIMARY KEY NOT NULL,
+    id   NUMERIC(9,0) PRIMARY KEY NOT NULL,
     name VARCHAR(32)     NOT NULL UNIQUE
 );
 
@@ -11,7 +11,7 @@
 -- Lookup table for optional matching with differing river names in SedDB
 -- Add name here and set rivers.seddb_name_id to id
 CREATE TABLE seddb_name (
-    id       int PRIMARY KEY NOT NULL,
+    id       NUMERIC(9,0) PRIMARY KEY NOT NULL,
     name     VARCHAR(256) NOT NULL
 );
 
@@ -19,13 +19,13 @@
 CREATE SEQUENCE RIVERS_ID_SEQ;
 
 CREATE TABLE rivers (
-    id              int PRIMARY KEY NOT NULL,
+    id              NUMERIC(9,0) PRIMARY KEY NOT NULL,
     model_uuid      CHAR(36)        UNIQUE,
-    official_number int8,
+    official_number NUMERIC(12,0),
     name            VARCHAR(256)    NOT NULL UNIQUE,
-    km_up           int DEFAULT 0   NOT NULL,
-    wst_unit_id int                 NOT NULL REFERENCES units(id),
-    seddb_name_id   int REFERENCES seddb_name(id),
+    km_up           NUMERIC(1,0) DEFAULT 0   NOT NULL,
+    wst_unit_id NUMERIC(9,0)                 NOT NULL REFERENCES units(id),
+    seddb_name_id   NUMERIC(9,0) REFERENCES seddb_name(id),
     CHECK(km_up IN(0,1))
 );
 
@@ -33,18 +33,19 @@
 CREATE SEQUENCE ATTRIBUTES_ID_SEQ;
 
 CREATE TABLE attributes (
-    id    int PRIMARY KEY NOT NULL,
+    id    NUMERIC(9,0) PRIMARY KEY NOT NULL,
     value VARCHAR(256)    NOT NULL UNIQUE
 );
 
 -- segments from/to at a river
 CREATE SEQUENCE RANGES_ID_SEQ;
 
+--FIXME: make precision and scale of a and b columns equal with the km columns of the other tables
 CREATE TABLE ranges (
-    id       int PRIMARY KEY NOT NULL,
-    river_id int             NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
-    a        NUMERIC         NOT NULL,
-    b        NUMERIC,
+    id       NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    river_id NUMERIC(9,0)             NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
+    a        NUMERIC(14,10)         NOT NULL,
+    b        NUMERIC(14,10),
     UNIQUE (river_id, a, b),
     CHECK (a < b)
 );
@@ -54,7 +55,7 @@
 CREATE SEQUENCE POSITIONS_ID_SEQ;
 
 CREATE TABLE positions (
-    id    int PRIMARY KEY NOT NULL,
+    id    NUMERIC(9,0) PRIMARY KEY NOT NULL,
     value VARCHAR(256)    NOT NULL UNIQUE
 );
 
@@ -62,16 +63,16 @@
 CREATE SEQUENCE EDGES_ID_SEQ;
 
 CREATE TABLE edges (
-    id     int PRIMARY KEY NOT NULL,
-    top    NUMERIC,
-    bottom NUMERIC
+    id     NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    top    NUMERIC(6,2),
+    bottom NUMERIC(6,2)
 );
 
 -- Types of annotatations (Hafen, Bruecke, Zufluss, ...)
 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ;
 
 CREATE TABLE annotation_types (
-    id    int PRIMARY KEY NOT NULL,
+    id    NUMERIC(9,0) PRIMARY KEY NOT NULL,
     name  VARCHAR(256)    NOT NULL UNIQUE
 );
 
@@ -80,30 +81,31 @@
 CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
 
 CREATE TABLE annotations (
-    id           int PRIMARY KEY NOT NULL,
-    range_id     int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
-    attribute_id int NOT NULL REFERENCES attributes(id),
-    position_id  int REFERENCES positions(id),
-    edge_id      int REFERENCES edges(id),
-    type_id      int REFERENCES annotation_types(id)
+    id           NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    range_id     NUMERIC(9,0) NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
+    attribute_id NUMERIC(9,0) NOT NULL REFERENCES attributes(id),
+    position_id  NUMERIC(9,0) REFERENCES positions(id),
+    edge_id      NUMERIC(9,0) REFERENCES edges(id),
+    type_id      NUMERIC(9,0) REFERENCES annotation_types(id)
 );
 
 -- Pegel
 CREATE SEQUENCE GAUGES_ID_SEQ;
 
+--FIXME: make precision and scale of station column equal with the km columns of the other tables
 CREATE TABLE gauges (
-    id              int PRIMARY KEY NOT NULL,
+    id              NUMERIC(9,0) PRIMARY KEY NOT NULL,
     name            VARCHAR(256)    NOT NULL,
     -- remove river id here because range_id references river already
-    river_id        int             NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
-    station         NUMERIC         NOT NULL,
-    aeo             NUMERIC         NOT NULL,
-    official_number int8,
+    river_id        NUMERIC(9,0)             NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
+    station         NUMERIC(8,4)         NOT NULL,
+    aeo             NUMERIC(9,2)         NOT NULL,
+    official_number NUMERIC(12,0),
 
     -- Pegelnullpunkt
-    datum    NUMERIC NOT NULL,
+    datum    NUMERIC(6,2) NOT NULL,
     -- Streckengueltigkeit
-    range_id int REFERENCES ranges (id) ON DELETE CASCADE,
+    range_id NUMERIC(9,0) REFERENCES ranges (id) ON DELETE CASCADE,
 
     UNIQUE (name, river_id),
     UNIQUE (official_number, river_id),
@@ -114,7 +116,7 @@
 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;
 
 CREATE TABLE main_value_types (
-    id   int PRIMARY KEY NOT NULL,
+    id   NUMERIC(9,0) PRIMARY KEY NOT NULL,
     name VARCHAR(256)    NOT NULL UNIQUE
 );
 
@@ -122,18 +124,18 @@
 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;
 
 CREATE TABLE named_main_values (
-    id      int PRIMARY KEY NOT NULL,
+    id      NUMERIC(9,0) PRIMARY KEY NOT NULL,
     name    VARCHAR(256)    NOT NULL,
-    type_id int NOT NULL REFERENCES main_value_types(id)
+    type_id NUMERIC(9,0) NOT NULL REFERENCES main_value_types(id)
 );
 
 -- Table for time intervals
 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;
 
 CREATE TABLE time_intervals (
-    id         int PRIMARY KEY NOT NULL,
-    start_time TIMESTAMP       NOT NULL,
-    stop_time  TIMESTAMP,
+    id         NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    start_time TIMESTAMP(0)       NOT NULL,
+    stop_time  TIMESTAMP(0),
     CHECK (start_time <= stop_time)
 );
 
@@ -142,12 +144,12 @@
 CREATE SEQUENCE MAIN_VALUES_ID_SEQ;
 
 CREATE TABLE main_values (
-    id             int PRIMARY KEY NOT NULL,
-    gauge_id       int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE,
-    named_value_id int NOT NULL REFERENCES named_main_values(id),
-    value          NUMERIC NOT NULL,
+    id             NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    gauge_id       NUMERIC(9,0) NOT NULL REFERENCES gauges(id) ON DELETE CASCADE,
+    named_value_id NUMERIC(9,0) NOT NULL REFERENCES named_main_values(id),
+    value          NUMERIC(12,2) NOT NULL,
 
-    time_interval_id int REFERENCES time_intervals(id),
+    time_interval_id NUMERIC(9,0) REFERENCES time_intervals(id),
 
     -- TODO: better checks
     UNIQUE (gauge_id, named_value_id, time_interval_id)
@@ -157,12 +159,12 @@
 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;
 
 CREATE TABLE discharge_tables (
-    id               int PRIMARY KEY NOT NULL,
-    gauge_id         int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE,
+    id               NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    gauge_id         NUMERIC(9,0) NOT NULL REFERENCES gauges(id) ON DELETE CASCADE,
     description      VARCHAR(256) NOT NULL,
     bfg_id           VARCHAR(50),
-    kind             int NOT NULL DEFAULT 0,
-    time_interval_id int REFERENCES time_intervals(id),
+    kind             NUMERIC(9,0) NOT NULL DEFAULT 0,
+    time_interval_id NUMERIC(9,0) REFERENCES time_intervals(id),
     UNIQUE(gauge_id, bfg_id, kind)
 );
 
@@ -170,10 +172,10 @@
 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;
 
 CREATE TABLE discharge_table_values (
-    id       int PRIMARY KEY NOT NULL,
-    table_id int NOT NULL REFERENCES discharge_tables(id) ON DELETE CASCADE,
-    q        NUMERIC NOT NULL,
-    w        NUMERIC NOT NULL,
+    id       NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    table_id NUMERIC(9,0) NOT NULL REFERENCES discharge_tables(id) ON DELETE CASCADE,
+    q        NUMERIC(9,4) NOT NULL,
+    w        NUMERIC(6,2) NOT NULL,
 
     UNIQUE (table_id, q, w)
 );
@@ -181,7 +183,7 @@
 -- WST files
 --lookup table for wst kinds
 CREATE TABLE wst_kinds (
-    id 	     int PRIMARY KEY NOT NULL,
+    id 	     NUMERIC(9,0) PRIMARY KEY NOT NULL,
     kind     VARCHAR(64) NOT NULL
 );
 INSERT INTO wst_kinds (id, kind) VALUES (0, 'basedata');
@@ -197,10 +199,10 @@
 CREATE SEQUENCE WSTS_ID_SEQ;
 
 CREATE TABLE wsts (
-    id          int PRIMARY KEY NOT NULL,
-    river_id    int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
+    id          NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    river_id    NUMERIC(9,0) NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
     description VARCHAR(256) NOT NULL,
-    kind        int NOT NULL REFERENCES wst_kinds(id) DEFAULT 0,
+    kind        NUMERIC(9,0) NOT NULL REFERENCES wst_kinds(id) DEFAULT 0,
     -- TODO: more meta infos
     UNIQUE (river_id, description)
 );
@@ -209,14 +211,14 @@
 CREATE SEQUENCE WST_COLUMNS_ID_SEQ;
 
 CREATE TABLE wst_columns (
-    id          int PRIMARY KEY NOT NULL,
-    wst_id      int NOT NULL REFERENCES wsts(id) ON DELETE CASCADE,
+    id          NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    wst_id      NUMERIC(9,0) NOT NULL REFERENCES wsts(id) ON DELETE CASCADE,
     name        VARCHAR(256) NOT NULL,
     description VARCHAR(256),
     source      VARCHAR(256),
-    position    int NOT NULL DEFAULT 0,
+    position    NUMERIC(9,0) NOT NULL DEFAULT 0,
 
-    time_interval_id int REFERENCES time_intervals(id),
+    time_interval_id NUMERIC(9,0) REFERENCES time_intervals(id),
 
     UNIQUE (wst_id, name),
     UNIQUE (wst_id, position)
@@ -226,10 +228,10 @@
 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
 
 CREATE TABLE wst_column_values (
-    id            int PRIMARY KEY NOT NULL,
-    wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
-    position      NUMERIC NOT NULL,
-    w             NUMERIC NOT NULL,
+    id            NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    wst_column_id NUMERIC(9,0) NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
+    position      NUMERIC(9,5) NOT NULL,
+    w             NUMERIC(9,5) NOT NULL,
 
     UNIQUE (position, wst_column_id),
     UNIQUE (position, wst_column_id, w)
@@ -239,18 +241,18 @@
 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;
 
 CREATE TABLE wst_q_ranges (
-    id       int PRIMARY KEY NOT NULL,
-    range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
-    q        NUMERIC NOT NULL
+    id       NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    range_id NUMERIC(9,0) NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
+    q        NUMERIC(10,5) NOT NULL
 );
 
 -- bind q ranges to wst columns
 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;
 
 CREATE TABLE wst_column_q_ranges (
-    id             int PRIMARY KEY NOT NULL,
-    wst_column_id  int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
-    wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id) ON DELETE CASCADE,
+    id             NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    wst_column_id  NUMERIC(9,0) NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
+    wst_q_range_id NUMERIC(9,0) NOT NULL REFERENCES wst_q_ranges(id) ON DELETE CASCADE,
 
     UNIQUE (wst_column_id, wst_q_range_id)
 );
@@ -258,9 +260,9 @@
 CREATE SEQUENCE OFFICIAL_LINES_ID_SEQ;
 
 CREATE TABLE official_lines (
-    id                  int PRIMARY KEY NOT NULL,
-    wst_column_id       int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
-    named_main_value_id int NOT NULL REFERENCES named_main_values(id) ON DELETE CASCADE,
+    id                  NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    wst_column_id       NUMERIC(9,0) NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
+    named_main_value_id NUMERIC(9,0) NOT NULL REFERENCES named_main_values(id) ON DELETE CASCADE,
 
     UNIQUE (wst_column_id, named_main_value_id)
 );
@@ -315,29 +317,29 @@
 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;
 
 CREATE TABLE cross_sections (
-    id               int PRIMARY KEY NOT NULL,
-    river_id         int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
-    time_interval_id int REFERENCES time_intervals(id),
+    id               NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    river_id         NUMERIC(9,0) NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
+    time_interval_id NUMERIC(9,0) REFERENCES time_intervals(id),
     description      VARCHAR(256)
 );
 
 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ;
 
 CREATE TABLE cross_section_lines (
-    id               int PRIMARY KEY NOT NULL,
-    km               NUMERIC         NOT NULL,
-    cross_section_id int             NOT NULL REFERENCES cross_sections(id) ON DELETE CASCADE,
+    id               NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    km               NUMERIC(9,5)         NOT NULL,
+    cross_section_id NUMERIC(9,0)             NOT NULL REFERENCES cross_sections(id) ON DELETE CASCADE,
     UNIQUE (km, cross_section_id)
 );
 
 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
 
 CREATE TABLE cross_section_points (
-    id                    int PRIMARY KEY NOT NULL,
-    cross_section_line_id int             NOT NULL REFERENCES cross_section_lines(id) ON DELETE CASCADE,
-    col_pos               int             NOT NULL,
-    x                     NUMERIC         NOT NULL,
-    y                     NUMERIC         NOT NULL
+    id                    NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    cross_section_line_id NUMERIC(9,0)             NOT NULL REFERENCES cross_section_lines(id) ON DELETE CASCADE,
+    col_pos               NUMERIC(9,0)             NOT NULL,
+    x                     NUMERIC(7,2)         NOT NULL,
+    y                     NUMERIC(7,2)         NOT NULL
 );
 
 -- Indices for faster access of the points
@@ -351,39 +353,40 @@
 CREATE SEQUENCE HYKS_ID_SEQ;
 
 CREATE TABLE hyks (
-    id          int PRIMARY KEY NOT NULL,
-    river_id    int             NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
+    id          NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    river_id    NUMERIC(9,0)             NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
     description VARCHAR(256)    NOT NULL
 );
 
 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
 
+--FIXME: make precision and scale of km column equal with the km columns of the other tables
 CREATE TABLE hyk_entries (
-    id          int PRIMARY KEY NOT NULL,
-    hyk_id      int             NOT NULL REFERENCES hyks(id) ON DELETE CASCADE,
-    km          NUMERIC         NOT NULL,
-    measure     TIMESTAMP,
+    id          NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    hyk_id      NUMERIC(9,0)             NOT NULL REFERENCES hyks(id) ON DELETE CASCADE,
+    km          NUMERIC(7,2)         NOT NULL,
+    measure     TIMESTAMP(0),
     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) ON DELETE CASCADE,
-    top           NUMERIC         NOT NULL,
-    bottom        NUMERIC         NOT NULL,
-    distance_vl   NUMERIC         NOT NULL,
-    distance_hf   NUMERIC         NOT NULL,
-    distance_vr   NUMERIC         NOT NULL,
+    id            NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    formation_num NUMERIC(9,0)             NOT NULL DEFAULT 0,
+    hyk_entry_id  NUMERIC(9,0)             NOT NULL REFERENCES hyk_entries(id) ON DELETE CASCADE,
+    top           NUMERIC(6,2)         NOT NULL,
+    bottom        NUMERIC(6,2)         NOT NULL,
+    distance_vl   NUMERIC(8,2)         NOT NULL,
+    distance_hf   NUMERIC(8,2)         NOT NULL,
+    distance_vr   NUMERIC(8,2)         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,
+    id          NUMERIC(9,0) PRIMARY KEY NOT NULL,
     name        VARCHAR(50)     NOT NULL UNIQUE,
     description VARCHAR(256)
 );
@@ -391,11 +394,11 @@
 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) ON DELETE CASCADE,
-    type_id      int             NOT NULL REFERENCES hyk_flow_zone_types(id),
-    a            NUMERIC         NOT NULL,
-    b            NUMERIC         NOT NULL,
+    id           NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    formation_id NUMERIC(9,0)             NOT NULL REFERENCES hyk_formations(id) ON DELETE CASCADE,
+    type_id      NUMERIC(9,0)             NOT NULL REFERENCES hyk_flow_zone_types(id),
+    a            NUMERIC(7,2)         NOT NULL,
+    b            NUMERIC(7,2)         NOT NULL,
     CHECK (a <= b)
 );