DatabaseCreation: init.sql

File init.sql, 15.4 KB (added by 231170, 5 days ago)
Line 
1CREATE TABLE BRANDS
2(
3 id BIGSERIAL PRIMARY KEY,
4 name VARCHAR(63) NOT NULL,
5 CONSTRAINT UQ_BRANDS_NAME UNIQUE (name),
6 CONSTRAINT CK_BRANDS_NAME_NOT_EMPTY CHECK (btrim(name) <> '')
7);
8
9CREATE TABLE PRODUCTS
10(
11 id BIGSERIAL PRIMARY KEY,
12 name VARCHAR(63) NOT NULL,
13 description TEXT,
14 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
15 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
16 CONSTRAINT CK_PRODUCTS_NAME_NOT_EMPTY CHECK (btrim(name) <> '')
17);
18
19CREATE TABLE PRODUCT_VARIANTS
20(
21 id BIGSERIAL PRIMARY KEY,
22 product_id BIGINT NOT NULL,
23 sku VARCHAR(63) NOT NULL,
24 brand_id BIGINT NOT NULL,
25 barcode VARCHAR(63),
26 price NUMERIC(10, 2) NOT NULL,
27 weight NUMERIC(10, 2),
28 status VARCHAR(50) NOT NULL,
29 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
30 CONSTRAINT FK_PRODUCT_VARIANTS_PRODUCT FOREIGN KEY (product_id) REFERENCES PRODUCTS (id) ON DELETE RESTRICT,
31 CONSTRAINT UQ_PRODUCT_VARIANTS_SKU UNIQUE (sku),
32 CONSTRAINT FK_PRODUCT_VARIANTS_BRAND FOREIGN KEY (brand_id) REFERENCES BRANDS (id) ON DELETE RESTRICT,
33 CONSTRAINT UQ_PRODUCT_VARIANTS_BARCODE UNIQUE (barcode),
34 CONSTRAINT CK_PRODUCT_VARIANTS_SKU_NOT_EMPTY CHECK (btrim(sku) <> ''),
35 CONSTRAINT CK_PRODUCT_VARIANTS_BARCODE_NOT_EMPTY CHECK (barcode IS NULL OR btrim(barcode) <> ''),
36 CONSTRAINT CK_PRODUCT_VARIANTS_PRICE_NONNEGATIVE CHECK (price >= 0),
37 CONSTRAINT CK_PRODUCT_VARIANTS_WEIGHT_NONNEGATIVE CHECK (weight IS NULL OR weight >= 0),
38 CONSTRAINT CK_PRODUCT_VARIANTS_STATUS_NOT_EMPTY CHECK (btrim(status) <> '')
39);
40
41--we don't want to keep images for products that don't exist
42CREATE TABLE PRODUCT_IMAGES
43(
44 id BIGSERIAL PRIMARY KEY,
45 url TEXT NOT NULL,
46 position INT NOT NULL,
47 product_variants_id BIGINT NOT NULL,
48 CONSTRAINT FK_PRODUCT_IMAGES_VARIANT FOREIGN KEY (product_variants_id) REFERENCES PRODUCT_VARIANTS (id) ON DELETE CASCADE,
49 CONSTRAINT UQ_PRODUCT_IMAGES_VARIANT_POSITION UNIQUE (product_variants_id, position),
50 CONSTRAINT CK_PRODUCT_IMAGES_URL_NOT_EMPTY CHECK (btrim(url) <> ''),
51 CONSTRAINT CK_PRODUCT_IMAGES_POSITION_POSITIVE CHECK (position > 0)
52);
53
54CREATE TABLE CATEGORIES
55(
56 id BIGSERIAL PRIMARY KEY,
57 name VARCHAR(63) NOT NULL,
58 parent_id BIGINT,
59 CONSTRAINT FK_CATEGORIES_PARENT FOREIGN KEY (parent_id) REFERENCES CATEGORIES (id) ON DELETE SET NULL,
60 CONSTRAINT UQ_CATEGORIES_PARENT_NAME UNIQUE (parent_id, name),
61 CONSTRAINT CK_CATEGORIES_NAME_NOT_EMPTY CHECK (btrim(name) <> ''),
62 CONSTRAINT CK_CATEGORIES_NOT_OWN_PARENT CHECK (parent_id IS NULL OR parent_id <> id)
63);
64
65
66--Junction tables have no meaning without either foreign keys
67CREATE TABLE PRODUCT_CATEGORIES
68(
69 product_id BIGINT NOT NULL,
70 category_id BIGINT NOT NULL,
71 PRIMARY KEY (product_id, category_id),
72 CONSTRAINT FK_PRODUCT_CATEGORIES_PRODUCT FOREIGN KEY (product_id) REFERENCES PRODUCTS (id) ON DELETE CASCADE,
73 CONSTRAINT FK_PRODUCT_CATEGORIES_CATEGORY FOREIGN KEY (category_id) REFERENCES CATEGORIES (id) ON DELETE CASCADE
74);
75
76CREATE TABLE ATTRIBUTES
77(
78 id BIGSERIAL PRIMARY KEY,
79 name VARCHAR(63) NOT NULL,
80 data_type VARCHAR(50) NOT NULL,
81 unit VARCHAR(50),
82 is_variant_attribute BOOLEAN NOT NULL DEFAULT FALSE,
83 CONSTRAINT UQ_ATTRIBUTES_NAME UNIQUE (name),
84 CONSTRAINT CK_ATTRIBUTES_NAME_NOT_EMPTY CHECK (btrim(name) <> ''),
85 CONSTRAINT CK_ATTRIBUTES_DATA_TYPE_NOT_EMPTY CHECK (btrim(data_type) <> '')
86);
87
88CREATE TABLE ATTRIBUTE_VALUES
89(
90 id BIGSERIAL PRIMARY KEY,
91 attribute_id BIGINT NOT NULL,
92 value VARCHAR(63) NOT NULL,
93 CONSTRAINT FK_ATTRIBUTE_VALUES_ATTRIBUTE FOREIGN KEY (attribute_id) REFERENCES ATTRIBUTES (id) ON DELETE RESTRICT,
94 CONSTRAINT UQ_ATTRIBUTE_VALUES_ATTRIBUTE_VALUE UNIQUE (attribute_id, value),
95 CONSTRAINT UQ_ATTRIBUTE_VALUES_ATTRIBUTE_ID_ID UNIQUE (attribute_id, id),
96 CONSTRAINT CK_ATTRIBUTE_VALUES_VALUE_NOT_EMPTY CHECK (btrim(value) <> '')
97);
98
99
100--Deleting a variant should delete its attribute assignments
101--But deleting an attribute definition that is already used should be blocked
102CREATE TABLE VARIANT_ATTRIBUTES
103(
104 product_variant_id BIGINT NOT NULL,
105 attribute_value_id BIGINT NOT NULL,
106 attribute_id BIGINT NOT NULL,
107 PRIMARY KEY (product_variant_id, attribute_value_id, attribute_id),
108 CONSTRAINT FK_VARIANT_ATTRIBUTES_VARIANT FOREIGN KEY (product_variant_id) REFERENCES PRODUCT_VARIANTS (id) ON DELETE CASCADE,
109 CONSTRAINT FK_VARIANT_ATTRIBUTES_ATTRIBUTE FOREIGN KEY (attribute_id) REFERENCES ATTRIBUTES (id) ON DELETE RESTRICT,
110 CONSTRAINT FK_VARIANT_ATTRIBUTES_ATTRIBUTE_VALUE FOREIGN KEY (attribute_id, attribute_value_id) REFERENCES ATTRIBUTE_VALUES (attribute_id, id) ON DELETE RESTRICT
111);
112
113CREATE TABLE WAREHOUSES
114(
115 id BIGSERIAL PRIMARY KEY,
116 name VARCHAR(63) NOT NULL,
117 address TEXT,
118 city VARCHAR(63) NOT NULL,
119 country VARCHAR(63) NOT NULL,
120 CONSTRAINT UQ_WAREHOUSES_NAME UNIQUE (name),
121 CONSTRAINT CK_WAREHOUSES_NAME_NOT_EMPTY CHECK (btrim(name) <> ''),
122 CONSTRAINT CK_WAREHOUSES_CITY_NOT_EMPTY CHECK (btrim(city) <> ''),
123 CONSTRAINT CK_WAREHOUSES_COUNTRY_NOT_EMPTY CHECK (btrim(country) <> '')
124);
125
126CREATE TABLE SECTIONS
127(
128 id BIGSERIAL PRIMARY KEY,
129 warehouse_id BIGINT NOT NULL,
130 name VARCHAR(63) NOT NULL,
131 description TEXT,
132 CONSTRAINT FK_SECTIONS_WAREHOUSE FOREIGN KEY (warehouse_id) REFERENCES WAREHOUSES (id) ON DELETE RESTRICT,
133 CONSTRAINT UQ_SECTIONS_WAREHOUSE_NAME UNIQUE (warehouse_id, name),
134 CONSTRAINT CK_SECTIONS_NAME_NOT_EMPTY CHECK (btrim(name) <> '')
135);
136
137CREATE TABLE LOCATIONS
138(
139 id BIGSERIAL PRIMARY KEY,
140 section_id BIGINT NOT NULL,
141 row_number INT NOT NULL,
142 column_number INT NOT NULL,
143 level_number INT NOT NULL,
144 location_code VARCHAR(63) NOT NULL,
145 CONSTRAINT FK_LOCATIONS_SECTION FOREIGN KEY (section_id) REFERENCES SECTIONS (id) ON DELETE RESTRICT,
146 CONSTRAINT UQ_LOCATIONS_CODE UNIQUE (location_code),
147 CONSTRAINT UQ_LOCATIONS_POSITION UNIQUE (section_id, row_number, column_number, level_number),
148 CONSTRAINT CK_LOCATIONS_ROW_POSITIVE CHECK (row_number > 0),
149 CONSTRAINT CK_LOCATIONS_COLUMN_POSITIVE CHECK (column_number > 0),
150 CONSTRAINT CK_LOCATIONS_LEVEL_POSITIVE CHECK (level_number > 0),
151 CONSTRAINT CK_LOCATIONS_CODE_NOT_EMPTY CHECK (btrim(location_code) <> '')
152);
153
154CREATE TABLE BINS
155(
156 id BIGSERIAL PRIMARY KEY,
157 location_id BIGINT NOT NULL,
158 bin_code VARCHAR(63) NOT NULL,
159 capacity INT NOT NULL,
160 CONSTRAINT FK_BINS_LOCATION FOREIGN KEY (location_id) REFERENCES LOCATIONS (id) ON DELETE RESTRICT,
161 CONSTRAINT UQ_BINS_CODE UNIQUE (bin_code),
162 CONSTRAINT UQ_BINS_LOCATION UNIQUE (location_id, bin_code),
163 CONSTRAINT CK_BINS_CODE_NOT_EMPTY CHECK (btrim(bin_code) <> ''),
164 CONSTRAINT CK_BINS_CAPACITY_POSITIVE CHECK (capacity > 0)
165);
166
167CREATE TABLE INVENTORY
168(
169 id BIGSERIAL PRIMARY KEY,
170 product_variant_id BIGINT NOT NULL,
171 bin_id BIGINT NOT NULL,
172 quantity INT NOT NULL DEFAULT 0,
173 reserved_quantity INT NOT NULL DEFAULT 0,
174 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
175 status TEXT NOT NULL,
176 CONSTRAINT FK_INVENTORY_VARIANT FOREIGN KEY (product_variant_id) REFERENCES PRODUCT_VARIANTS (id) ON DELETE RESTRICT,
177 CONSTRAINT FK_INVENTORY_BIN FOREIGN KEY (bin_id) REFERENCES BINS (id) ON DELETE RESTRICT,
178 CONSTRAINT UQ_INVENTORY_VARIANT_BIN UNIQUE (product_variant_id, bin_id),
179 CONSTRAINT CK_INVENTORY_QUANTITY_NONNEGATIVE CHECK (quantity >= 0),
180 CONSTRAINT CK_INVENTORY_RESERVED_NONNEGATIVE CHECK (reserved_quantity >= 0),
181 CONSTRAINT CK_INVENTORY_RESERVED_WITHIN_QUANTITY CHECK (reserved_quantity <= quantity),
182 CONSTRAINT CK_INVENTORY_STATUS_NOT_EMPTY CHECK (btrim(status) <> '')
183);
184
185
186
187CREATE TABLE EMPLOYEES
188(
189 id BIGSERIAL PRIMARY KEY,
190 employee_number VARCHAR(63) NOT NULL,
191 first_name VARCHAR(63) NOT NULL,
192 last_name VARCHAR(63) NOT NULL,
193 email VARCHAR(63) NOT NULL,
194 phone VARCHAR(63),
195 job_title VARCHAR(63) NOT NULL,
196 employment_status VARCHAR(63) NOT NULL,
197 hired_at TIMESTAMP NOT NULL,
198 terminated_at TIMESTAMP,
199 manager_id BIGINT,
200 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
201 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
202 CONSTRAINT FK_EMPLOYEES_MANAGER FOREIGN KEY (manager_id) REFERENCES EMPLOYEES (id) ON DELETE SET NULL,
203 CONSTRAINT UQ_EMPLOYEES_EMPLOYEE_NUMBER UNIQUE (employee_number),
204 CONSTRAINT UQ_EMPLOYEES_EMAIL UNIQUE (email),
205 CONSTRAINT CK_EMPLOYEES_EMPLOYEE_NUMBER_NOT_EMPTY CHECK (btrim(employee_number) <> ''),
206 CONSTRAINT CK_EMPLOYEES_FIRST_NAME_NOT_EMPTY CHECK (btrim(first_name) <> ''),
207 CONSTRAINT CK_EMPLOYEES_LAST_NAME_NOT_EMPTY CHECK (btrim(last_name) <> ''),
208 CONSTRAINT CK_EMPLOYEES_EMAIL_FORMAT CHECK (email ~* '^[^@[:space:]]+@[^@[:space:]]+\.[^@[:space:]]+$'
209 ),
210 CONSTRAINT CK_EMPLOYEES_JOB_TITLE_NOT_EMPTY CHECK (btrim(job_title) <> ''),
211 CONSTRAINT CK_EMPLOYEES_STATUS_NOT_EMPTY CHECK (btrim(employment_status) <> ''),
212 CONSTRAINT CK_EMPLOYEES_TERM_AFTER_HIRE CHECK (terminated_at IS NULL OR terminated_at >= hired_at)
213);
214
215CREATE TABLE EMPLOYEE_WAREHOUSE_ASSIGNMENTS
216(
217 id BIGSERIAL PRIMARY KEY,
218 employee_id BIGINT NOT NULL,
219 warehouse_id BIGINT NOT NULL,
220 start_date DATE NOT NULL,
221 end_date DATE,
222 is_primary BOOLEAN NOT NULL DEFAULT FALSE,
223 notes TEXT,
224 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
225 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
226 CONSTRAINT FK_EMPLOYEE_ASSIGNMENTS_EMPLOYEE FOREIGN KEY (employee_id) REFERENCES EMPLOYEES (id) ON DELETE RESTRICT,
227 CONSTRAINT FK_EMPLOYEE_ASSIGNMENTS_WAREHOUSE FOREIGN KEY (warehouse_id) REFERENCES WAREHOUSES (id) ON DELETE RESTRICT,
228 CONSTRAINT UQ_EMPLOYEE_ASSIGNMENTS_UNIQUE_PERIOD UNIQUE (employee_id, warehouse_id, start_date),
229 CONSTRAINT CK_EMPLOYEE_ASSIGNMENTS_DATE_ORDER CHECK (end_date IS NULL OR end_date >= start_date)
230);
231
232CREATE TABLE TRANSACTION_TYPES
233(
234 code VARCHAR(50) PRIMARY KEY,
235 description TEXT NOT NULL,
236 CONSTRAINT CK_TRANSACTION_TYPES_CODE_NOT_EMPTY CHECK (btrim(code) <> ''),
237 CONSTRAINT CK_TRANSACTION_TYPES_DESCRIPTION_NOT_EMPTY CHECK (btrim(description) <> '')
238);
239
240CREATE TABLE INVENTORY_TRANSACTIONS
241(
242 id BIGSERIAL PRIMARY KEY,
243 transaction_type VARCHAR(50) NOT NULL,
244 created_by_employee BIGINT NOT NULL,
245 notes TEXT,
246 status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
247 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
248 last_updated_by BIGINT,
249 accepted_by BIGINT,
250 packed_by BIGINT,
251 CONSTRAINT FK_INVENTORY_TRANSACTIONS_TYPE FOREIGN KEY (transaction_type) REFERENCES TRANSACTION_TYPES (code) ON DELETE RESTRICT,
252 CONSTRAINT FK_INVENTORY_TRANSACTIONS_CREATED_BY FOREIGN KEY (created_by_employee) REFERENCES EMPLOYEES (id) ON DELETE RESTRICT,
253 CONSTRAINT FK_INVENTORY_TRANSACTIONS_LAST_UPDATED_BY FOREIGN KEY (last_updated_by) REFERENCES EMPLOYEES (id) ON DELETE RESTRICT,
254 CONSTRAINT FK_INVENTORY_TRANSACTIONS_ACCEPTED_BY FOREIGN KEY (accepted_by) REFERENCES EMPLOYEES (id) ON DELETE RESTRICT,
255 CONSTRAINT FK_INVENTORY_TRANSACTIONS_PACKED_BY FOREIGN KEY (packed_by) REFERENCES EMPLOYEES (id) ON DELETE RESTRICT,
256 CONSTRAINT CK_INVENTORY_TRANSACTIONS_STATUS CHECK (status IN ('PENDING', 'APPROVED', 'CANCELLED'))
257);
258
259CREATE TABLE INVENTORY_MOVEMENTS
260(
261 id BIGSERIAL PRIMARY KEY,
262 product_variant_id BIGINT NOT NULL,
263 from_bin_id BIGINT,
264 to_bin_id BIGINT,
265 quantity INT NOT NULL,
266 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
267 inventory_transactions_id BIGINT NOT NULL,
268 CONSTRAINT FK_INVENTORY_MOVEMENTS_VARIANT FOREIGN KEY (product_variant_id) REFERENCES PRODUCT_VARIANTS (id) ON DELETE RESTRICT,
269 CONSTRAINT FK_INVENTORY_MOVEMENTS_FROM_BIN FOREIGN KEY (from_bin_id) REFERENCES BINS (id) ON DELETE RESTRICT,
270 CONSTRAINT FK_INVENTORY_MOVEMENTS_TO_BIN FOREIGN KEY (to_bin_id) REFERENCES BINS (id) ON DELETE RESTRICT,
271 CONSTRAINT FK_INVENTORY_MOVEMENTS_TRANSACTION FOREIGN KEY (inventory_transactions_id) REFERENCES INVENTORY_TRANSACTIONS (id) ON DELETE RESTRICT,
272 CONSTRAINT CK_INVENTORY_MOVEMENTS_QUANTITY_POSITIVE CHECK (quantity > 0),
273 CONSTRAINT CK_INVENTORY_MOVEMENTS_AT_LEAST_ONE_BIN CHECK (from_bin_id IS NOT NULL OR to_bin_id IS NOT NULL),
274 CONSTRAINT CK_INVENTORY_MOVEMENTS_DIFFERENT_BINS CHECK (from_bin_id IS NULL OR to_bin_id IS NULL OR from_bin_id <> to_bin_id)
275);
276
277CREATE TABLE DELIVERY_TRANSACTIONS
278(
279 delivery_note TEXT,
280 supplier_company TEXT NOT NULL,
281 inventory_transactions_id BIGINT NOT NULL PRIMARY KEY,
282 CONSTRAINT FK_DELIVERY_TRANSACTIONS_TRANSACTION FOREIGN KEY (inventory_transactions_id) REFERENCES INVENTORY_TRANSACTIONS (id) ON DELETE RESTRICT,
283 CONSTRAINT CK_DELIVERY_TRANSACTIONS_SUPPLIER_NOT_EMPTY CHECK (btrim(supplier_company) <> '')
284);
285
286CREATE TABLE SHIPMENT_TRANSACTIONS
287(
288 shipment_number BIGINT NOT NULL,
289 destination_adress TEXT NOT NULL,
290 inventory_transactions_id BIGINT NOT NULL PRIMARY KEY,
291 CONSTRAINT FK_SHIPMENT_TRANSACTIONS_TRANSACTION FOREIGN KEY (inventory_transactions_id) REFERENCES INVENTORY_TRANSACTIONS (id) ON DELETE RESTRICT,
292 CONSTRAINT CK_SHIPMENT_TRANSACTIONS_SHIPMENT_NUMBER_POSITIVE CHECK (shipment_number > 0),
293 CONSTRAINT CK_SHIPMENT_TRANSACTIONS_DESTINATION_NOT_EMPTY CHECK (btrim(destination_adress) <> '')
294);
295
296CREATE TABLE ROLES
297(
298 id SERIAL PRIMARY KEY,
299 name TEXT NOT NULL,
300 description TEXT,
301 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
302 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
303 CONSTRAINT UQ_ROLES_NAME UNIQUE (name),
304 CONSTRAINT CK_ROLES_NAME_NOT_EMPTY CHECK (btrim(name) <> '')
305);
306
307CREATE TABLE PERMISSIONS
308(
309 id SERIAL PRIMARY KEY,
310 name TEXT NOT NULL,
311 description TEXT,
312 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
313 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
314 CONSTRAINT UQ_PERMISSIONS_NAME UNIQUE (name),
315 CONSTRAINT CK_PERMISSIONS_NAME_NOT_EMPTY CHECK (btrim(name) <> '')
316);
317
318
319--Junction tables have no meaning without either foreign keys
320CREATE TABLE ROLES_EMPLOYEES
321(
322 roles_id INT NOT NULL,
323 employees_id BIGINT NOT NULL,
324 PRIMARY KEY (roles_id, employees_id),
325 CONSTRAINT FK_ROLES_EMPLOYEES_ROLE FOREIGN KEY (roles_id) REFERENCES ROLES (id) ON DELETE CASCADE,
326 CONSTRAINT FK_ROLES_EMPLOYEES_EMPLOYEE FOREIGN KEY (employees_id) REFERENCES EMPLOYEES (id) ON DELETE CASCADE
327);
328
329
330--Junction tables have no meaning without either foreign keys
331CREATE TABLE PERMISSIONS_ROLES
332(
333 permissions_id INT NOT NULL,
334 roles_id INT NOT NULL,
335 PRIMARY KEY (permissions_id, roles_id),
336 CONSTRAINT FK_PERMISSIONS_ROLES_PERMISSION FOREIGN KEY (permissions_id) REFERENCES PERMISSIONS (id) ON DELETE CASCADE,
337 CONSTRAINT FK_PERMISSIONS_ROLES_ROLE FOREIGN KEY (roles_id) REFERENCES ROLES (id) ON DELETE CASCADE
338);