DgeMEvjq

1CREATE DATABASE IF NOT EXISTS app;
2
3USE app;
4
5CREATE TABLE sellers
6(
7  seller_id   INT   NOT NULL  AUTO_INCREMENT,
8  seller_name  VARCHAR(255)  NOT NULL,
9  PRIMARY KEY (seller_id)
10)   ENGINE = InnoDB;
11
12CREATE TABLE products
13(
14  product_id  INT  NOT NULL  AUTO_INCREMENT,
15  product_name  VARCHAR(255)  NOT NULL,
16  seller_id   INT  NOT NULL,
17  product_price  DECIMAL  NOT NULL,
18  PRIMARY KEY (product_id),
19  INDEX (product_name),
20  FOREIGN KEY (seller_id) REFERENCES sellers(seller_id)
21)   ENGINE = InnoDB;
22
23CREATE TABLE inventory
24(
25  inventory_id  INT   NOT NULL  AUTO_INCREMENT,
26  product_id  INT   NOT NULL,
27  inventory_available   INT   NOT NULL,
28  PRIMARY KEY (inventory_id),
29  FOREIGN KEY (product_id) REFERENCES products(product_id)
30)   ENGINE = InnoDB;
31
32CREATE TABLE discounts
33(
34  discount_id  INT  NOT NULL  AUTO_INCREMENT,
35  product_id  INT  NOT NULL,
36  discount_amount  INT  NOT NULL,
37  discount_type  ENUM('VALUE','PERCENT')  DEFAULT 'PERCENT',
38  PRIMARY KEY (discount_id),
39  FOREIGN KEY (product_id) REFERENCES products(product_id)
40)   ENGINE = InnoDB;
41
42CREATE TABLE customers
43(
44  customer_id  INT  NOT NULL  AUTO_INCREMENT,
45  customer_name VARCHAR(255)  NOT NULL,
46  customer_surname  VARCHAR(255)  NOT NULL,
47  PRIMARY KEY (customer_id)
48)   ENGINE = InnoDB;
49
50CREATE TABLE cards
51(
52  card_id  INT  NOT NULL  AUTO_INCREMENT,
53  card_token  VARCHAR(255)  NOT NULL,
54  PRIMARY KEY (card_id),
55  INDEX (card_token)
56)   ENGINE = InnoDB;
57
58CREATE TABLE declined_cards
59(
60  declined_id  INT  NOT NULL  AUTO_INCREMENT,
61  card_id  INT  NOT NULL,
62  declined_time  TIMESTAMP  DEFAULT  CURRENT_TIMESTAMP,
63  PRIMARY KEY (declined_id),
64  FOREIGN KEY (card_id) REFERENCES cards(card_id)
65)   ENGINE = InnoDB;
66
67
68CREATE TABLE checkouts
69(
70  checkout_id  INT  NOT NULL  AUTO_INCREMENT,
71  customer_id  INT  NOT NULLs,
72  card_id  INT  NOT NULL,
73  checkout_date  NOT NULL  TIMESTAMP  DEFAULT  CURRENT_TIMESTAMP,
74  PRIMARY KEY (checkout_id),
75  FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
76  FOREIGN KEY (card_id) REFERENCES cards(card_id)
77)   ENGINE = InnoDB;
78
79CREATE TABLE purchases
80(
81  purchase_id  INT  NOT NULL  AUTO_INCREMENT,
82  product_id  INT  NOT NULL,
83  checkout_id  INT  NOT NULL,
84  discount_id  INT  NOT NULL,
85  PRIMARY KEY (purchase_id),
86  FOREIGN KEY (product_id) REFERENCES products(product_id),
87  FOREIGN KEY (checkout_id) REFERENCES checkouts(checkout_id),
88  FOREIGN KEY (discount_id) REFERENCES discounts(discount_id)
89)   ENGINE = InnoDB;