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

Продолжаем проектирование базы начатое в предыдущей статье. В ней мы рассмотрели создание структуры основных таблиц. Кинозалов и фильмов. Продолжим проектирование нашей базы. Что в бизнесе кинотеатра связывает залы и фильмы? Конечно сеансы.

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();

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

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