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	slobodjanmarija1@gmail.com	5f4dcc3b5aa765d61d8327deb882cf99	ADMIN	2019-01-01 00:00:00	2019-01-01 00:00:00	TRUE	03bfe72c-0d5d-11ea-8d71-362b9e155667	1
2	Volodymyr	Huk	huk.volodymyr@gmail.com	630bf032efe4507f2c57b280995925a9	WORKER	2019-05-01 00:00:00	2019-05-01 00:00:00	TRUE	1874136e-0d5d-11ea-8d71-362b9e155667	1
3	null	null	svefankiv.olha@gmail.com	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      INT         NOT NULL,
  admin_id     INT         NOT NULL,
  created_date DATETIME    NOT NULL,
  active       boolean default 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,
  active              boolean default 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,
    PRIMARY KEY (id),
    UNIQUE INDEX id_UNIQUE (id ASC) VISIBLE
);

ALTER TABLE warehouses
    ADD CONSTRAINT parent
        FOREIGN KEY (parent_id)
            REFERENCES warehouses (id);

---

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	type	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	2	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	null	IN
2019-12-31 10:30:00	1	1	10	3	20	null	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	john.doe@shopify.com	null	null	CLIENT	TRUE
1	Charles Brocade	charly.br@gmail.com	+1-541-754-3010	null	SUPPLIER	TRUE
2	Kevin Buttler	kevin.b@gmail.com	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

---