561TAa6P

1measurements         
2---                  
3measurement_id SERIAL         | NOT NULL 
4db_id INTEGER                 | NOT NULL
5patient_id INTEGER            | NOT NULL
6channel_id INTEGER            | NOT NULL        
7c_id INTEGER                  | NOT NULL  
8time TIMESTAMP WITH TIME ZONE | DEFAULT CURRENT_TIMESTAMP 
9
10c_matrices
11---
12c_id SERIAL                   | NOT NULL
13matrix_data double TODO       | NOT NULL
14event_index_start INTEGER     | NOT NULL
15event_index_end INTEGER       | NOT NULL
16c_size_in_bytes INTEGER       | NOT NULL 
17
18patients
19---
20patient_id SERIAL             | NOT NULL
21age VARYING(256)              | NOT NULL
22gender VARYING(256)           | NOT NULL
23treatment_id INTEGER          | NOT NULL
24diagnosis_id INTEGER          | NOT NULL
25
26databases
27---
28db_id SERIAL                  | NOT NULL
29db_name VARYING(256)          | NOT NULL
30
31channels
32---
33channel_id SERIAL             | NOT NULL
34channel_name VARYING(256)     | NOT NULL
35
36treatments
37---
38treatment_id SERIAL           | NOT NULL
39drug_A_id INTEGER             
40...
41drug_N_id INTEGER             
42
43diagnosis
44---
45diagnosis_id SERIAL           | NOT NULL
46diagnosis_A_id INTEGER        
47... 
48diagnosis_N_id INTEGER
49	
50DROP TABLE IF EXISTS measurements, c_matrices; 
51
52CREATE TABLE measurements (
53        measurement_id SERIAL PRIMARY KEY NOT NULL,
54        db_id INTEGER NOT NULL, 
55        patient_id INTEGER NOT NULL, 
56        channel_id INTEGER NOT NULL, 
57        c_id INTEGER NOT NULL, 
58        time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
59        CONSTRAINT no_duplicate_measurements UNIQUE (time), 
60        CONSTRAINT no_same_measurements UNIQUE (db_id, patient_id, channel_id, c_id) 
61);
62
63CREATE TABLE c_matrices (
64        c_id SERIAL PRIMARY KEY NOT NULL, 
65        measurement_id INTEGER NOT NULL, 
66        event_index_start INTEGER NOT NULL,
67        event_index_end INTEGER NOT NULL, 
68        c_matrix_data TODO NOT NULL,
69        c_size_in_bytes INTEGER NOT NULL, 
70        UNIQUE (measurement_id, event_index_start, event_index_end)
71);
72	
73-- http://dba.stackexchange.com/a/107062/69807
74WITH ins1 AS (
75   INSERT INTO measurements (measurement_id, db_id, patient_id, channel_id) -- TODO not sure since this autogenerated
76   VALUES 1, 2, 3, 4 -- if data comes from client app
77   -- SELECT ... if data from other db TODO  how to do this here SELECT c_id FROM c_matrices WHERE measurement_id==777 
78   RETURNING measurement_id  -- generating a serial ID
79   )
80INSERT INTO c_matrices (measurement_id, event_index_start, event_index_end, c_matrix_data, c_size_in_bytes)
81VALUES 1, 2, 3, $c_data -- not sure
82RETURNING c_id -- generating a serial id -- TODO right?
83FROM   ins1;