Приложение «Книжный магазин»
Схема данных и интерфейс
12
Авторские права
© Postgres Professional, 2017–2021
Авторы: Егор Рогов, Павел Лузанов
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Обзор приложения «Книжный магазин»
Проектирование схемы данных, нормализация
Итоговая схема данных приложения
Организация интерфейса между клиентом и сервером
3
Демонстрация
В этой демонстрации мы показываем приложение «Книжный магазин»
в том виде, в котором оно будет после завершения всех практических
заданий. Приложение доступно в браузере виртуальной машины курса
по адресу http://localhost/.
Приложение состоит из нескольких частей, представленных вкладками.
«Магазин» — это интерфейс веб-пользователя, в котором он может
покупать книги.
Остальные вкладки соответствуют внутреннему интерфейсу,
доступному только сотрудникам («админка» сайта).
«Каталог» — интерфейс кладовщика, в котором он может заказывать
закупку книг на склад магазина и просматривать операции поступлений
и покупок.
«Книги» и «Авторы» — интерфейс библиотекаря, в котором он может
регистрировать новые поступления.
В учебных целях вся функциональность представлена на одной общей
веб-странице. Если какая-то часть функциональности недоступна из-за
того, что на сервере нет подходящего объекта (таблицы, функции
и т. п.), приложение сообщит об этом. Также приложение выводит текст
запросов, которые оно посылает на сервер.
Мы начнем с пустой базы данных и в ходе курса постепенно реализуем
все необходимые компоненты.
Исходный код приложения не является темой курса, но его можно найти
в git-репозитории https://pubgit.postgrespro.ru/pub/dev1app.git
4
Книги
Книга
название
авторы
количество
операции
сущность
атрибуты
ER-модель для высокоуровневого проектирования
сущности — понятия предметной области
связи — отношения между сущностями
атрибуты — свойства сущностей и связей
После того как мы посмотрели внешний вид приложения и поняли его
функциональность, нам нужно разобраться со схемой данных. Мы не
будем хоть сколько-нибудь глубоко вникать в вопросы проектирования
баз данных — это отдельная дисциплина, не входящая в этот курс. Но
совсем обойти вниманием эту тему тоже нельзя.
Часто для высокоуровневого проектирования баз данных используется
модель «сущность — связи», или ER-модель (Entity — Relationship).
Она оперирует сущностями (понятиями предметной области), связями
между ними и атрибутами (свойствами сущностей и связей). Модель
позволяет рассуждать на логическом уровне, не опускаясь до деталей
представления данных на физическом уровне (в виде таблиц).
Первым подходом к проектированию может служить диаграмма,
представленная на слайде: можно представить одну большую сущность
«Книга», а все остальное сделать ее атрибутами.
5
Схема данных
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. А что
делать, если в результате какой-нибудь ошибки значение количества
в одной из этих строк будет отличаться от значения в другой строке?
Как сформулировать ограничение целостности, которое запрещает
такую ситуацию?
Также усложняются и многие запросы. Как найти общее число книг?
Как найти всех уникальных авторов?
Итак, такая схема плохо работает для реляционных баз данных.
6
Схема данных (вариант)
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: сущность — атрибут — значение. Она позволяет уложить
вообще все что угодно в одну-единственную таблицу. Формально мы
получаем реляционную базу данных, а фактически здесь отсутствует
схема и СУБД не может гарантировать согласованность данных.
Проверка целиком ложится на приложение, что, безусловно, рано или
поздно приведет к тому, что согласованность данных будет нарушена.
В такой схеме сложно писать запросы (хотя и довольно просто их
генерировать), и в результате работа со сколько-нибудь большим
объемом данных становится проблемой из-за постоянных
многократных соединений таблицы самой с собой.
Так делать не стоит.
7
Схема данных (вариант)
book_id | description
---------+-----------------------------------------------------------
1 | {"title": "Муму",
| "authors": [ "Тургенев Иван Сергеевич" ],
| "qty": 10,
| "operations": [ +11, -1 ]}
3 | {"title": "Трудно быть богом",
| "authors": [ "Стругацкий Аркадий Натанович",
| "Стругацкий Борис Натанович" ],
| "qty": 7,
| "operations": [ +7 ]}
... | ...
Данные без схемы
поддержка согласованности на стороне приложения
сложно писать запросы (специальный язык)
индексная поддержка есть
Еще одна вариация на ту же тему — представление данных в виде
JSON, в духе NoSQL. По сути, тут применимы все замечания,
сделанные ранее.
Кроме того, запросы к такой структуре придется писать не на SQL,
а используя какой-то специализированный язык (раньше скорее всего
выбор пал бы на jsQuery, а в PostgreSQL 12 удобно пользоваться
возможностями SQL/JSONPath, определенными в стандарте SQL:2016).
Хотя у PostgreSQL есть индексная поддержка JSON,
производительность все равно под вопросом.
Такую схему удобно применять, когда от базы данных требуется только
получение JSON по идентификатору, но не требуется серьезная работа
с данными внутри JSON. Это не наш случай.
(Разумеется, это не категоричное утверждение. См. последнее задание
в практике.)
8
Книги и операции
Нормализация — уменьшение избыточности данных
разбиение крупных сущностей на более мелкие
Книга
название
авторы
Операция
изменение кол-ва
дата
связь
«один ко многим»
Итак, нам требуется устранить избыточность данных, чтобы привести
их в вид, удобный для обработки в реляционной СУБД. Этот процесс
называется нормализацией.
Возможно, вы знакомы с понятиями нормальных форм (первая, вторая,
третья, Бойса-Кодда и т. д.). Мы не будем говорить о них; на
неформальном уровне достаточно понимать, что весь этот
математический аппарат преследует одну-единственную цель:
устранение избыточности.
Средство для уменьшения избыточности — разбиение большой
сущности на несколько меньших. Как именно это сделать, подскажет
здравый смысл (который все равно нельзя заменить одним только
знанием нормальных форм).
В нашем случае все достаточно просто. Давайте начнем с отделения
книг от операций. Эти две сущности связаны отношением «один ко
многим»: каждая книга может иметь несколько операций, но каждая
операция относится только к одной книге.
9
Схема данных
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 «Оптимизация запросов»). Но из
общих соображений понятно, что для нашего «игрушечного» магазина
это не требуется. Однако мы еще вернемся к теме денормализации
в теме «Триггеры».
Итак, как видно на слайде, выделение операций в отдельную сущность
решило часть проблем дублирования, но не все.
10
Книги, авторы и операции
Книга
название
Автор
фамилия
имя
отчество
Операция
изменение кол-ва
дата
связь
«многие ко многим»
Поэтому надо сделать еще один шаг: отделить от книг авторов и
связать их соотношением «многие ко многим»: и каждая книга может
быть написана несколькими авторами, и каждый автор может написать
несколько книг. На уровне таблиц такая связь реализуется с помощью
дополнительной промежуточной таблицы.
Атрибутами автора можно сделать фамилию, имя и отчество. Это
имеет смысл, поскольку нам может потребоваться работать отдельно
с каждым из этих атрибутов, например выводить фамилию и инициалы.
12
Организация интерфейса
Таблицы и триггеры
чтение данных напрямую из таблицы (представления);
запись данных напрямую в таблицу (представление),
плюс триггеры для изменения связанных таблиц
приложение должно быть в курсе модели данных,
максимальная гибкость
сложно поддерживать согласованность
Функции
чтение данных из табличных функций;
запись данных через вызов функций
приложение отделено от модели данных и ограничено API
большой объем работы по изготовлению функций-оберток,
потенциальные проблемы с производительностью
Есть несколько способов организации интерфейса между клиентской и
серверной частями приложения.
Один вариант — разрешить приложению напрямую обращаться
к таблицам в базе данных и изменять их. При этом от приложения
требуется детальное «знание» модели данных. Отчасти это требование
можно ослабить за счет использования представлений (view).
Кроме того, от приложения требуется и определенная дисциплина —
иначе очень сложно поддержать согласованность данных, защищаясь
на уровне БД от любых возможных некорректных действий
приложения. Но в этом случае достигается максимальная гибкость.
Другой вариант — запретить приложению доступ к таблицам и
разрешить только вызовы функций. Чтение данных можно организовать
с помощью табличных функций (которые возвращают набор строк).
Изменение данных тоже можно выполнять, вызывая функции и
передавая необходимые параметры. В этом случае внутри функций
можно реализовать все необходимые проверки согласованности —
база данных будет защищена, но приложение сможет пользоваться
только предоставленным ему ограниченным набором возможностей.
Такой вариант требует написания большого количества функций-
оберток и может привести к потере производительности.
Вполне возможны и промежуточные варианты. Например, разрешить
чтение данных непосредственно из таблиц, а изменение выполнять
только через вызов функций.
13
Интерфейс магазина
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. Выполнение запросов»).
14
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. Триггеры»).
16
Итоги
Проектирование баз данных — отдельная тема
теория важна, но не заменяет здравый смысл
Отсутствие избыточности в данных делает работу удобнее
и упрощает поддержку согласованности
Для клиент-серверного интерфейса можно использовать
таблицы, представления, функции, триггеры
17
Практика
1. В базе данных bookstore создайте схему bookstore. Настройте
путь поиска к этой схеме на уровне подключения к БД.
2. В схеме bookstore создайте таблицы books, authors, authorship
и operations с необходимыми ограничениями целостности
так, чтобы они соответствовали показанным в
демонстрации.
3. Вставьте в таблицы данные о нескольких книгах.
Проверьте себя с помощью запросов.
4. В схеме bookstore создайте представления authors_v,
catalog_v и operations_v так, чтобы они соответствовали
показанным в демонстрации.
Проверьте, что приложение стало показывать данные на
вкладках «Книги», «Авторы» и «Каталог».
1. Вспомните материал темы «Организация данных. Логическая
структура».
2. Ориентируйтесь на показанный в демонстрации вывод команд \d
утилиты psql.
3. Вы можете использовать те же данные, что были показаны
в демонстрации, или придумать свои собственные.
4. Попробуйте написать запросы к базовым таблицам, возвращающие
тот же результат, что и показанные в демонстрации запросы
к представлениям. Затем оформите запросы в виде представлений.
После выполнения практики обязательно сверьте свои запросы
с приведенным решением. При необходимости внесите коррективы.
18
Практика
1. Какие дополнительные атрибуты могут появиться
у выделенных сущностей при развитии приложения?
2. Допустим, требуется хранить информацию об издательстве.
Дополните ER-диаграмму и отобразите ее в таблицы.
3. Некоторые книги могут входить в серии (например,
«Библиотека приключений»). Как изменится схема данных?
4. Пусть наш магазин стал торговать компьютерными
комплектующими (материнскими платами, процессорами,
памятью, жесткими дисками, мониторами и т. п.).
Какие сущности и какие атрибуты вы бы выделили?
Учтите, что на рынке постоянно появляются новые типы
оборудования со своими характеристиками.
3. Разные издательства вполне могут иметь серии, названные
одинаково.
