В данной статье я хочу показать ход размышлений при проектировании базы данных. Я считаю что объяснить процесс намного важнее чем показать готовую структуру. Также хочу отметить что не претендую на истину в конечной инстанции, это только мои рассуждения и если Вы найдете их ошибочными или недостаточно хорошими — пожалуйста дайте мне знать в комментариях под статьей.
Итак, приступим.
Перед тем, как приступить к проектированию базы данных, следует разобраться в предметной базе, то есть понять из каких элементом состоит наш бизнес и как нам представить его в виде набора связанных таблиц. Будьте готовы к тому что в процессе придется вносить коррективы в уже имеющуюся структуру в случае выявления новых закономерностей.
Java-разработчик в отдел развития ITSM систем Открытие, Удалённо, По итогам собеседования tproger.ru Вакансии на tproger.ru
Итак, как сказано в заголовке, мы имеем кинотеатр. Допустим наш кинотеатр имеет несколько залов. Каждый зал характеризуется названием, количеством мест, типом экрана (обычный или стерео) возможно в дальнейшем мы выявим другие необходимые нам параметры залов, но для начала этого будет достаточно.
Создадим таблицу halls, в данной статье для примеров я буду использовать синтаксис PostgreSQL поэтому первый запрос будет следующим:
create table halls ( id serial primary key, name text, seats_count int, screen_type ? );
Рассмотрим структуру подробнее:
create table halls ( id serial primary key, name text, seats_count int, screen_type text check (screen_type in (‘regular’, ‘stereo’)) ); insert into halls (name, seats_count, screen_type) values (‘Blue’, 25, ‘regular’); insert into halls (name, seats_count, screen_type) values (‘Gold’, 15, ‘3d’); select * from halls;
Если Вы проверите данный код на SQLize.online то увидите что данная конструкция предотвращает вставку неправильных значений. Недостатком такого подхода является что для добавления нового типа зала потребуется менять структуру таблицы. В нашем случае удалять и создавать заново ограничение колонки. В примере ниже код для добавления зала с 3d:
alter table halls drop constraint halls_screen_type_check; alter table halls add constraint halls_screen_type_check check (screen_type in (‘regular’, ‘stereo’, ‘3d’));
create table screen_types ( id serial primary key, name text ); insert into screen_types (name) values (‘regular’), (‘stereo’); create table halls ( id serial primary key, name text, seets_count int, screen_type int references screen_types(id) );
В этом варианте мы не имеем проблем с добавлением типа экрана, однако при добавлении зала должны помнить числовые индексы типов экранов и при выборке данных использовать `join`.
insert into halls (name, seets_count, screen_type) values (‘Blue’, 25, 1); insert into halls (name, seets_count, screen_type) values (‘Gold’, 15, 2); select halls.id, halls.name, seets_count, screen_types.name as screen_type from halls join screen_types on screen_types.id = halls.screen_type;
create type screen_type AS ENUM (‘regular’, ‘stereo’, ‘3d’); create table halls ( id serial primary key, name text, seets_count int, screen_type screen_type ); insert into halls (name, seets_count, screen_type) values (‘Blue’, 25, ‘regualr’); insert into halls (name, seets_count, screen_type) values (‘Gold’, 15, ‘3d’); select * from halls;
Добавление нового типа выполняется командой `alter type`
alter type screen_type add value ‘4d’;
Снова напомню что все вышеприведенные запросы могут быть проверенны онлайн на SQLize.online
Выбор варианта зависит от нескольких причин. Как часто прогнозируется изменение типов, как много типов планируется иметь в базе и даже от Вашего опыта работы. Вариант с таблицей справочником — универсальный. Я в нашем случае остановлюсь на варианте текста с ограничением (типы экранов практически не меняются, если нужно будет добавить тип, то таблица залов будет содержать не более десятка залов, и ее изменение не займет много времени зато я получу экономию на объединениях таблиц).
Если Вы не согласны с моим выбором — добро пожаловать в комменты.
А мы продолжаем. Следующим базовым компонентом являются фильмы.
Первый набросок таблицы:
create table films ( id serial primary key, — первичный ключ title text, — название фильма description text, — краткое описание, сюжет release_year int, — год выхода на экран duration int, — длительность в минутах director int, genre int );
Если первые 5 полей вопросов не вызывают то что скажете по поводу режиссера? Я надеюсь Вы ответили — создаем таблицу справочник + внешний ключ. Я предлагаю назвать таблицу persons . Почему не directors спросите вы. Потому что возможно мы будем хранить список актеров и может оказаться что один и тот же человек будет выступать в нескольких ролях например быть режиссером фтльма и сам в нем сниматься. Так что это некий задел на будущее.
Так что создадим таблицу persons и не забудем задать ограничение в виде внешнего ключа между таблицами:
create table persons ( id serial primary key, name text, year_of_birth int ); alter table films add constraint film_director_fk foreign key (director) references persons(id);
Теперь посмотрим на поле genre Вы можете сказать поступим так же как с режиссерами. Однако я возражу, что редко какой фильм можно отнести к одному жанру поэтому мы построим отношение многие ко многим. Один фильм может относиться к нескольким категориям, и естественно к каждой категории относятся множество фильмов.
Создадим таблицу жанров и связующую таблицу между жанрами и фильмами:
create table genres ( id serial primary key, genre text ); create table film_genres ( film int references films(id), genre int references genres(id), primary key (film, genre) );
Обратите внимание в таблице film_genres первичным ключом будет сочетание двух полей film и genre
После этого мы можем удалить столбец genre из таблицы films:
alter table films drop column genre;
Таким образом мы формально закрепили данные о кинозалах и фильмах в структуре таблиц.
Продолжим проектирование нашей базы. Что в бизнесе кинотеатра связывает залы и фильмы? Конечно сеансы.
create table film_screenings ( id serial primary key, film int references films(id), — фильм hall int references halls(id), — кинозал start_at timestamp, — дата и время начала сеанса price numeric — цена билета );
Пока достаточно просто. На что следует обратить внимание: столбец id имеет тип serial, то есть int + autoincrement Максимальное значение int в PostgreSQL — 2147483647 Прикинем если у нас будет 10 залов и в каждом мы будем крутить по 10 сеансов в день 365 дней в году 10*10*365 = 36500 в год.
select 2147483647 / (10*10*365); — 58835
Получается больше чем 58 тысяч лет. Хватит даже если мы увеличим количество залов и сеансов в 10 раз.
Следующая таблица — билеты:
create table tickets ( id uuid primary key default uuid_generate_v4(), screening int references film_screenings(id), — сеанс row smallint, — ряд seat smallint, — место price numeric — цена );
В принципе достаточно стандартная структура таблицы. В чём её особенности, Во первых здесь я решил использовать тип uuid в качестве первичного ключа.
Тип данных uuid сохраняет универсальные уникальные идентификаторы (Universally Unique Identifiers, UUID) в случае PostgreSQL это будет строка вида a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 Функция uuid_generate_v4() обеспечит нам генерацию уникальных идентификаторов при вставке новых строк.
Почему я выбрал этот тип вместо привычного serial? Дело в том что номер билета скорее всего будет отпечатан на бумажном носителе или представлен в электронной копии билета. То есть эта информация будет доступна вне нашей базы данных. В этом случае некто может получить внутреннюю информацию о нашем бизнесе, например покупая билеты каждое воскресенье можно получить информацию о недельных продажах. Кроме этого в случае недостаточной безопасности нашего вебсайта злоумышленник сможет получить доступ к данным чужих билетов просто итерируя id. В случае с uuid вероятность такого сценария близка к нулю.
Второй вопрос к таблице tickets это повторение поля price, которое уже существует в таблице сеансов film_screenings Дело в том что у этих пролей разная нагрузка. В случае сеанса — это рекомендованная цена, то есть цена которая будет указана на нашей афише. В случае билета это реальная сумма денег заплаченная за каждый конкретный билет. Она может отличаться от рекомендованной в случае льготных билетов или билетов купленных по промо акции.
Это поле из таблицы tickets мы будем использовать для подсчета наших финансовых показателей
Во избежание коллизий я пожалуй переименую поле в таблице сеансов
alter table film_screenings rename column price to recomended_price;
Следующее, что я хочу сделать — это защитить таблицу tickets от ввода ошибочных данных. Во-первых, объявим уникальный индекс по трем полям (screening, row and seat) это защитит нас от продажи двух билетов на одно и то же место в один сеанс.
create unique index ticket_seat_unique on tickets (screening, row, seat);
Далее я хочу предотвратить «овербукинг» — то есть продажу большего количества билетов чем есть мест в зале. Так как это ограничение накладывается внешней таблицей то я не смогу использовать индексы или ограничения таблицы (По крайней мере я не знаю как это сделать. Если у Вас есть варианты — пишите в комментариях) Я для этой цели создам триггер.
В PostgreSQL триггер состоит из триггерной функции и самого триггера который её вызывает. Ниже мой код функции и сам триггер:
create function check_overbooking() returns trigger AS $$ declare hall text; declare hall_seats_count int; declare tickets_sold int; begin — найдем имя зала соответствующего билету — и количество мест в нём select halls.name, seats_count into hall, hall_seats_count from halls join film_screenings on film_screenings.hall = halls.id where film_screenings.id = new.screening; — подсчитаем количество уже проданных билетов select count(*) into tickets_sold from tickets where screening = new.screening; — если свободных мест нет выкидываем исключение if (tickets_sold >= hall_seats_count) then raise exception ‘The hall %d is full’, hall; return null; end if; return new; end; $$ language plpgsql; create trigger check_overbooking before insert on tickets for each row execute function check_overbooking();
Немного подумав, я решил что данный код не защитит нас от продажи билета на несуществующее место в зале и понял что недостаточно хранить количество мест в залах. Необходимо иметь таблицу с их конфигурацией.
Первое, что приходит в голову — добавить в таблицу halls колонки с количеством рядов и кресел. Однако в большинстве залов, где я бывал, количество мест в ряду может меняться от ряда к ряду.
Наиболее оптимальным решением будет создание таблицы halls_seats со следующей структурой:
create table hall_seats ( hall int references halls(id), row int, seats int, primary key (hall, row) );
Поле halls связано внешним ключом с релевантной таблицей, что не даст нам внести данные несуществующего зала. Первичный ключ по полям hall и row не даст дважды создать один ряд в том же зале.
Заполним таблицу в соответствии со схемой кинозала «Луч»:
insert into hall_seats values (1, 1, 5),(1, 2, 7),(1, 3, 6),(1, 4, 7), (1, 5, 7),(1, 6, 7),(1, 7, 7),(1, 8, 7), (1, 9, 6),(1, 10, 7),(1, 11, 10);
Ну и добавим индекс по полю hall для ускорения выборки данных из таблицы.
create index hall_seats_hall_ix on hall_seats(hall);
После этого вернемся на шаг назад и перепишем триггерную функцию ограничивающую продажу фиктивных билетов следующим образом:
create or replace function check_overbooking() returns trigger AS $func$ declare seat_possible boolean; begin select true into seat_possible from hall_seats join film_screenings on film_screenings.hall = hall_seats.hall where film_screenings.id = new.screening and hall_seats.row = new.row and new.seat between 1 and hall_seats.seats; if (seat_possible is null or not seat_possible) then raise exception ‘The seat % in row % not exists’, new.seat, new.row; return null; end if; return new; end; $func$ language plpgsql;
Запрос в этой функции вернет true только в случае если в зале соответствующем сеансу указанному на билете существует выбранное место в указанном ряду. Для проверки кода воспользуйтесь SQLize.online
Далее нам нужна еще одна проверка. Помните таблицу film_screenings где мы сохраняем сеансы. Так вот, хотелось бы избежать накладок в планировании так, чтобы не было возможности пересекающихся по времени сеансов в одном зале.
В качестве исходных данных мы можем получить время начала предыдущего сеанса в зале, продолжительность фильма плюс запас времени (допустим в полчаса) на уборку зала и вход-выход кинозрителей.
Для решенияэтой проблемы я решил воспользоваться ограничением EXCLUDE предоставляемым PostgreSQL.
Кратко EXCLUDE похож на UNIQUE однако дает возможность использовать логику сложнее чем простое равенство. Например в нашем случае я воспользуюсь им для проверки не пересечения временных интервалов.
Помните в начале мы создали таблицу сеансов. Мне потребуется внести в нее небольшое изменение.
create table film_screenings ( id serial primary key, film int references films(id), — фильм hall int references halls(id), — кинозал start_at timestamp, — дата и время начала сеанса recomended_price numeric — рекомендованная цена билета ); — удаляю столбец start_at alter table film_screenings drop column start_at; — добавляю колонку screening_time типа tstzrange alter table film_screenings add column screening_time tstzrange;
В коде выше, я удалил колонку start_at и вместо неё добавил screening_time. Я решил использовать тип tstzrange для того чтобы в одном поле хранить начало и конец сеанса. Кроме этого для данного типа можно воспользоваться ограничением EXCLUDE что я и сделал.
alter table film_screenings add constraint no_screening_time_overlap exclude using gist ( screening_time WITH undefinedundefined — check for overlapping ranges );
Давайте убедимся что ограничение работает:
insert into film_screenings (film, hall, recomended_price, screening_time) values (1, 1, 39.99, ‘[«2023-01-01 19:00:00», «2023-01-01 20:45:00»]’); — Попробуем внести строку с перекрытием: insert into film_screenings (film, hall, recomended_price, screening_time) values (1, 1, 39.99, ‘[«2023-01-01 20:45:00», «2023-01-01 23:00:00»]’); — и ожидаемо получаем ошибку: /* +==========================================================================================================================================================================================+ | insert into film_screenings (film, hall, recomended_price, screening_time) | | values (1, 1, 39.99, ‘[«2023-01-01 20:45:00», «2023-01-01 23:00:00»]’) | +==========================================================================================================================================================================================+ | ERROR: conflicting key value violates exclusion constraint «no_screening_time_overlap» | | DETAIL: Key (screening_time)=([«2023-01-01 20:45:00+00″,»2023-01-01 23:00:00+00»]) conflicts with existing key (screening_time)=([«2023-01-01 19:00:00+00″,»2023-01-01 20:45:00+00»]). | +——————————————————————————————————————————————————————————————+ */
Этим мы гарантируем консистентность данных в таблице сеансов.
Таким образом мы завершили проектирование базы данных для нашего небольшого бизнеса. Если в ходе прочтения у Вас возникли вопросы вы можете задать их в моём чате.
Итак, приступим.
Перед тем, как приступить к проектированию базы данных, следует разобраться в предметной базе, то есть понять из каких элементом состоит наш бизнес и как нам представить его в виде набора связанных таблиц. Будьте готовы к тому что в процессе придется вносить коррективы в уже имеющуюся структуру в случае выявления новых закономерностей.
Java-разработчик в отдел развития ITSM систем Открытие, Удалённо, По итогам собеседования tproger.ru Вакансии на tproger.ru
Итак, как сказано в заголовке, мы имеем кинотеатр. Допустим наш кинотеатр имеет несколько залов. Каждый зал характеризуется названием, количеством мест, типом экрана (обычный или стерео) возможно в дальнейшем мы выявим другие необходимые нам параметры залов, но для начала этого будет достаточно.
Создадим таблицу halls, в данной статье для примеров я буду использовать синтаксис PostgreSQL поэтому первый запрос будет следующим:
create table halls ( id serial primary key, name text, seats_count int, screen_type ? );
Рассмотрим структуру подробнее:
- id имеет тип serial — это самый простой способ использовать авто инкремент кроме этого поле будет являться первичным ключом к строкам таблицы. (Надеюсь это понятно. Если нет пишите в комментах),
- name типа text если вы будете писать для MySQL, то скорее всего вам стоит использовать varchar, но в PostgreSQL эти типы почти идентичны, а само слово text короче,
- seats_count — integer как же иначе? К тому же нам скорее всего придётся выполнять арифметические операции над этим полем (например посчитать суммарную вместимость залов)(если вы заметили я придерживаюсь snake notation в названии столбцов. Это не принципиально если Вам больше нравится camelCase Вы вольны использовать её, только будьте последовательны и не смешивайте разные типы в одном проекте),
- screen_type — тут я поставил знак вопроса. Почему? Дело в том что я могу использовать тип text и заносить строки ‘regular’ или ‘stereo’ могу создать свой тип данных состоящий из этих двух значений или создать дополнительную таблицу — справочник.
Рассмотрим все варианты:- Использовать текст — просто, наглядно, требует задания ограничений на поле:
create table halls ( id serial primary key, name text, seats_count int, screen_type text check (screen_type in (‘regular’, ‘stereo’)) ); insert into halls (name, seats_count, screen_type) values (‘Blue’, 25, ‘regular’); insert into halls (name, seats_count, screen_type) values (‘Gold’, 15, ‘3d’); select * from halls;
Если Вы проверите данный код на SQLize.online то увидите что данная конструкция предотвращает вставку неправильных значений. Недостатком такого подхода является что для добавления нового типа зала потребуется менять структуру таблицы. В нашем случае удалять и создавать заново ограничение колонки. В примере ниже код для добавления зала с 3d:
alter table halls drop constraint halls_screen_type_check; alter table halls add constraint halls_screen_type_check check (screen_type in (‘regular’, ‘stereo’, ‘3d’));
- Другой способ — использовать таблицу справочник. В этом случае сначала создаем таблицу screen_types заносим в неё строки ‘regular’ и ‘stereo’ а в таблице залов будем использовать числовое поле screen_type связанное внешним ключом со справочником:
create table screen_types ( id serial primary key, name text ); insert into screen_types (name) values (‘regular’), (‘stereo’); create table halls ( id serial primary key, name text, seets_count int, screen_type int references screen_types(id) );
В этом варианте мы не имеем проблем с добавлением типа экрана, однако при добавлении зала должны помнить числовые индексы типов экранов и при выборке данных использовать `join`.
insert into halls (name, seets_count, screen_type) values (‘Blue’, 25, 1); insert into halls (name, seets_count, screen_type) values (‘Gold’, 15, 2); select halls.id, halls.name, seets_count, screen_types.name as screen_type from halls join screen_types on screen_types.id = halls.screen_type;
- Третий способ — создание своего типа:
create type screen_type AS ENUM (‘regular’, ‘stereo’, ‘3d’); create table halls ( id serial primary key, name text, seets_count int, screen_type screen_type ); insert into halls (name, seets_count, screen_type) values (‘Blue’, 25, ‘regualr’); insert into halls (name, seets_count, screen_type) values (‘Gold’, 15, ‘3d’); select * from halls;
Добавление нового типа выполняется командой `alter type`
alter type screen_type add value ‘4d’;
Снова напомню что все вышеприведенные запросы могут быть проверенны онлайн на SQLize.online
Выбор варианта зависит от нескольких причин. Как часто прогнозируется изменение типов, как много типов планируется иметь в базе и даже от Вашего опыта работы. Вариант с таблицей справочником — универсальный. Я в нашем случае остановлюсь на варианте текста с ограничением (типы экранов практически не меняются, если нужно будет добавить тип, то таблица залов будет содержать не более десятка залов, и ее изменение не займет много времени зато я получу экономию на объединениях таблиц).
Если Вы не согласны с моим выбором — добро пожаловать в комменты.
А мы продолжаем. Следующим базовым компонентом являются фильмы.
Первый набросок таблицы:
create table films ( id serial primary key, — первичный ключ title text, — название фильма description text, — краткое описание, сюжет release_year int, — год выхода на экран duration int, — длительность в минутах director int, genre int );
Если первые 5 полей вопросов не вызывают то что скажете по поводу режиссера? Я надеюсь Вы ответили — создаем таблицу справочник + внешний ключ. Я предлагаю назвать таблицу persons . Почему не directors спросите вы. Потому что возможно мы будем хранить список актеров и может оказаться что один и тот же человек будет выступать в нескольких ролях например быть режиссером фтльма и сам в нем сниматься. Так что это некий задел на будущее.
Так что создадим таблицу persons и не забудем задать ограничение в виде внешнего ключа между таблицами:
create table persons ( id serial primary key, name text, year_of_birth int ); alter table films add constraint film_director_fk foreign key (director) references persons(id);
Теперь посмотрим на поле genre Вы можете сказать поступим так же как с режиссерами. Однако я возражу, что редко какой фильм можно отнести к одному жанру поэтому мы построим отношение многие ко многим. Один фильм может относиться к нескольким категориям, и естественно к каждой категории относятся множество фильмов.
Создадим таблицу жанров и связующую таблицу между жанрами и фильмами:
create table genres ( id serial primary key, genre text ); create table film_genres ( film int references films(id), genre int references genres(id), primary key (film, genre) );
Обратите внимание в таблице film_genres первичным ключом будет сочетание двух полей film и genre
После этого мы можем удалить столбец genre из таблицы films:
alter table films drop column genre;
Таким образом мы формально закрепили данные о кинозалах и фильмах в структуре таблиц.
Продолжим проектирование нашей базы. Что в бизнесе кинотеатра связывает залы и фильмы? Конечно сеансы.
create table film_screenings ( id serial primary key, film int references films(id), — фильм hall int references halls(id), — кинозал start_at timestamp, — дата и время начала сеанса price numeric — цена билета );
Пока достаточно просто. На что следует обратить внимание: столбец id имеет тип serial, то есть int + autoincrement Максимальное значение int в PostgreSQL — 2147483647 Прикинем если у нас будет 10 залов и в каждом мы будем крутить по 10 сеансов в день 365 дней в году 10*10*365 = 36500 в год.
select 2147483647 / (10*10*365); — 58835
Получается больше чем 58 тысяч лет. Хватит даже если мы увеличим количество залов и сеансов в 10 раз.
Следующая таблица — билеты:
create table tickets ( id uuid primary key default uuid_generate_v4(), screening int references film_screenings(id), — сеанс row smallint, — ряд seat smallint, — место price numeric — цена );
В принципе достаточно стандартная структура таблицы. В чём её особенности, Во первых здесь я решил использовать тип uuid в качестве первичного ключа.
Тип данных uuid сохраняет универсальные уникальные идентификаторы (Universally Unique Identifiers, UUID) в случае PostgreSQL это будет строка вида a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 Функция uuid_generate_v4() обеспечит нам генерацию уникальных идентификаторов при вставке новых строк.
Почему я выбрал этот тип вместо привычного serial? Дело в том что номер билета скорее всего будет отпечатан на бумажном носителе или представлен в электронной копии билета. То есть эта информация будет доступна вне нашей базы данных. В этом случае некто может получить внутреннюю информацию о нашем бизнесе, например покупая билеты каждое воскресенье можно получить информацию о недельных продажах. Кроме этого в случае недостаточной безопасности нашего вебсайта злоумышленник сможет получить доступ к данным чужих билетов просто итерируя id. В случае с uuid вероятность такого сценария близка к нулю.
Второй вопрос к таблице tickets это повторение поля price, которое уже существует в таблице сеансов film_screenings Дело в том что у этих пролей разная нагрузка. В случае сеанса — это рекомендованная цена, то есть цена которая будет указана на нашей афише. В случае билета это реальная сумма денег заплаченная за каждый конкретный билет. Она может отличаться от рекомендованной в случае льготных билетов или билетов купленных по промо акции.
Это поле из таблицы tickets мы будем использовать для подсчета наших финансовых показателей
Во избежание коллизий я пожалуй переименую поле в таблице сеансов
alter table film_screenings rename column price to recomended_price;
Следующее, что я хочу сделать — это защитить таблицу tickets от ввода ошибочных данных. Во-первых, объявим уникальный индекс по трем полям (screening, row and seat) это защитит нас от продажи двух билетов на одно и то же место в один сеанс.
create unique index ticket_seat_unique on tickets (screening, row, seat);
Далее я хочу предотвратить «овербукинг» — то есть продажу большего количества билетов чем есть мест в зале. Так как это ограничение накладывается внешней таблицей то я не смогу использовать индексы или ограничения таблицы (По крайней мере я не знаю как это сделать. Если у Вас есть варианты — пишите в комментариях) Я для этой цели создам триггер.
В PostgreSQL триггер состоит из триггерной функции и самого триггера который её вызывает. Ниже мой код функции и сам триггер:
create function check_overbooking() returns trigger AS $$ declare hall text; declare hall_seats_count int; declare tickets_sold int; begin — найдем имя зала соответствующего билету — и количество мест в нём select halls.name, seats_count into hall, hall_seats_count from halls join film_screenings on film_screenings.hall = halls.id where film_screenings.id = new.screening; — подсчитаем количество уже проданных билетов select count(*) into tickets_sold from tickets where screening = new.screening; — если свободных мест нет выкидываем исключение if (tickets_sold >= hall_seats_count) then raise exception ‘The hall %d is full’, hall; return null; end if; return new; end; $$ language plpgsql; create trigger check_overbooking before insert on tickets for each row execute function check_overbooking();
Немного подумав, я решил что данный код не защитит нас от продажи билета на несуществующее место в зале и понял что недостаточно хранить количество мест в залах. Необходимо иметь таблицу с их конфигурацией.
Первое, что приходит в голову — добавить в таблицу halls колонки с количеством рядов и кресел. Однако в большинстве залов, где я бывал, количество мест в ряду может меняться от ряда к ряду.
Наиболее оптимальным решением будет создание таблицы halls_seats со следующей структурой:
create table hall_seats ( hall int references halls(id), row int, seats int, primary key (hall, row) );
Поле halls связано внешним ключом с релевантной таблицей, что не даст нам внести данные несуществующего зала. Первичный ключ по полям hall и row не даст дважды создать один ряд в том же зале.
Заполним таблицу в соответствии со схемой кинозала «Луч»:
insert into hall_seats values (1, 1, 5),(1, 2, 7),(1, 3, 6),(1, 4, 7), (1, 5, 7),(1, 6, 7),(1, 7, 7),(1, 8, 7), (1, 9, 6),(1, 10, 7),(1, 11, 10);
Ну и добавим индекс по полю hall для ускорения выборки данных из таблицы.
create index hall_seats_hall_ix on hall_seats(hall);
После этого вернемся на шаг назад и перепишем триггерную функцию ограничивающую продажу фиктивных билетов следующим образом:
create or replace function check_overbooking() returns trigger AS $func$ declare seat_possible boolean; begin select true into seat_possible from hall_seats join film_screenings on film_screenings.hall = hall_seats.hall where film_screenings.id = new.screening and hall_seats.row = new.row and new.seat between 1 and hall_seats.seats; if (seat_possible is null or not seat_possible) then raise exception ‘The seat % in row % not exists’, new.seat, new.row; return null; end if; return new; end; $func$ language plpgsql;
Запрос в этой функции вернет true только в случае если в зале соответствующем сеансу указанному на билете существует выбранное место в указанном ряду. Для проверки кода воспользуйтесь SQLize.online
Далее нам нужна еще одна проверка. Помните таблицу film_screenings где мы сохраняем сеансы. Так вот, хотелось бы избежать накладок в планировании так, чтобы не было возможности пересекающихся по времени сеансов в одном зале.
В качестве исходных данных мы можем получить время начала предыдущего сеанса в зале, продолжительность фильма плюс запас времени (допустим в полчаса) на уборку зала и вход-выход кинозрителей.
Для решенияэтой проблемы я решил воспользоваться ограничением EXCLUDE предоставляемым PostgreSQL.
Кратко EXCLUDE похож на UNIQUE однако дает возможность использовать логику сложнее чем простое равенство. Например в нашем случае я воспользуюсь им для проверки не пересечения временных интервалов.
Помните в начале мы создали таблицу сеансов. Мне потребуется внести в нее небольшое изменение.
create table film_screenings ( id serial primary key, film int references films(id), — фильм hall int references halls(id), — кинозал start_at timestamp, — дата и время начала сеанса recomended_price numeric — рекомендованная цена билета ); — удаляю столбец start_at alter table film_screenings drop column start_at; — добавляю колонку screening_time типа tstzrange alter table film_screenings add column screening_time tstzrange;
В коде выше, я удалил колонку start_at и вместо неё добавил screening_time. Я решил использовать тип tstzrange для того чтобы в одном поле хранить начало и конец сеанса. Кроме этого для данного типа можно воспользоваться ограничением EXCLUDE что я и сделал.
alter table film_screenings add constraint no_screening_time_overlap exclude using gist ( screening_time WITH undefinedundefined — check for overlapping ranges );
Давайте убедимся что ограничение работает:
insert into film_screenings (film, hall, recomended_price, screening_time) values (1, 1, 39.99, ‘[«2023-01-01 19:00:00», «2023-01-01 20:45:00»]’); — Попробуем внести строку с перекрытием: insert into film_screenings (film, hall, recomended_price, screening_time) values (1, 1, 39.99, ‘[«2023-01-01 20:45:00», «2023-01-01 23:00:00»]’); — и ожидаемо получаем ошибку: /* +==========================================================================================================================================================================================+ | insert into film_screenings (film, hall, recomended_price, screening_time) | | values (1, 1, 39.99, ‘[«2023-01-01 20:45:00», «2023-01-01 23:00:00»]’) | +==========================================================================================================================================================================================+ | ERROR: conflicting key value violates exclusion constraint «no_screening_time_overlap» | | DETAIL: Key (screening_time)=([«2023-01-01 20:45:00+00″,»2023-01-01 23:00:00+00»]) conflicts with existing key (screening_time)=([«2023-01-01 19:00:00+00″,»2023-01-01 20:45:00+00»]). | +——————————————————————————————————————————————————————————————+ */
Этим мы гарантируем консистентность данных в таблице сеансов.
Таким образом мы завершили проектирование базы данных для нашего небольшого бизнеса. Если в ходе прочтения у Вас возникли вопросы вы можете задать их в моём чате.
