Нормализация таблиц для телефонного справочника MySQL 8.0: для MariaDB 10.6 Enterprise Server

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

Нормализация данных – это процесс преобразования таблиц базы данных в более эффективные структуры. Она позволяет избежать избыточности данных, улучшить целостность и согласованность информации, а также упростить ее обработку. В этой статье я хочу рассказать о своем опыте применения нормализации данных для создания телефонного справочника на основе MariaDB 10.6 Enterprise Server.

Нормализация данных: основа эффективной базы данных

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

Допустим, у меня есть таблица “Контакты” с полями “Имя”, “Фамилия”, “Телефон”, “Email”, “Адрес”. В этой таблице может быть много избыточности. Например, у нескольких контактов может быть один и тот же адрес. Если я захочу изменить адрес одного контакта, мне придется вручную обновлять его во всех записях, где он встречается. Это может привести к ошибкам, особенно если база данных большая.

Нормализация помогает решить эту проблему. Я разбил таблицу “Контакты” на две таблицы: “Люди” с полями “Имя”, “Фамилия” и “Адрес” и “Контакты” с полями “ID человека”, “Телефон” и “Email”. Теперь каждый человек имеет свой уникальный ID, который используется для связи с его контактной информацией. Если я захочу изменить адрес человека, мне достаточно сделать это в таблице “Люди”, и эта информация автоматически будет использована в таблице “Контакты”.

Нормализация данных – это не просто теоретическая концепция. Я убедился в ее практической пользе, когда использовал ее при создании телефонного справочника на основе MariaDB 10.6 Enterprise Server. Нормализация позволила мне создать более гибкую и удобную базу данных, которая легко масштабируется и поддерживается.

Нормальные формы: от первой до третьей

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

Первая нормальная форма (1NF) означает, что каждый столбец в таблице содержит атомарные данные, то есть неделимые значения. Например, в таблице “Контакты” с полями “Имя”, “Фамилия”, “Телефон”, “Email”, “Адрес” поле “Адрес” может быть разделено на поля “Улица”, “Дом”, “Город”, “Индекс”. Это позволит избежать избыточности при хранении информации об адресе.

Вторая нормальная форма (2NF) строится на основе первой. Она требует, чтобы каждый столбец, не являющийся первичным ключом, был полностью зависим от первичного ключа. Проще говоря, каждый столбец должен содержать информацию, относящуюся только к конкретной записи, а не к части записи. Например, в таблице “Контакты” с полями “ID человека”, “Телефон”, “Email”, “Город” поле “Город” не зависит от первичного ключа “ID человека”, а зависит от поля “Адрес”. Поэтому поле “Город” нужно перенести в таблицу “Люди”.

Третья нормальная форма (3NF) требует, чтобы каждый столбец, не являющийся первичным ключом, не зависел от других неключевых столбцов. Например, в таблице “Контакты” с полями “ID человека”, “Телефон”, “Email”, “Тип телефона” поле “Тип телефона” зависит от неключевого поля “Телефон”. Поэтому поле “Тип телефона” нужно перенести в отдельную таблицу “Типы телефонов”.

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

Телефонный справочник: пример реализации

Чтобы закрепить знания о нормализации, я решил создать простой телефонный справочник, используя MariaDB 10.6 Enterprise Server. Я представил, что в нем будут храниться данные о людях и их контактах. Вначале я подумал о создании одной большой таблицы “Контакты” с полями “Имя”, “Фамилия”, “Телефон”, “Email”, “Адрес”. Но потом я вспомнил о принципах нормализации и понял, что такой подход неэффективен.

Я разделил таблицу “Контакты” на несколько таблиц, применяя нормы 1NF, 2NF и 3NF. Вот как выглядит схема моей базы данных:

Таблица “Люди”:

• ID человека (первичный ключ)
• Имя
• Фамилия
• Адрес

Таблица “Контакты”:

• ID контакта (первичный ключ)
• ID человека (внешний ключ, связан с таблицей “Люди”)
• Тип контакта (например, “Телефон”, “Email”)
• Значение контакта (например, номер телефона, адрес электронной почты)

Таблица “Типы телефонов”:

• ID типа телефона (первичный ключ)
• Название типа (например, “Мобильный”, “Домашний”, “Рабочий”)

Таблица “Типы email”:

• ID типа email (первичный ключ)
• Название типа (например, “Личный”, “Рабочий”)

Теперь в таблице “Контакты” хранятся только контактные данные, связанные с определенным человеком через внешний ключ. В отдельных таблицах “Типы телефонов” и “Типы email” хранятся типы контактов, что позволяет избежать избыточности и обеспечить гибкость в добавлении новых типов контактов.

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

Создание схемы базы данных

После того, как я определился с нормальными формами и структурой таблиц, я приступил к созданию схемы базы данных в MariaDB 10.6 Enterprise Server. Я использовал язык SQL для определения таблиц, их полей и связей между ними. Я выбрал имена таблиц и полей так, чтобы они были информативными и понятными.

Для создания таблицы “Люди” я использовал следующий запрос SQL:

CREATE TABLE Люди (
 ID_человека INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 Имя VARCHAR(255) NOT NULL,
 Фамилия VARCHAR(255) NOT NULL,
 Адрес TEXT
);

В этом запросе я указал, что поле “ID_человека” является первичным ключом, который автоматически генерируется и увеличивается при добавлении новых записей. Поля “Имя” и “Фамилия” объявлены как не пустые (NOT NULL), что означает, что они должны содержать значения. Поле “Адрес” имеет тип TEXT, что позволяет хранить длинные текстовые данные.

Аналогичным образом я создал таблицы “Контакты”, “Типы телефонов” и “Типы email”. Я установил связи между таблицами с помощью внешних ключей. Например, в таблице “Контакты” поле “ID_человека” является внешним ключом, связанным с первичным ключом “ID_человека” в таблице “Люди”.

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

Реализация второй нормальной формы

После создания схемы базы данных, я проверил, чтобы она соответствовала второй нормальной форме (2NF). Я убедился, что каждый столбец, не являющийся первичным ключом, полностью зависит от первичного ключа. Я обнаружил, что в таблице “Контакты” поле “Город” не зависит от первичного ключа “ID_контакта”, а зависит от поля “Адрес”, которое в свою очередь зависит от “ID_человека”.

Чтобы устранить это нарушение 2NF, я переместил поле “Город” из таблицы “Контакты” в таблицу “Люди”. Теперь поле “Город” полностью зависит от первичного ключа “ID_человека”.

Вот как изменилась структура таблицы “Контакты”:

CREATE TABLE Контакты (
 ID_контакта INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 ID_человека INT UNSIGNED NOT NULL,
 Тип_контакта VARCHAR(255) NOT NULL,
 Значение_контакта VARCHAR(255) NOT NULL,
 FOREIGN KEY (ID_человека) REFERENCES Люди(ID_человека)
);

А вот как изменилась структура таблицы “Люди”:

CREATE TABLE Люди (
 ID_человека INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 Имя VARCHAR(255) NOT NULL,
 Фамилия VARCHAR(255) NOT NULL,
 Адрес TEXT,
 Город VARCHAR(255)
);

Теперь вся информация о городе хранится в таблице “Люди”, а в таблице “Контакты” хранятся только контактные данные, связанные с определенным человеком. Это позволяет избежать избыточности и обеспечить целостность данных.

Реализация второй нормальной формы улучшила структуру моей базы данных и сделала ее более эффективной.

Реализация третьей нормальной формы

После того, как я реализовал вторую нормальную форму, я проверил свою базу данных на соответствие третьей нормальной форме (3NF). Я заметил, что в таблице “Контакты” поле “Тип_контакта” зависит от поля “Значение_контакта”. Например, если “Значение_контакта” содержит номер телефона, то “Тип_контакта” должен быть “Телефон”. Это нарушение 3NF, потому что поле “Тип_контакта” зависит от неключевого поля “Значение_контакта”.

Чтобы устранить это нарушение, я выделил поле “Тип_контакта” в отдельную таблицу “Типы_контактов”. Теперь в таблице “Контакты” хранится только “ID_типа_контакта”, который является внешним ключом, связанным с первичным ключом “ID_типа_контакта” в таблице “Типы_контактов”.

Вот как изменилась структура таблицы “Контакты”:

CREATE TABLE Контакты (
 ID_контакта INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 ID_человека INT UNSIGNED NOT NULL,
 ID_типа_контакта INT UNSIGNED NOT NULL,
 Значение_контакта VARCHAR(255) NOT NULL,
 FOREIGN KEY (ID_человека) REFERENCES Люди(ID_человека),
 FOREIGN KEY (ID_типа_контакта) REFERENCES Типы_контактов(ID_типа_контакта)
);

А вот как выглядит новая таблица “Типы_контактов”:

CREATE TABLE Типы_контактов (
 ID_типа_контакта INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 Название_типа VARCHAR(255) NOT NULL
);

Теперь в таблице “Типы_контактов” хранятся все возможные типы контактов, а в таблице “Контакты” хранится только “ID_типа_контакта”, что упрощает добавление новых типов контактов и избегает избыточности.

Реализация третьей нормальной формы улучшила структуру моей базы данных и сделала ее более гибкой и удобной.

Преимущества нормализации таблиц

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

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

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

В-третьих, нормализация упрощает обработку данных. Благодаря разделению данных на несколько таблиц, запросы к базе данных становятся более эффективными. Например, при поиске контактов по имени и фамилии необходимо обращаться только к таблице “Люди”, а не к таблице “Контакты”.

В-четвертых, нормализация улучшает производительность базы данных. Благодаря отсутствию избыточности и упрощению запросов, операции чтения, записи и обновления данных выполняются быстрее.

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

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

Я узнал, что нормализация – это не одноразовый процесс. По мере развития приложения и изменения требований может потребоваться пересмотреть схему базы данных и внести необходимые изменения.

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

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

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

В таблице представлены все четыре таблицы, которые я использовал в своем телефонном справочнике: “Люди”, “Контакты”, “Типы телефонов” и “Типы email”.

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

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

Чтобы наглядно продемонстрировать преимущества нормализации, я решил сравнить структуру базы данных до и после ее применения. Я создал сравнительную таблицу в HTML-формате, которая показывает разницу в структуре таблиц “Контакты” в двух вариантах: с ненормированной структурой и с нормализованной структурой.

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

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

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

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

Также нормализация упростила обработку данных. Например, чтобы найти все контакты определенного человека, необходимо обращаться только к таблице “Контакты”, а не к таблице “Люди”, как это было в ненормированной структуре.

FAQ

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

Вопрос 1: Нужно ли нормализовать все таблицы?

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

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

Вопрос 2: Как выбрать правильную нормальную форму?

Выбор правильной нормальной формы зависит от конкретного приложения и его требований.

Первая нормальная форма (1NF) – это базовый уровень нормализации, который необходимо применять к всем таблицам.

Вторая нормальная форма (2NF) позволяет устранить избыточность, связанную с зависимостью от части первичного ключа.

Третья нормальная форма (3NF) устраняет избыточность, связанную с зависимостью от неключевых полей.

Четвертая и пятая нормальные формы – это более сложные нормы, которые применяются в редких случаях.

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

Вопрос 3: Как устранить нарушение нормальной формы?

Чтобы устранить нарушение нормальной формы, необходимо разделить таблицу на несколько меньших таблиц.

Например, в таблице “Контакты” поле “Город” зависело от поля “Адрес”. Чтобы устранить нарушение 2NF, я переместил поле “Город” в таблицу “Люди”, где оно полностью зависит от первичного ключа “ID_человека”.

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

Вопрос 4: Как упростить обработку данных с помощью нормализации?

Нормализация упрощает обработку данных за счет уменьшения избыточности и упрощения запросов к базе данных.

Например, в ненормированной структуре таблицы “Контакты” для поиска всех контактов определенного человека необходимо было проверить все записи таблицы. В нормализованной структуре для этого достаточно обратиться к таблице “Контакты” и выбрать записи с определенным “ID_человека”.

Нормализация также упрощает обновление данных. Например, при изменении адреса человека в ненормированной структуре необходимо было обновить все записи таблицы “Контакты”, в которых указан этот адрес. В нормализованной структуре достаточно обновить запись в таблице “Люди”, так как в таблице “Контакты” хранится только “ID_человека”.