7ACvmLu4

1SET FOREIGN_KEY_CHECKS=0;
2SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
3SET AUTOCOMMIT = 0;
4START TRANSACTION;
5SET time_zone = "+00:00";
6
7DROP TABLE IF EXISTS `category`;
8DROP TABLE IF EXISTS `items`;
9DROP TABLE IF EXISTS `products`;
10DROP TABLE IF EXISTS `products_suppliers`;
11DROP TABLE IF EXISTS `vat`;
12DROP TABLE IF EXISTS `suppliers`;
13
14CREATE TABLE `category` (
15  `id` int(11) NOT NULL,
16  `name` varchar(255) NOT NULL
17) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
18
19CREATE TABLE `items` (
20  `id` int(11) NOT NULL,
21  `tag` varchar(64) NOT NULL,
22  `amount` float NOT NULL,
23  `state` enum('WAREHOUSE','SHELF','BASKET','CHECKOUT','SOLD','STOLEN') NOT NULL,
24  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
25  `payload` text NOT NULL,
26  `product_id` int(11) NOT NULL
27) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
28
29CREATE TABLE `products` (
30  `id` int(11) NOT NULL,
31  `name` varchar(255) NOT NULL,
32  `sku` varchar(100) NOT NULL,
33  `net_price` float NOT NULL,
34  `unit` varchar(10) NOT NULL,
35  `minimum_amount` int(11) NOT NULL,
36  `minimum_order` int(11) NOT NULL,
37  `vat_id` int(11) NOT NULL,
38  `category_id` int(11) DEFAULT NULL
39) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
40
41CREATE TABLE `products_suppliers` (
42  `id` int(11) NOT NULL,
43  `product_id` int(11) NOT NULL,
44  `supplier_id` int(11) NOT NULL
45) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
46
47CREATE TABLE `suppliers` (
48  `id` int(11) NOT NULL,
49  `name` varchar(255) NOT NULL,
50  `email` varchar(255) NOT NULL
51) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
52
53CREATE TABLE `vat` (
54  `id` int(11) NOT NULL,
55  `rate` float NOT NULL,
56  `name` varchar(255) NOT NULL
57) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
58
59
60ALTER TABLE `category`
61  ADD PRIMARY KEY (`id`);
62
63ALTER TABLE `items`
64  ADD PRIMARY KEY (`id`),
65  ADD UNIQUE KEY `tag` (`tag`),
66  ADD KEY `fk_tag_product` (`product_id`);
67
68ALTER TABLE `products`
69  ADD PRIMARY KEY (`id`),
70  ADD UNIQUE KEY `sku` (`sku`),
71  ADD KEY `fk_category` (`category_id`),
72  ADD KEY `fk_vat` (`vat_id`);
73
74ALTER TABLE `products_suppliers`
75  ADD PRIMARY KEY (`id`),
76  ADD KEY `fk_product` (`product_id`),
77  ADD KEY `fk_supplier` (`supplier_id`);
78
79ALTER TABLE `suppliers`
80  ADD PRIMARY KEY (`id`);
81
82ALTER TABLE `vat`
83  ADD PRIMARY KEY (`id`);
84
85
86ALTER TABLE `category`
87  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
88
89ALTER TABLE `items`
90  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
91
92ALTER TABLE `products`
93  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
94
95ALTER TABLE `products_suppliers`
96  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
97
98ALTER TABLE `suppliers`
99  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
100
101ALTER TABLE `vat`
102  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
103
104
105ALTER TABLE `items`
106  ADD CONSTRAINT `fk_tag_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON UPDATE CASCADE;
107
108ALTER TABLE `products`
109  ADD CONSTRAINT `fk_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
110  ADD CONSTRAINT `fk_vat` FOREIGN KEY (`vat_id`) REFERENCES `vat` (`id`) ON UPDATE CASCADE;
111
112ALTER TABLE `products_suppliers`
113  ADD CONSTRAINT `fk_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON UPDATE CASCADE,
114  ADD CONSTRAINT `fk_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON UPDATE CASCADE;
115SET FOREIGN_KEY_CHECKS=1;
116COMMIT;