RelationalDesign: schema_creation.sql

File schema_creation.sql, 12.5 KB (added by 202033, 3 weeks ago)
Line 
1DROP TABLE IF EXISTS review CASCADE;
2DROP TABLE IF EXISTS appointmentpackageusage CASCADE;
3DROP TABLE IF EXISTS appointmentservice CASCADE;
4DROP TABLE IF EXISTS payment CASCADE;
5DROP TABLE IF EXISTS userpackagepurchase CASCADE;
6DROP TABLE IF EXISTS packageservice CASCADE;
7DROP TABLE IF EXISTS loyaltycard CASCADE;
8DROP TABLE IF EXISTS appointment CASCADE;
9DROP TABLE IF EXISTS service CASCADE;
10DROP TABLE IF EXISTS package CASCADE;
11DROP TABLE IF EXISTS category CASCADE;
12DROP TABLE IF EXISTS userrole CASCADE;
13DROP TABLE IF EXISTS role CASCADE;
14DROP TABLE IF EXISTS status CASCADE;
15DROP TABLE IF EXISTS availability CASCADE;
16DROP TABLE IF EXISTS "User" CASCADE;
17
18DROP TYPE IF EXISTS appointment_type CASCADE;
19
20CREATE TYPE appointment_type AS ENUM ('pre-booked', 'walk-in');
21
22CREATE TABLE "User" (
23 user_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
24 full_name varchar NOT NULL,
25 email varchar NOT NULL,
26 phone varchar NOT NULL,
27 created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
28 password_hash text NOT NULL,
29 CONSTRAINT user_email_key UNIQUE (email)
30);
31
32CREATE TABLE role (
33 role_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
34 name varchar NOT NULL,
35 CONSTRAINT role_name_key UNIQUE (name)
36);
37
38CREATE TABLE category (
39 category_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
40 name varchar NOT NULL,
41 CONSTRAINT category_name_key UNIQUE (name)
42);
43
44CREATE TABLE status (
45 status_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
46 name varchar NOT NULL,
47 CONSTRAINT status_name_key UNIQUE (name)
48);
49
50CREATE TABLE package (
51 package_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
52 name varchar NOT NULL,
53 max_usage integer NOT NULL,
54 total_price numeric NOT NULL DEFAULT 0,
55 CONSTRAINT package_max_usage_check CHECK (max_usage > 0),
56 CONSTRAINT package_total_price_nonnegative CHECK (total_price >= 0)
57);
58
59CREATE TABLE service (
60 service_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
61 name varchar NOT NULL,
62 price numeric NOT NULL,
63 duration_minutes integer NOT NULL,
64 category_id integer NOT NULL,
65 CONSTRAINT fk_service_category
66 FOREIGN KEY (category_id) REFERENCES category(category_id)
67 ON DELETE RESTRICT,
68 CONSTRAINT service_price_check CHECK (price >= 0),
69 CONSTRAINT service_duration_minutes_check CHECK (duration_minutes > 0)
70);
71
72CREATE TABLE appointment (
73 appointment_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
74 appointment_time timestamp NOT NULL,
75 end_time timestamp NOT NULL,
76 notes text,
77 type appointment_type NOT NULL,
78 total_price numeric NOT NULL,
79 user_id integer NOT NULL,
80 status_id integer NOT NULL,
81 points_awarded boolean NOT NULL DEFAULT false,
82 CONSTRAINT fk_appointment_user
83 FOREIGN KEY (user_id) REFERENCES "User"(user_id)
84 ON DELETE CASCADE,
85 CONSTRAINT fk_appointment_status
86 FOREIGN KEY (status_id) REFERENCES status(status_id)
87 ON DELETE RESTRICT,
88 CONSTRAINT chk_appointment_times CHECK (end_time > appointment_time),
89 CONSTRAINT appointment_total_price_check CHECK (total_price >= 0)
90);
91
92CREATE TABLE loyaltycard (
93 card_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
94 points integer NOT NULL DEFAULT 0,
95 user_id integer NOT NULL,
96 CONSTRAINT fk_loyaltycard_user
97 FOREIGN KEY (user_id) REFERENCES "User"(user_id)
98 ON DELETE CASCADE,
99 CONSTRAINT loyaltycard_user_id_key UNIQUE (user_id),
100 CONSTRAINT loyaltycard_points_nonnegative CHECK (points >= 0)
101);
102
103CREATE TABLE userrole (
104 user_id integer NOT NULL,
105 role_id integer NOT NULL,
106 CONSTRAINT userrole_pkey PRIMARY KEY (user_id, role_id),
107 CONSTRAINT fk_userrole_user
108 FOREIGN KEY (user_id) REFERENCES "User"(user_id)
109 ON DELETE CASCADE,
110 CONSTRAINT fk_userrole_role
111 FOREIGN KEY (role_id) REFERENCES role(role_id)
112 ON DELETE CASCADE
113);
114
115CREATE TABLE packageservice (
116 package_id integer NOT NULL,
117 service_id integer NOT NULL,
118 discounted_price numeric NOT NULL,
119 CONSTRAINT packageservice_pkey PRIMARY KEY (package_id, service_id),
120 CONSTRAINT fk_packageservice_package
121 FOREIGN KEY (package_id) REFERENCES package(package_id)
122 ON DELETE CASCADE,
123 CONSTRAINT fk_packageservice_service
124 FOREIGN KEY (service_id) REFERENCES service(service_id)
125 ON DELETE CASCADE,
126 CONSTRAINT packageservice_discounted_price_check CHECK (discounted_price >= 0)
127);
128
129CREATE TABLE userpackagepurchase (
130 purchase_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
131 user_id integer NOT NULL,
132 package_id integer NOT NULL,
133 purchased_at timestamp NOT NULL DEFAULT now(),
134 total_uses integer NOT NULL,
135 remaining_uses integer NOT NULL,
136 status text NOT NULL,
137 expires_at date,
138 CONSTRAINT userpackagepurchase_user_id_fkey
139 FOREIGN KEY (user_id) REFERENCES "User"(user_id)
140 ON DELETE CASCADE,
141 CONSTRAINT userpackagepurchase_package_id_fkey
142 FOREIGN KEY (package_id) REFERENCES package(package_id)
143 ON DELETE RESTRICT,
144 CONSTRAINT userpackagepurchase_status_check
145 CHECK (status IN ('ACTIVE', 'EXPIRED', 'CANCELLED')),
146 CONSTRAINT userpackagepurchase_uses_check
147 CHECK (total_uses >= 0 AND remaining_uses >= 0)
148);
149
150CREATE TABLE appointmentservice (
151 appointment_id integer NOT NULL,
152 service_id integer NOT NULL,
153 CONSTRAINT appointmentservice_pkey PRIMARY KEY (appointment_id, service_id),
154 CONSTRAINT fk_appointmentservice_appointment
155 FOREIGN KEY (appointment_id) REFERENCES appointment(appointment_id)
156 ON DELETE CASCADE,
157 CONSTRAINT fk_appointmentservice_service
158 FOREIGN KEY (service_id) REFERENCES service(service_id)
159 ON DELETE CASCADE
160);
161
162CREATE TABLE appointmentpackageusage (
163 appointment_id integer PRIMARY KEY,
164 purchase_id integer NOT NULL,
165 service_id integer NOT NULL,
166 used_units integer NOT NULL DEFAULT 1,
167 finalized_at timestamp NULL,
168 CONSTRAINT appointmentpackageusage_appointment_id_fkey
169 FOREIGN KEY (appointment_id) REFERENCES appointment(appointment_id)
170 ON DELETE CASCADE,
171 CONSTRAINT appointmentpackageusage_purchase_id_fkey
172 FOREIGN KEY (purchase_id) REFERENCES userpackagepurchase(purchase_id)
173 ON DELETE RESTRICT,
174 CONSTRAINT appointmentpackageusage_service_id_fkey
175 FOREIGN KEY (service_id) REFERENCES service(service_id)
176 ON DELETE RESTRICT,
177 CONSTRAINT appointmentpackageusage_used_units_check CHECK (used_units > 0)
178);
179
180CREATE TABLE payment (
181 payment_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
182 amount numeric NOT NULL,
183 method varchar NOT NULL,
184 "timestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
185 status varchar NOT NULL,
186 appointment_id integer NULL,
187 points_used integer NOT NULL DEFAULT 0,
188 package_purchase_id integer NULL,
189 CONSTRAINT fk_payment_appointment
190 FOREIGN KEY (appointment_id) REFERENCES appointment(appointment_id)
191 ON DELETE CASCADE,
192 CONSTRAINT fk_payment_package_purchase
193 FOREIGN KEY (package_purchase_id) REFERENCES userpackagepurchase(purchase_id)
194 ON DELETE SET NULL,
195 CONSTRAINT payment_amount_check CHECK (amount >= 0),
196 CONSTRAINT payment_points_used_nonnegative CHECK (points_used >= 0),
197 CONSTRAINT payment_status_allowed CHECK (status IN ('PENDING', 'PAID', 'FAILED', 'REFUNDED')),
198 CONSTRAINT payment_target_required CHECK (appointment_id IS NOT NULL OR package_purchase_id IS NOT NULL)
199);
200
201CREATE TABLE review (
202 review_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
203 rating integer NOT NULL,
204 comment text,
205 created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
206 payment_id integer NOT NULL,
207 CONSTRAINT fk_review_payment
208 FOREIGN KEY (payment_id) REFERENCES payment(payment_id)
209 ON DELETE CASCADE,
210 CONSTRAINT review_rating_range CHECK (rating >= 1 AND rating <= 5),
211 CONSTRAINT review_unique_payment UNIQUE (payment_id)
212);
213
214CREATE TABLE availability (
215 availability_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
216 date date NOT NULL,
217 start_time time NOT NULL,
218 end_time time NOT NULL,
219 is_closed boolean NOT NULL DEFAULT false,
220 CONSTRAINT availability_end_after_start CHECK (end_time > start_time)
221);
222
223CREATE UNIQUE INDEX idx_payment_paid_unique
224 ON payment (appointment_id)
225 WHERE status = 'PAID';
226
227CREATE UNIQUE INDEX idx_payment_package_purchase_paid_unique
228 ON payment (package_purchase_id)
229 WHERE status = 'PAID' AND appointment_id IS NULL;
230
231CREATE UNIQUE INDEX idx_availability_unique_window
232 ON availability (date, start_time, end_time);
233
234
235