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

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

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

Рассмотрим несколько базовых принципов нормализации реляционных баз данных.

В таблице каждая строка должна содержать одинаковое число столбцов. В принципе по-другому и быть не может, ведь при создании таблицы с помощью 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 указывались названия, а не ID тем, пришлось бы найти все строки с необходимым названием и обновить их.
В sections мог быть еще одни столбец, например "описание темы", который относится к теме, а не странице. Иначе мы бы нарушили нормальную форму, согласно которой запись должна описывать одну сущность. Если же потребуется получить полные сведения о какой-нибудь странице, в том числе описание ее темы, это можно сделать через JOIN.
На таблицу sections может быть внешний ключ из какой-нибудь другой таблицы, а не только из pages.

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