Проектирование реляционной БД от анализа предметной области до физической реализации на PostgreSQL — со сквозной аналитикой поверх готовой схемы.
Учётная система гостиницы: номерной фонд, гости, бронирования, дополнительные услуги и платежи. Проект прошёл полный цикл — анализ предметной области, ER-моделирование, нормализация до 4НФ, физическая реализация с ограничениями целостности, разграничение прав доступа по ролям и стратегия резервного копирования.
Поверх схемы собран слой аналитики: отраслевые метрики гостиничного бизнеса (Occupancy, ADR, RevPAR), ABC-анализ услуг, RFM-сегментация гостей и когортное удержание — на оконных функциях и CTE.
Репозиторий разворачивается одной командой и сразу наполняется демонстрационными данными за полтора года, поэтому все запросы можно выполнить и увидеть результат.
Стек: PostgreSQL 18 · PL/pgSQL · Docker Compose
make upПри первом старте PostgreSQL выполняет скрипты из sql/ по порядку: создаёт
схему, наполняет справочники, ставит триггеры, представления, индексы, роли и
генерирует демо-данные (40 номеров, 2500 гостей, ~2800 бронирований за
2025 – 2026 гг.).
Подключиться и выполнить аналитический запрос:
docker compose exec db psql -U hotel -d hotel -f /dev/stdin < analytics/01_occupancy.sqlИли через make: make up, make psql, make analytics, make reset.
Ниже — полный набор команд, которые есть в проекте, и что каждая из них делает.
| Команда | Что делает | Когда использовать |
|---|---|---|
make up |
Поднимает PostgreSQL через Docker Compose и ждёт, пока БД станет доступной. | Для первого запуска или после остановки контейнера. |
make down |
Останавливает контейнер, но сохраняет данные в том же томе. | Когда нужно остановить БД без потери данных. |
make reset |
Полностью удаляет контейнер и тома базы, затем снова запускает проект с нуля. | Для полной пересборки схемы и демо-данных. |
make psql |
Открывает интерактивную консоль PostgreSQL внутри контейнера. | Для ручного запроса, проверки данных или экспериментов в SQL. |
make check |
Печатает сводку по таблицам, представлениям и триггерам: число строк, количество views, триггеров. | Чтобы убедиться, что объекты созданы и база содержит данные. |
make test |
Запускает SQL-проверки ограничений целостности из tests/constraint_tests.sql. | Для проверки корректности правил БД и валидации ограничений. |
make benchmark |
Снимает EXPLAIN (ANALYZE, BUFFERS) до и после создания индексов на раздутой до 1 млн строк таблице. |
Чтобы увидеть, как индекс меняет план. После прогона нужен make reset. |
make occupancy |
Выполняет аналитический SQL из analytics/01_occupancy.sql. | Для расчёта occupancy, ADR, RevPAR и загрузки по номерам. |
make revenue |
Выполняет аналитический SQL из analytics/02_revenue.sql. | Для анализа выручки, услуг, ABC-анализа и чеков. |
make guests |
Выполняет аналитический SQL из analytics/03_guests.sql. | Для RFM-сегментации, удержания и анализа гостей. |
make analytics |
Запускает все три аналитических файла подряд: occupancy, revenue, guests. | Когда нужно прогнать весь аналитический слой одним действием. |
15 таблиц: 6 основных, 4 вспомогательных и 5 справочников.
erDiagram
RoomCategories ||--o{ Rooms : "категория"
RoomStatuses ||--o{ Rooms : "статус"
Rooms ||--o{ Bookings : "бронируется"
BookingStatuses ||--o{ Bookings : "статус"
Nations ||--o{ Guests : "гражданство"
Guests ||--o{ GuestContacts : "контакты"
Guests ||--o{ GuestDocuments : "документы"
DocTypes ||--o{ GuestDocuments : "вид"
Bookings ||--o{ BookingGuests : "M:N"
Guests ||--o{ BookingGuests : "M:N"
Bookings ||--o{ BookingServices : "M:N"
Services ||--o{ BookingServices : "M:N"
Bookings ||--o{ Payments : "оплата"
PaymentMethods ||--o{ Payments : "способ"
Rooms {
numeric r_num PK
varchar r_cat FK
numeric r_floor
numeric r_cap
varchar r_status FK
}
Bookings {
numeric b_id PK
numeric b_room FK
date b_booked
date b_begin
date b_end
date b_finish
varchar b_status FK
}
Guests {
numeric g_id PK
varchar g_fname
varchar g_name
varchar g_patr
date g_born
varchar g_nation FK
}
BookingGuests {
numeric bg_book PK "FK"
numeric bg_guest PK "FK"
char bg_main
}
BookingServices {
numeric bs_book PK "FK"
varchar bs_svc PK "FK"
date bs_date PK
numeric bs_qty
}
Payments {
varchar pay_num PK
numeric pay_book FK
numeric pay_sum
date pay_date
varchar pay_method FK
}
Services {
varchar s_name PK
numeric s_price
}
RoomCategories {
varchar rc_name PK
numeric rc_price
}
Полная схема со справочниками — в docs/03-normalization.md.
Четыре места, где выбор реализации был неочевиден.
Один номер нельзя занять дважды на пересекающиеся даты. Триггер здесь ломается при конкурентных транзакциях: две параллельные вставки не видят незакоммиченных строк друг друга и обе проходят проверку.
ALTER TABLE Bookings ADD CONSTRAINT no_booking_overlap
EXCLUDE USING gist (
b_room WITH =,
daterange(b_begin, b_end) WITH &&
) WHERE (b_status IN ('забронировано', 'заселён'));EXCLUDE опирается на GiST-индекс и проверяется СУБД атомарно. Диапазон
полуоткрытый — заезд в день выезда предыдущего гостя пересечением не считается.
Условие распадается на части разной природы, и каждая требует своего механизма. «Не более одного» — декларативно, частичным уникальным индексом. «Хотя бы один» — отложенным constraint-триггером: немедленная проверка отвергала бы вставку первой же строки состава гостей.
CREATE UNIQUE INDEX uq_main_guest ON BookingGuests (bg_book)
WHERE bg_main = 'Y';
CREATE CONSTRAINT TRIGGER tr_main_guest_exists
AFTER INSERT OR UPDATE OR DELETE ON BookingGuests
DEFERRABLE INITIALLY DEFERRED -- проверка на COMMIT
FOR EACH ROW EXECUTE PROCEDURE check_main_guest_exists();Устаревшее значение не удаляется, а помечается датой окончания — иначе удаление статуса из справочника оборвало бы ссылки исторических записей. Внешний ключ проверяет существование строки, но не её актуальность, поэтому попадание даты события в период актуальности контролируют триггеры.
Администратор оформляет заезды, но не должен трогать завершённые брони. Права на
базовую таблицу ему не выдаются вовсе — UPDATE выдан на представление
curr_bookings, отбирающее только активные брони. Правило переносится в схему
БД, и прямой запрос из SQL-клиента его не обойдёт.
Разбор всех десяти ограничений целостности и обоснование выбора реализации — docs/04-constraints.md.
Запросы в analytics/ работают поверх схемы и демо-данных.
| Файл | Что считает |
|---|---|
| 01_occupancy.sql | Occupancy, ADR, RevPAR по месяцам и категориям; сезонность по дням недели; простой номеров (gaps & islands); рейтинг номеров |
| 02_revenue.sql | структура выручки, скользящее среднее и YTD; ABC-анализ услуг; задолженности; медиана и p90 чека |
| 03_guests.sql | RFM-сегментация; когортное удержание; повторные визиты; глубина бронирования и отмены; кандидаты в программу лояльности; география |
Подбор свободных номеров на произвольные даты — основная задача менеджера —
оформлен функцией available_rooms в sql/04_views.sql:
представление параметры принимать не может. Условие занятости то же, что в
ограничении no_booking_overlap, поэтому выдача запроса и поведение схемы
разойтись не могут.
SELECT * FROM available_rooms('2026-09-10', '2026-09-15');
room_num | category | capacity | price_per_night | total_cost
----------+-------------+----------+-----------------+------------
102 | Одноместный | 2 | 3500.00 | 17500.00
104 | Одноместный | 1 | 3500.00 | 17500.00
502 | Люкс | 4 | 12000.00 | 60000.00Загрузка, средняя цена и доход на доступный номер по месяцам —
occ_mom показывает изменение загрузки к предыдущему месяцу:
month | sold_nights | available_nights | occupancy_pct | adr | revpar | occ_mom
---------+-------------+------------------+---------------+------+--------+---------
2025-01 | 537 | 1240 | 43.3 | 7215 | 3125 |
2025-02 | 624 | 1120 | 55.7 | 6961 | 3878 | 12.4
2025-03 | 712 | 1240 | 57.4 | 6894 | 3958 | 1.7
2025-04 | 717 | 1200 | 59.8 | 7210 | 4308 | 2.4
2025-05 | 695 | 1240 | 56.0 | 7092 | 3975 | -3.8
2025-06 | 748 | 1200 | 62.3 | 7021 | 4376 | 6.3
ABC-анализ услуг: четыре позиции из восьми дают 70 % выручки — именно они заслуживают продвижения.
service | price | orders | revenue | share_pct | cum_share_pct | abc_class
------------------------+---------+--------+-----------+-----------+---------------+-----------
Трансфер | 2500.00 | 621 | 3032500.00| 22.2 | 22.2 | A
SPA-зона | 2200.00 | 583 | 2556400.00| 18.7 | 40.8 | A
Поздний выезд | 1800.00 | 595 | 2136600.00| 15.6 | 56.5 | A
Дополнительная кровать | 1500.00 | 622 | 1861500.00| 13.6 | 70.1 | A
Мини-бар | 1200.00 | 630 | 1538400.00| 11.2 | 81.3 | B
Химчистка | 900.00 | 593 | 1074600.00| 7.9 | 89.2 | B
Завтрак | 750.00 | 596 | 897750.00| 6.6 | 95.7 | C
Парковка | 500.00 | 585 | 586500.00| 4.3 | 100.0 | C
Когортное удержание: доля гостей когорты, вернувшихся через N месяцев. Пустые ячейки — месяцы, которые для молодых когорт ещё не наступили, и ноль в них был бы ошибкой.
cohort | cohort_size | m1_pct | m2_pct | m3_pct | m4_pct | m5_pct | m6_pct
---------+-------------+--------+--------+--------+--------+--------+--------
2025-01 | 172 | 9 | 12 | 12 | 8 | 11 | 13
2025-02 | 173 | 12 | 9 | 8 | 9 | 11 | 10
2025-03 | 153 | 8 | 7 | 10 | 8 | 8 | 9
...
2026-06 | 53 | 13 | 9 | | | |
2026-07 | 34 | 12 | | | | |
Ограничение, которое ничего не отвергает, бесполезно.
tests/constraint_tests.sql выполняет 15 проверок:
каждая пытается провести заведомо недопустимую операцию и ожидает отказа СУБД,
а часть тестов, наоборот, подтверждает, что допустимая операция проходит
(заезд в день выезда предыдущего гостя, отменённая бронь не блокирует даты).
Файл работает в одной транзакции и завершается ROLLBACK, поэтому база
остаётся нетронутой.
step | outcome
------+---------------------------------------------------------------
1 | ok CHECK дата выезда не позже даты заезда
2 | ok EXCLUDE пересечение бронирований одного номера
3 | ok EXCLUDE заезд в день выезда предыдущего гостя разрешён
4 | ok EXCLUDE отменённая бронь не блокирует даты
5 | ok ТРИГГЕР превышение вместимости номера
6 | ok ИНДЕКС второй основной гость в бронировании
7 | ok ТРИГГЕР бронирование без основного гостя
8 | ok CHECK контакт гостя без телефона и email
9 | ok ТРИГГЕР платёж раньше даты бронирования
10 | ok ТРИГГЕР ссылка на неактуальный способ оплаты
11 | ok ТРИГГЕР дата рождения гостя в будущем
12 | ok CHECK отрицательная стоимость категории
13 | ok FK удаление категории при существующих номерах
14 | ok ТРИГГЕР заселение гостя без действительного документа
15 | ok ПРЕДСТАВЛЕНИЕ смена статуса через curr_bookings применилась
passed | failed
--------+--------
15 | 0
Скрипт benchmark/index_benchmark.sql снимает
EXPLAIN (ANALYZE, BUFFERS) до и после создания индекса. Замеры ниже сделаны на
PostgreSQL 18.1, прогретом кеше и таблице Payments, раздутой до 1 млн строк.
Почему пришлось раздувать таблицу. На демо-объёме (4877 платежей) выборка за неделю выполняется за 0.324 мс. Планировщик берёт индекс и там, но при таком времени обосновать что-либо замерами невозможно — разница тонет в погрешности. Индексы начинают окупаться на объёме, и честнее это показать, чем утверждать.
| Запрос (1 млн строк) | План | Время |
|---|---|---|
| Выборка за неделю, индекса нет | Parallel Seq Scan | 101.3 мс |
Она же, есть idx_pay_date |
Bitmap Index Scan | 15.5 мс |
Месяц через date_trunc(pay_date) |
Parallel Seq Scan | 205.2 мс |
| Тот же месяц, переписанный диапазоном | Bitmap Index Scan | 23.5 мс |
Разница не только во времени: без индекса запрос читает 13 050 страниц и отбрасывает 993 тысячи строк фильтром, с индексом — 7 713 страниц.
Функция над индексируемым столбцом. Оба варианта возвращают одни и те же
49 978 строк (скрипт проверяет это отдельным запросом), но date_trunc('month', pay_date) = '2026-03-01' работает вдевятеро дольше: в индексе лежат значения
столбца, а не значения функции от него, поэтому условие к индексу неприменимо.
Лечится переписыванием в диапазон pay_date >= ... AND pay_date < ... — либо
индексом по выражению, если от функции не избавиться.
Порядок полей в составном индексе — с оговоркой про версию. Классическое
правило: индекс (pay_method, pay_date) не поможет запросу, который фильтрует
только по дате. На PostgreSQL 18 это уже не так — здесь появился skip scan, и
при трёх различных значениях pay_method планировщик обходит индекс отдельным
проходом на каждое значение:
| Запрос | План | Время |
|---|---|---|
Фильтр только по pay_date |
Bitmap Index Scan, Index Searches: 7 | 22.2 мс |
Фильтр по pay_method + pay_date |
Bitmap Index Scan, Index Searches: 1 | 13.6 мс |
sql/
01_schema.sql таблицы, ключи, CHECK, EXCLUDE, частичный индекс
02_reference_data.sql наполнение справочников
03_triggers.sql процедурные ограничения целостности
04_views.sql 7 представлений + функция подбора номеров
05_indexes.sql индексы под запросы представлений
06_roles_grants.sql 4 роли и матрица прав
07_sample_data.sql генератор демо-данных
analytics/ аналитические запросы
tests/ проверка ограничений целостности
benchmark/ замеры планов выполнения (в развёртывание не входит)
docs/ проектная документация и исходный отчёт (PDF)
docker-compose.yml
Makefile
| Раздел | Содержание |
|---|---|
| 1. Анализ предметной области | правила предметной области, сущности, ER-диаграмма, группы пользователей |
| 2. Логическое проектирование | преобразование ER в схему, отношения до нормализации |
| 3. Нормализация до 4НФ | разбор каждого отношения, итоговая схема |
| 4. Ограничения целостности | 10 ограничений и обоснование способа реализации |
| 5. Разграничение доступа | матрица прав, доступ через представления |
| 6. Резервное копирование | PITR, расписание, сроки хранения |
Исходный отчёт по работе: docs/report.pdf.