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

В данной статье я хочу показать ход размышлений при проектировании базы данных. Я считаю что объяснить процесс намного важнее чем показать готовую структуру. Также хочу отметить что не претендую на истину в конечной инстанции, это только мои рассуждения и если Вы найдете их ошибочными или недостаточно хорошими – пожалуйста дайте мне знать в комментариях под статьей.

Итак, приступим.

Перед тем, как приступить к проектированию базы данных, следует разобраться в предметной базе, то есть понять из каких элементом состоит наш бизнес и как нам представить его в виде набора связанных таблиц. Будьте готовы к тому что в процессе придется вносить коррективы в уже имеющуюся структуру в случае выявления новых закономерностей.

Итак, как сказано в заголовке, мы имеем кинотеатр. Допустим наш кинотеатр имеет несколько залов. Каждый зал характеризуется названием, количеством мест, типом экрана (обычный или стерео) возможно в дальнейшем мы выявим другие необходимые нам параметры залов, но для начала этого будет достаточно.

Создадим таблицу 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"]).  |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
*/
		

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

Таким образом мы завершили проектирование базы данных для нашего небольшого бизнеса. Если в ходе прочтения у Вас возникли вопросы вы можете задать их в моём чате. 

Если Вам понравилась статья, Вы можете поддержать автора.