Проектирование базы данных - это процесс определения структуры и, следовательно, логических аспектов базы данных. Целью проектирования базы данных является представление некоторой «вселенной дискурса» - типов фактов, бизнес-правил и других требований, которые база данных предназначена для моделирования.

Результатом дизайна базы данныхявляется план построения базы данных как модели вселенной дискурса («бизнес»). домен "или предметная область, о которой информация будет занесена в базу данных).

Большинство баз данных, которые собирают и управляют полупостоянными данными, работают под управлением системы управления базами данных (СУБД). Выдающимися продуктами СУБД являются Microsoft SQL Server, Oracle СУБД и IBM DB2. Есть десятки других. Многие вопросы и ответы, которые вы найдете под этим тегом, относятся к одному из этих продуктов СУБД, но некоторые проблемы проектирования не зависят от СУБД.

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

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

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

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

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

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

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

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


Какие вопросы появятся в теге «дизайн базы данных»?

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

Большинство вопросов касаются реляционных баз данных, включая базы данных SQL, которые обычно называют реляционными. Несколько вопросов касаются «действительно реляционных» баз данных или «нереляционных» или «постреляционных» баз данных. Некоторые о полуструктурированных или неструктурированных данных.

Многие вопросы, помеченные как «дизайн базы данных», также будут помечены как «моделирование данных». Существует огромное совпадение между этими двумя предметами.

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

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

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

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

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

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


Каковы распространенные ошибки при разработке баз данных?

1. Не использовать соответствующие индексы

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

2. Не обеспечивает ссылочную целостность

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

Довольно часто можно увидеть этот сбой в базах данных MySQL.

Больше здесь:

3. Отсутствующие или неправильно выбранные ключи

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

4. Написание запросов, которые требуют DISTINCT для работы

Вы часто видите это в ORM-сгенерированных запросах. Посмотрите на вывод журнала из Hibernate, и вы увидите, что все запросы начинаются с:

SELECT DISTINCT ...

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

Почему я ненавижу DISTINCT:

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

5. Предпочтение агрегации над объединениями

Другая распространенная ошибка - не понимать, насколько более дорогая агрегация (то есть предложение GROUP BY) может быть сравнена с объединениями.

Например:

Из оператора SQL - "присоединиться" к группе "с помощью":

Первый запрос:

SELECT userid
FROM userrole
WHERE roleid IN (1, 2, 3)
GROUP by userid
HAVING COUNT(1) = 3

Время запроса: 0,312 с

Второй запрос:

SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid AND t2.roleid = 2
JOIN userrole t3 ON t2.userid = t3.userid AND t3.roleid = 3
AND t1.roleid = 1

Время запроса: 0,016 с

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

6. Не упрощать сложные запросы через представления

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

  • Партия: люди и организации;
  • Партийная роль: что делали эти партии, например, Сотрудник и Работодатель;
  • Партийно-ролевые отношения: как эти роли связаны друг с другом.

Примере:

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

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

CREATE VIEW vw_employee AS
SELECT p.title, p.given_names, p.surname, p.date_of_birth, p2.party_name employer_name
FROM person p
JOIN party py ON py.id = p.id
JOIN party_role child ON p.id = child.party_id
JOIN party_role_relationship prr ON child.id = prr.child_id AND prr.type = 'EMPLOYMENT'
JOIN party_role parent ON parent.id = prr.parent_id = parent.id
JOIN party p2 ON parent.party_id = p2.id

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

7. Не дезинфицирующий ввод

Это огромный. Если вы не знаете, что делаете, очень легко создать сайты, уязвимые для атак. Ничто не суммирует это лучше, чем история маленьких столов Бобби.

Данные, предоставленные пользователем посредством URL-адресов, данных формы и файлов cookie, всегда должны рассматриваться как враждебные и очищаться. Убедитесь, что вы получаете то, что ожидаете.

8. Не использовать готовые заявления

Подготовленные операторы - это когда вы компилируете запрос за вычетом данных, используемых во вставках, обновлениях и предложениях WHERE, а затем предоставляете их позже. Например:

SELECT * FROM users WHERE username = 'bob'

Против

SELECT * FROM users WHERE username = ?

Или

SELECT * FROM users WHERE username = :username

В зависимости от вашей платформы.

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

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

Подготовленные операторы также лучше защитят вас от SQL-инъекцийатак.

9. Не достаточно нормализуется

Нормализация базы данных- это процесс оптимизации структуры базы данных или того, как вы организуете свои данные в таблицы.

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

Это можно найти в Лучшем способе хранения списка идентификаторов пользователей:

Я видел в других системах, что список хранится в сериализованном массиве PHP.

Но отсутствие нормализации проявляется во многих формах.

Больше:

10. Нормализация слишком много

Это может показаться противоречием предыдущему пункту, но нормализация, как и многие другие, является инструментом. Это средство для достижения цели, а не самоцель. Многие разработчики забывают об этом и начинают воспринимать «средство» как «конец». Модульное тестированиеявляется ярким примером этого.

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

Больше:

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

11. Плохо реализованные эксклюзивные дуги

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

12. Не проводить анализ производительности по запросам вообще

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

13. Чрезмерная зависимость от конструкций UNION ALL и особенно UNION

Термины UNION в SQL просто объединяют конгруэнтные наборы данных, что означает, что они имеют одинаковый тип и количество столбцов. Разница между ними заключается в том, что UNION ALL представляет собой простую конкатенацию и должна быть предпочтительнее, когда это возможно, тогда как UNION будет неявно делать DISTINCT для удаления дублирующихся кортежей.

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

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

14. Использование условий OR в запросах

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

Плохо:

... WHERE a = 2 OR a = 5 OR a = 11

Лучше:

... WHERE a IN (2, 5, 11)

Теперь ваш оптимизатор SQL может эффективно превратить первый запрос во второй. Но это не так. Просто не делай этого.

15. Не разрабатывать свою модель данных для использования в высокопроизводительных решениях.

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

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

Преждевременная оптимизация - корень всего зла

16. Неправильное использование транзакций базы данных

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

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

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

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

Также обратите особое внимание на тонкости взаимодействия между уровнем соединения с вашей базой данных и ядром базы данных.

17. Непонимание парадигмы на основе множеств

Язык SQL следует определенной парадигме, подходящей для конкретных задач. Несмотря на различные специфичные для поставщика расширения, язык пытается справиться с проблемами, тривиальными в таких языках, как Java, C #, Delphi, и т.п.

Это отсутствие понимания проявляется в нескольких отношениях.

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

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