Проектирование базы данных MySQL

Проектирование базы данных MySQL

Тщательное проектирование базы данных чрезвычайно важно для безупречной работы приложения. Как установка принтера в дальнем конце офиса ведет к снижению производительности труда, размещение данных со слабыми взаимосвязями снижает эффективность и может вынудить сервер базы данных тратить значительное время на поиск требуемых данных. Разрабатывая структуру базы данных, задумайтесь о вопросах, возникающих при работе с ней. Например: «Какие дополнительные сведения имеются о продаваемом продукте?» Или: «Верны ли эти имя пользователя и пароль?»

MySQL – это реляционная база данных. Важная особенность реляционных систем, их отличие от одноуровневых баз данных – возможность располагать данные в нескольких таблицах. Взаимосвязанные данные можно хранить в отдельных таблицах и объединять по ключу, общему для обеих таблиц. Ключ – это отношение (relation) между таблицами. Выбор первичного ключа (primary key) – наиболее важное решение, принимаемое при разработке новой базы данных. Самое главное, что следует понимать, – вы должны гарантировать уникальность выбранного ключа. Если есть вероятность того, что значение некоторого атрибута может совпасть у двух записей, то его нельзя использовать в качестве первичного ключа. Если таблица содержит ключевые поля из другой таблицы, то между ними образуется связь – взаимоотношением внешнего ключа (foreign key), например «начальник-подчиненный» или «покупатель-покупка».

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

Код доступен только после покупки курса MySQL с нуля.

Теперь, когда у нас есть отдельные таблицы для хранения взаимосвязанных данных, нужно подумать об элементах в каждой таблице, которые будут описывать связи с элементами в других таблицах.

Нормализация

Представление о взаимоотношениях данных и наиболее эффективном способе их организации называется нормализацией. Нормализация заключается в разделении данных на основе логических взаимоотношений с целью минимизировать дублирование данных. Повторяющиеся данные понапрасну расходуют дисковое пространство сервера и затрудняют их обслуживание. При внесении изменений в повторяющиеся данные есть риск пропустить какие-то из них, что может привести к возникновению несогласованностей в базе данных.

С другой стороны, лучшее – враг хорошего: когда данные хранятся по частям в отдельных таблицах, это может потребовать слишком больших накладных расходов на их извлечение, да и запросы могут получаться чересчур замысловатыми. Главная цель – найти золотую середину.

Продолжим пример с книжным интернет-магазином. Сайт магазина должен хранить данные о покупателях, включая имя и фамилию пользователя, адрес и номер телефона, а также информацию о книгах, включая название, автора, количество страниц и дату продажи каждой книги. Изначально мы разместили всю информацию в одной таблице:
Ненормированные данные

Размещение всех данных в одной таблице может показаться заманчивым, однако такой способ приводит к напрасному расходованию пространства в базе данных и делает утомительной операцию обновления данных. С каждой новой покупкой все сведения о покупателе записываются повторно. Для каждой книги можно указать не больше двух авторов. Кроме того, если покупатель переедет и поменяет адрес, это потребует внести изменения в каждую запись, связанную с этим покупателем.

Формы нормализации

Чтобы нормализовать базу данных, начнем с самых главных правил и будем продвигаться вперед шаг за шагом. Процесс нормализации состоит из трех этапов, называемых формами. Первый этап, который называется приведением к первой нормальной форме, должен быть выполнен перед приведением базы данных ко второй нормальной форме. Аналогично, невозможно привести базу данных к третьей нормальной форме, минуя вторую. Процесс нормализации приводит структуру данных в соответствие с тремя нормальными формами.

Первая нормальная форма

Необходимо, чтобы приведенная к первой нормальной форме база данных соответствовала трем требованиям. Ни одна таблица не должна иметь повторяющихся столбцов, содержащих одинаковые по смыслу значения, и все столбцы должны содержать единственное значение. Обязательно должен быть определен первичный ключ, который уникальным образом описывал бы каждую строку. Это может быть один столбец или комбинация из нескольких столбцов, в зависимости от того, сколько потребуется столбцов для обеспечения уникальной идентификации строк.

В созданной нами таблице нарушено требование, предъявляемое к повторяющимся столбцам, потому что в столбцах "bookAuthor1" и "bookAuthor2" хранятся одинаковые по смыслу данные. Это несоответствие надо устранить, в противном случае вам может потребоваться добавить много полей для хранения имен авторов (например, если у книги три автора), что приведет к неоправданному расходу пространства, или может не хватить предусмотренного количества полей для хранения всех имен, если над книгой трудились много авторов. Решение заключается в том, чтобы переместить имена всех авторов в отдельную таблицу, которая будет связана с таблицей книг.

Вторая нормальная форма

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

Чтобы привести таблицу базы данных ко второй нормальной форме, нужно определить, какие из ее столбцов содержат одни и те же данные для нескольких строк. Такие столбцы нужно поместить в отдельную таблицу, связав ее с первоначальной по ключу. Другими словами, нужно отыскать поля, не зависящие от первичного ключа. Поскольку имена авторов и такие сведения о книгах, как количество страниц, никак не связаны с первичным ключом, идентифицирующем покупателя, выделим эту информацию в отдельные таблицы (это действие будет включать в себя также нормализацию по первой форме, т.к. мы выделяем в отдельную таблицу имена авторов):

Код доступен только после покупки курса MySQL с нуля.

Этот код позволяет создать дополнительную таблицу Books хранящую данные о книгах. Однако мы еще не выполнили полную нормализацию по второй форме. Можно заметить, что в нескольких строках таблицы Customers повторяется информация о пользователях, сделавших несколько заказов. Для приведения ко второй нормальной форме мы определим новую таблицу Orders (Заказы):

Код доступен только после покупки курса MySQL с нуля.

Теперь данные оформлены безупречно. У нас появились отдельные таблицы со сведениями о покупателях (Customers), книгах (Books) и покупках (Orders).

Третья нормальная форма

Если вы завершили приведение к первой и второй нормальным формам, возможно, вам не потребуется ничего больше делать с базой данных, чтобы привести ее к третьей нормальной форме. Для приведения к третьей нормальной форме нужно просмотреть таблицы и выделить данные, которые не зависят от первичного ключа, но зависят от других значений. Пока еще не совсем понятно, как применить это к нашим таблицам. В таблице Customers компоненты адреса не имеют прямого отношения к покупателю. Название улицы и номер дома связаны с почтовым индексом, почтовый индекс – с городом и, наконец, сам город – с областью или краем. Третья нормальная форма требует, чтобы каждая такая часть данных была выделена в отдельную таблицу.

Ниже показано, как можно разделить информацию об адресе создав отдельную таблицу Adresses:

Код доступен только после покупки курса MySQL с нуля.

С практической точки зрения, вы можете обнаружить, что после приведения к третьей нормальной форме было создано больше таблиц, чем вам хотелось бы иметь в своей базе данных. Поэтому вы должны сами решать, когда остановить процесс нормализации. Хорошо, если ваши данные будут соответствовать, по крайней мере, второй нормальной форме. Цель – избежать избыточности данных, предотвратить их повреждение и минимизировать занимаемое данными пространство. Кроме того, нужно убедиться, что одни и те же значения не хранятся в нескольких местах. В противном случае, когда эти данные изменятся, вам придется обновлять их в нескольких местах, что может привести к повреждению базы данных.

Как вы могли заметить, третья нормальная форма еще сильнее снижает избыточность данных, но ценой простоты их представления и производительности. В нашем примере не приходится ожидать, что информация об адресах будет часто изменяться. Однако третья нормальная форма позволяет снизить риск появления орфографических ошибок в названиях городов и улиц. Поскольку это ваша база данных, вам и определять соотношение между нормализацией, простотой и производительностью.

Типы связей

Взаимоотношения или связи, в базах данных подразделяются на следующие категории:

  • связи "один-к-одному";
  • связи "один-ко-многим";
  • связи "многие-ко-многим".

Мы рассмотрим каждую из этих связей на примере созданной нами базы данных.

Связи "один-к-одному"

При связи "один-к-одному" каждому элементу соответствует один и только один другой элемент. Например, в контексте книжного интернет-магазина связь "один-к-одному" существует между покупателем и адресом доставки. Каждый покупатель должен иметь единственный адрес доставки. Знак ключа рядом с каждой из таблиц на рисунке ниже указывает на поле, которое является ключом для этой таблицы:
Связь один к одному

Например, чтобы вывести адрес пользователя "Александр Иванов" можно воспользоваться следующей SQL-конструкцией:

Код доступен только после покупки курса MySQL с нуля.

Результат запроса при связи один к одному

Связь "один-ко-многим"

В случае связи "один-ко-многим" каждый ключ из одной таблицы может встречаться несколько раз в другой таблице. Это наиболее распространенный тип связи. Например, у одного покупателя может быть несколько заказов, в то же время каждый заказ имеет свой уникальный идентификатор, но два покупателя могут заказать одну и ту же книгу:
Связь один ко многим

Например, чтобы вывести все заказы пользователя "Александр Иванов" можно воспользоваться следующей SQL-конструкцией:

Код доступен только после покупки курса MySQL с нуля.

Результат запроса при связи один ко многим

Связь "многие-ко-многим"

Связь "многие-ко-многим" возникает между двумя таблицами, когда в каждой из них может присутствовать несколько ключей другой таблицы. Например, покупатель приобретает в интернет-магазине сразу несколько книг. Или одну и ту же книгу приобретают несколько покупателей. На рисунке ниже показана связь "многие-ко-многим" между покупателями и приобретенными книгами:
Связь многие ко многим

Чтобы данные со связью "многие-ко-многим" могли быть представлены в базе данных, этот тип связи преобразуется в две связи "один-ко-многим" с помощью таблицы отображения (mapping table). В нашем случае такой таблицей является Orders.

Вот таким вот образом у нас и проектируется база данных.

Читайте также
Комментарии