Проектирование базы данных для маленьких на примере кинотеатра. Часть 4 - сеансы

В предыдущей части я остановился на планировании таблицы сеансов. Мне нужно было найти способ предотвратить создание пересекающихся по времени сеансов в одном зале. И в этом случае я решил воспользоваться ограничением 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 && -- 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"]).  |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Таким образом мы гарантируем консистентность данных в таблице сеансов.

В следующем цикле статей мы рассмотрим базовый анализ данных нашего кинотеатра. А можете экперементировать в построении баз данных на SQLize.online