Что такое денормализация? Это намеренное внесение изменений в нормализованную схему бд, после которых она перестает соответствовать правилам НФ.

Для чего это делается? Основная цель — это увеличение производительности ценой увеличения избыточности, возможных коллизий, а также некоторым неудобством составления сложных выборок. 

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

Но иногда базы имеют настолько сложную структуру, что при выполнении выборки перелопачиваются сотни тысяч строк кода и это сказывается на быстродействии. И если схема нормализованна, то запрос будет идти уже не к одной таблице, а ко многим (в зависимости от числа сущностей). Что в свою очередь, увеличит время выполнения запроса еще в N раз.

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

Еще несколько ссылочек.

• Нормализация баз данных: третья нормальная форма
• Нормализация баз данных: вторая нормальная форма
• Нормализация баз данных: первая нормальная форма
• Нормализация базы данных
• urpmi база заблокирована (возможно ее использует другая программа)
• Как лечить database is locked в mandriva linux

Большинство из таких сервисов предоставяют бесплатный пробный доступ на несколько дней, но я искал полностью бесплатный вариант, пускай и не сильно функциональный.

Lumzy

Дают только 15 дней триала. Немного детский интерфейс, есть ночной вариант дизайна. Попробовал накидать форму, не смог поменять текст в текстовом поле. На том и закрыл его, не совсем он интуитивен.

Mockingbird

Крайне серьёзный онлайн-редактор с дизайном в стиле мака. Есть возможность сохранять, приглашать друзей для совместной работы. И просто отличный интерфейс, все понятно, все находится само собой. За 20 минут набросал концепт, впечатления от работы крайне положительные. Бесплатная версия дает 1 проект и 10 страниц. Для небольшой работы вместе вполне пригодится.

MockFlow

Еще один весьма неплохой редактор. Есть возможность импорта-экспорта, расшаривания. Весьма широкие наборы компоненты. И одна супер фича — есть пользовательская библиотека готовых шаблонов. Можно выбрать уже созданные кем-то шаблоны и заюзать их для своих целей. Есть ревизии для проектов. В халявной версии 4 страницы, 10 мегабайт хранилица и двое сотрудников для одновременного труда. Экспорт с водной маркой сервиса.Конкурент mockingbird.

Еще несколько ссылочек.

• Ничего нет похожего на этот пост, он неповторим

Третья нормальная форма.

База данных будет находиться в третьей нормальной форме, если она приведена ко второй нормальной форме и каждый не ключевой столбец независим друг от друга. Если следовать процессу нормализации правильно до этой точки, с приведением к 3НФ может и не возникнуть вопросов. Следует знать, что 3НФ нарушается,  если изменив значение в одном столбце, потребуется изменение и в другом столбце.  В примере с форумом (рисунок вверху), проблем с приведением к 3НФ не возникнет, но можно рассмотреть как образец гипотетическую ситуацию, где это может произойти.

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

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

Если вы чувствуете, что все эти действия могут быть излишними, вы правы.  Честно, в верхних уровнях нормализации часто нет необходимости.  Смысл в том, что нужно стараться нормализовать базу данных, но иногда приходиться идти на уступки ради того, чтобы не допустить чрезмерного усложнения. Потребности приложения и структура данных в базе подскажут, насколько потребуется проводить процесс нормализации.

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

Чтобы привести базу к третьей нормальной форме, надо:

1. Определить, в каких полях каких таблиц имеется взаимозависимость. Как только что говорилось, поля, которые зависят больше друг от друга (как город от штата), чем от ряда в целом. В базе форума такой проблемы нет. Взглянув на таблицу сообщений, увидите, что каждый заголовок, каждое тело сообщения относится к своему message ID.

2. Создайте соответствующие таблицы. Если есть проблемный столбец в шаге 1, создавайте раздельные таблицы для него. Как города и штаты, в примере с клиентами.

3. Создайте или выделите первичные ключи. Каждая таблица должна иметь первичный ключ. Для примера с клиентами это будут city ID и state ID.

4. Создайте необходимые внешние ключи, которые образуют любое из отношений. В нашем примере нужно добавить state ID в таблицу городов и city ID в таблицу клиентов. Это свяжет каждого клиента с городом и штатом, где они живут.

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

Подсказки:

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

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

Нарушения правил нормализации

Убедившись, что база данных в 3НФ поможет гарантировать надёжность и жизнеспособность, не нужно полностью нормализовывать все базу, с которыми вы работаете. Перед тем, как использовать эти методы, имейте ввиду, что это может иметь долгосрочные разрушающие последствия.

Две основных причины, чтобы нарушить правила нормализации — удобство и быстродействие. Меньшим число таблиц проще управлять, чем большим. Кроме того, из-за более сложного характера, нормализованные таблицы более медленные для обновления, изменения и выдачи данных. Вкратце, нормализация это сделка между целостностью/расширяемостью и простотой/скоростью. С другой стороны, есть достаточно способов чтобы улучшить производительность базы данных, но не так много способов чтобы исправить повреждённые данные, возникшие из-за плохого дизайна структуры.

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

Ссылки:

1. Введение
2. Первая нормальная форма
3. Вторая нормальная форма
4. Вики о нормализации
5. Mysql Workbench — программа для проектирования баз данных

Еще несколько ссылочек.

• Денормализация базы данных
• Нормализация баз данных: вторая нормальная форма
• Нормализация баз данных: первая нормальная форма
• Нормализация базы данных
• urpmi база заблокирована (возможно ее использует другая программа)
• Как лечить database is locked в mandriva linux

Вторая нормальная форма.

Для приведения таблиц ко второй нормальной форме (2НФ), приводимые таблицы должны быть уже в 1НФ. Нормализация должна проходить по порядку.

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

На рисунке выше и названия фильмов и имена актеров нарушают правила 2НФ (сами не являются ключами и не зависят от первичного ключа).

После всех преобразований, база данных с фильмами будет иметь минимум 4 таблицы.

Чтобы привести бд ко второй нормальной форме, потребовалось 4 таблицы. Режиссёры (directors), представлены в фильмах (movies) через внешний ключ director ID, фильмы в таблице фильмы-актёры (movies-actors) через movie ID, актеры через actor ID

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

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

Чтобы привести базу ко второй нормальной форме, надо:

Чтобы привести эту базу к 2НФ, нужно 3 таблицы минимум

• Определить все столбцы, которые не находятся в прямой зависимости от первичного ключа этой таблицы. На рисунке выше у таблиц users и forums нет первичного ключа. У таблицы messages первичный ключ — message ID, от которого зависят все остальные поля этой таблицы.
• Создаем необходимые поля в таблицах users и forums, выделяем из существующих полей или создаем из новых первичные ключи.
  

  Для каждой таблицы нужен свой первичный ключ

• Создаем внешние ключи и обозначаем их отношения между таблицами. Конечным шагом нормализации до 2НФ будет являться выделение внешних ключей для связи с ассоциированными таблицами. Первичный ключ одной таблицы должен быть внешним ключом в другой. На рисунке снизу показана связь между ключами трех таблиц. Поле user ID таблицы messages является первичным ключом поля user ID таблицы users.  Тип связи между ними — один ко многим. Один пользователь может оставить много сообщений, но у сообщения может быть только один пользователь. Такая же связь соединяет таблицы forums и messages через forum ID. У форума может быть много сообщений, но сообщение может находиться только в одном форуме.

Чтобы соотнести три таблицы, в messages добавлено два внешних ключа, ведущих на первичные ключи в своих таблицах

Подсказки:

• Другой способ приведения схемы к 2НФ — посмотреть на отношения между таблицами. Идеальный вариант — создать все отношения вида один-к-многим. Отношения вида многие-к-многим нуждаются в реструктуризации.
• Если взглянуть еще раз на таблицу movies-actors, то можно заметить, что она является промежуточной таблицей. Она превращает отношение многие-к-многим между movies и actors в один-к-многим. Можно вводить такие промежуточные таблицы, у которых все столбцы являются ключами. В таких таблицах не требуется свой собственный первичный ключ, поскольку он может быть комбинацией двух внешних ключей.
• Нормализованная должным образом таблица никогда не будет иметь повторяющихся рядов (двух и более рядов, значения которых не являются ключами и содержат совпадающие данные).
• Чтобы упростить нормализацию, помните, что при приведении к 1НФ вы ищете дубли горизонтально (дубли столбцов), а при приведении к 2НФ — вертикально (дубли рядов).

Еще несколько ссылочек.

• Денормализация базы данных
• Нормализация баз данных: третья нормальная форма
• Нормализация баз данных: первая нормальная форма
• Нормализация базы данных
• urpmi база заблокирована (возможно ее использует другая программа)
• Как лечить database is locked в mandriva linux

Первая нормальная форма

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

Чтобы привести таблицу к 1НФ, нужно соблюсти два правила:

1. Атомарность или неделимость. Каждая колонка должна содержать одно неделимое значение.
2. Таблица не должна содержать повторяющихся колонок или групп данных.

Например, если таблица содержит в одном поле полный адрес человека (улица, город, почтовый код), не будет отвечать правилам 1НФ, поскольку будет содержать различные значения в одном столбце, что будет нарушением правила об атомарности. Или если бд содержит данные о фильмах и в ней есть столбцы актер1, актер2, актер3, также не будет отвечать правилам, поскольку будет иметь место повторению данных.

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

Чтобы привести таблицу к первой нормальной форме, следует:

• Найти все поля, которые содержат многосоставные части информации. На рисунке выше, поле message date содержит день, месяц, год и время, которое можно разбить на составные части, но в данном примере такая детализация даты не нужна.  Mysql может работать и с таким форматом — благодаря типу DATETIME. В этом примере разбито имя пользователя на имя и фамилию. Еще примерами неудачных решений могут быть поля, в которых хранятся сразу все телефоны человека (мобильный, рабочий) или его интересы (готовка, танцы).
• Те данные, которые можно разбить на составные части, нужно выносить в отдельные поля. На рисунке выше так разнесено полное имя на имя и фамилию.
• Выносите повторяющиеся данные в отдельную таблицу. В примере с форумом такой проблемы нет, поэтому возьмем в качестве примера таблицу, содержащую информацию о фильмах. Там есть несколько полей actor, которые являются повторяемыми. Повторяемые поля тут несут две проблемы. Если хранить информацию об актерах таким образом, то их число будет лимитировано числом таблиц. Даже если их будет 100, то все равно это будет пределом для некоторых фильмов. И вторая проблема — будет большое количество пустых(NULL) ячеек для большинства остальных записей, чего также следует избегать. Решением этой проблемы станет создание отдельной таблицы для актеров, куда будет заносится информация обо всех необходимых фильмах. Имена актеров также разбиты, чтобы соблюсти атомарность. Также в этой таблице присутствует свой первичный ключ, что является необходимым условием для нормализации.
• Дважды проверьте, все ли таблицы подходят под условия первой нормальной формы.

проблема избыточных полей

решение проблемы избыточных полей

Подсказки

• Простейший путь приведения к 1НФ — это пройтись глазами по всем столбцам. Проверьте каждый ряд на отсутствие повторения схожих данных и делимости.
• Разные источники трактуют процесс нормализации по своему, в основном более сухим, техническим языком. Более важен результат нормализации, а не повторение правил и умных слов.

Следующий пост будет посвящен второй нормальной форме — 2НФ.

На почитать

• Мой вводный пост о нормализации
• Вики о 1НФ Там же дано четкое определение атомарности.
  
• Сайт Мурманского технического университета

Еще несколько ссылочек.

• Денормализация базы данных
• Нормализация баз данных: третья нормальная форма
• Нормализация баз данных: вторая нормальная форма
• Нормализация базы данных
• urpmi база заблокирована (возможно ее использует другая программа)
• Как лечить database is locked в mandriva linux

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

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

На практике редко нормализуют дальше 3-ей нормальной формы. Поподробнее узнать обо всех нормальных формах и теории по ссылкам внизу поста.

Все примеры из книги показаны на бд для форума с обычной для форумов структурой — посты, авторы, время и т.п.

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

Ключи

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

Первичный ключ — это уникальный идентификатор, отвечающий следующим условиям:

1. Он должен иметь значение, не NULL.
2. Быть неизменным — значение ключа не должно меняться.
3. Иметь уникальное значение для каждой строки.

Внешние ключи — это ссылки на первичные ключи других таблиц, которые удовлетворяют условиям выше.

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

Для указания первичного ключа, надо найти поле, которое будет подходить под все три условия. Если такого поля нет, его надо создать. В идеале, поле должно иметь тип integer.

 Отношения

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

Отношение один-к-одному означает что поле1 соотносится с полем2. Пример — у каждого человека свой номер паспорта. 1 человек- 1 паспорт. Тут отношение один-к-одному.

Один-к-многим указывает, что поле1 может соотносится как с полем2, так и с полем3, полемN… В книге приведён пример — у одного мужчины может быть много женщин и наоборот Автор юморист Один-к-многим самая распространённая связь между таблицами в нормализованных базах.

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

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

Создание структуры базы данных (схема) и отношений между таблицами можно ускорить, использую различные CASE-средства. В конце будет ссылка на mysql workbench, бесплатную кроссплатформенную программу.

На почитать:

• Нормальные формы на вики
• Основные приемы нормализации от мелкомягких. Кстати, это не далеко не первый годный материал, который я нашел у них в knowledge base. Туфту не пишут
• Аскетичный сайт с кучей теории по реляционным субд и реляционной алгебре
• Mysql Workbench

Еще несколько ссылочек.

• Денормализация базы данных
• Нормализация баз данных: третья нормальная форма
• Нормализация баз данных: вторая нормальная форма
• Нормализация баз данных: первая нормальная форма
• urpmi база заблокирована (возможно ее использует другая программа)
• Как лечить database is locked в mandriva linux

Но это не очень красивое решение. Очень запросов много + на переменные память расходуется. Проще использовать вложенные запросы, они же nested queries. Но тут возникает проблемка -  если вставлять  только данные из INSERT, то все ок, но если добавлять еще и какие-нибудь неизменные значения, то выдается Subquery returned more than 1 value.

Чтобы не обьяснять на пальцах — сама таблица B

CREATE TEMPORARY TABLE `B` (
  `status` TINYINT(4) NULL DEFAULT NULL ,
  `pair` TEXT NULL DEFAULT NULL ,
)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

Копируем из таблицы A и вставляем в B так

INSERT INTO `B` (`pair`)
            SELECT `pair` FROM `A` ORDER BY RAND() LIMIT 100;

Если поле status имеет неизменное значение, например 123, то просто вот так уже не вставить

INSERT INTO `B` (`status`, `pair`) VALUES (
            (SELECT `pair` FROM `A` ORDER BY RAND() LIMIT 100),
            123);

Не совсем красивое решение нашел

UPDATE `B` SET `status` = '123' WHERE 1=1;

Но хотелось бы в одну строку все заделать. Отпишитесь плиз в коментах, кто знает, как реализовать.

Еще несколько ссылочек.

• Скрипт для тюнинга MYSQL
• MYSQL RAND(), как получить рандомную строку из БД
• Робкий tar )
• Постинг в вордпресс из скриптов.
• Локали и кодировки.
• Mysql, логируем все запросы
• Резервное копирование MySQL (mysql backup)

Проблем может быть две — первая это включенная директива magic_quotes. Лечится примерно так -

(get_magic_quotes_gpc()) ? stripslashes(unserialize($variable)) : unserialize($variable);

За работу кода не отвечаю, но думаю мысль понятна.

Второй вариант, с которым втух я на несколько часов, это переносы строки. Если массив имеет в себе ячейку, в которой несколько строк, то он нифига не сериализуется взад, а выдаст false. Лечится убиванием \r\n

$str = preg_replace("/\n/", "", $str);
$str = preg_replace("/\r/", "", $str);

Еще несколько ссылочек.

• Класс для работы с сервисами сокращения ссылок
• Простой способ проверки процесса на существование
• Примеры использования strace для отладки скриптов
• Размножение строк — функция генерации всех возможных значений из шаблона {}
• Окончания числительных в php
• Watchdog для своих скриптов или проверка состояния процесса
• Подстановка ссылок в текст в процентном соотношении.
• Количество фолловеров в твиттере числом
• Заливка файлов на радикал PHP
• А php будет в cpp компилится

Сама команда:

find . -type f -name "*.php" -exec wc -l {} +;

Еще несколько ссылочек.

• Простой способ проверки процесса на существование
• Как узнать статус процесса по pid
• Организация бекапа на серверах
• Bash скрипты
• Примеры использования strace для отладки скриптов
• Что делать, если сбил все права?
• Watchdog для своих скриптов или проверка состояния процесса
• Гном сьел моего парня!
• Изменить размер картинки пакетно
• Вышла Mandriva Linux 2012 alpha 2

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.51a-24+lenny5-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated -InnoDB -ISAM -NDBCluster 

[--] Data in MyISAM tables: 5M (Tables: 260)
[OK] Total fragmented tables: 0

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 12h 14m 59s (4M q [33.837 qps], 39K conn, TX: 822M, RX: 460M)
[--] Reads / Writes: 30% / 70%
[--] Total buffers: 57.0M global + 50.5M per thread
(400 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 19.8G (293%
of installed RAM)
[!!] Slow queries: 10%
(443K/4M)
[OK] Highest usage of available connections: 38%
(152/400)
[OK] Key buffer size / total MyISAM indexes:
15.0M/4.7M
[OK] Key buffer hit rate: 99.1% (9M cached
/ 91K reads)
[OK] Query cache efficiency: 75.5% (2M cached /
2M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp
sorts / 439K sorts)
[OK] Temporary tables created on disk: 1% (8K
on disk / 444K total)
[OK] Thread cache hit rate: 99% (307 created /
39K connections)
[!!] Table cache hit rate: 0% (5 open /
11K opened)
[OK] Open file limit used: 0%
(11/65K)
[OK] Table locks acquired immediately: 99%
(2M immediate / 2M
locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    table_cache (> 32562)

Сам скрипт лежит тут: веб, гит

Еще несколько ссылочек.

• Вложенные SQL запросы: одновременное добавление одинарных и множественных строк в INSERT
• MYSQL RAND(), как получить рандомную строку из БД
• Робкий tar )
• Постинг в вордпресс из скриптов.
• Локали и кодировки.
• Mysql, логируем все запросы
• Резервное копирование MySQL (mysql backup)