База SQLite и основы SQL - 5. Нормализация
12-11-2021Время чтения ~ 7 мин.SQL 3309
Нормализацию базы делают для того, чтобы избежать логических ошибок. Изменение структуры таблиц достаточно затратная операция. Помимо того, что нужно будет позаботиться о сохранности данных, придётся ещё переписывать sql-запросы, а также программный код, их обслуживающий. Поэтому лучше потратить больше времени на обдумывание структуры и тестирование базы.
Нормализация никакого отношения к языку SQL или СУБД не имеет. Это всего лишь подход или рекомендация. Поэтому для SQLite нет никаких проблем работать с любой, даже неоптимальной структурой. Нормализация нужна нам, разработчикам, чтобы избежать головной боли в будущем.
Первая нормальная форма (1НФ)
Мы рассмотрели 1НФ, когда привели таблицу my3 к атомарному виду. То есть вместо
id name skill ----------------- 1 Вася php, js, sql 2 Петя js, sql, pascal
Нужно обеспечить атомарность каждого поля:
id name php js sql pascal ------------------------------- 1 Вася 1 1 1 0 2 Петя 0 1 1 1
Ещё один пример. Например есть таблица клиентов, где указывается номер телефона. Вместо:
name phone ----------------- Вася 050-123-34-67, 068-550-55-55 Петя 098-345-67-89
Нужно делать так:
name phone ----------------- Вася 050-123-34-67 Вася 068-550-55-55 Петя 098-345-67-89
При этом, заметьте, 1НФ по сути не указывает на структуру таблицы, а лишь на то, как должны добавляться данные. Поскольку поле phone текстовое, то мы можем внести в него что угодно. Но когда таблица соответствует 1НФ, то мы уверены, что каждое поле phone содержит ровно один телефон.
Вторая нормальная форма (2НФ)
Вторая нормальная форма означает, что база соответствует 1НФ, а также обеспечивает уникальность каждой записи. По сути в прошлых примерах мы обеспечили уникальность с помощью PRIMARY KEY (первичный ключ). Ещё это похоже на то, как мы использовали UNIQUE для двух полей. Но UNIQUE — это т.н. ограничение целостности, настоящий составной первичный ключ можно создать так:
CREATE TABLE my4 ( id INTEGER, inn INTEGER, name TEXT, CONSTRAINT idx PRIMARY KEY (id, inn) );
Конструкция CONSTRAINT создаёт ключ и в данном случае это будет PRIMARY KEY, который состоит из двух полей id и inn. Если у нас есть составной ключ, то это будет 2НФ. Но на практике обычно просто считают, что каждая таблица должна иметь первичный ключ. Только в этом случае у нас есть возможность однозначно идентифицировать каждую запись.
2НФ также накладывает ограничения на набор данных в таблице. Например есть таблица:
name position price ---------------------- Вася php 100 Петя js 200
Если первичный ключ name, то есть зависимость name-position (сотрудник-должность). Также мы видим, что есть зависимость position-price (должность-оклад), но нет зависимости name-price (сотрудник-оклад). То есть price никак не зависит от первичного ключа. И вот это как раз нарушение 2НФ. Вместо этого нужно создать две таблицы:
name position --------------- Вася php Петя js position price --------------- php 100 js 200
Только в этом случае мы получаем 2НФ, где все поля зависят от первичного ключа.
Третья нормальная форма
3НФ должна отвечать 1НФ и 2НФ и при этом формировать данные в виде отношений между ключами таблиц. Например есть данные такого вида:
name skill ----------- Вася php, js Петя sql, js
Чтобы привести эти данные к 3НФ по сути нужно сделать так, чтобы вынести всё, что возможно в справочные таблицы, а итоговая таблица будет хранить только связи ключей.
Таблица USERS ============= id name ----------- 1 Вася 2 Петя Таблица SKILLS ============== id skill ----------- 1 php 2 js 3 sql Таблица EMPLOYEES ================= user skill ----------- 1 1 1 2 2 2 2 3
Такая база будет полностью соответствовать 3НФ, где таблица EMPLOYEES хранит только связи между таблицами, а остальные таблицы соответствуют 2НФ.
На практике же не всегда имеет смысл использовать 3НФ, поскольку это может несколько «раздуть» базу.
Например, пусть у нас есть таблица pages, где мы храним записи для CMS.
id title content user_id stаtus_id ------------------------------------------- 1 название текст 2 1
Поле user_id ссылается на таблицу users:
id name --------- 1 Вася 2 Петя
Поле stаtus_id указывает таблицу статусов страницы (опубликовано/черновик):
id status --------- 1 publish 2 draft
Формально здесь всё верно, но таблица статусов по сути будет всегда иметь только два возможных значения. Поэтому вместо того, чтобы создавать отдельную таблицу используют обычное поле stаtus, которое хранит номер или текст статуса публикации.
CREATE TABLE myPages ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, title TEXT NOT NULL, content TEXT DEFAULT '', user_id INTEGER, status TEXT DEFAULT 'publish' );
Такой подход упрощает работу с базой, хотя при этом нарушается 3НФ.
Стоит отметить, что в других СУБД есть специальный тип данных ENUM — перечисления, где можно указать список возможных значений поля. Но в SQLite такой возможности не предусмотрено. То есть предполагается, что в «правильной» базе все перечисления должны быть в отдельных таблицах.
Хотя в SQLite можно добавить проверку вводимых данных с помощью ограничения CHECK.
Связь между таблицами
Мы уже понимаем, что база скорее всего будет состоять из нескольких таблиц. Для того, чтобы их между собой «связывать» используются разные подходы.
Чтобы понять как это работает, рассмотрим простые таблицы.
CREATE TABLE t1 ( id INTEGER PRIMARY KEY, name TEXT ); INSERT INTO t1 VALUES (1, 'T1'); INSERT INTO t1 VALUES (2, 'T2'); INSERT INTO t1 VALUES (3, 'T3'); INSERT INTO t1 VALUES (4, 'T4'); INSERT INTO t1 VALUES (5, 'T5'); SELECT * FROM t1; id name --------- 1 T1 2 T2 3 T3 4 T4 5 T5
Аналогично сделаем вторую таблицу:
CREATE TABLE r1 ( id INTEGER PRIMARY KEY, name TEXT ); INSERT INTO r1 VALUES (1, 'R1'); INSERT INTO r1 VALUES (2, 'R2'); INSERT INTO r1 VALUES (3, 'R3'); SELECT * FROM r1; id name --------- 1 R1 2 R2 3 R3
А теперь предположим, что поля id в этих таблицах указывают друг на друга и мы хотим получить все записи, где эти id совпадают.
SELECT * FROM t1, r1 WHERE t1.id = r1.id; id name id:1 name:1 ------------------------ 1 R1 1 T1 2 R2 2 T2 3 R3 3 T3
То что сейчас произошло называется присоединение (объединение или соединение) таблиц. Во WHERE указывается условие такого присоединения. При этом во FROM мы указываем какие таблицы будут задействованы. Поскольку поля в таблицах одноимённые, то мы используем синтаксис таблица.поле. В большинстве sql-запросов с несколькими таблицами поля указываются именно в таком виде.
Мы можем этот запрос немного изменить, чтобы указать только те поля, которые нужны в результате.
SELECT t1.id AS id, t1.name AS T, r1.name AS R FROM t1, r1 WHERE t1.id = r1.id; id R T --------- 1 R1 T1 2 R2 T2 3 R3 T3
Команда AS позволяет создавать синонимы и во многих случаях используют не исходные имена таблиц, а их синонимы.
SELECT t.id AS id, t.name AS T, r.name AS R FROM t1 AS t, r1 AS r WHERE t.id = r.id; id R T --------- 1 R1 T1 2 R2 T2 3 R3 T3
В большинстве случаев вы будете использовать именно такой вариант.
Синоним таблицы нужен для того, чтобы сделать sql-запрос более читабельным, поскольку обычно названия таблиц достаточно длинные. У AS, конечно есть и другие назначения.
Данный пример показывает один из самых простых способов получить данные из двух таблиц. Работает это следующим образом. Вначале происходит получение данных из FROM — здесь две таблицы, поэтому происходит объединение данных:
SELECT * FROM t1, r1; id name id:1 name:1 ------------------------ 1 T1 1 R1 1 T1 2 R2 1 T1 3 R3 2 T2 1 R1 2 T2 2 R2 2 T2 3 R3 3 T3 1 R1 3 T3 2 R2 3 T3 3 R3 4 T4 1 R1 4 T4 2 R2 4 T4 3 R3 5 T5 1 R1 5 T5 2 R2 5 T5 3 R3
SQLite нашла все комбинации между таблицами - 15 штук (5*3 — по количеству записей). Это первая итерация запроса.
После этого происходит выполнение условия во WHERE t1.id = r1.id. Попробуйте ради интереса вручную найти одинаковые id в этой таблице и убедиться, что там останутся ровно три записи.
По сути объединение таблиц произошло в команде FROM, но мы фильтруем результат под наше условие. Это ключевой момент в понимании того, как нужно работать с несколькими таблицами. При этом, заметьте, абсолютно всё равно по какому полю происходит объединение, главное, чтобы оно было одного типа. Обычно это INTEGER. Так же всё равно как будут устроены эти таблицы. Мы всё также можем делать выборки из них по отдельности.
Мы можем объединять больше таблиц. Сделаем ещё одну аналогичную.
CREATE TABLE q1 ( id INTEGER PRIMARY KEY, name TEXT ); INSERT INTO q1 VALUES (1, 'Q1'); INSERT INTO q1 VALUES (2, 'Q2'); INSERT INTO q1 VALUES (3, 'Q3'); SELECT * FROM q1; id name --------- 1 Q1 2 Q2 3 Q3
И объединим все три таблицы в одну.
SELECT t.id AS id, t.name AS T, r.name AS R, q.name AS Q FROM t1 AS t, r1 AS r, q1 AS q WHERE t.id = r.id AND t.id = q.id; id R T Q ------------- 1 R1 T1 Q1 2 R2 T2 Q2 3 R3 T3 Q3
Работает он точно также — вначале объединение таблиц во FROM, а потом срабатывает условие WHERE. Попробуйте выполнить запрос
SELECT * FROM t1, r1, q1;
и убедиться, что будут выведены 45 строк (5*3*3).
Когда таблицы объединяются подобным образом, то говорят о пересечении таблиц - в результат выборки попадают только те записи, которые совпадают по указанному полю. В SQL есть аналог такой операции, который выполняется командой INNER JOIN. Об этом пойдёт речь позже.