Пять простых ошибок проектирования баз данных, которых следует избегать

  1. Обеспечение целостности через приложения
  2. Приложение Хвост виляет Базой Собак:

Большинство из нас в отрасли знают об опасностях плохого проектирования баз данных, но игнорируют их в реальных базах данных. Вредные недостатки дизайна часто остаются незамеченными. В некоторых случаях ограничения СУБД или самого языка SQL могут усугубить проблему. В других случаях это могут быть неопытные разработчики баз данных, которые уделяют больше внимания написанию причудливого кода, но не могут сосредоточиться на создании хорошей модели данных.

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

Рисунок 1

Несколько лет назад Дон Петерсон написал статью для SQL Server Central это детализировало общую практику создания единой справочной таблицы для различных типов данных, обычно называемых таблицей кодов или «таблицей допустимых значений» (AVT). Эти таблицы имеют тенденцию быть массивными и иметь кучу несвязанных данных. Достаточно правильно, Дон назвал эти таблицы таблицами Massively Unified Code-Key (MUCK) (Петерсон, 2006) Хотя многие другие писали об этом на протяжении многих лет, это имя, кажется, наиболее эффективно отражает неуклюжесть, связанную с такой структурой.

Во многих случаях данные в этих таблицах имеют тип VARCHAR (n), хотя реальный тип данных этих значений может быть любым в диапазоне от INTEGER до DATETIME . Они в основном представлены в трех столбцах, которые могут принимать некоторую форму образца таблицы (рисунок 1):

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

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

Рисунки 2-5

Затем он решает объединить их все из-за сходства их столбцов. Он предполагает, что он устраняет избыточные таблицы и упрощает базу данных; у него будет меньше таблиц, он сэкономит пространство, повысит эффективность и т. д. Люди также предполагают, что это снижает сложность требуемого SQL, поскольку для доступа к данным любого типа можно написать одну подпрограмму / хранимую процедуру.

Так что с ним не так?

  • Во-первых, вы теряете средства для обеспечения точных данных; ограничения. Объединяя различные сущности в одну таблицу, вы не имеете декларативных средств для ограничения значений определенной категории. Нет простого способа применить простые ограничения внешнего ключа без добавления categoryid во всех ссылочных ключах.
  • Во-вторых, вы должны представлять каждый тип данных в виде строки с этим типом универсальной таблицы поиска. Такое смешение разных типов может быть проблемой, потому что ограничения проверки не могут быть наложены без серьезного взлома кода. В приведенном нами примере, если код скидки - CHAR (3), а location_nbr - INT (4), каким должен быть тип данных столбца code в таблице Common Lookup?
  • В-третьих, вы привержены жесткости и последующей сложности. Вы можете спросить, как такой простой и гибкий дизайн может быть жестким? Что ж, рассматривая наш пример стандартной схемы таблицы поиска, просто представьте, что таблица «LocationCode» включает в себя еще один столбец, который может быть «регион». Как насчет последствий добавления статуса в таблицу «DiscountType»? Просто для того, чтобы изменить одну категорию, вам нужно рассмотреть возможность освободить все строки в таблице независимо от того, применим ли к ним новый столбец или нет. А как насчет сложности? Часто идея использования общих справочных таблиц исходит из идеи обобщения сущностей, где одна таблица представляет «вещь» - почти все.
    Сравните это с фундаментальным правилом, согласно которому правильно спроектированная таблица представляет собой набор фактов о сущностях или отношениях того же рода. Проблема с обобщением сущностей заключается в том, что таблица становится кучей несвязанных строк: следовательно, вы теряете точность значений, за чем следует путаница и, зачастую, нежелательная сложность.
    Основная цель СУБД состоит в том, чтобы обеспечить соблюдение правил, регулирующих представление и обработку данных. Убедитесь, что вы не путаете термины «обобщение», «повторное использование» и т. Д. В контексте проектирования базы данных в той степени, в которой вы не можете контролировать то, что разрабатывается.
  • Наконец, в-четвертых, вы столкнулись с проблемами физической реализации. Хотя логическое проектирование считается совершенно отдельным от физической реализации, в коммерческих продуктах СУБД, таких как SQL Server, на физические реализации может влиять логическое проектирование и наоборот. На крупных предприятиях такие распространенные таблицы поиска могут увеличиваться до сотен тысяч строк и требуют интенсивной физической настройки базы данных. Проблемы с блокировками и параллелизмом при работе с такими большими таблицами также должны контролироваться. Внутреннее представление определенного набора строк в физическом хранилище может быть определяющим фактором того, насколько эффективно можно получать доступ к значениям и манипулировать ими с помощью SQL-запросов.

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

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

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

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

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

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

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

Рисунок 6

Здесь значения ins_code в таблице PolicyHolders могут быть ограничены двумя способами. Одним из способов может быть использование таблицы поиска, которая содержит допустимые значения для ins_code . Альтернативой является ограничение проверки таблицы PolicyHolders в соответствии с:

ПРОВЕРИТЬ (ins_code IN («IC», «FS», «MC», «PPO», «POS», «HMO»))

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

  1. Если список значений изменяется в течение определенного периода времени, вы должны использовать отдельную таблицу с ограничением внешнего ключа, а не с проверочным ограничением.
  2. Если список значений больше 15 или 20, вам следует рассмотреть отдельную таблицу.
  3. Если список значений является общим или повторно используемым, по крайней мере, три или более раз использовавшимся в одной и той же базе данных, у вас есть очень веские основания использовать отдельную таблицу.

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

В идеале таблица представляет собой набор объектов, каждый из которых имеет набор атрибутов, представленных в виде столбцов. Иногда дизайнеры могут оказаться в мире альтернативных программных «парадигм» и попытаться реализовать их. Одна такая модель называется Entity-Attribute-Value (или в некоторых контекстах как object-attribute-model), которая является псевдонимом для таблицы, имеющей три столбца, один для типа объекта, который она должна представлять, другой для параметр или атрибут или свойство этого объекта и третий для фактического значения этого свойства.

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

Рис 7

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

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

Рис 8

Принимая это до крайности, нет необходимости в дополнительных таблицах - все данные могут быть сведены в одну таблицу! Слава этому изобретению принадлежит так называемым разработчикам «клинических баз данных», которые решили, что когда различные элементы данных неизвестны, частично известны или редки, лучше всего использовать EAV (Надкарни, 2002). Проблема в том, что многие новички соблазняются применять этот подход в базах данных SQL, и результаты обычно хаотичны. Фактически, многие люди предполагают, что это хорошо, что они не знают природу данных!

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

У моделей EAV есть множество проблем.

  • Во-первых, огромное количество данных само по себе практически неуправляемо.
  • Во-вторых, нет никакого возможного способа определить необходимые ограничения - любые потенциальные ограничения проверки должны будут включать обширное жесткое кодирование для соответствующих имен атрибутов. Поскольку в одном столбце содержатся все возможные значения, тип данных обычно VARCHAR (n).
  • В-третьих, даже не думайте о наличии каких-либо полезных внешних ключей.
  • Наконец, есть сложность и неловкость запросов. Некоторые люди считают полезным иметь возможность объединять различные данные в одну таблицу, когда это необходимо - они называют ее «масштабируемой». В действительности, поскольку EAV смешивает данные с метаданными, намного сложнее манипулировать данными даже для простых требований. Рассмотрим простой запрос для поиска сотрудников, родившихся после 1950 года. В традиционной модели вы должны иметь:

ВЫБЕРИТЕ имя, фамилию

ОТ сотрудников

ГДЕ date_of_birth> '12/31/1950';

В модели EAV есть один способ написать сопоставимый запрос:

ВЫБЕРИТЕ МАКС (СЛУЧАЙ emp_property КОГДА 'first_name'

ТО значение

КОНЕЦ) КАК first_name,

MAX (CASE emp_property КОГДА 'last_name'

ТО значение

END) AS last_name

FROM EmployeeValues

ГДЕ emp_nbr IN (ВЫБЕРИТЕ emp_nbr

FROM EmployeeValues

ГДЕ emp_property = 'date_of_birth'

И CAST (значение КАК DATETIME)> '12/31/1950')

И emp_property IN ('имя_первой', 'имя_популярной')

GROUP BY emp_nbr;

Листинг 1

Для тех, кто разбирается в Transact-SQL, добавьте несколько новых столбцов с различными типами данных, попробуйте несколько запросов и посмотрите, насколько это весело!

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

Существует несколько способов, которыми приложение может проникнуть в область управления данными. Я кратко объясню пару способов и предложу некоторые рекомендации о том, как это предотвратить.

Обеспечение целостности через приложения

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

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

Решение простое.

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

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

Как правило, базы данных - это не просто хранилища данных; они являются источником правил, связанных с этими данными. По возможности объявляйте ограничения целостности в базе данных для каждого обязательного правила. Используйте хранимые процедуры и триггеры только тогда, когда декларативное обеспечение целостности с помощью ключей и ограничений невозможно. Только прикладные правила должны быть реализованы через приложение.

Приложение Хвост виляет Базой Собак:

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

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

Давайте просто проясним что-то, прежде чем продолжить: «значение данных» здесь относится к значению атрибута сущности; «элемент данных» относится к единице метаданных, такой как имя столбца или имя таблицы. Неправильно используя значения данных в качестве элементов данных, я имею в виду практику разделения значений атрибутов определенного объекта и представления его в нескольких столбцах или таблицах. Джо Селко называет это именно так - «разделение атрибутов» (Celko, 2005) ,

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

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

Рисунок 9

Здесь вы увидите, что один атрибут в бизнес-модели, «объем продаж», представлен в виде ряда столбцов. Это усложняет жизнь почти каждому, кто использует такую ​​схему.

Теперь, что сделало бы такой дизайн нежелательным?

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

Кстати, многие люди считают это нарушением первой нормальной формы. Это недоразумение, так как здесь нет многозначных столбцов (Паскаль, 2005). Для подробного ознакомления, пожалуйста, обратитесь к простой статье: Факты и заблуждения о первой нормальной форме

Идеальный способ создать эту таблицу - это что-то вроде:

Идеальный способ создать эту таблицу - это что-то вроде:

Рис 10

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

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

1. Используйте запрос UNION:

SELECT sales_person, 'jan' AS "Месяц", jan_sales AS "Продажи"

FROM salesdata

СОЮЗ ВСЕХ

ВЫБЕРИТЕ sales_person, 'feb', feb_sales

FROM salesdata

СОЮЗ ВСЕХ

ВЫБЕРИТЕ sales_person, 'mar', mar_sales

FROM salesdata

СОЮЗ ВСЕХ

ВЫБЕРИТЕ sales_person, 'apr', apr_sales

ОТ salesdata;

2. Используйте JOIN для производной таблицы с именами столбцов:

ВЫБЕРИТЕ sales_person,

м как "месяц",

СЛУЧАЙ m КОГДА 'jan' THEN jan_sales

КОГДА 'ФЕВ', ТО ФЕБ

КОГДА 'МАР', ТО

КОГДА «апр», ТО «апрель»

END AS sales

FROM salesdata

CROSS JOIN (ВЫБЕРИТЕ 'jan' UNION

ВЫБЕРИТЕ 'feb' UNION

ВЫБЕРИТЕ 'mar' UNION

ВЫБЕРИТЕ «апрель») месяцев (м);

3. Используйте UNPIVOT:

SELECT sales_person, "month", sales

FROM (ВЫБЕРИТЕ sales_person,

jan_sales, feb_sales, mar_sales, apr_sales, may_sales

FROM salesdata) s (sales_person, январь, февраль, мар, апрель, май)

UNPIVOT

(продажи за «месяц» IN (январь, февраль, мар, апр, май)) m;

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

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

Рисунок 11

Здесь отдельные значения атрибута «месяц» присваиваются каждой таблице. Этот дизайн имеет сходные недостатки, такие как дублирование ограничений и сложность в выражении простых запросов. Чтобы быть полезными, таблицы должны быть в формате UNION, чтобы сформировать единую таблицу с дополнительным столбцом, представляющим месяц. Было бы проще начать с одной базовой таблицы.

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

Как примечание, эта проблема широко обсуждалась специалистами по реляционным технологиям, особенно в отношении ограничений, которые она накладывает на обновления представлений. Некоторые определили это как прямое нарушение Информационного принципа (реляционного принципа, который требует представления всех данных в базе данных исключительно как значения в таблице) и рекомендовали, чтобы никакие две таблицы в базе данных не имели перекрывающихся значений . Первоначально определенная как Новый принцип проектирования, эта рекомендация для каждой таблицы иметь одно значение или предикат в настоящее время известна как принцип ортогонального проектирования в реляционной литературе. (Date & McGoveran, 1995) ,

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

  1. Celko, J. (2005). Стиль программирования SQL. Сан-Франциско, Калифорния: издательство Morgan Kaufman.
  2. Дата, CJ & McGoveran, D. (1995). Принцип ортогонального дизайна. В CJ Date & D. McGoveran, Реляционные базы данных записей 1991-1994. Аддисон Уэсли.
  3. Надкарни, PM (2002) . Введение в EAV системы. Национальное собрание GCRC. Балтимор, MD.
  4. Паскаль Ф. (2005, март). Что Первая Нормальная Форма действительно означает, а значит нет. Получено 1 октября 2009 г. из базы данных Debunkings: http://www.dbdebunk.com/page/page/629796.htm
  5. Петерсон Д. (2006, 24 марта). Таблица поиска Безумие. Получено 26 сентября 2009 г. из SQL Server Central: http://www.sqlservercentral.com/articles/Advanced/lookuptablemadness/1464/
  6. Teorey, TJ (1994). Моделирование и дизайн базы данных: фундаментальные принципы. Морган Кауфманн.

Но насколько это влияет на дизайн?
В конце концов, это приводит к уменьшению количества таблиц, что делает базу данных проще, не так ли?
Так что с ним не так?
Вы можете спросить, как такой простой и гибкий дизайн может быть жестким?
Как насчет последствий добавления статуса в таблицу «DiscountType»?
А как насчет сложности?
Итак, следует ли вам избегать использования проверочных ограничений?
Так каковы преимущества, которые рекламируются для EAV?
Существуют ли какие-либо хорошие статистические измерения, сравнения и анализы для устранения различий в производительности между теми же правилами, которые применяются СУБД и приложением?
Насколько эффективно приложение может применять правила, связанные с данными?