Splitty/setup_db.sql at main · RiadZX/Splitty · 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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
DROP TABLE PART_OF IF EXISTS;
DROP TABLE PAID_FOR IF EXISTS;
DROP TABLE DEBTS IF EXISTS;
DROP TABLE IN_EVENT IF EXISTS;
DROP TABLE PAID_IN IF EXISTS;

DROP TABLE EVENTS IF EXISTS;
DROP TABLE PARTICIPANTS IF EXISTS;
DROP TABLE PERSON IF EXISTS;
DROP TABLE QUOTE IF EXISTS;
DROP TABLE EXPENSES IF EXISTS;


CREATE TABLE participants (
    participant_id integer NOT NULL,
    name character varying(50) NOT NULL,
    PRIMARY KEY ( participant_id )
);

CREATE TABLE events (
    event_id integer NOT NULL,
    title character varying(50) NOT NULL,
    event_code character varying(30) NOT NULL,
    date date NOT NULL,
    PRIMARY KEY (event_id)
);

CREATE TABLE expenses (
    expense_id integer NOT NULL,
    event_id integer NOT NULL,
    paid_by integer NOT NULL,
    title character varying(50) NOT NULL,
    amount numeric(10,10) NOT NULL,
    date date NOT NULL,
    PRIMARY KEY (expense_id)
);

CREATE TABLE in_event (
    transaction_id integer NOT NULL,
    participant_id integer NOT NULL,
    event_id integer NOT NULL,
    PRIMARY KEY (transaction_id),
    FOREIGN KEY (event_id) REFERENCES events(event_id),
    FOREIGN KEY (participant_id) REFERENCES participants(participant_id)
);

CREATE TABLE paid_in (
    transaction_id integer NOT NULL,
    expense_id integer NOT NULL,
    event_id integer NOT NULL,
    PRIMARY KEY (transaction_id),
    FOREIGN KEY (event_id) REFERENCES events(event_id),
    FOREIGN KEY (expense_id) REFERENCES expenses(expense_id)
);

CREATE TABLE paid_for (
    transaction_id integer NOT NULL,
    expense_id integer NOT NULL,
    participant_id integer NOT NULL,
    PRIMARY KEY (transaction_id),
    FOREIGN KEY (participant_id) REFERENCES participants(participant_id),
    FOREIGN KEY (expense_id) REFERENCES expenses(expense_id)
);

CREATE TABLE debts (
    debt_id integer NOT NULL,
    expense_id integer NOT NULL,
    amount numeric(10,10) NOT NULL,
    paid boolean DEFAULT false NOT NULL,
    participant_id integer NOT NULL,
    PRIMARY KEY (debt_id),
    FOREIGN KEY (participant_id) REFERENCES participants(participant_id),
    FOREIGN KEY (expense_id) REFERENCES expenses(expense_id)
);

CREATE TABLE part_of (
    part_id integer NOT NULL,
    participant_id integer,
    event_id integer NOT NULL,
    PRIMARY KEY (part_id),
    FOREIGN KEY (participant_id) REFERENCES participants(participant_id),
    FOREIGN KEY (event_id) REFERENCES events(event_id)
);