· 6 years ago · Dec 08, 2019, 06:29 PM
1--
2-- WMS DB INIT SCRIPT
3--
4
5CREATE DATABASE IF NOT EXISTS `wms` CHARACTER SET utf8 COLLATE utf8_polish_ci;
6USE `wms`;
7SET FOREIGN_KEY_CHECKS=0;
8
9
10DROP TABLE IF EXISTS `warehouse`;
11CREATE TABLE `warehouse` (
12 warehouse_id INT(11) NOT NULL AUTO_INCREMENT,
13 warehouse_owner_id INT(11) NOT NULL,
14 warehouse_created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
15 warehouse_name VARCHAR(40) NOT NULL,
16 warehouse_description VARCHAR(255),
17 warehouse_status TINYINT NOT NULL DEFAULT 1,
18 warehouse_payment_status TINYINT NOT NULL DEFAULT 1,
19 CONSTRAINT warehouse_id_pk PRIMARY KEY(warehouse_id)
20) ENGINE=InnoDB AUTO_INCREMENT=1;
21
22DROP TABLE IF EXISTS `user`;
23CREATE TABLE `user` (
24 user_id INT(11) NOT NULL AUTO_INCREMENT,
25 user_warehouse_id INT(11),
26 user_role_id INT(11) NOT NULL,
27 user_surname VARCHAR(30),
28 user_name VARCHAR(20),
29 user_employment_date TIMESTAMP,
30 user_position VARCHAR(20),
31 user_salary INT(11),
32 user_login VARCHAR(20) NOT NULL,
33 user_email VARCHAR(30),
34 user_password VARCHAR(255) NOT NULL,
35 CONSTRAINT user_id_pk PRIMARY KEY(user_id)
36) ENGINE=InnoDB AUTO_INCREMENT=1;
37
38DROP TABLE IF EXISTS `user_role`;
39CREATE TABLE `user_role` (
40 ur_id INT(11) NOT NULL AUTO_INCREMENT,
41 ur_name VARCHAR (20),
42 CONSTRAINT ur_id_pk PRIMARY KEY(ur_id)
43) ENGINE=InnoDB AUTO_INCREMENT=1;
44
45DROP TABLE IF EXISTS `client`;
46CREATE TABLE `client` (
47 client_id INT(11) NOT NULL AUTO_INCREMENT,
48 client_warehouse_id INT(11) NOT NULL,
49 client_nip INT(10),
50 client_name VARCHAR(40) NOT NULL,
51 client_address VARCHAR(30),
52 client_phone VARCHAR(12),
53 client_email VARCHAR(15),
54 client_contact_person VARCHAR(25),
55 CONSTRAINT client_id_pk PRIMARY KEY(client_id)
56) ENGINE=InnoDB AUTO_INCREMENT=1;
57
58DROP TABLE IF EXISTS `product`;
59CREATE TABLE `product` (
60 product_id INT(11) NOT NULL AUTO_INCREMENT,
61 product_warehouse_id INT(11) NOT NULL,
62 product_deliverer_id INT(11) NOT NULL,
63 product_code INT(10) NOT NULL,
64 product_name VARCHAR(50) NOT NULL,
65 product_brand VARCHAR(50) NOT NULL,
66 product_category VARCHAR(15) NOT NULL,
67 product_taxcat_id INT(11) NOT NULL,
68 product_buy_nett_price INT(11) NOT NULL,
69 product_sell_nett_price INT(11) NOT NULL,
70 product_desc VARCHAR(255),
71 product_status TINYINT NOT NULL DEFAULT 1,
72 product_unit VARCHAR(4),
73 product_optimal_quant INT(11) NOT NULL,
74 product_minimal_quant INT(3) NOT NULL DEFAULT 60,
75 CONSTRAINT product_id_pk PRIMARY KEY(product_id)
76) ENGINE=InnoDB AUTO_INCREMENT=1;
77
78DROP TABLE IF EXISTS `order`;
79CREATE TABLE `order` (
80 order_id INT(11) NOT NULL AUTO_INCREMENT,
81 order_warehouse_id INT(11) NOT NULL,
82 order_client_id INT(11) NOT NULL,
83 order_number VARCHAR(10) NOT NULL,
84 order_created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
85 order_annotation VARCHAR(30),
86 order_status TINYINT NOT NULL DEFAULT 1,
87 CONSTRAINT order_id_pk PRIMARY KEY(order_id)
88) ENGINE=InnoDB AUTO_INCREMENT=1;
89
90DROP TABLE IF EXISTS `ordered_products`;
91CREATE TABLE `ordered_products` (
92 op_product_id INT(11) NOT NULL,
93 op_order_id INT(11) NOT NULL,
94 op_amount INT(11)
95) ENGINE=InnoDB AUTO_INCREMENT=1;
96
97DROP TABLE IF EXISTS `warehouse_content`;
98CREATE TABLE `warehouse_content` (
99 wc_warehouse_id INT(11) NOT NULL,
100 wc_product_id INT(11) NOT NULL,
101 wc_available INT(11) NOT NULL,
102 wc_reserved INT(11) NOT NULL
103) ENGINE=InnoDB AUTO_INCREMENT=1;
104
105DROP TABLE IF EXISTS `delivery`;
106CREATE TABLE `delivery` (
107 delivery_id INT(11) NOT NULL AUTO_INCREMENT,
108 delivery_warehouse_id INT(11) NOT NULL,
109 delivery_deliverer_id INT(11) NOT NULL,
110 delivery_number VARCHAR(20) NOT NULL,
111 delivery_date TIMESTAMP NOT NULL,
112 delivery_status TINYINT NOT NULL DEFAULT 1,
113 CONSTRAINT delivery_id_pk PRIMARY KEY(delivery_id)
114) ENGINE=InnoDB AUTO_INCREMENT=1;
115
116DROP TABLE IF EXISTS `deliverer`;
117CREATE TABLE `deliverer` (
118 deliverer_id INT(11) NOT NULL,
119 deliverer_warehouse_id INT(11) NOT NULL,
120 deliverer_nip INT(10) NOT NULL,
121 deliverer_name VARCHAR(40) NOT NULL,
122 deliverer_address VARCHAR(30),
123 deliverer_phone VARCHAR(12),
124 deliverer_email VARCHAR(30),
125 deliverer_page VARCHAR(40),
126 deliverer_contact_person VARCHAR(40),
127 CONSTRAINT deliverer_id_pk PRIMARY KEY(deliverer_id)
128) ENGINE=InnoDB AUTO_INCREMENT=1;
129
130DROP TABLE IF EXISTS `delivery_products`;
131CREATE TABLE `delivery_products` (
132 dp_delivery_id INT(11) NOT NULL,
133 dp_product_id INT(11) NOT NULL,
134 dp_quantity INT(11) NOT NULL,
135 dp_value INT(11),
136 dp_order_id INT(11)
137) ENGINE=InnoDB AUTO_INCREMENT=1;
138
139DROP TABLE IF EXISTS `taxcat`;
140CREATE TABLE `taxcat` (
141 taxcat_id INT(11) NOT NULL AUTO_INCREMENT,
142 taxcat_name VARCHAR(10) NOT NULL,
143 taxcat_tax INT(2) NOT NULL,
144 CONSTRAINT taxcat_id_pk PRIMARY KEY(taxcat_id)
145) ENGINE=InnoDB AUTO_INCREMENT=1;
146
147
148ALTER TABLE user
149 ADD CONSTRAINT user_warehouse_fk
150 FOREIGN KEY (user_warehouse_id)
151 REFERENCES warehouse(warehouse_id);
152
153ALTER TABLE user
154 ADD CONSTRAINT user_role_fk
155 FOREIGN KEY (user_role_id)
156 REFERENCES user_role(ur_id);
157
158ALTER TABLE warehouse
159 ADD CONSTRAINT warehouse_user_fk
160 FOREIGN KEY (warehouse_owner_id)
161 REFERENCES user(user_id);
162
163ALTER TABLE client
164 ADD CONSTRAINT client_warehouse_fk
165 FOREIGN KEY (client_warehouse_id)
166 REFERENCES warehouse(warehouse_id);
167
168ALTER TABLE product
169 ADD CONSTRAINT product_warehouse_fk
170 FOREIGN KEY (product_warehouse_id)
171 REFERENCES warehouse(warehouse_id);
172
173ALTER TABLE product
174 ADD CONSTRAINT product_deliverer_fk
175 FOREIGN KEY (product_deliverer_id)
176 REFERENCES deliverer(deliverer_id);
177
178ALTER TABLE product
179 ADD CONSTRAINT product_taxcat_fk
180 FOREIGN KEY (product_taxcat_id)
181 REFERENCES taxcat(taxcat_id);
182
183ALTER TABLE `order`
184 ADD CONSTRAINT order_warehouse_fk
185 FOREIGN KEY (order_warehouse_id)
186 REFERENCES warehouse(warehouse_id);
187
188ALTER TABLE `order`
189 ADD CONSTRAINT order_client_fk
190 FOREIGN KEY (order_client_id)
191 REFERENCES CLIENT(client_id);
192
193ALTER TABLE ordered_products
194 ADD CONSTRAINT op_product_pk
195 FOREIGN KEY (op_product_id)
196 REFERENCES product(product_id);
197
198ALTER TABLE ordered_products
199 ADD CONSTRAINT op_order_fk
200 FOREIGN KEY (op_order_id)
201 REFERENCES `order`(order_id);
202
203ALTER TABLE warehouse_content
204 ADD CONSTRAINT wc_warehouse_fk
205 FOREIGN KEY (wc_warehouse_id)
206 REFERENCES warehouse(warehouse_id);
207
208ALTER TABLE warehouse_content
209 ADD CONSTRAINT wc_product_fk
210 FOREIGN KEY (wc_product_id)
211 REFERENCES product(product_id);
212
213ALTER TABLE deliverer
214 ADD CONSTRAINT deliverer_warehouse_fk
215 FOREIGN KEY (deliverer_id)
216 REFERENCES warehouse(warehouse_id);
217
218ALTER TABLE delivery_products
219 ADD CONSTRAINT dp_delivery_fk
220 FOREIGN KEY (dp_delivery_id)
221 REFERENCES delivery(delivery_id);
222
223ALTER TABLE delivery_products
224 ADD CONSTRAINT dp_product_fk
225 FOREIGN KEY (dp_product_id)
226 REFERENCES product(product_id);
227
228ALTER TABLE delivery_products
229 ADD CONSTRAINT dp_order_fk
230 FOREIGN KEY (dp_order_id)
231 REFERENCES `order`(order_id);
232
233ALTER TABLE delivery
234 ADD CONSTRAINT delivery_warehouse_fk
235 FOREIGN KEY (delivery_warehouse_id)
236 REFERENCES warehouse(warehouse_id);
237
238ALTER TABLE delivery
239 ADD CONSTRAINT delivery_deliverer_fk
240 FOREIGN KEY (delivery_deliverer_id)
241 REFERENCES deliverer(deliverer_id);