1 files changed, 34 insertions, 40 deletions
diff --git a/sql/ddl/pgsql.sql b/sql/ddl/pgsql.sql
index 18c0661..09c7133 100644
--- a/sql/ddl/pgsql.sql
+++ b/sql/ddl/pgsql.sql
@@ -1,51 +1,45 @@
-create table users
-(
-    id          serial primary key,
-    name        text not null,
-    pwd         text not null,
-    description text
+CREATE TABLE users (
+    id          SERIAL PRIMARY KEY,
+    name        TEXT NOT NULL UNIQUE,
+    pwd         TEXT NOT NULL,
+    description TEXT
 );
 
-create unique index users_name ON users(name);
-
-create table categories
-(
-    id        serial primary key,
-    name      text not null,
-    parent_id integer references categories,
-    user_id   integer not null references users
+CREATE TABLE categories (
+    id        SERIAL PRIMARY KEY,
+    name      TEXT    NOT NULL,
+    parent_id INTEGER REFERENCES categories,
+    user_id   INTEGER NOT NULL REFERENCES users
 );
 
-create index parent_id
-    on categories (parent_id);
+CREATE INDEX parent_id
+    ON categories (parent_id);
 
-create index user_id
-    on categories (user_id);
+CREATE INDEX user_id
+    ON categories (user_id);
 
 
-create table const_expenses
-(
-    id          serial primary key,
-    description text,
-    expense     numeric(10, 2) not null,
-    months      smallint not null,
-    start       date not null,
-    "end"       date not null,
-    prev_id     integer references const_expenses,
-    category_id integer not null references categories,
-    user_id     integer not null references users
+CREATE TABLE const_expenses (
+    id          SERIAL PRIMARY KEY,
+    description TEXT,
+    expense     NUMERIC(10, 2) NOT NULL,
+    duration    INTERVAL       NOT NULL,
+    start       DATE           NOT NULL,
+    "end"       DATE           NOT NULL,
+    prev_id     INTEGER REFERENCES const_expenses,
+    category_id INTEGER        NOT NULL REFERENCES categories,
+    user_id     INTEGER        NOT NULL REFERENCES users
 );
 
-create table single_expenses
-(
-    id          bigserial primary key,
-    description text,
-    expense     numeric(10, 2) not null,
-    year        smallint not null,
-    month       smallint not null,
-    day         smallint not null,
-    category_id integer not null references categories,
-    user_id     integer not null references users
+CREATE TABLE single_expenses (
+    id          BIGSERIAL PRIMARY KEY,
+    description TEXT,
+    expense     NUMERIC(10, 2) NOT NULL,
+    date        DATE           NOT NULL,
+    -- we need the cast to timestamp, because it is only considered immutable then
+    corr_month  DATE GENERATED ALWAYS AS (DATE_TRUNC('month', date::timestamp)) STORED,
+    category_id INTEGER        NOT NULL REFERENCES categories,
+    user_id     INTEGER        NOT NULL REFERENCES users
 );
 
-CREATE INDEX idx_single_date ON single_expenses(user_id, year, month);
-\ No newline at end of file
+CREATE INDEX idx_single_month ON single_expenses (user_id, corr_month);
+\ No newline at end of file