DB schema

Database schema

Users

create table users
(
    id           bigint unsigned not null auto_increment primary key ,
    first_name   varchar(255) null,
    last_name    varchar(255) null,
    email        varchar(255) unique not null,
    password     varchar(255) null,
    role         enum ('ADMIN','WORKER'),
    created_date datetime not null,
    updated_date datetime not null,
    active       boolean default FALSE,
    email_uuid   varchar(64) not null,
    account_id   int null
);

id	first_name	last_name	email	password	role	created_date	updated_date	active	email_uuid	account_id
1	Mariia	Slobodian	[email protected]	5f4dcc3b5aa765d61d8327deb882cf99	ADMIN	2019-01-01 00:00:00	2019-01-01 00:00:00	TRUE	03bfe72c-0d5d-11ea-8d71-362b9e155667	1
2	Volodymyr	Huk	[email protected]	630bf032efe4507f2c57b280995925a9	WORKER	2019-05-01 00:00:00	2019-05-01 00:00:00	TRUE	1874136e-0d5d-11ea-8d71-362b9e155667	1
3	null	null	[email protected]	null	WORKER	2019-01-02 00:12:00	2019-01-02 00:12:00	FALSE	2de6f4dc-0d5d-11ea-8d71-362b9e155667	1

---

Accounts

CREATE TABLE accounts
(
  id           INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name         VARCHAR(45) NOT NULL,
  type_id      INT         NOT NULL,
  created_date DATETIME    NOT NULL,
  active       boolean default TRUE
);

id	name	type_id	created_date	active
1	travel goods	2	2019-01-01 13:00:00	true
2	building materials	2	2019-11-07 14:00:00	true

---

Account_types

CREATE TABLE account_types
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name                     VARCHAR(45) NOT NULL,
  price                    DOUBLE      NOT NULL,
  level                    INT         NOT NULL,
  max_warehouses           INT         NOT NULL,
  max_warehouse_depth      INT         NOT NULL,
  max_users                INT         NOT NULL,
  max_suppliers            INT         NOT NULL,
  max_clients              INT         NOT NULL,
  deep_warehouse_analytics boolean     NOT NULL,
  item_storage_advisor     boolean     NOT NULL,
  active                   boolean default TRUE
);

id	name	price	level	max_warehouses	max_warehouse_depth	max_users	max_suppliers	max_clients	deep_warehouse_analytics	item_storage_advisor	active
1	basic	0	1	3	3	3	20	20	false	false	true
2	premium	300	2	1000	1000	10000	10000	10000	true	true	true

---

Warehouses

CREATE TABLE warehouses
(
    id         INT UNSIGNED NOT NULL,
    name       VARCHAR(45)  NOT NULL,
    info       VARCHAR(100) NULL,
    capacity   INT UNSIGNED NULL,
    is_bottom  TINYINT(1)   NOT NULL,
    parent_id  INT UNSIGNED NULL,
    account_id INT          NOT NULL,
    active     TINYINT(1)   NULL,
    top_warehouse_id  INT UNSIGNED,
    PRIMARY KEY (id),
    UNIQUE INDEX id_UNIQUE (id ASC) VISIBLE
);

constraint parent___fk
  foreign key (parent_id) references warehouses (id)
    on update cascade,
      constraint top_id___fk
        foreign key (top_warehouse_id) references warehouses (id)
          on update cascade

id	name	info	capacity	is_bottom	parent_id	account_id	active	top_warehouse_id
1	Warehouse_A	products	0	0	null	1	1	1
2	Warehouse_B	parts	0	0	null	1	1	2
3	Warehouse_1	sweets	0	0	null	2	1	3
4	Section_A1	products	0	0	1	1	1	1
5	Section_A2	parts	0	0	1	1	1	1
6	Section_B1	metal	0	0	2	1	1	2
7	Section_B2	plastic	0	0	2	1	1	2
8	Section_1-1	products	0	0	3	2	1	3
9	Section_1-2	other	0	0	3	2	1	3
10	Shelf_A1-1	fruit	20	1	1	4	1	1
11	Shelf_A1-2	parts	10	1	1	4	1	1
12	Shelf_B1-1	metal	30	1	2	1	1	2
13	Shelf_B1-2	plastic	10	1	2	1	1	2
14	Shelf_1-1	products	30	1	3	2	1	3
15	Shelf_1-2	other	20	1	3	2	1	3
16	Shelf_A2-1	fruit	20	1	1	4	1	1
17	Shelf_A2-2	parts	10	1	1	4	1	1
18	Shelf_B2-1	metal	30	1	2	1	1	2
19	Shelf_B2-2	plastic	30	1	2	1	1	2
20	Shelf_2-1	products	20	1	3	2	1	3
21	Shelf_2-2	other	20	1	3	2	1	3

---

Items

create table items
(
    id          bigint AUTO_INCREMENT PRIMARY KEY,
    name_item   varchar(255) NOT NULL,
    unit        varchar(255) not null,
    description varchar(255) not null,
    volume      int not null,
    active      tinyint      not null,
    account_id  bigint       not null
);

id	name_item	unit	description	volume	active	account_id
1	Devaytis	block	high carbonated mineral water	12	1	1
2	Bonakva	block	low carbonated mineral water	6	1	2
3	Artesiancka	block	high carbonated mineral water	9	1	3

---

Saved Items

create table saved_items
(
    id           bigint AUTO_INCREMENT PRIMARY KEY,
    item_id     bigint NOT NULL,
    quantity     int    NOT NULL,
    warehouse_id bigint not null
);

id	items_id	quantity	warehouse_id
1	1	100	1
2	2	120	2
3	3	90	3

---

Events

CREATE TABLE events
(
    id             int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
    message        text             NOT NULL,
    date           datetime         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    account_id     int(11)          NOT NULL,
    warehouse_id   int(11)                   DEFAULT NULL,
    author_id      int(11)          NOT NULL,
    name           varchar(32)      NOT NULL,
    transaction_id int(11)                   DEFAULT NULL
);

id	message	date	org_id	warehouse_id	author_id	name	transaction_id
2707	User Vasyl login	2000-07-27	122	7	34432	LOGIN	NULL
2222	Phones were shipped from warehouse 12	2005-01-13	235	12	53872	ITEM_SHIPPED	66723

---

Transactions

create table transactions
(
    id           bigint unsigned  not null auto_increment primary key,
    timestamp    timestamp        not null default current_timestamp,
    account_id   int(11) unsigned not null,
    worker_id    int(11) unsigned not null,
    item_id      int(11) unsigned not null,
    quantity     int(11) unsigned not null,
    associate_id int(11) unsigned null,
    moved_from   int(11) unsigned null,
    moved_to     int(11) unsigned null,
    type         enum ('IN', 'OUT', 'MOVE')
);

timestamp	account_id	worker_id	associate_id	item_id	quantity	moved_from	moved_to	type
2019-01-01 00:00:00	1	1	1	1	10	null	33	IN
2019-12-31 10:30:00	1	1	10	3	20	1	null	OUT
2020-01-20 00:30:00	2	2	null	2	1	22	5	MOVE

---

Associates

create table associates
(
    id              int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
    account_id      int(11)          not null,
    name            varchar(45)      not null,
    email           varchar(60)      null,
    phone           varchar(20)      null,
    additional_info mediumtext       null,
    type            enum ('CLIENT', 'SUPPLIER'),
    active          boolean default TRUE
);

account_id	name	email	phone	additional_info	type	active
1	John Doe	[email protected]	null	null	CLIENT	TRUE
1	Charles Brocade	[email protected]	+1-541-754-3010	null	SUPPLIER	TRUE
2	Kevin Buttler	[email protected]	null	Inactive supplier	SUPPLIER	FALSE

---

Addresses

create table addresses
(
    id           int(11) unsigned not null auto_increment primary key,
    country      varchar(255)     null,
    city         varchar(255)     null,
    address      varchar(255)     null,
    zip          varchar(11)      null,
    latitude     float(10, 8)     null,
    longitude    float(11, 8)     null,
    warehouse_id int(11) unsigned null,
    associate_id int(11) unsigned null
);

country	city	address	zip	latitude	longitude	warehouse_id	associate_id
Japan	Tokyo	Kyobashi MID Bldg., 13-10, Kyobashi 2-chome, Chuo-ku	153-0051	35.6850	139.7514	null	2
United States	New York	151 Pennington Street Brooklyn, NY 11236	10004	40.6943	-73.9249	1	null

---