5-Improve DB structure

[janis-rullis]

https://github.com/lauris-printify/homework-v3/blob/master/docker/backup/mysql/api.sql

• [x] Write new ALTER migrations and don't touch the existing ones.
• [x] Register used queries so could compare improvements.
• [x] Add indexes.
• [x] Add comments.
• [x] Limit sizes like add UNSIGNED or if the max is too big then change to SMALLINT.
• [x] Register improvements here.
• [x] Create issues for tasks marked as later in the comments below.
• [x] Test impact also with #21.

[janis-rullis]

order

• owner_id-index.
• int-unsigned.
• costs-smallint https://github.com/janis-rullis/sql/blob/master/mysql/Number/Int-max-value.md . Max cost should not be limited.

later #29

11 #12 #13 #14

https://github.com/janis-rullis/pr1/wiki/Uses-cases-and-Conditions

• created.
• status.
• soft delete.

maybe

• indexed unique hash field. https://github.com/janis-rullis/sql/blob/master/mysql/Index/Unique-index.md https://github.com/janis-rullis/sql/blob/master/mysql/String/Unique-texts.md
• sys-info - https://github.com/janis-rullis/code-ruu/blob/master/laravel5/database/migrations/2019_12_19_124854_add_product.php

[janis-rullis]

product

• owner_id - unsigned, index when list user products.
• sku - unique, see order comment above.
• cost - smallint unsigned.

later #30

9

• soft delete.
• created.
• type-enum, maybe a an index if need to filter a type

[janis-rullis]

relation

Unsigned, indexes.

later #31

12

• soft delete.
• created.

[janis-rullis]

user

• balance - smallint unsigned.

later #32

8

• balance - init value 10000.
• comment - init gives 100$.
• soft delete.
• created.

[janis-rullis]

Register used queries

• phpMyAdmin tutorial at Debconf13, part 4 : triggers/routines/events and server status monitor
• PhpMyAdmin/Status/Monitor: Login as root user, enable logs, execute API requests, click in the chart so that the time-range window appears, select a range +/- an hour and press analyze.

[janis-rullis]

user before improvements

Query

EXPLAIN SELECT t0.id AS id_1, t0.name AS name_2, t0.surname AS surname_3, t0.balance AS balance_4 FROM user t0;

[janis-rullis]

user improvements

Query

ALTER TABLE`user`
    CHANGE `balance` `balance` smallint(5) unsigned NOT NULL DEFAULT '10000' COMMENT 'All users upon creation have $100 in their balance. #8'; 

Improvement

• Speed: Not impacted.
• balance - size is more fitting, has an initial value and a comment.

Result

Endpoint

Not broken. Returns

{
  "id": 4,
  "name": "John",
  "surname": "Doe",
  "balance": 10000
}

Tests

Green.

[janis-rullis]

relation before improvements

View user's order

EXPLAIN
SELECT
    t0.id AS id_1,
    t0.owner_id AS owner_id_2,
    t0.order_id AS order_id_3,
    t0.product_id AS product_id_4,
    t0.quantity AS quantity_5
FROM
    relation t0
WHERE
    t0.order_id = 1 AND t0.owner_id = 1

[janis-rullis]

order before improvements

View user's all orders

SELECT
    t0.id AS id_1,
    t0.owner_id AS owner_id_2,
    t0.name AS name_3,
    t0.surname AS surname_4,
    t0.street AS street_5,
    t0.country AS country_6,
    t0.phone AS phone_7,
    t0.state AS state_8,
    t0.zip AS zip_9,
    t0.production_cost AS production_cost_10,
    t0.shipping_cost AS shipping_cost_11,
    t0.express_shipping AS express_shipping_12,
    t0.total_cost AS total_cost_13
FROM
    `order` t0
WHERE
    t0.owner_id = 1

[janis-rullis]

Relation improvements

• Before.

UNSIGNED query

ALTER TABLE`relation`
      CHANGE `owner_id` `owner_id` int(11) UNSIGNED NOT NULL,
      CHANGE `order_id` `order_id` int(11) UNSIGNED NOT NULL,
      CHANGE `product_id` `product_id` int(11) UNSIGNED NOT NULL,
      CHANGE `quantity` `quantity` int(11) UNSIGNED NOT NULL

INDEX query

ALTER TABLE`relation`
      ADD INDEX `owner_order` (`owner_id`, `order_id`);

Improvements

• Make integer field UNSIGNED because this will allow more POSITIVE values.
• indexes to find items by owner and order faster.

Endpoint

Works.

Tests

Green.

[janis-rullis]

order improvements

• Before

Make integer field UNSIGNED because this will allow more POSITIVE values

ALTER TABLE`order`
      CHANGE `owner_id` `owner_id` int(11) UNSIGNED NOT NULL,
      CHANGE `express_shipping` `express_shipping` tinyint(1) UNSIGNED DEFAULT NULL,
      CHANGE `production_cost` `production_cost` int(11) UNSIGNED NOT NULL,
      CHANGE `shipping_cost` `shipping_cost` int(11) UNSIGNED NOT NULL,
      CHANGE `total_cost` `total_cost` int(11) UNSIGNED NOT NULL;    

Add indexes to find items by owner faster

ALTER TABLE `order`
      ADD INDEX `owner_id` (`owner_id`);

Improved

Endpoints

• Works.

Tests

Green.

[janis-rullis]

product before improvements

Get user's products

EXPLAIN
SELECT
    t0.id AS id_1,
    t0.owner_id AS owner_id_2,
    t0.type AS type_3,
    t0.title AS title_4,
    t0.sku AS sku_5,
    t0.cost AS cost_6
FROM
    product t0
WHERE
    t0.owner_id = 1;

[janis-rullis]

product improvements

Make integer field UNSIGNED

because this will allow more POSITIVE values, also decrease the cost to SMALLINT

ALTER TABLE`product`
      CHANGE `owner_id` `owner_id` int(11) UNSIGNED NOT NULL,
      CHANGE `cost` `cost` SMALLINT(5) UNSIGNED NOT NULL COMMENT 'Limited to smallint because user cant afford nothing more than 10000 (see users init balance) #8 #9 ';

Add indexes to find user's products faster

ALTER TABLE `product`
      ADD INDEX `owner_id` (`owner_id`),
      ADD UNIQUE INDEX `sku` (`sku`);

Improvements

Endpoints

Works.

Tests

Green.