health.balance/ddl.sql at main · objektwerks/health.balance · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
DROP SCHEMA PUBLIC CASCADE;
CREATE SCHEMA PUBLIC;

CREATE TABLE account (
  id BIGSERIAL PRIMARY KEY,
  license VARCHAR(36) UNIQUE NOT NULL,
  email_address VARCHAR NOT NULL,
  pin VARCHAR(7) NOT NULL,
  activated BIGINT NOT NULL,
  deactivated BIGINT NOT NULL
);

CREATE TABLE profile (
  id BIGSERIAL PRIMARY KEY,
  account_id BIGINT REFERENCES account(id),
  name VARCHAR UNIQUE NOT NULL,
  created BIGINT NOT NULL
);

CREATE TABLE edible (
  id BIGSERIAL PRIMARY KEY,
  profile_id BIGINT REFERENCES profile(id),
  kind VARCHAR NOT NULL,
  detail VARCHAR NOT NULL,
  organic BOOL NOT NULL,
  calories INT NOT NULL,
  ate BIGINT NOT NULL
);

CREATE TABLE drinkable (
  id BIGSERIAL PRIMARY KEY,
  profile_id BIGINT REFERENCES profile(id),
  kind VARCHAR NOT NULL,
  detail VARCHAR NOT NULL,
  organic BOOL NOT NULL,
  count INT NOT NULL,
  calories INT NOT NULL,
  drank BIGINT NOT NULL
);

CREATE TABLE expendable (
  id BIGSERIAL PRIMARY KEY,
  profile_id BIGINT REFERENCES profile(id),
  kind VARCHAR NOT NULL,
  detail VARCHAR NOT NULL,
  sunshine BOOL NOT NULL,
  freshair BOOL NOT NULL,
  calories INT NOT NULL,
  start BIGINT NOT NULL,
  finish BIGINT NOT NULL
);

CREATE TABLE measurable (
  id BIGSERIAL PRIMARY KEY,
  profile_id BIGINT REFERENCES profile(id),
  kind VARCHAR NOT NULL,
  measurement INT NOT NULL,
  measured BIGINT NOT NULL
);

CREATE TABLE fault (
  cause VARCHAR NOT NULL,
  occurred BIGINT NOT NULL
);