Основы нормализации реляционных баз данных

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

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

Давайте подробнее рассмотрим ключевые аспекты нормализации реляционных баз данных.

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

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

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

sqlite> CREATE TABLE dates (
   ...> _id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...> page_id INTEGER NOT NULL,
   ...> date TEXT,
   ...> FOREIGN KEY (page_id) REFERENCES pages(_id)
   ...> );

Вывод информации о правках в формате, удобном для восприятия:

sqlite> SELECT dates.date, pages.title
   ...> FROM dates, pages
   ...> ON dates.page_id == pages._id
   ...> ORDER BY date(dates.date) DESC;
2019-06-04|Boolean Lows
2019-06-03|Amount of Information
2019-06-01|What is Algorithm
2019-05-30|Boolean Lows
2019-05-26|Amount of Information
2019-05-26|Binary System
2019-05-25|What is Information

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

Запрос, извлекающий последние изменения для каждой страницы:

sqlite> SELECT max(date(dates.date)), pages.url
   ...> FROM dates, pages
   ...> ON dates.page_id == pages._id
   ...> GROUP BY pages.url
   ...> ORDER BY date(dates.date) DESC;
2019-06-04|boolean
2019-06-03|amount-information
2019-06-01|algorithm
2019-05-26|binary
2019-05-25|information

В данном случае, для каждой группы строк вычисляется максимальное значение в поле даты. Сортировка применяется после этого.

Для просмотра истории изменений определенной страницы можно использовать следующий запрос:

sqlite> SELECT date FROM dates WHERE page_id == 8 ORDER BY date DESC;

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

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

В нашем случае мы могли бы просто записывать тематику страницы словами в таблице pages, не создавая таблицу sections. Однако, важно создавать отдельную таблицу для тем по следующим причинам:

• Изменение названия темы проще выполнить в таблице sections, потому что оно упоминается всего лишь один раз. Если бы в pages использовались названия, пришлось бы сменить их во всех соответствующих строках.

• В sections может быть добавлен дополнительный столбец, например, "описание темы", который будет касаться темы, а не страницы. Иначе это бы нарушило нормализацию, по которой запись должна описывать только одну сущность. Используя JOIN можно получить полные данные о странице, включая описание её темы.

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

Процесс проектирования базы данных и её нормализации является сложным и идет перед наполнением БД и работой с ней. Чтобы создать грамотно спроектированную, сложную базу данных, необходимо иметь глубокое понимание области применения, а также знать SQL и особенности используемой СУБД.