Использование предложения ALTER TABLE для нормализации таблицы

Задача
У вас есть таблица, не приведенная к нормальной форме.

Решение
ALTER TABLE поможет нормализовать ее.

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

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

CREATE TABLE client_billing
(
id INT UNSIGNED NOT NULL, # идентификационный номер клиента
name CHAR(20) NOT NULL, # имя клиента
address CHAR(20) NOT NULL, # адрес клиента
date DATE NOT NULL, # дата предоставления услуги
minutes INT NOT NULL, # количество оплачиваемых минут
description CHAR(60) NOT NULL # предоставленная услуга
);

Пока для каждого клиента есть всего одна строка, таблица выглядит замечательно:

+---+-------+-----------------+--------------+----------+---------------------+
| id | name | address | date | minutes | description |
+---+-------+-----------------+---------------+----------+--------------------+
| 21 | John | 46 North Ave.


| 2001-07-15 | 48 | consult by phone |
| 43 | Toby | 123 Elm St. | 2001-07-13 | 12 | office visit |
+---+-------+------------------+--------------+----------+---------------------+

Но когда вы введете новые данные и одному клиенту будет соответствовать несколько строк, станет очевидной избыточность некоторой информации.

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

+---+------+------------------+---------------+----------+---------------------+
| id | name | address | date | minutes | description |
+---+------+------------------+--------------+----------+----------------------+
| 21 | John | 46 North Ave. | 2001-07-15 | 48 | consult by phone |
| 21 | John | 46 North Ave. | 2001-07-19 | 120 | court appearance |
| 43 | Toby | 123 Elm St. | 2001-07-13 | 12 | office visit |
| 43 | Toby | 123 Elm St. | 2001-07-14 | 60 | draft proposal |
| 43 | Toby | 123 Elm St.


| 2001-07-16 | 180 | present proposal |
+---+------+------------------+--------------+----------+----------------------+

Для исправления ситуации следует разбить информацию на две таблицы и сопоставлять их записи, используя значения id:

• Одна таблица (client_info) будет хранить информацию, уникальную для каждого клиента, она содержит по одной строке для каждого клиента: идентификатор, фамилия и адрес.

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

Другими словами, таблицу client_billing можно разделить на таблицы client_info и bill_item так:

Таблица client_info:

+----+-------+-----------------+
| id | name | address |
+----+------+------------------+
| 21 | John | 46 North Ave. |
| 43 | Toby | 123 Elm St. |
+---+-------+------------------+

Таблица bill_item:

+----+-------------+----------+-----------------------+
| id | date | minutes | description |
+----+-------------+----------+-----------------------+
| 21 | 2001-07-15 | 48 | consult by phone |
| 21 | 2001-07-19 | 120 | court appearance |
| 43 | 2001-07-13 | 12 | office visit |
| 43 | 2001-07-14 | 60 | draft proposal |
| 43 | 2001-07-16 | 180 | present proposal |
+---+---------------+----------+----------------------+

Чтобы выполнить такое преобразование, сначала создайте таблицы client_info и bill_item, определяя все столбцы так же, как и в исходной таблице client_billing:

CREATE TABLE client_info
(
id INT UNSIGNED NOT NULL, # идентификатор клиента
name CHAR(20) NOT NULL, # имя клиента
address CHAR(20) NOT NULL # адрес клиента
);
CREATE TABLE bill_item
(
id INT UNSIGNED NOT NULL, # идентификатор клиента
date DATE NOT NULL, # дата предоставления услуги
minutes INT NOT NULL, # количество оплачиваемых минут
description CHAR(60) NOT NULL # описание предоставленной услуги
);

Затем используйте INSERT INTO ...


SELECT для копирования соответствующих столбцов из таблицы client_billing в две новые таблицы. В таблицу client_info скопируем информацию о клиентах так:

INSERT INTO client_info (id,name,address)
SELECT id,name,address FROM client_billing;

Аналогично для таблицы bill_item:

INSERT INTO bill_item (id,date,minutes,description)
SELECT id,date,minutes,description FROM client_billing;

Записи двух новых таблиц связаны посредством значений id, так что было бы удобно индексировать этот столбец в каждой из таблиц, чтобы сделать связь более эффективной. Однако не станем ограничиваться просто созданием предложения ALTER TABLE имя_таблицы ADD INDEX (id) для каждой таблицы.

Во-первых, таблица client_info содержит несколько записей для каждого клиента, которые необходимо опять свернуть в единую запись. Значит, будем создавать для столбца id индекс PRIMARY KEY или UNIQUE, используя ключевое слово IGNORE для удаления повторяющихся записей. Кроме того, логично предположить, что многие запросы к таблице bill_item будут использовать дату, так что можно включить в индекс столбец date. Создающие такие индексы предложения ALTER TABLE выглядят так:

ALTER IGNORE TABLE client_info ADD PRIMARY KEY (id);
ALTER TABLE bill_item ADD INDEX (id, date);

После выполнения только что описанной процедуры таблица client_billing больше не понадобится, и ее можно удалить:

DROP TABLE client_billing;

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

mysql> SELECT client_info.id, client_info.name, client_info.address,
-> SUM(bill_item.minutes) AS 'total minutes'
-> FROM client_info, bill_item
-> WHERE client_info.id = bill_item.id
-> GROUP BY client_info.id;

+---+-------+-----------------+----------------+
| id | name | address | total minutes |
+---+-------+-----------------+----------------+
| 21 | John | 46 North Ave. | 168 |
| 43 | Toby | 123 Elm St. | 252 |
+---+-------+-----------------+----------------+

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

CREATE TABLE test_subject
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(20) NOT NULL, # имя испытуемого
date1 DATE, # дата и результат первого теста
result1 INT,
date2 DATE, # дата и результат второго теста
result2 INT,
PRIMARY KEY (id)
);

Информация в таблице будет выглядеть так:

+--+-------+--------------+---------+-------------+---------+
| id | name | date1 | result1 | date2 | result2 |
+--+-------+--------------+---------+-------------+---------+
| 1 | Fred | 2001-07-13 | 78 | 2001-07-14 | 85 |
| 2 | Barry | 2001-07-12 | 79 | 2001-07-14 | 82 |
| 3 | Portia | 2001-07-16 | 82 | 2001-07-18 | 95 |
+--+-------+--------------+---------+--------------+---------+

Если теперь вы захотите вычислить, например, средний результат каждого протестированного, то выполните запрос, подобный предложенному:

SELECT id, name, (result1 + result2) / 2 FROM test_subject;

Если вы захотите провести третий и четвертый тесты, то без проблем сможете добавить в таблицу столбцы:

ALTER TABLE test_subject
ADD date3 DATE, ADD result3 INT,
ADD date4 DATE, ADD result4 INT;

Но запрос, вычисляющий средний результат, усложнится:

SELECT id, name, (result1 + result2 + result3 + result4) / 4
FROM test_subject;

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

• Первая таблица будет хранить уникальную информацию о каждом испытуемом.

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

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

• Создадим таблицу test_result для хранения дат и результатов тестов, а также идентификаторов испытуемых. Чтобы обеспечить сортировку результатов, можно добавить столбец с номером теста. (Для упорядочивания можно было бы использовать дату, но кто-то ведь может пройти несколько тестов за один день. С явным номером теста не возникнет таких проблем.)

• Скопируем идентификаторы испытуемых, даты и результаты из таблицы test_subject в test_result.

• Удалим из таблицы test_subject столбцы даты и результата, оставив только идентификаторы и имена испытуемых.

Начнем с создания таблицы для хранения результатов тестов:

CREATE TABLE test_result
(
id INT UNSIGNED NOT NULL,
test_num INT NOT NULL,
date DATE,
result INT
);

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

INSERT INTO test_result (id,test_num,date,result)
SELECT id,1,date1,result1 FROM test_subject WHERE result1 IS NOT NULL;INSERT INTO test_result (id,test_num,date,result)
SELECT id,2,date2,result2 FROM test_subject WHERE result2 IS NOT NULL;

Каждое предложение INSERT INTO ... SELECT указывает значение test_num «вручную», так как соответствующее значение не содержится в таблице test_subject и не может быть непосредственно получено из ее содержимого.

Инструкция WHERE обеспечивает копирование только строк с не-NULL-результатами теста. Тем самым обрабатывается возможность неполноты записей test_subject (значение NULL указывает на то, что испытуемый еще не прошел данный тест). Если в таблице test_subject есть результаты всех тестов, в инструкции WHERE нет необходимости, и ее можно опустить.

Таблица test_result заполнена данными, можно индексировать ее. Заметьте, что, хотя столбец id в таблице test_subject является столбцом с уникальными значениями, в таблице test_result это уже не так, ведь для каждого испытуемого в ней содержится несколько записей. Однако можно создать уникальный индекс, используя сочетание id и test_num, считая, что каждый тест предлагается каждому участнику лишь единожды:

ALTER TABLE test_result ADD PRIMARY KEY (id, test_num);

Столбцы с результатами тестов в таблице test_subject больше не нужны, и их можно удалить:

ALTER TABLE test_subject DROP date1, DROP result1, DROP date2, DROP result2;

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

SELECT id, AVG(result) FROM test_result GROUP BY id;

Чтобы вывести и имена испытуемых, выполним соединение таблицы test_result с таблицей test_subject:

SELECT test_result.id, test_subject.name, AVG(test_result.result)
FROM test_subject, test_result
WHERE test_subject.id = test_result.id
GROUP BY test_result.id;

Можно определить испытуемых, которые прошли не все тесты. Например, если проводилось всего четыре теста, можно найти количество испытуемых, которым в таблице test_result соответствует менее четырех результатов:

SELECT test_subject.id, test_subject.name, COUNT(test_result.result) AS count
FROM test_subject LEFT JOIN test_result ON test_subject.id = test_result.id
GROUP BY test_subject.id
HAVING count < 4;

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

Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Добавление и удаление индексов
Изменение значения столбца по умолчанию
Изменение определения или имени столбца
Изменение типа таблицы
Переименование таблицы