RelationalSchema: ddl.sql

File ddl.sql, 8.3 KB (added by 221164, 8 days ago)
Line 
1CREATE TABLE IF NOT EXISTS users (
2 id BIGSERIAL PRIMARY KEY,
3 email VARCHAR(255) NOT NULL UNIQUE,
4 password VARCHAR(255) NOT NULL,
5 phone_number VARCHAR(50) UNIQUE,
6 street VARCHAR(255),
7 city VARCHAR(255)
8 );
9
10CREATE TABLE IF NOT EXISTS employees (
11 user_id BIGSERIAL PRIMARY KEY,
12 net_salary DECIMAL(10,2),
13 gross_salary DECIMAL(10,2),
14 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
15 );
16
17CREATE TABLE IF NOT EXISTS customers (
18 user_id BIGSERIAL PRIMARY KEY,
19 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
20 );
21
22CREATE TABLE IF NOT EXISTS staff_roles (
23 id BIGSERIAL PRIMARY KEY,
24 name VARCHAR(255) NOT NULL
25 );
26
27CREATE TABLE IF NOT EXISTS front_staff (
28 employee_id BIGSERIAL PRIMARY KEY,
29 tip_percent DECIMAL(10,2),
30 staff_role_id BIGINT NOT NULL,
31 FOREIGN KEY (employee_id) REFERENCES employees (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
32 FOREIGN KEY (staff_role_id) REFERENCES staff_roles (id) ON DELETE CASCADE ON UPDATE CASCADE
33 );
34
35CREATE TABLE IF NOT EXISTS back_staff (
36 employee_id BIGSERIAL PRIMARY KEY,
37 staff_role_id BIGINT NOT NULL,
38 FOREIGN KEY (employee_id) REFERENCES employees (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
39 FOREIGN KEY (staff_role_id) REFERENCES staff_roles (id) ON DELETE CASCADE ON UPDATE CASCADE
40 );
41
42CREATE TABLE IF NOT EXISTS managers (
43 employee_id BIGSERIAL PRIMARY KEY,
44 FOREIGN KEY (employee_id) REFERENCES employees (user_id) ON DELETE CASCADE ON UPDATE CASCADE
45 );
46
47CREATE TABLE IF NOT EXISTS shifts (
48 id BIGSERIAL PRIMARY KEY,
49 date DATE NOT NULL,
50 start_time TIME NOT NULL,
51 end_time TIME NOT NULL,
52 manager_id BIGINT NOT NULL,
53 FOREIGN KEY (manager_id) REFERENCES managers (employee_id) ON DELETE CASCADE ON UPDATE CASCADE
54 );
55
56CREATE TABLE IF NOT EXISTS assignments (
57 id BIGSERIAL PRIMARY KEY,
58 clock_in_time TIME,
59 clock_out_time TIME,
60 manager_id BIGINT NOT NULL,
61 employee_id BIGINT NOT NULL,
62 shift_id BIGINT NOT NULL,
63 FOREIGN KEY (manager_id) REFERENCES managers (employee_id) ON DELETE CASCADE ON UPDATE CASCADE,
64 FOREIGN KEY (employee_id) REFERENCES employees (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
65 FOREIGN KEY (shift_id) REFERENCES shifts (id) ON DELETE CASCADE ON UPDATE CASCADE
66 );
67
68CREATE TABLE IF NOT EXISTS tables (
69 table_number BIGSERIAL PRIMARY KEY,
70 seat_capacity INT NOT NULL
71);
72
73CREATE TABLE IF NOT EXISTS reservations (
74 id BIGSERIAL PRIMARY KEY,
75 user_id BIGINT NOT NULL,
76 creation_timestamp TIMESTAMP NOT NULL,
77 datetime TIMESTAMP NOT NULL,
78 number_of_people BIGINT NOT NULL,
79 stay_length DECIMAL(10,2) NULL, --hours
80 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
81 );
82
83CREATE TABLE IF NOT EXISTS frontstaff_managed_reservations (
84 id BIGSERIAL PRIMARY KEY,
85 reservation_id BIGINT NOT NULL,
86 front_staff_id BIGINT NOT NULL,
87 table_number BIGINT NOT NULL,
88 FOREIGN KEY (reservation_id) REFERENCES reservations (id) ON DELETE CASCADE ON UPDATE CASCADE,
89 FOREIGN KEY (front_staff_id) REFERENCES front_staff (employee_id) ON DELETE CASCADE ON UPDATE CASCADE,
90 FOREIGN KEY (table_number) REFERENCES tables (table_number) ON DELETE CASCADE ON UPDATE CASCADE
91 );
92
93CREATE TABLE IF NOT EXISTS categories (
94 id BIGSERIAL PRIMARY KEY,
95 name VARCHAR(255) NOT NULL,
96 is_available BOOLEAN NOT NULL DEFAULT TRUE
97 );
98
99CREATE TABLE IF NOT EXISTS products (
100 id BIGSERIAL PRIMARY KEY,
101 name VARCHAR(255) NOT NULL,
102 price DECIMAL(10,2) NOT NULL,
103 category_id BIGINT NOT NULL,
104 manage_inventory BOOLEAN NOT NULL DEFAULT FALSE,
105 tax_class VARCHAR(4) NOT NULL,
106 FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE CASCADE ON UPDATE CASCADE
107 );
108
109CREATE TABLE IF NOT EXISTS inventories (
110 product_id BIGSERIAL PRIMARY KEY,
111 quantity INT NOT NULL,
112 restock_level INT NULL,
113 FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE ON UPDATE CASCADE
114 );
115
116CREATE TABLE IF NOT EXISTS orders (
117 id BIGSERIAL PRIMARY KEY,
118 status VARCHAR(255) NOT NULL DEFAULT 'PENDING',
119 datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
120 );
121
122CREATE TABLE IF NOT EXISTS order_items (
123 id BIGSERIAL PRIMARY KEY,
124 order_id BIGINT NOT NULL,
125 product_id BIGINT NOT NULL,
126 is_processed BOOLEAN NOT NULL DEFAULT FALSE,
127 quantity INT NOT NULL,
128 price DECIMAL(10,2) NOT NULL,
129 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
130 FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE ON UPDATE CASCADE,
131 FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE ON UPDATE CASCADE
132 );
133
134CREATE TABLE IF NOT EXISTS tab_orders (
135 order_id BIGSERIAL PRIMARY KEY,
136 front_staff_id BIGINT NOT NULL,
137 table_number BIGINT NOT NULL,
138 FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE ON UPDATE CASCADE,
139 FOREIGN KEY (table_number) REFERENCES tables (table_number) ON DELETE CASCADE ON UPDATE CASCADE,
140 FOREIGN KEY (front_staff_id) REFERENCES front_staff (employee_id) ON DELETE CASCADE ON UPDATE CASCADE
141 );
142
143
144CREATE TABLE IF NOT EXISTS online_orders (
145 order_id BIGSERIAL PRIMARY KEY,
146 delivery_address VARCHAR(255) NOT NULL,
147 customer_id BIGINT NOT NULL,
148 FOREIGN KEY (customer_id) REFERENCES customers (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
149 FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE ON UPDATE CASCADE
150 );
151
152CREATE TABLE IF NOT EXISTS payments (
153 id BIGSERIAL PRIMARY KEY,
154 order_id BIGINT NOT NULL,
155 amount DECIMAL(10,2) NOT NULL,
156 payment_type VARCHAR(32) NOT NULL,
157 tip_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
158 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
159 FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE ON UPDATE CASCADE
160 );