DatabaseCreation: DDL-MarketNET.sql

File DDL-MarketNET.sql, 14.9 KB (added by 231072, 11 days ago)
Line 
1CREATE TABLE IF NOT EXISTS AppUser(
2 user_id SERIAL PRIMARY KEY,
3 first_name VARCHAR(255) NOT NULL,
4 last_name VARCHAR(255) NOT NULL,
5 email VARCHAR(255) NOT NULL CHECK (email LIKE '%@%.%'),
6 password VARCHAR(255) NOT NULL CHECK (LENGTH(password) >= 8),
7 country_code VARCHAR(4) CHECK (country_code LIKE '^\+\d{1,3}$'),
8 phone_number INT,
9 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
10 is_verified SMALLINT DEFAULT 0 NOT NULL CHECK (is_verified IN (0,1)),
11 is_active SMALLINT DEFAULT 1 NOT NULL CHECK (is_active IN (0,1))
12);
13
14
15
16CREATE TABLE IF NOT exists UserAddress (
17 user_address_id SERIAL PRIMARY KEY,
18 user_id INT NOT NULL,
19 country VARCHAR(255) NOT NULL,
20 city VARCHAR(255) NOT NULL,
21 street VARCHAR(255) NOT NULL,
22 house_number INT NOT NULL,
23 is_primary SMALLINT NOT NULL,
24 CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES AppUser(user_id) ON DELETE CASCADE ON UPDATE CASCADE
25);
26
27
28CREATE TABLE IF NOT exists Category (
29 category_id SERIAL PRIMARY KEY,
30 name VARCHAR(255) NOT NULL,
31 description VARCHAR(255) NOT NULL,
32 parent_category INT,
33 CONSTRAINT fk_parent_category FOREIGN KEY(parent_category) REFERENCES Category(category_id) ON DELETE SET NULL ON UPDATE CASCADE
34);
35
36
37CREATE TABLE IF NOT exists CategoryAttributes (
38 category_attribute_id SERIAL PRIMARY KEY,
39 category_id INT NOT NULL,
40 attribute_name VARCHAR(255) NOT NULL,
41 CONSTRAINT fk_category FOREIGN KEY(category_id) REFERENCES Category(category_id) ON DELETE CASCADE ON UPDATE CASCADE
42);
43
44
45
46CREATE TABLE IF NOT exists Product (
47 product_id SERIAL PRIMARY KEY,
48 title VARCHAR(255) NOT NULL,
49 description VARCHAR(255) NOT NULL,
50 price DECIMAL(10,2) NOT NULL,
51 currency VARCHAR(255) NOT NULL CHECK (currency IN ('MKD','EUR','USD')),
52 category_id INT NOT NULL,
53 seller_id INT NOT NULL,
54 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
55 is_active BOOLEAN DEFAULT TRUE NOT NULL,
56 quantity INT DEFAULT 1,
57 CONSTRAINT fk_product_seller FOREIGN KEY (seller_id) REFERENCES AppUser(user_id) ON DELETE RESTRICT ON UPDATE CASCADE,
58 CONSTRAINT fk_product_category FOREIGN KEY (category_id) REFERENCES Category(category_id) ON DELETE RESTRICT ON UPDATE CASCADE
59);
60
61
62
63CREATE TABLE if not exists ProductImages (
64 image_id SERIAL PRIMARY KEY,
65 product_id INT NOT NULL,
66 image_url VARCHAR(255) NOT NULL,
67 CONSTRAINT fk_image_product FOREIGN KEY (product_id) REFERENCES Product(product_id) ON DELETE CASCADE ON UPDATE CASCADE
68);
69
70
71
72CREATE TABLE IF NOT exists ProductAttributes (
73 attribute_id SERIAL PRIMARY KEY,
74 product_id INT NOT NULL,
75 category_attribute_id INT NOT NULL,
76 attribute_value VARCHAR(255) NOT NULL,
77 CONSTRAINT fk_attr_product FOREIGN KEY (product_id) REFERENCES Product(product_id) ON DELETE CASCADE ON UPDATE CASCADE,
78 CONSTRAINT fk_attr_category_attr FOREIGN KEY (category_attribute_id) REFERENCES CategoryAttributes(category_attribute_id) ON DELETE CASCADE ON UPDATE CASCADE
79);
80
81
82CREATE TABLE IF NOT exists ProductStats (
83 product_id INT PRIMARY KEY,
84 views_count INT DEFAULT 0 NOT NULL,
85 favorites_count INT DEFAULT 0 NOT NULL,
86 sells_count INT DEFAULT 0 NOT NULL,
87 CONSTRAINT fk_stats_product FOREIGN KEY (product_id) REFERENCES Product(product_id) ON DELETE CASCADE ON UPDATE CASCADE
88);
89
90
91CREATE TABLE IF NOT exists ProductViews (
92 view_id SERIAL PRIMARY KEY,
93 user_id INT NOT NULL,
94 product_id INT NOT NULL,
95 viewed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
96 CONSTRAINT fk_views_product FOREIGN KEY (product_id) REFERENCES Product(product_id) ON DELETE CASCADE ON UPDATE CASCADE,
97 CONSTRAINT fk_views_user FOREIGN KEY (user_id) REFERENCES AppUser(user_id) ON DELETE SET NULL ON UPDATE CASCADE
98);
99
100
101CREATE TABLE Package (
102 package_id SERIAL PRIMARY KEY,
103 name VARCHAR(255) NOT NULL CHECK (name IN ('BRONZE','SILVER','GOLD')),
104 description VARCHAR(255) NOT NULL,
105 currency VARCHAR(3) DEFAULT 'EUR' NOT NULL CHECK (currency IN ('EUR','USD','MKD')),
106 price INT NOT NULL CHECK (
107 (name = 'BRONZE' AND price = 2) OR
108 (name = 'SILVER' AND price = 5) OR
109 (name = 'GOLD' AND price = 10)
110 ),
111 visibility_level VARCHAR(255) NOT NULL CHECK (visibility_level IN ('BASIC','STANDARD','PREMIUM')),
112 duration_days INT NOT NULL DEFAULT 30
113);
114
115
116CREATE TABLE UserPackages (
117 user_package_id SERIAL PRIMARY KEY,
118 seller_id INT NOT NULL,
119 package_id INT NOT NULL,
120 start_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
121 end_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
122 CONSTRAINT fk_user_package_package FOREIGN KEY (package_id) REFERENCES Package(package_id) ON DELETE RESTRICT ON UPDATE CASCADE,
123 CONSTRAINT fk_user_package_seller FOREIGN KEY (seller_id) REFERENCES AppUser(user_id) ON DELETE CASCADE ON UPDATE CASCADE
124);
125
126
127CREATE TABLE Favorites (
128 favorite_id SERIAL PRIMARY KEY,
129 user_id INT NOT NULL,
130 product_id INT NOT NULL,
131 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
132 CONSTRAINT fk_fav_user FOREIGN KEY (user_id) REFERENCES AppUser(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
133 CONSTRAINT fk_fav_product FOREIGN KEY (product_id) REFERENCES Product(product_id) ON DELETE CASCADE ON UPDATE CASCADE
134);
135
136
137CREATE TABLE Cart (
138 cart_id SERIAL PRIMARY KEY,
139 user_id INT NOT NULL,
140 created_at DATE NOT NULL,
141 total_price INT NOT NULL,
142 CONSTRAINT fk_cart_user FOREIGN KEY (user_id) REFERENCES AppUser(user_id) ON DELETE CASCADE ON UPDATE CASCADE
143);
144
145
146
147CREATE TABLE CartItems (
148 cart_item_id SERIAL PRIMARY KEY,
149 cart_id INT NOT NULL,
150 product_id INT NOT NULL,
151 quantity INT NOT NULL,
152 price_at_time INT NOT NULL,
153 CONSTRAINT fk_cartitem_cart FOREIGN KEY (cart_id) REFERENCES Cart(cart_id) ON DELETE CASCADE ON UPDATE CASCADE,
154 CONSTRAINT fk_cartitem_product FOREIGN KEY (product_id) REFERENCES Product(product_id) ON DELETE RESTRICT ON UPDATE CASCADE
155);
156
157
158CREATE TYPE order_status AS ENUM ('PENDING','CONFIRMED','SHIPPED','DELIVERED','CANCELLED');
159
160CREATE TABLE "Order" (
161 order_id SERIAL PRIMARY KEY,
162 buyer_id INT NOT NULL,
163 cart_id INT,
164 total_price DECIMAL(10,2) NOT NULL,
165 status order_status NOT NULL DEFAULT 'PENDING',
166 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
167 CONSTRAINT fk_order_buyer FOREIGN KEY (buyer_id) REFERENCES AppUser(user_id) ON DELETE RESTRICT ON UPDATE CASCADE,
168 CONSTRAINT fk_order_cart FOREIGN KEY (cart_id) REFERENCES Cart(cart_id) ON DELETE SET NULL ON UPDATE CASCADE
169);
170
171
172CREATE TABLE OrderItems (
173 order_item_id SERIAL PRIMARY KEY,
174 order_id INT NOT NULL,
175 product_id INT NOT NULL,
176 seller_id INT NOT NULL,
177 price_at_time DECIMAL(10,2) NOT NULL,
178 quantity INT NOT NULL CHECK (quantity > 0),
179 CONSTRAINT fk_orderitem_product FOREIGN KEY (product_id) REFERENCES Product(product_id) ON DELETE RESTRICT ON UPDATE CASCADE,
180 CONSTRAINT fk_orderitem_order FOREIGN KEY (order_id) REFERENCES "Order"(order_id) ON DELETE CASCADE ON UPDATE CASCADE,
181 CONSTRAINT fk_orderitem_seller FOREIGN KEY (seller_id) REFERENCES AppUser(user_id) ON DELETE RESTRICT ON UPDATE CASCADE
182);
183
184
185
186CREATE TABLE Carriers (
187 carrier_id SERIAL PRIMARY KEY,
188 name VARCHAR(255) NOT NULL,
189 website VARCHAR(255),
190 contact_email VARCHAR(255) NOT NULL,
191 contact_phone VARCHAR(20) NOT NULL CHECK (contact_phone LIKE '+%'),
192 joined_at DATE NOT NULL,
193 is_active SMALLINT DEFAULT 1 NOT NULL CHECK (is_active IN (0,1))
194);
195
196
197
198CREATE TABLE Shipment (
199 shipment_id SERIAL PRIMARY KEY,
200 order_id INT NOT NULL,
201 carrier_id INT NOT NULL,
202 user_address_id INT NOT NULL,
203 tracking_number VARCHAR(255) NOT NULL UNIQUE,
204 status VARCHAR(255) NOT NULL CHECK (status IN ('PROCESSING','TRANSIT','DELIVERED')),
205 delivery_method VARCHAR(255) NOT NULL CHECK (delivery_method IN ('STANDARD','EXPRESS')),
206 estimated_delivery_date DATE NOT NULL,
207 actual_delivery_date DATE,
208 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
209 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
210 CONSTRAINT fk_shipment_address FOREIGN KEY (user_address_id) REFERENCES UserAddress(user_address_id) ON DELETE RESTRICT ON UPDATE CASCADE,
211 CONSTRAINT fk_shipment_order FOREIGN KEY (order_id) REFERENCES "Order"(order_id) ON DELETE CASCADE ON UPDATE CASCADE,
212 CONSTRAINT fk_shipment_carrier FOREIGN KEY (carrier_id) REFERENCES Carriers(carrier_id) ON DELETE RESTRICT ON UPDATE CASCADE
213);
214
215
216CREATE TABLE Payment (
217 payment_id SERIAL PRIMARY KEY,
218 user_id INT NOT NULL,
219 order_id INT NOT NULL,
220 package_id INT NOT NULL,
221 amount INT NOT NULL,
222 payment_method VARCHAR(255) NOT NULL CHECK (payment_method IN ('CARD','PAYPAL','CRYPTO','CASH')),
223 transaction_date DATE NOT NULL,
224 CONSTRAINT fk_payment_user FOREIGN KEY (user_id) REFERENCES AppUser(user_id) ON DELETE RESTRICT ON UPDATE CASCADE,
225 CONSTRAINT fk_payment_order FOREIGN KEY (order_id) REFERENCES "Order"(order_id) ON DELETE RESTRICT ON UPDATE CASCADE,
226 CONSTRAINT fk_payment_package FOREIGN KEY (package_id) REFERENCES UserPackages(user_package_id) ON DELETE RESTRICT ON UPDATE CASCADE
227);
228
229
230CREATE TABLE Transactions (
231 transaction_id SERIAL PRIMARY KEY,
232 payment_id INT NOT NULL,
233 seller_id INT NOT NULL,
234 amount INT NOT NULL,
235 status VARCHAR(255) NOT NULL CHECK (status IN ('PENDING','PROCESSING','COMPLETED','FAILED','CANCELLED')),
236 transaction_date DATE NOT NULL,
237 CONSTRAINT fk_transaction_payment FOREIGN KEY (payment_id) REFERENCES Payment(payment_id) ON DELETE RESTRICT ON UPDATE CASCADE,
238 CONSTRAINT fk_transaction_seller FOREIGN KEY (seller_id) REFERENCES AppUser(user_id) ON DELETE RESTRICT ON UPDATE CASCADE
239);
240
241
242
243CREATE TABLE Conversation (
244 conversation_id SERIAL PRIMARY KEY,
245 product_id INT NOT NULL,
246 buyer_id INT NOT NULL,
247 seller_id INT NOT NULL,
248 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
249
250 UNIQUE (product_id, buyer_id, seller_id),
251 CHECK (buyer_id <> seller_id),
252
253 CONSTRAINT fk_conversation_product FOREIGN KEY (product_id) REFERENCES Product(product_id) ON DELETE CASCADE ON UPDATE CASCADE,
254 CONSTRAINT fk_conversation_seller FOREIGN KEY (seller_id) REFERENCES AppUser(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
255 CONSTRAINT fk_conversation_buyer FOREIGN KEY (buyer_id) REFERENCES AppUser(user_id) ON DELETE CASCADE ON UPDATE CASCADE
256);
257
258
259CREATE TABLE Message (
260 message_id SERIAL PRIMARY KEY,
261 conversation_id INT NOT NULL,
262 sender_id INT NOT NULL,
263 text VARCHAR(255) NOT NULL,
264 send_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
265 seen SMALLINT NOT NULL CHECK (seen IN (0,1)),
266
267 CONSTRAINT fk_message_conversation FOREIGN KEY (conversation_id)
268 REFERENCES Conversation(conversation_id) ON DELETE CASCADE,
269 CONSTRAINT fk_message_sender FOREIGN KEY (sender_id)
270 REFERENCES AppUser(user_id) ON DELETE RESTRICT
271);
272
273
274CREATE TABLE Badge (
275 badge_id SERIAL PRIMARY KEY,
276 name VARCHAR(255) NOT NULL CHECK (name IN (
277 'NEW_SELLER','VERIFIED','TOP_SELLER','TRUSTED_SELLER','POPULAR',
278 'ACTIVE_USER','LOYAL_CUSTOMER',
279 'FIRST_SALE','HIGH_VOLUME',
280 'TOP_RATED','RECOMMENDED','PREMIUM_SELLER','GOLD_SELLER'
281 )),
282 description VARCHAR(255) NOT NULL,
283 icon_url VARCHAR(255) NOT NULL
284);
285
286CREATE TABLE UserBadge (
287 user_badge_id SERIAL PRIMARY KEY,
288 user_id INT NOT NULL,
289 badge_id INT NOT NULL,
290 awarded_at DATE NOT NULL,
291 CONSTRAINT fk_userbadge_user FOREIGN KEY (user_id)
292 REFERENCES AppUser(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
293 CONSTRAINT fk_userbadge_badge FOREIGN KEY (badge_id)
294 REFERENCES Badge(badge_id) ON DELETE RESTRICT ON UPDATE CASCADE
295);
296
297
298CREATE TABLE Review (
299 review_id SERIAL PRIMARY KEY,
300 product_id INT NOT NULL,
301 buyer_id INT NOT NULL,
302 seller_id INT NOT NULL,
303 rating INT NOT NULL CHECK (rating > 0 AND rating < 6),
304 comment VARCHAR(255) NOT NULL,
305 created_at DATE NOT NULL,
306 CONSTRAINT fk_review_product FOREIGN KEY (product_id)
307 REFERENCES Product(product_id) ON DELETE CASCADE ON UPDATE CASCADE,
308 CONSTRAINT fk_review_seller FOREIGN KEY (seller_id)
309 REFERENCES AppUser(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
310 CONSTRAINT fk_review_buyer FOREIGN KEY (buyer_id)
311 REFERENCES AppUser(user_id) ON DELETE SET NULL ON UPDATE CASCADE
312);
313
314
315CREATE TABLE SavedSearches (
316 search_id SERIAL PRIMARY KEY,
317 user_id INT NOT NULL,
318 category_id INT NOT NULL,
319 min_price INT NOT NULL,
320 max_price INT NOT NULL,
321 location VARCHAR(255) NOT NULL,
322 filters VARCHAR(255),
323 CONSTRAINT fk_savedsearch_user FOREIGN KEY (user_id)
324 REFERENCES AppUser(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
325 CONSTRAINT fk_savedsearch_category FOREIGN KEY (category_id)
326 REFERENCES Category(category_id) ON DELETE CASCADE ON UPDATE CASCADE
327);
328
329
330CREATE TABLE SavedSearchesAttributes (
331 saved_search_attribute_id SERIAL PRIMARY KEY,
332 saved_search_id INT NOT NULL,
333 category_attribute_id INT NOT NULL,
334 attribute_value VARCHAR(255) NOT NULL,
335 CONSTRAINT fk_savedsearchattr_savedsearch FOREIGN KEY (saved_search_id)
336 REFERENCES SavedSearches(search_id) ON DELETE CASCADE ON UPDATE CASCADE,
337 CONSTRAINT fk_savedsearchattr_categoryattr FOREIGN KEY (category_attribute_id)
338 REFERENCES CategoryAttributes(category_attribute_id) ON DELETE CASCADE ON UPDATE CASCADE
339);
340
341
342
343CREATE TABLE Notifications (
344 notification_id SERIAL PRIMARY KEY,
345 user_id INT NOT NULL,
346 type VARCHAR(255) NOT NULL,
347 title VARCHAR(255) NOT NULL,
348 message VARCHAR(255) NOT NULL,
349 favorite_id INT NOT NULL,
350 saved_search_id INT NOT NULL,
351 message_id INT,
352 order_id INT NOT NULL,
353 CONSTRAINT fk_notifications_order FOREIGN KEY (order_id)
354 REFERENCES "Order"(order_id) ON DELETE CASCADE ON UPDATE CASCADE,
355 CONSTRAINT fk_notifications_favorite FOREIGN KEY (favorite_id)
356 REFERENCES Favorites(favorite_id) ON DELETE CASCADE ON UPDATE CASCADE,
357 CONSTRAINT fk_notifications_savedsearch FOREIGN KEY (saved_search_id)
358 REFERENCES SavedSearches(search_id) ON DELETE CASCADE ON UPDATE CASCADE,
359 CONSTRAINT fk_notifications_user FOREIGN KEY (user_id)
360 REFERENCES AppUser(user_id) ON DELETE CASCADE ON UPDATE CASCADE
361);
362
363
364
365
366CREATE TABLE IF NOT EXISTS Country (
367 country_id SERIAL PRIMARY KEY,
368 country_name VARCHAR(255) NOT NULL,
369 country_code VARCHAR(4) NOT NULL CHECK (country_code LIKE '^\+\d{1,3}$')
370);
371
372
373ALTER TABLE AppUser
374ADD COLUMN country_id INT;
375
376
377
378ALTER TABLE AppUser
379ADD CONSTRAINT fk_user_country
380FOREIGN KEY (country_id)
381REFERENCES Country(country_id);
382
383
384
385
386
387