Проектирование баз данных
23-11-2021Время чтения ~ 10 мин.SQL 3403
Создать идеальную базу очень сложно. Всё дело в том, что база данных не может быть оторвана от реальности — базы нужны для того, чтобы решать какие-то конкретные задачи. Например у вас есть список клиентов в таблице Экселя. Если их количество приближается к сотне, то есть смысл подумать о том, чтобы перевести эти данные в базу, чтобы можно было ими удобней управлять и быстро находить нужные контакты. То есть обычно ставится задача, а уже потом под неё проектируется база.
Я не знаю где сейчас учат этим навыкам, но лично мой опыт накапливался в «боевых» условиях — когда на нашем предприятии (это было давно, пока я не ушёл во фриланс), решили автоматизировать часть процессов. Опыта по базам у меня тогда не было, но я легко ухватил суть по учебникам и быстро спроектировал и запустил первую версию программы. Она прекрасно работала, пока не стал вопрос её расширить на другие задачи.
При проектировании базы очень сложно предугадать какие поля нужны будут в будущем. В моём случае изначально ставилась задача создать базу адресов и почтальонов для доставки одной газеты. Потом бизнес расширился и появилось ещё несколько газет. Потом ещё и у нас появилось целое отделение по доставке корреспонденции, где речь уже шла о тысячах изданий и сотнях почтальонов.
То есть я хочу сказать, что неважно как вы спроектируете базу, вам всё равно придётся её изменять и дорабатывать.
Поэтому при проектировании баз для бизнеса нужно как можно лучше понимать стоящие задачи и точно улавливать с какими данными придётся работать. Разработчику приходится иногда проявлять чудеса интуиции, чтобы заранее добавить в базу возможности, которые не очевидны в момент постановки задачи.
Например вы делаете базу с таблицами адресов вашего города. Казалось бы достаточно простая таблица, где будут поля для улицы (TEXT), номера дома (INTEGER) и номер квартиры (INTEGER). Но со временем вы узнаёте, что номер дома может содержать ещё и букву, а номер квартиры либо букву, либо что-то вроде дроби (/). То есть изначальная структура базы не в полной мере отражает суровую реальность. После того, как вы внесли изменения, вдруг выясняется, что есть ещё и такое деление, как «корпус» и его тоже следует учитывать. Ну и в завершении может выяснится, что в общем-то не проблема работать с соседними сёлами или городами и в базе нужно уже добавить и поле для населённого пункта. Если поле «улица» было уникальным, то это приводит к поломке всей базы, поскольку одноимённые улицы часто встречаются в разных городах.
Технически любая база состоит из достаточно примитивных вещей — всего лишь таблицы с данными. Но самую большую сложность представляют связи между этими таблицами. Именно здесь основной «затык», поскольку очень тяжело держать в голове и данные и их связи.
Я немного погуглил на эту тему и нашёл только одну статью на Хабре, где на примере кинотеатра рассказывается о том, как создать базу поэтапно (будет время, почитайте).
При проектировании базы нужно сразу исходить из итоговых задач. Например, при создании базы клиентов, нужно в первую очередь решить какая именно информация может потребоваться в итоге. Разработчик создаёт одну единственную таблицу (в общем-то и Эксель сгодится), где указывает все нужные поля (столбцы). Это будет абсолютно ненормализованная база, с кучей ошибок и дублей. На этапе проектирования это не проблема.
Вторым этапом определяется какие выборки нужно будет получить из этой базы. Например список клиентов по городам или нужно знать сколько покупок делает каждый клиент. Может при каких-то условиях ему положена скидка? А может это купон скидочный? Или он участник акции?
Такие вопросы позволяют уточнять набор полей исходной таблицы и эти итерации происходят до тех пор, пока все необходимые поля будут добавлены.
Следующий этап связан с наполнением базы. Существует два способа внести данные в таблицу: первый — это набрать текстом и второй — выбрать из списка.
Когда данные набираются текстом, то сразу решается вопрос о том, насколько критична правильность ввода данных. Например поле «примечание» может быть совершенно произвольным, но для адреса скорее всего придётся придерживаться строгого порядка, поскольку для базы «Киев», «киев» и «Київ» совершенно разные значения.
Или другой пример, например номер телефона можно указывать в международном формате, а можно в местном. В одном случае мы решаем вопрос о маске ввода, а в другом выделяем код страны в отдельное поле.
Поэтому важно решить какие данные можно вводить произвольно, а какие можно выбрать из списка.
Здесь самое время вспомнить о нормальных формах. Если поле предлагается выбирать из готового списка, то это всегда подразумевает создание справочной таблицы. Справочная таблица фактически содержит только список, который можно использовать в другой таблице.
Клиенты ******* id name --------- 1 Вася 2 Петя 3 Саша Заказы ****** id id_сlient date sum ------------------------------- 1 1 2021-11-11 100$ 2 2 2021-11-12 10$ 3 2 2021-11-13 50$ 4 1 2021-11-14 70$ 5 3 2021-11-15 20$
Таблица «Клиенты» (точнее её поле id) используется в таблице «Заказы» в виде поля-ссылки id_сlient. Такая связь между таблицами называется один-ко-многим.
Таким образом одним из начальных этапов проектирования базы будет определение того, какие данные можно вынести в справочные таблицы. После этого в базе запросы строятся уже с учётом номера клиента, который хранится в единственной таблице. Это один из способов избежания избыточности. При вводе данных в базу уже не нужно набирать вручную имя клиента — нужно будет выбрать его из выпадающего списка таблицы «Клиенты».
Связь один-ко-многим предполагает существование как минимум двух таблиц. При этом справочная таблица («Клиенты») может хранить данные не только в виде одного поля (имя), но и другие, например номер телефона. Но здесь важно понимать 1НФ, поскольку работа с такой таблицей происходит точно также, как и с любой другой.
Скажем мы решили ввести номера телефонов для клиентов. Логично поступить так:
Клиенты ******* id name phone ---------------- 1 Вася 050-00-... 2 Петя 068-00-... 3 Саша 091-00-...
Но потом выясняется, что у клиентов может быть несколько номеров телефонов. Можно, конечно, добавить новое поле для второго телефона, но потом выяснится, что нужно ещё третье и т.д. Суть здесь в том, что лучше создать отдельную таблицу номеров телефонов и использовать их в таблице «Клиенты».
Телефоны ******** id_сlient phone ---------------- 1 050-00-... 3 091-00-... 2 068-00-... 1 067-00-... 3 095-00-... Клиенты ******* id name --------- 1 Вася 2 Петя 3 Саша
Таким образом, чтобы получить все телефоны клиента нужно выполнить запрос к таблице «Телефоны», где указать номер клиента. При этом у клиента может быть сразу много телефонов, а поле phone можно сделать уникальным, чтобы исключить дубли.
Другая частая задача — это связи между двумя таблицами, которые не содержат ссылки друг на друга. Хороший пример — рубрики на сайте. У нас есть две таблицы. Первая — записи сайта, вторая — рубрики (название, ссылки, описание). У записи может быть множество рубрик, поэтому мы не можем в этих таблицах придумать поля для их связи. Вместо этого делается отдельная связующая таблица, которая хранит id этих таблиц.
Записи ****** id title ---------- 1 Hello, world! 2 Создание сайтов 3 Обо мне Рубрики ******* id slug -------- 1 news 2 bussines 3 about
Эти таблицы не имеют между собой связи, поэтому их размещаем в третьей таблице:
category2page ************* id_category id_page -------------------- 3 1 2 2 1 3 3 1
Такая связь между таблицами называется многие-ко-многим.
Если мы захотим например добавить новую сущность к записям (например «каталог»), то делаются две таблицы: первая «Каталог», где хранятся данные о каталоге и вторая — связующая (иногда она называется промежуточной) catalog2page.
Использование связующей таблицы, на самом деле, очень классная штука. Хотя она и усложняет sql-запросы, но позволяет расширять базу без переделки существующих таблиц. Более того, многие таблицы можно расширять за счёт дополнительной таблицы как раз таким способом.
Такой жизненный пример. В MaxSite CMS есть таблица comusers, которая хранит данные о комментаторах сайта (аналогично устроена и таблица users, но это не важно). В этой таблице есть поля для хранения номера ICQ, MSN и Jabber. На момент создания системы, казалось, что этих полей будет достаточно, однако прошло уже достаточно времени, чтобы выросло поколение, которое даже не знает, что такое «аська». Сейчас актуальны другие соц.сети и наверное лучше было бы сделать поле для Инстаграма и Тиктока. С другой стороны, кто даст гарантию, что этот тренд скоро не закончится и все не ломанутся в другую соц.сеть?
Простой способ — это использовать существующее поле под новую задачу. Например в MaxSite CMS поле msn хранит ссылку Твиттера и пока это не потеряло актуальности. Но как добавить новые поля?
Вот здесь как раз и пригодится «расширяющая» таблица. Она хранит ссылку на юзера, а также дополнительное поле (ключ) и его значение (value). Такие таблицы называют metа-таблицами.
users ***** id name login password ------------------------- 1 Вася vasya 123 2 Петя petr 456 3 Саша alex 789 users_meta ********** id id_users key val --------------------------- 1 1 twitter @vasya 2 2 twitter @petr 3 1 telegram @vasil 4 1 tiktok @super
Таким образом, чтобы получить все данные по пользователю, нужно сделать дополнительный запрос к users_meta. Если станет задача добавить новое meta-поле, то делается это на уровне программирования внешнего интерфейса программы, поскольку ограничений в этой таблице нет.
Хотя можно усложнить задачу — создать таблицу, которая будет хранить список возможных значений поля key с той целью, чтобы вначале создать список возможных (допустимых) значений key.
users_meta_key ************** id key ------- 1 twitter 2 telegram 3 tiktok users_meta ********** id id_users id_key val --------------------------- 1 1 1 @vasya 2 2 1 @petr 3 1 2 @vasil 4 1 3 @super
Такой способ «расширения» таблиц можно использовать для любых задач. Например если придумать «сущности» для группировки записей, то по сути мы «изобретём» т.н. таксономию из Друпала (и ВП, который её от него и позаимствовал).
На самом деле, там чуть сложней, но суть такая же.
Если на этапе проектирования мы не знаем нужны ли нам meta-таблицы для других, то используется немного другой подход. Он применяется в MaxSite CMS. Делается общая для всех таблица meta, которая хранит ещё и название «расширяемой» таблицы.
meta **** id key id_obj table value -------------------------------- 1 tags 7 page метка
В данном примере показана строчка для хранения метки (ключ tags) в таблице page для записи с №7. Таким образом можно хранить meta-данные для любой таблицы. Плюс такого подхода в том, что мы не затрагиваются основные таблицы — мы работает только с дополнительными данными.
Строго говоря полеtableне обязательно должно хранить именно имя таблицы. Можно там хранить что-то вроде «группы» или «типа» meta-значений. Просто когда мы используем именноtable, то мы точно знаем, что это за таблица и где искатьid_obj.
При этом, заметьте, мы можем напрямую обратиться к таблицеmetaи получить список всех записей для определённой метки. Такие таблицы могут использоваться для быстрого поиска набораid_obj«родительской» таблицы по заданному ключу. То есть совершенно не обязательно делать запрос кpage, чтобы получить список записей с заданной меткой.
Минус же такого подхода в более сложных sql-запросах. Когда мы получаем список записей, то обращаемся к одной таблице (page), но там нет meta-данных. Поэтому нужно организовать цикл по всем найденным записям и внутри него выполнить ещё и запросы к meta-таблице. Только после этого на выходе мы получим массив всех данных каждой записи. С технической точки зрения это не так сложно, но создаёт дополнительную нагрузку на сервер и базу, да и код получается посложнее.
Обычно проектирование базы предполагает решение одной из основной задачи — ограничение избыточности. То есть данные должны храниться в единственном экземпляре. Если у нас есть клиент «Вася», то его имя мы указываем только один раз в одной таблице. Все остальные операции происходят только с его уникальным номером. Если же этого не сделать, то возникнут дубли клиента и если появится задача его изменить, то придётся делать это во всех таблицах. И это неправильно.
Когда мы создаём дополнительные таблицы, то неизбежно усложняем sql-запросы. Более того, это может быть даже несколько запросов, чтобы получить желаемый результат. Это цена нормализации. Очевидно, что это может привести к нагрузке на сервер и негативно скажется на скорости работы базы. Поэтому иногда разработчики специально отходят от правил нормализации с целью ускорить получения выборки.
Такой процесс называется денормализация и обычно заключается в том, чтобы в таблицах всё-таки хранить избыточные данные. Чаще всего такие операции происходят автоматически. Например есть несколько больших связанных таблиц. Чтобы получить данные по какому-то запросу потребуется выполнить сложные и долгие sql-запросы. Вместо этого в одной из таблиц сохраняется уже готовый результат выборки и запрос сокращается до одной таблицы. Получается что-то вроде кэширования. Обновление поля происходит либо через заданный промежуток времени, либо по отслеживанию изменения исходных данных.
Денормализацию используют для больших данных и там где скорость работы базы является критичной. В обычной практике всё-таки лучше стремиться к обычной нормализации, тем более, что скорость серверов постоянно увеличивается, а в «движках» баз применяются всё более сложные алгоритмы, улучшающие их работу. Так что денормализация — это скорее крайний случай.