Проектирование БД: связи, ключи и нормализация

-- Сервис онлайн-записи: клиенты, мастера, услуги, записи
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE clients (
  id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name           text NOT NULL CHECK (name <> ''),
  phone          text NOT NULL UNIQUE,
  -- кто посоветовал салон: необязательная ссылка на другого клиента
  referred_by_id bigint REFERENCES clients (id) ON DELETE SET NULL,
  created_at     timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE masters (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name      text    NOT NULL,
  is_active boolean NOT NULL DEFAULT true
);

-- 1:1 — первичный ключ профиля одновременно внешний ключ на мастера
CREATE TABLE master_profiles (
  master_id bigint PRIMARY KEY REFERENCES masters (id) ON DELETE CASCADE,
  bio       text,
  photo_url text
);

CREATE TABLE services (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name         text          NOT NULL UNIQUE,
  duration_min integer       NOT NULL CHECK (duration_min > 0),
  price        numeric(10,2) NOT NULL CHECK (price > 0)
);

-- N:M — какие услуги выполняет мастер
CREATE TABLE master_services (
  master_id  bigint NOT NULL REFERENCES masters (id) ON DELETE CASCADE,
  service_id bigint NOT NULL REFERENCES services (id) ON DELETE CASCADE,
  PRIMARY KEY (master_id, service_id)
);

-- 1:N от клиента и от пары «мастер — услуга»
CREATE TABLE appointments (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  client_id  bigint        NOT NULL REFERENCES clients (id) ON DELETE RESTRICT,
  master_id  bigint        NOT NULL,
  service_id bigint        NOT NULL,
  starts_at  timestamptz   NOT NULL,
  ends_at    timestamptz   NOT NULL,
  price      numeric(10,2) NOT NULL CHECK (price >= 0),
  status     text          NOT NULL DEFAULT 'booked'
             CHECK (status IN ('booked', 'done', 'cancelled', 'no_show')),
  CHECK (ends_at > starts_at),
  -- записать можно только на услугу, которую этот мастер выполняет
  FOREIGN KEY (master_id, service_id)
    REFERENCES master_services (master_id, service_id) ON DELETE RESTRICT,
  -- у мастера не может быть двух пересекающихся записей, кроме отменённых
  EXCLUDE USING gist (master_id WITH =, tstzrange(starts_at, ends_at) WITH &&)
    WHERE (status <> 'cancelled')
);

-- Внешний ключ не создаёт индекс на ссылающихся столбцах — добавляем сами
CREATE INDEX ON appointments (client_id);
CREATE INDEX ON appointments (master_id, service_id);
CREATE INDEX ON master_services (service_id);
CREATE INDEX ON clients (referred_by_id);

-- Вымышленные данные салона
INSERT INTO clients (name, phone, referred_by_id) VALUES
  ('Aigerim Seitova',  '+7 700 000 0001', NULL),
  ('Madina Omarova',   '+7 700 000 0002', 1),
  ('Daniyar Akhmetov', '+7 700 000 0003', NULL);

INSERT INTO masters (name) VALUES ('Aliya'), ('Saule');
INSERT INTO master_profiles (master_id, bio) VALUES
  (1, 'Colorist'),
  (2, 'Nail artist');

INSERT INTO services (name, duration_min, price) VALUES
  ('Haircut',  60,  8000.00),
  ('Coloring', 120, 25000.00),
  ('Manicure', 90,  7000.00);

INSERT INTO master_services (master_id, service_id) VALUES
  (1, 1), (1, 2),
  (2, 3);

INSERT INTO appointments (client_id, master_id, service_id, starts_at, ends_at, price) VALUES
  (1, 1, 2, '2026-10-20 10:00+05', '2026-10-20 12:00+05', 25000.00),
  (3, 1, 1, '2026-10-20 12:00+05', '2026-10-20 13:00+05',  8000.00),
  (1, 2, 3, '2026-10-21 15:00+05', '2026-10-21 16:30+05',  7000.00);

docker run -d --rm --name booking-db -e POSTGRES_PASSWORD=change-me-locally postgres:17-alpine

docker exec booking-db psql -U postgres \
  -c "CREATE ROLE booking_app LOGIN PASSWORD 'change-me'" \
  -c "CREATE DATABASE booking OWNER booking_app" \
  -c "CREATE DATABASE shop_design OWNER booking_app"

docker cp db booking-db:/db
docker exec booking-db psql -U booking_app -d booking -v ON_ERROR_STOP=1 \
  -f /db/booking.sql -f /db/booking-seed.sql

booking=> \d appointments
                                 Table "public.appointments"
   Column   |           Type           | Collation | Nullable |           Default            
------------+--------------------------+-----------+----------+------------------------------
 id         | bigint                   |           | not null | generated always as identity
 client_id  | bigint                   |           | not null | 
 master_id  | bigint                   |           | not null | 
 service_id | bigint                   |           | not null | 
 starts_at  | timestamp with time zone |           | not null | 
 ends_at    | timestamp with time zone |           | not null | 
 price      | numeric(10,2)            |           | not null | 
 status     | text                     |           | not null | 'booked'::text
Indexes:
    "appointments_pkey" PRIMARY KEY, btree (id)
    "appointments_client_id_idx" btree (client_id)
    "appointments_master_id_service_id_idx" btree (master_id, service_id)
    "appointments_master_id_tstzrange_excl" EXCLUDE USING gist (master_id WITH =, tstzrange(starts_at, ends_at) WITH &&) WHERE (status <> 'cancelled'::text)
Check constraints:
    "appointments_check" CHECK (ends_at > starts_at)
    "appointments_price_check" CHECK (price >= 0::numeric)
    "appointments_status_check" CHECK (status = ANY (ARRAY['booked'::text, 'done'::text, 'cancelled'::text, 'no_show'::text]))
Foreign-key constraints:
    "appointments_client_id_fkey" FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT
    "appointments_master_id_service_id_fkey" FOREIGN KEY (master_id, service_id) REFERENCES master_services(master_id, service_id) ON DELETE RESTRICT

booking=> INSERT INTO master_profiles (master_id, bio) VALUES (1, 'Second profile');
ERROR:  duplicate key value violates unique constraint "master_profiles_pkey"
DETAIL:  Key (master_id)=(1) already exists.
booking=> INSERT INTO appointments (client_id, master_id, service_id, starts_at, ends_at, price) VALUES (2, 2, 1, '2026-10-22 10:00+05', '2026-10-22 11:00+05', 8000.00);
ERROR:  insert or update on table "appointments" violates foreign key constraint "appointments_master_id_service_id_fkey"
DETAIL:  Key (master_id, service_id)=(2, 1) is not present in table "master_services".
booking=> INSERT INTO appointments (client_id, master_id, service_id, starts_at, ends_at, price) VALUES (2, 1, 1, '2026-10-20 11:30+05', '2026-10-20 12:30+05', 8000.00);
ERROR:  conflicting key value violates exclusion constraint "appointments_master_id_tstzrange_excl"
DETAIL:  Key (master_id, tstzrange(starts_at, ends_at))=(1, ["2026-10-20 06:30:00+00","2026-10-20 07:30:00+00")) conflicts with existing key (master_id, tstzrange(starts_at, ends_at))=(1, ["2026-10-20 05:00:00+00","2026-10-20 07:00:00+00")).
booking=> INSERT INTO appointments (client_id, master_id, service_id, starts_at, ends_at, price) VALUES (2, 1, 1, '2026-10-20 13:00+05', '2026-10-20 14:00+05', 8000.00);
INSERT 0 1
booking=> UPDATE appointments SET status = 'cancelled' WHERE id = 2;
UPDATE 1
booking=> INSERT INTO appointments (client_id, master_id, service_id, starts_at, ends_at, price) VALUES (2, 1, 1, '2026-10-20 12:00+05', '2026-10-20 13:00+05', 8000.00) RETURNING id;
 id 
----
  7
(1 row)

INSERT 0 1

booking=> DELETE FROM clients WHERE id = 1;
ERROR:  update or delete on table "clients" violates foreign key constraint "appointments_client_id_fkey" on table "appointments"
DETAIL:  Key (id)=(1) is still referenced from table "appointments".
booking=> DELETE FROM masters WHERE id = 1;
ERROR:  update or delete on table "master_services" violates foreign key constraint "appointments_master_id_service_id_fkey" on table "appointments"
DETAIL:  Key (master_id, service_id)=(1, 1) is still referenced from table "appointments".
booking=> INSERT INTO masters (name) VALUES ('Trainee') RETURNING id;
 id 
----
  3
(1 row)

INSERT 0 1
booking=> INSERT INTO master_profiles (master_id, bio) VALUES (3, 'Junior');
INSERT 0 1
booking=> INSERT INTO master_services (master_id, service_id) VALUES (3, 1);
INSERT 0 1
booking=> DELETE FROM masters WHERE id = 3;
DELETE 1
booking=> SELECT count(*) AS profiles_left FROM master_profiles WHERE master_id = 3;
 profiles_left 
---------------
             0
(1 row)

booking=> DELETE FROM appointments WHERE client_id = 1;
DELETE 2
booking=> DELETE FROM clients WHERE id = 1 RETURNING id, name;
 id |      name       
----+-----------------
  1 | Aigerim Seitova
(1 row)

DELETE 1
booking=> SELECT id, name, referred_by_id FROM clients ORDER BY id;
 id |       name       | referred_by_id 
----+------------------+----------------
  2 | Madina Omarova   |               
  3 | Daniyar Akhmetov |               
(2 rows)

-- Заказы, как их ведут в таблице Excel: одна строка — одна позиция
CREATE TABLE order_sheet (
  order_no     integer,
  ordered_on   date,
  client_name  text,
  client_email text,
  client_city  text,
  product      text,
  category     text,
  price        numeric(10,2),
  quantity     integer
);

INSERT INTO order_sheet VALUES
  (1, '2026-09-02', 'Aigerim Seitova',  'aigerim@example.com', 'Almaty', 'Espresso Blend 1 kg',        'coffee', 12900.00, 1),
  (1, '2026-09-02', 'Aigerim Seitova',  'aigerim@example.com', 'Almaty', 'Paper filters V60',          'gear',    2500.00, 2),
  (2, '2026-09-05', 'Daniyar Akhmetov', NULL,                  'Astana', 'Ethiopia Yirgacheffe 250 g', 'coffee',  5600.00, 2),
  (3, '2026-09-14', 'Aigerim Seitova',  'aigerim@example.com', 'Almaty', 'Ethiopia Yirgacheffe 250 g', 'coffee',  5600.00, 1);

shop_design=> SELECT * FROM order_sheet;
 order_no | ordered_on |   client_name    |    client_email     | client_city |          product           | category |  price   | quantity 
----------+------------+------------------+---------------------+-------------+----------------------------+----------+----------+----------
        1 | 2026-09-02 | Aigerim Seitova  | aigerim@example.com | Almaty      | Espresso Blend 1 kg        | coffee   | 12900.00 |        1
        1 | 2026-09-02 | Aigerim Seitova  | aigerim@example.com | Almaty      | Paper filters V60          | gear     |  2500.00 |        2
        2 | 2026-09-05 | Daniyar Akhmetov |                     | Astana      | Ethiopia Yirgacheffe 250 g | coffee   |  5600.00 |        2
        3 | 2026-09-14 | Aigerim Seitova  | aigerim@example.com | Almaty      | Ethiopia Yirgacheffe 250 g | coffee   |  5600.00 |        1
(4 rows)

shop_design=> UPDATE order_sheet SET client_city = 'Astana' WHERE order_no = 3;
UPDATE 1
shop_design=> SELECT DISTINCT client_name, client_city FROM order_sheet ORDER BY 1, 2;
   client_name    | client_city 
------------------+-------------
 Aigerim Seitova  | Almaty
 Aigerim Seitova  | Astana
 Daniyar Akhmetov | Astana
(3 rows)

-- 3НФ: каждый факт хранится в одном месте
CREATE TABLE clients (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  text NOT NULL,
  city  text,
  email text UNIQUE
);

CREATE TABLE products (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name     text          NOT NULL UNIQUE,
  category text          NOT NULL,
  price    numeric(10,2) NOT NULL CHECK (price > 0)
);

CREATE TABLE orders (
  id         bigint PRIMARY KEY,
  client_id  bigint NOT NULL REFERENCES clients (id),
  ordered_on date   NOT NULL
);

CREATE TABLE order_items (
  order_id   bigint        NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  product_id bigint        NOT NULL REFERENCES products (id),
  quantity   integer       NOT NULL CHECK (quantity > 0),
  price      numeric(10,2) NOT NULL CHECK (price > 0),
  PRIMARY KEY (order_id, product_id)
);

-- Перенос данных из плоской таблицы
INSERT INTO clients (name, city, email)
SELECT DISTINCT client_name, client_city, client_email
FROM order_sheet
ORDER BY client_name;

INSERT INTO products (name, category, price)
SELECT DISTINCT product, category, price
FROM order_sheet
ORDER BY product;

INSERT INTO orders (id, client_id, ordered_on)
SELECT DISTINCT s.order_no, c.id, s.ordered_on
FROM order_sheet s
JOIN clients c ON c.name = s.client_name;

INSERT INTO order_items (order_id, product_id, quantity, price)
SELECT s.order_no, p.id, s.quantity, s.price
FROM order_sheet s
JOIN products p ON p.name = s.product;

$ docker exec booking-db psql -U booking_app -d shop_design -v ON_ERROR_STOP=1 -1 -f /db/normalize.sql; echo "exit $?"
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
psql:/db/normalize.sql:34: ERROR:  duplicate key value violates unique constraint "clients_email_key"
DETAIL:  Key (email)=(aigerim@example.com) already exists.
exit 3
shop_design=> UPDATE order_sheet SET client_city = 'Astana' WHERE client_name = 'Aigerim Seitova';
UPDATE 3
$ docker exec booking-db psql -U booking_app -d shop_design -v ON_ERROR_STOP=1 -1 -f /db/normalize.sql; echo "exit $?"
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 3
INSERT 0 3
INSERT 0 4
exit 0

SELECT o.id AS order_no, o.ordered_on, c.name AS client, c.city,
       p.name AS product, i.quantity, i.price
FROM orders o
JOIN clients c     ON c.id = o.client_id
JOIN order_items i ON i.order_id = o.id
JOIN products p    ON p.id = i.product_id
ORDER BY o.id, p.name;
 order_no | ordered_on |      client      |  city  |          product           | quantity |  price   
----------+------------+------------------+--------+----------------------------+----------+----------
        1 | 2026-09-02 | Aigerim Seitova  | Astana | Espresso Blend 1 kg        |        1 | 12900.00
        1 | 2026-09-02 | Aigerim Seitova  | Astana | Paper filters V60          |        2 |  2500.00
        2 | 2026-09-05 | Daniyar Akhmetov | Astana | Ethiopia Yirgacheffe 250 g |        2 |  5600.00
        3 | 2026-09-14 | Aigerim Seitova  | Astana | Ethiopia Yirgacheffe 250 g |        1 |  5600.00
(4 rows)

UPDATE clients SET city = 'Shymkent' WHERE email = 'aigerim@example.com';
UPDATE 1
INSERT INTO products (name, category, price) VALUES ('Kalita Wave 185', 'gear', 18500.00);
INSERT 0 1
DELETE FROM orders WHERE id = 2;
DELETE 1
SELECT name, city FROM clients ORDER BY id;
       name       |   city   
------------------+----------
 Aigerim Seitova  | Shymkent
 Daniyar Akhmetov | Astana
(2 rows)

-- 1. Сумма строки считается самой базой и не может разойтись с quantity и price
ALTER TABLE order_items
  ADD COLUMN line_total numeric(12,2) GENERATED ALWAYS AS (quantity * price) STORED;

-- 2. Готовый отчёт: хранится как таблица, обновляется по команде
CREATE MATERIALIZED VIEW client_revenue AS
SELECT c.name, count(DISTINCT o.id) AS orders, sum(i.line_total) AS revenue
FROM clients c
JOIN orders o      ON o.client_id = c.id
JOIN order_items i ON i.order_id = o.id
GROUP BY c.name;

shop_design=> SELECT order_id, product_id, quantity, price, line_total FROM order_items ORDER BY 1, 2;
 order_id | product_id | quantity |  price   | line_total 
----------+------------+----------+----------+------------
        1 |          1 |        1 | 12900.00 |   12900.00
        1 |          3 |        2 |  2500.00 |    5000.00
        3 |          2 |        1 |  5600.00 |    5600.00
(3 rows)

shop_design=> UPDATE order_items SET line_total = 0;
ERROR:  column "line_total" can only be updated to DEFAULT
DETAIL:  Column "line_total" is a generated column.
shop_design=> SELECT * FROM client_revenue ORDER BY name;
      name       | orders | revenue  
-----------------+--------+----------
 Aigerim Seitova |      2 | 23500.00
(1 row)

shop_design=> INSERT INTO orders (id, client_id, ordered_on) VALUES (4, 2, '2026-10-01');
INSERT 0 1
shop_design=> INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (4, 1, 3, 12900.00);
INSERT 0 1
shop_design=> SELECT * FROM client_revenue ORDER BY name;
      name       | orders | revenue  
-----------------+--------+----------
 Aigerim Seitova |      2 | 23500.00
(1 row)

shop_design=> REFRESH MATERIALIZED VIEW client_revenue;
REFRESH MATERIALIZED VIEW
shop_design=> SELECT * FROM client_revenue ORDER BY name;
       name       | orders | revenue  
------------------+--------+----------
 Aigerim Seitova  |      2 | 23500.00
 Daniyar Akhmetov |      1 | 38700.00
(2 rows)