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

Продолжим разбираться с ограничениями таблиц. В конце предыдущей части я столкнулся с проблемой как предотвратить продажу билетов на несуществующие места. Первое, что приходит в голову - добавить в таблицу 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 где мы сохраняем сеансы. Так вот, хотелось бы избежать накладок в планировании так, чтобы не было возможности пересекающихся по времени сеансов в одном зале.

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

Попробуйте написать функцию для данной проверки самостоятельно. Ссылки на Ваш код оставляйте в комментах.

Я опубликую свой вариант в следующей части этой серии.