Описание тега database-design

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

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

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

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

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

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

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

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

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

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

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

Какие вопросы появятся в теге database-design?

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

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

Многие вопросы с меткой "дизайн базы данных" также будут иметь метку "моделирование данных". Эти два предмета во многом пересекаются.

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

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

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

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

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

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

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

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

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

2. Необеспечение ссылочной целостности

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

Этот сбой довольно часто встречается в базах данных MySQL.

Подробнее здесь:

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

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

4. Написание запросов, требующих DISTINCT работать

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

SELECT DISTINCT ...

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

Из раздела "Почему я ненавижу DISTINCT":

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

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. Не упрощайте сложные запросы с помощью представлений.

Не все поставщики баз данных поддерживают представления, но для тех, кто это делает, они могут значительно упростить запросы при разумном использовании. В качестве примера рассмотрим общую модель Party для 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. Плохо реализованные эксклюзивные дуги

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

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

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

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

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

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

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

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

Это может показаться безобидным. В конце концов, AND - это нормально. ИЛИ тоже должно быть хорошо? Неправильно. По сути, условие И ограничивает набор данных, тогда как условие ИЛИ увеличивает его, но не таким образом, чтобы его можно было оптимизировать. В частности, когда различные условия ИЛИ могут пересекаться, что вынуждает оптимизатор эффективно выполнять операцию 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 и т. Д.

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

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

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