48 lines
1.1 KiB
MySQL
48 lines
1.1 KiB
MySQL
|
CREATE TABLE users (
|
||
|
|
username TEXT PRIMARY KEY,
|
||
|
|
password TEXT NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE plans (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
owner TEXT REFERENCES users(username) NOT NULL,
|
||
|
|
description TEXT DEFAULT '',
|
||
|
|
join_code TEXT NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE members (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
plan_id INTEGER REFERENCES plans(id) NOT NULL,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
user_id TEXT REFERENCES users(username)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE polls (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
plan_id INTEGER REFERENCES plans(id) NOT NULL,
|
||
|
|
options TEXT DEFAULT ''
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE votes (
|
||
|
|
poll_id INTEGER REFERENCES polls(id),
|
||
|
|
member_id INTEGER REFERENCES members(id),
|
||
|
|
value text,
|
||
|
|
PRIMARY KEY (poll_id,member_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE expenses (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
plan_id INTEGER REFERENCES plans(id) NOT NULL,
|
||
|
|
payer_id INTEGER REFERENCES members(id) NOT NULL,
|
||
|
|
amount DECIMAL NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE debts (
|
||
|
|
expense_id INTEGER REFERENCES expenses(id) NOT NULL,
|
||
|
|
debtor_id INTEGER REFERENCES members(id) NOT NULL,
|
||
|
|
amount DECIMAL NOT NULL,
|
||
|
|
paid DECIMAL,
|
||
|
|
PRIMARY KEY (expense_id,debtor_id)
|
||
|
|
);
|