Учебное приложение
Книжный магазин
16
Авторские права
© Postgres Professional, 2017–2024
Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк
Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Обзор приложения «Книжный магазин»
Проектирование схемы данных, нормализация
Итоговая схема данных приложения
Организация интерфейса между клиентом и сервером
3
Приложение
Приложение состоит из нескольких частей, представленных вкладками.
«Магазин» — это интерфейс веб-пользователя, в котором он может
покупать книги.
Остальные вкладки соответствуют внутреннему интерфейсу,
доступному только сотрудникам («админка» сайта).
«Каталог» — интерфейс кладовщика, в котором он может заказывать
закупку книг на склад магазина и просматривать операции поступлений
и покупок.
«Книги» и «Авторы» — интерфейс библиотекаря, в котором он может
регистрировать авторов и их книги.
В учебных целях вся функциональность представлена на одной общей
веб-странице. Если какая-то часть функциональности недоступна из-за
того, что на сервере нет подходящего объекта (таблицы, функции
и т. п.), приложение сообщит об этом. Также приложение выводит текст
запросов, которые оно посылает на сервер.
В ходе курса мы начнем с пустой базы данных и постепенно реализуем
в ней все необходимые компоненты.
P. S. Исходный код приложения не является темой курса, но может
быть получен в репозитории https://pubgit.postgrespro.ru/pub/dev1app.git
5
Книги
Книга
название
авторы
количество
операции
сущность
атрибуты
ER-модель для высокоуровневого проектирования
сущности — понятия предметной области
связи — отношения между сущностями
атрибуты — свойства сущностей и связей
После того, как мы посмотрели на внешний вид приложения и поняли
его функциональность, нам нужно разобраться со схемой данных.
Мы не будем хоть сколько-нибудь глубоко вникать в вопросы
проектирования баз данных — это отдельная дисциплина, не входящая
в данный курс. Но совсем обойти вниманием эту тему тоже нельзя.
Часто для высокоуровневого проектирования баз данных используется
модель «сущность — связи», или ER-модель (Entity — Relationship).
Она оперирует сущностями (понятиями предметной области), связями
между ними и атрибутами (свойствами сущностей и связей). Модель
позволяет рассуждать на логическом уровне, не опускаясь до деталей
представления данных на физическом (в виде таблиц).
Первым подходом к проектированию может служить диаграмма,
представленная на слайде: можно представить одну большую сущность
«Книга», а все остальное сделать ее атрибутами.
6
Схема данных
id | title | author | qty | operation
−−−−+−−−−−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+−−−−−+−−−−−−−−−−−
1 | Муму | Тургенев Иван Сергеевич | 10 | +11
1 | МУМУ | Тургенев Иван Сергеевич | 10 | -1
2 | Отцы и дети | Тургенев Иван Сергеевич | 4 | +4
3 | Трудно быть богом | Стругацкий Аркадий Натанович | 7 | +7
3 | Трудно быть богом | Стругацкий Борис Натанович | 7 | 0
Данные дублируются
сложно поддерживать согласованность
сложно обновлять
сложно писать запросы
10 = 11 – 1
7,0
или 0,7
или 7,7
?
Очевидно, что это неправильный подход. На диаграмме это может
быть не так заметно, но давайте попробуем отобразить диаграмму
на таблицы БД. Это можно сделать разными способами, например так,
как показано на слайде: сущность становится таблицей, ее атрибуты —
столбцами этой таблицы.
Здесь хорошо видно, что часть данных дублируется (эти фрагменты
выделены на рисунке). Дублирование приводит к сложностям
с обеспечением согласованности — а это едва ли не главная задача
СУБД.
Например, каждая из двух строк, относящихся к книге 3, должна
содержать общее количество (7 штук). Что нужно сделать, чтобы
отразить покупку книги? С одной стороны, надо добавить строки для
отражения операции покупки (а сколько строк добавлять, еще две?).
С другой, во всех строках надо уменьшить количество с 7 до 6. А что
делать, если в результате какой-нибудь ошибки значение количества
в одной из этих строк будет отличаться от значения в другой строке?
Как сформулировать ограничение целостности, которое запрещает
такую ситуацию?
Также усложняются и многие запросы. Как найти общее число книг?
Как найти всех уникальных авторов?
Итак, такая схема плохо работает для реляционных баз данных.
7
Схема данных (вариант)
entity | attribute | value
−−−−−−−−+−−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−−
1 | title | Муму
1 | author | Тургенев Иван Сергеевич
1 | qty | 10
1 | operation | +11
1 | operation | -1
2 | title | Отцы и дети
2 | author | Тургенев Иван Сергеевич
2 | qty | 4
2 | operation | +4
... | ... | ...
Данные без схемы
поддержка согласованности на стороне приложения
сложно писать запросы
низкая производительность (множественные соединения)
Другой вариант отображения сущности в таблицу — так называемая
схема EAV: сущность — атрибут — значение. Она позволяет уложить
вообще все, что угодно, в одну-единственную таблицу. Формально мы
получаем реляционную базу данных, а фактически здесь отсутствует
схема и СУБД не может гарантировать согласованность данных.
Поддержка согласованности данных целиком ложится на приложение,
что, безусловно, рано или поздно приведет к ее нарушению.
В такой схеме сложно писать запросы (хотя и довольно просто их
генерировать), и в результате работа со сколько-нибудь большим
объемом данных становится проблемой из-за постоянных
многократных соединений таблицы самой с собой.
Однако следует отметить, что такой подход имеет и плюсы. Например,
очевидно, что такую «схему» данных легко наращивать: при
добавлении новой сущности или атрибута мы просто добавляем в нашу
таблицу новые строки.
И все-таки так делать не стоит.
8
Схема данных (вариант)
book_id | description
−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
1 | {"title": "Муму",
| "authors": [ "Тургенев Иван Сергеевич" ],
| "qty": 10,
| "operations": [ +11, -1 ]}
3 | {"title": "Трудно быть богом",
| "authors": [ "Стругацкий Аркадий Натанович",
| "Стругацкий Борис Натанович" ],
| "qty": 7,
| "operations": [ +7 ]}
... | ...
Данные без схемы
поддержка согласованности на стороне приложения
сложно писать запросы (специальный язык)
индексная поддержка есть
Еще одна вариация на ту же тему — представление данных в виде
JSON, в духе NoSQL. По сути, тут применимы все замечания,
сделанные ранее.
Кроме того, запросы к такой структуре придется писать не на SQL,
а используя какой-то специализированный язык (раньше скорее всего
выбор пал бы на jsQuery, а в PostgreSQL 16 удобно пользоваться
возможностями SQL/JSONPath, определенными в стандарте SQL:2016).
Хотя у PostgreSQL есть индексная поддержка JSON,
производительность все равно под вопросом.
Такую схему удобно применять, когда от базы данных требуется только
получение JSON по идентификатору, но не требуется серьезная работа
с данными внутри JSON. Это не наш случай.
(Разумеется, это не категоричное утверждение. См. последнее задание
в практике.)
9
Книги и операции
Нормализация — уменьшение избыточности данных
разбиение крупных сущностей на более мелкие
Книга
название
авторы
Операция
изменение кол-ва
дата
связь
«один ко многим»
Итак, нам требуется устранить избыточность данных, чтобы привести
их в вид, удобный для обработки в реляционной СУБД. Этот процесс
называется нормализацией.
Возможно, вы знакомы с понятиями нормальных форм (первая,
вторая, третья, Бойса-Кодда и т. д.). Мы не будем говорить о них;
на неформальном уровне достаточно понимать, что весь этот
математический аппарат преследует одну-единственную цель:
устранение избыточности.
Средство для уменьшения избыточности — разбиение большой
сущности на несколько меньших. Как именно это сделать, подскажет
здравый смысл (который все равно нельзя заменить одним только
знанием нормальных форм).
В нашем случае все достаточно просто. Давайте начнем с отделения
книг от операций. Эти две сущности связаны отношением «один
ко многим»: каждая книга может иметь несколько операций, но каждая
операция относится только к одной книге.
10
Схема данных
books
book_id | title | author
−−−−−−−−−+−−−−−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
1 | Муму | Тургенев Иван Сергеевич
2 | Отцы и дети | Тургенев Иван Сергеевич
3 | Трудно быть богом | Стругацкий Аркадий Натанович
3 | Трудно быть богом | Стругацкий Борис Натанович
operations
operation_id | book_id | qty_change | date_created
−−−−−−−−−−−−−−+−−−−−−−−−+−−−−−−−−−−−−+−−−−−−−−−−−−−−
1 | 1 | +10 | 2020-07-13
2 | 1 | -1 | 2020-08-25
3 | 3 | +7 | 2020-07-13
4 | 2 | +4 | 2020-07-13
На уровне структуры базы данных это может быть представлено двумя
таблицами: книги (books) и операции (operations).
Операция состоит в изменении количества книг (положительном при
заказе; отрицательном при покупке). Заметьте, что теперь у книги нет
атрибута «количество». Вместо этого достаточно просуммировать
изменения количества по всем операциям, относящимся к данной
книге. Дополнительный атрибут «количество» у книги снова привел бы
к избыточности данных.
Не исключено, что такое решение вас насторожило. Удобно ли вместо
простого обращения к полю подсчитывать сумму? Но мы можем
создать представление, которое для каждой книги будет показывать
количество. Это не приведет к появлению избыточности, поскольку
представление — это всего лишь запрос.
Второй момент — производительность. Если подсчет суммы приведет
к ухудшению производительности, мы можем выполнить обратный
процесс — денормализацию: добавить в таблицу книг поле
«количество» и обеспечить его согласованность с таблицей операций.
Надо ли этим заниматься — вопрос, ответ на который выходит за рамки
этого курса (он рассматривается в курсе QPT «Оптимизация
запросов»). Но из общих соображений понятно, что для нашего
«игрушечного» магазина это не требуется. Однако мы еще вернемся
к вопросу денормализации в теме «Триггеры».
Итак, как видно на слайде, выделение операций в отдельную сущность
решило часть проблем дублирования, но не все.
11
Книги, авторы и операции
Книга
название
Автор
фамилия
имя
отчество
Операция
изменение кол-ва
дата
связь
«многие ко многим»
Поэтому надо сделать еще один шаг: отделить авторов от книг
и связать их соотношением «многие ко многим»: и каждая книга может
быть написана несколькими авторами, и каждый автор может написать
несколько книг. На уровне таблиц такая связь реализуется с помощью
дополнительной промежуточной таблицы.
Атрибутами автора можно сделать фамилию, имя и отчество. Это
имеет смысл, поскольку нам может потребоваться работать отдельно
с каждым из этих атрибутов, например, выводить фамилию и
инициалы.
13
Организация интерфейса
Таблицы и триггеры
чтение данных напрямую из таблицы (представления);
запись данных напрямую в таблицу (представление),
плюс триггеры для изменения связанных таблиц
приложение должно быть в курсе модели данных,
максимальная гибкость
сложно поддерживать согласованность
Функции
чтение данных из табличных функций;
запись данных через вызов функций
приложение отделено от модели данных и ограничено API
большой объем работы по изготовлению функций-оберток,
потенциальные проблемы с производительностью
Есть несколько способов организации интерфейса между клиентской
и серверной частями приложения.
Один вариант — разрешить приложению напрямую обращаться
к таблицам в базе данных и изменять их. При этом от приложения
требуется детальное «знание» модели данных. Отчасти это требование
можно ослабить за счет использования представлений (view).
Кроме того, от приложения требуется и определенная дисциплина —
иначе очень сложно поддержать согласованность данных, защищаясь
на уровне БД от любых возможных некорректных действий
приложения. Но в этом случае достигается максимальная гибкость.
Другой вариант — запретить приложению доступ к таблицам
и разрешить только вызовы функций. Чтение данных можно
организовать с помощью табличных функций (которые возвращают
набор строк). Изменение данных можно выполнять, вызывая другие
функции и передавая им необходимые данные. В этом случае внутри
функций можно реализовать все необходимые проверки
согласованности — база данных будет защищена, но приложение
сможет пользоваться только предоставленным и ограниченным
набором возможностей. Такой вариант требует написания большого
количества функций-оберток и может привести к потере
производительности.
Вполне возможны и промежуточные варианты. Например, разрешить
чтение данных непосредственно из таблиц, а изменение выполнять
только через вызов функций.
14
Интерфейс покупателя
buy_book
books
book_id
title
authorship
book_id
author_id
seq_num
authors
author_id
last_name
first_name
middle_name
operations
operation_id
book_id
qty_change
date_created
покупка книги
get_catalog
список
книг
В нашем приложении мы попробуем разные варианты организации
интерфейса (хотя в реальной жизни обычно лучше систематически
придерживаться какого-то одного подхода).
Магазин будет использовать интерфейсные функции:
●
для поиска книг — get_catalog (тема «SQL. Составные типы»);
●
для покупки книг — buy_book («PL/pgSQL. Выполнение запросов»).
15
operations_v catalog_v
authors_v
Интерфейс сотрудника
add_book
books
book_id
title
authorship
book_id
author_id
seq_num
authors
author_id
last_name
first_name
middle_name
operations
operation_id
book_id
qty_change
date_created
add_author
UPDATE
добавление книги добавление автора
заказ книги
update_catalog_trigger
«Админка» для получения данных будет использовать представления
(которые мы создадим в практике к этой теме):
●
список книг — catalog_v;
●
список авторов — authors_v;
●
список операций — operations_v.
Добавление автора будет выполнять функция add_author (создадим
ее в теме «PL/pgSQL. Выполнение запросов»), добавление книги —
функция add_book («PL/pgSQL. Массивы»).
Для заказа книг сделаем представление catalog_v обновляемым
(«PL/pgSQL. Триггеры»).
17
Итоги
Проектирование баз данных — отдельная тема
теория важна, но не заменяет здравый смысл
Отсутствие избыточности в данных делает работу удобнее
и упрощает поддержку согласованности
Для клиент-серверного интерфейса можно использовать
таблицы, представления, функции, триггеры
18
Практика
1. В базе данных bookstore создайте схему bookstore. Настройте
путь поиска к этой схеме на уровне подключения к БД.
2. В схеме bookstore создайте таблицы books, authors,
authorship и operations с необходимыми ограничениями
целостности так, чтобы они соответствовали показанным
в демонстрации.
3. Вставьте в таблицы данные о нескольких книгах.
Проверьте себя с помощью запросов.
4. В схеме bookstore создайте представления authors_v,
catalog_v и operations_v так, чтобы они соответствовали
показанным в демонстрации.
Проверьте, что приложение стало показывать данные
на вкладках «Книги», «Авторы» и «Каталог».
1. Вспомните материал темы «Организация данных. Логическая
структура».
2. Ориентируйтесь на показанный в демонстрации вывод команд \d
утилиты psql.
3. Вы можете использовать те же данные, что были показаны
в демонстрации, или придумать свои собственные.
4. Попробуйте написать запросы к базовым таблицам, возвращающие
тот же результат, что и показанные в демонстрации запросы
к представлениям. Затем оформите запросы в виде представлений.
После выполнения практики обязательно сверьте свои запросы
с решением, приведенным к этой теме. При необходимости внесите
коррективы.
19
Практика+
1. Какие дополнительные атрибуты могут появиться
у выделенных сущностей при развитии приложения?
2. Допустим, требуется хранить информацию об издательстве.
Дополните ER-диаграмму и отобразите ее в таблицы.
3. Некоторые книги могут входить в серии (например,
«Библиотека приключений»). Как изменится схема данных?
4. Пусть наш магазин стал торговать компьютерными
комплектующими (материнскими платами, процессорами,
памятью, жесткими дисками, мониторами и т. п.).
Какие сущности и какие атрибуты вы бы выделили?
Учтите, что на рынке постоянно появляются новые типы
оборудования со своими характеристиками.
3. Разные издательства вполне могут иметь серии, названные
одинаково.
