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

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

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

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;

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