Когда денормализовать дизайн базы данных

Question

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

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

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

1) С временными данными. Например, создается счет, который ссылается на продукт. Если клиент запрашивает копию этого счета год спустя, мы должны иметь возможность предоставить точную копию оригинала. Что если цена, название или описание товара были обновлены? Старший парень предложил скопировать цену и другую информацию о продукте в таблицу счетов. Я думаю, может быть, у нас должна быть другая таблица, например productPrice, в которой есть поле даты, чтобы мы могли отслеживать изменения цены с течением времени. Нам нужно то же самое для описания продукта и названия, я думаю? Кажется сложным. Как вы думаете?

2) База данных представляет собой систему учета. Я не очень знаком с бухгалтерским учетом. На данный момент некоторые сводные данные получены и сохранены в базе данных. Например, общий объем продаж за год. Мой старший сотрудник сказал, что бухгалтеры любят проверять правильность вещей, сравнивая это значение с данными, которые фактически рассчитываются по счетам и т. Д., Чтобы дать им уверенность в том, что приложение работает правильно. Он сказал, что в данный момент, например, мы можем сказать, что кто-то по ошибке удалил счет за прошлый год, потому что итоговые суммы не будут одинаковыми. Он также указал, что вычисление этих итогов может быть довольно медленным. Конечно, я сказал, что данные не должны дублироваться и всегда должны быть рассчитаны при необходимости. Я предположил, что мы могли бы использовать SQL Reporting Services или другое решение, которое будет генерировать эти отчеты в одночасье и кэшировать их. Во всяком случае, он не убежден. Есть комментарии по этому поводу?

Спасибо большое:)
ура
отметка

РЕДАКТИРОВАТЬ

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

Answer 1

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

Ваши номера:

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

   • почему нет бумажной копии счета? В большинстве стран, которые были бы юридическим и налоговым требованием, какова сложность вылова старого счета?
   • если в базе данных есть требование хранить закрытые счета, то, конечно же, как только счет будет закрыт, вам понадобится метод сбора этой информации.
   • ProductPrice (на самом деле, я бы назвал это ProductDate) хорошая идея, но может и не понадобиться. Но вы правы, вам нужно оценить валюту данных в полном контексте всей базы данных.
   • Я не вижу, как поможет копирование цены продукта в таблицу счетов-фактур (не много ли позиций?)
   • в современных базах данных, где требуется отрыгивание копии счета, закрытый счет дополнительно хранится в другой форме, например, в формате XML. Один клиент сохраняет PDF как BLOB. Таким образом, нет никакого возни с тем, что цена продукта была пять лет назад. Но основные данные счета-фактуры являются оперативными и текущими, даже для закрытых счетов; Вы просто не можете пересчитать древний счет, используя текущие цены.
   • некоторые люди используют таблицу archive_invoice, но у нее есть проблемы, потому что теперь каждый сегмент кода или инструмент пользовательских отчетов должен выглядеть в двух местах (обратите внимание, что в наши дни некоторые пользователи понимают базы данных лучше, чем большинство разработчиков)
   • Во всяком случае, это все обсуждение, для вашего понимания. Ни одна из баз данных, которые я написал за 30 лет, никогда не сталкивалась с подобными проблемами, и все они соответствуют юридическим и налоговым требованиям.
     
     • База данных служит текущим и архивным целям из одного набора таблиц (без "архивных" таблиц).
     • После того, как Счет создан, он является юридическим документом и не может быть изменен или удален (его можно отменить или частично пополнить новым Счетом с отрицательными значениями). Они отмечены IsIssued/IsPaid/Etc
     • Products не могут быть удалены, они могут быть отмечены IsObsolete
     • Есть отдельные таблицы для InvoiceHeader и InvoiceItem
     • InvoiceItem имеет FKs для обоих InvoiceHeader а также Product
     • по многим причинам (не только тем, что вы упомянули) строка InvoiceItem содержит NumUnits; ProductPrice; TaxAmount; ExtendedPrice, Конечно, это выглядит как "денормализация", но это не так, потому что цены, ставки налогообложения и т. Д. Могут быть изменены. Но что более важно, юридическое требование состоит в том, что мы можем воспроизвести старый счет по требованию.
     • (где это может быть воспроизведено из бумажных файлов, это не требуется)
     • InvoiceTotalAmount это производный столбец, просто SUM() InvoiceItems
       ,

2. Это мусор. Бухгалтерские системы и бухгалтеры не "работают" таким образом.

   • Если это настоящая система учета, то она будет иметь JournalEntries или "двойную запись"; это то, что квалифицированная учетная запись требуется использовать (по закону).

     • Двойная запись не означает повторную запись; это означает, что каждая финансовая операция (одна сумма) должна иметь исходный и целевой счета, к которым она применяется; поэтому нет "денормализации" или дублирования. В банковской базе данных, поскольку финансовые транзакции выполняются с отдельными счетами, это обычно представляется как две отдельные финансовые транзакции (строки) в одной транзакции базы данных. Обычные коммерческие ограничения базы данных используются для обеспечения двух "сторон" каждой финансовой транзакции.
       ,

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

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

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

   • Сводные таблицы вполне нормальны, чтобы сэкономить время и вычислительную мощность, для пересчета информации, которая не изменяется, например: итоги с начала года для каждого года, но в этом году; MTD итоги за каждый месяц в этом году, но не в этом месяце. "Всегда пересчитывать" данные немного глупо, когда (а) информация очень велика и (б) не меняется. Рассчитать только за текущий месяц

     • В банковских системах (миллионы сделок в день) в EndOfDay мы также рассчитываем и храним ежедневную сумму. Они перезаписываются в течение последних пяти дней, потому что Аудиторы вносят изменения, и JournalEntries против финансовых транзакций за последние 5 дней допускаются.
     • Небанковские системы обычно не нуждаются в ежедневных итогах
       ,

   • Сводные таблицы не являются "денормализацией" (за исключением тех, кто только что узнал о "нормализации" из своего волшебного, постоянно меняющегося "источника" жидкости) или как непрофессионалы, которые применяют простые черно-белые правила ко всему). Опять же, определение здесь не обсуждается; это просто не относится к сводным таблицам.

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

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

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

Answer 2

1) Это архив. Все, что в нем, никогда не должно обновляться. Я пошел бы с предложением старшего парня и сделал бы эту таблицу счета-фактуры автономной. Возможно использовать блоб для самого счета, который содержит язык разметки?

2) Службы отчетов, складская таблица, которая обновляется триггером, что-то, что вы создаете скриптом всякий раз, когда... все это будет хорошо, я думаю Это действительно идеально для нормализации, но это не всегда быстро. У меня есть база данных здравоохранения хорошего размера, которой я управляю, которая полностью нормализована... и затем имеет ряд ненормализованных таблиц со свернутыми уравнениями и обычно полученными полями. Почти все запускается из этого ненормализованного набора - просто быстрее добавлять к ним триггер при загрузке файлов, чем постоянно извлекать из разных таблиц каждый раз, когда я хочу просмотреть отчет о 100000 записей.

Answer 3

Вы поднимаете действительные баллы, однако вы не совсем ясно о нормализации и что это значит, например, в

1) Заявление о том, что хранение счетов в том виде, в котором они были денормализованы, полностью и полностью неверно. Возьмем, к примеру, цену - если у вас есть бизнес-требование, в котором говорится, что вы должны вести историю цен, то сохранение только текущей цены является неправильным, и это нарушает требования. И это не имеет ничего общего с нормализацией, оно просто не разработано хорошо. Денормализация - это введение в вашу модель (и другие артефакты) возможностей для неоднозначности - и в этом случае вы просто не моделируете свое проблемное пространство должным образом.
Нет ничего плохого в моделировании вашей базы данных для поддержки временных данных (или управления версиями и / или разделения областей базы данных на архивные / временные и рабочий набор).

Глядя на нормализацию, не глядя на семантику (с точки зрения требований), невозможно.

Кроме того, если ваш старший разработчик не видит разницы, то, я думаю, он не получил своего стажа в разработке RDBMS;)

2) Вторая часть действительно денормализация. Однако, если вы когда-нибудь встретите старшего аналитика по БД, который серьезно проповедует нормализацию, вы услышите, как он / она скажет, что вполне допустимо денормализовать, если вы делаете это сознательно и гарантируете, что выигрывают недостатки избыточного веса и что аномалии не будут вас кусать. Они также скажут вам нормализовать логическую модель и то, что в физической модели вам разрешено отклоняться от идеала для различных целей (производительность, обслуживание и т. Д.). В моей книге основная цель нормализации состоит в том, чтобы у вас не было скрытых аномалий (см. Эту статью на 5NF, например)

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

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

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

РЕДАКТИРОВАТЬ: Термин "денормализованный" в (2) не является правильным, если вы посмотрите на формальное определение нормальных форм и если вы считаете, что дизайн денормализован, если он нарушает любую из нормальных форм (для некоторых людей это очевидно, и нет Другой способ об этом).

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

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

Большая часть теории проектирования связана с сокращением избыточности; нормализация уменьшает избыточность в релварах, ортогональность уменьшает ее по релварам.

подробно из базы данных: теория отношений для практиков

и на следующей странице

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

Таким образом, правильный термин для избыточности между relvars - это ортогональность (в основном все нормальные формы говорят об одном relvar, поэтому, если вы строго посмотрите на нормализацию, она никогда не предложит каких-либо улучшений из-за зависимостей между двумя различными relvars).

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

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

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

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

Кроме того, нормализация всегда зависит от семантики и бизнес-правил, которые вы пытаетесь смоделировать. Например, DBAPerformance приводит пример, в котором хранится TaxAmount в таблице транзакций нет денормализованного дизайна, но он не упоминает, что это зависит от того, какую систему вы пытаетесь смоделировать (это очевидно?); например, если транзакция имеет другой атрибут с именем TaxRate обычно он будет денормализован, поскольку существует функциональная зависимость от набора неключевых атрибутов (TaxAmount = Amount * TaxRate => FD: Amount,TaxRate -> TaxAmount), и один из них должен быть удален или гарантированно согласован.

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

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

Answer 4

Я согласен с вашим старшим по поводу (1). Строка таблицы транзакций должна охватывать все состояние на момент транзакции. Период. То, что вы предлагаете, не записывает фактические данные, поэтому это недопустимо. Я также согласен с (2). Все, что хочет бизнес путем перекрестной проверки, вы должны реализовать. Бухгалтерский учет основан на перекрестной проверке, двойной записи, свернутых бухгалтерских книгах и т. Д. Вы должны это сделать. Это настолько фундаментально, что вы даже не должны воспринимать это как денормализацию, просто как реализацию бизнес-требований.

Answer 5

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

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

Answer 6

Ваш старший разработчик делает чрезвычайно важные замечания. Я сам усвоил эти трудности, обслуживая системы, которые не нормализуют исторические данные.

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

С точки зрения общего количества в базе данных. Это спасло мою задницу раньше, когда я внес изменение в приложение, из-за которого числа не складывались одинаково (не так сложно, как вы думаете). В живом приложении итоги дали мне определенное место, чтобы вернуться, чтобы исправить расхождения. Я уже писал об этом раньше, вы можете прочитать это здесь: http://jlrand.com/?p=95

Answer 7

Для № 1

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

Для № 2

Запись системы бухгалтерского учета в БД с нуля - это большой проект. Убедитесь, что бухгалтеры предоставляют вам бизнес-правила, чтобы вы могли измерить точность своих систем. Последнее, что вам нужно, это вступление финансового директора в собрание DBA и объявление о том, что DB чрезмерно заряжает клиента, еще хуже то, что вы недооцениваете и выводите компанию из бизнеса.

Если у вас есть SQL Server, взгляните на базу данных Adventure Works. Если вы ненавидите MS, тогда посмотрите на Adventure Works и не делайте так.

Answer 8

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

Answer 9

Нормализация базы данных удаляет дубликаты и делает SQL-запросы для обновления данных более эффективными (и дает некоторые другие улучшения).

Но если большинство ваших запросов используются для выбора данных, а запросы выбора соединяются с несколькими таблицами одновременно, вы можете рассмотреть возможность денормализации этих таблиц. Это увеличит объем дискового пространства, необходимого для данных, время выполнения SQL-запросов на обновление, но улучшит отдельные запросы.