Хабрахабр

[Перевод] Стандарты проектирования баз данных

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

Если хотите этому научиться, то здесь я вкратце рассказал основы. Эта статья не про нормализацию БД.

Если эти стандарты применялись широко, то вам будет легко пользоваться БД, потому что не придётся изучать и запоминать новые наборы стандартов каждый раз, начиная работу с новой БД.
Если у вас есть рабочая БД, то нужно ответить себе на вопрос: «какие стандарты можно применить для облегчения использования этой базы данных?».

Я постоянно сталкиваюсь с базами, в которых таблицы именованы в стиле CustomerOrders или customer_orders. Какой лучше использовать? Возможно, вы хотите применять уже устоявшийся стандарт, но если вы создаёте новую базу, то для повышения доступности рекомендую использовать_подчёркивания. Фраза «under value» имеет другое значение по сравнению с «undervalue», но с подчёркиванием первая будет всегда under_value, а вторая — undervalue. А при использовании CamelCase мы получим Undervalue и UnderValue, которые идентичны с точки зрения не чувствительного к регистру SQL. Более того, если у вас есть проблемы со зрением и вы постоянно экспериментируете с гарнитурами и кеглем, чтобы выделять слова, то подчёркивание читается гораздо легче.

Наконец, CamelCase труден в прочтении для тех, для кого английский не является родным.
Подводя итог, это не строгая рекомендация, а личное предпочтение.

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

В SQL есть ключевое слово user. У вас есть пользователи — users? constraint — это зарезервированное слово. Вам нужна таблица с ограничениями — constraints? Просто используйте множественную форму существительных, и тогда большинство зарезервированных слов не доставят вам хлопот в SQL. Слово audit
зарезервировано, но вам нужна таблица audit? Даже PostgreSQL, в котором есть прекрасный SQL-парсер, запнулся на таблице user.

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

Я сам грешил этим годами. Однажды работал с клиентом в Париже, и администратор БД на меня пожаловался, когда я дал колонке с идентификаторами название id. Я думал, что он просто педант. Ведь, название колонки customers.id является однозначным, а customers.customer_id — это повтор информации.

А позднее мне пришлось отлаживать вот это:

SELECT thread.* FROM email thread JOIN email selected ON selected.id = thread.id JOIN character recipient ON recipient.id = thread.recipient_id JOIN station_area sa ON sa.id = recipient.id JOIN station st ON st.id = sa.id JOIN star origin ON origin.id = thread.id JOIN star destination ON destination.id = st.id
LEFT JOIN route ON ( route.from_id = origin.id AND route.to_id = destination.id ) WHERE selected.id = ? AND ( thread.sender_id = ? OR ( thread.recipient_id = ? AND ( origin.id = destination.id OR ( route.distance IS NOT NULL AND now() >= thread.datesent + ( route.distance * interval '30 seconds' ) ))))
ORDER BY datesent ASC, thread.parent_id ASC

Замечаете проблему? Если бы SQL использовал полные имена id, вроде email_id, star_id или station_id, то баги сразу вылезали бы по мере того, как я писал этот код, а не позже, когда я пытался понять, что я сделал не так.

Позднее скажете спасибо. Сделайте себе одолжение и используйте для ID полные названия.

Давайте колонкам как можно более описательные названия. Скажем, колонка temperature никак не связана с этим:

SELECT name, 'too cold' FROM areas WHERE temperature < 32;

Я живу во Франции, и для нас температура в 32 градуса будет «слишком жарко». Поэтому лучше назвать колонку fahrenheit.

SELECT name, 'too cold' FROM areas WHERE fahrenheit < 32;

Теперь всё совершенно ясно.

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

SELECT * FROM some_table s JOIN some_other_table o ON o.owner = s.person_id;

C этим кодом действительно всё в порядке. Но когда вы посмотрите определение таблицы, то увидите, что у some_other_table.owner есть ограничение по внешнему ключу с companies.company_id. Так что, по сути, этот SQL ошибочен. Нужно было использовать идентичные имена:

SELECT * FROM some_table s JOIN some_other_table o ON o.company_id = s.person_id;

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

Если у вас есть таблица с исходным складом и конечным, то вы можете захотеть сравнить source_id с destination_id с warehouse_id. Однако хочу отметить, что так не всегда можно сделать. В таком случае лучше дать названия source_warehouse_id и destination_warehouse_id.

Если вам кажется, что это приведёт к путанице, можете назвать колонку owning_company_id. Также отмечу, что в приведённом примере owner будет лучше описывать назначение, чем company_id. Тогда название подскажет вам назначение колонки.

Этот совет известен многим опытным разработчикам баз данных, но, к сожалению, говорят о нём недостаточно часто: без уважительной причины не допускайте наличия в БД NULL-значений.
Это важная, но достаточно сложная тема. Сначала обсудим теорию, затем — её влияние на архитектуру БД, и в заключение разберём практический пример серьёзных проблем, вызванных наличием NULL-значений.

Типы баз данных

В базе могут быть данные разных типов: INTEGER, JSON, DATETIME и т. д. Тип ассоциирован с колонкой и любое добавленное в неё значение должно соответствовать этому типу.

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

CustomerAccount.java:5: error: bad operand types for binary operator '>' if ( current > threshhold ) { ^ first type: String second type: int

Даже если вы не замечаете, что current > threshhold сравнивает не сравнимые типы, компилятор это выловит за вас.

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

SELECT name, birthdate FROM customers WHERE customer_id > weight;

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

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

SQL-стандарт правильно реализовал их только в одном месте, в предикатах IS NULL и IS NOT NULL. И тут на сцене появляются NULL-значения. И поэтому существуют IS NULL и IS NOT NULL вместо = NULL и != NULL. Поскольку NULL-значение по определению неизвестно, у вас не может быть разработанных для него операторов. А любое сравнение NULL-значений приводит к появлению нового NULL-значения.

Если для вас это звучит странно, то станет куда проще, если вы напишете «unknown» вместо NULL:

Сравнение NULL неизвестных значений приводит к появлению NULL неизвестных значений.

Ага, теперь понятно!

Что означает NULL-значение?

Вооружившись крохами теории, рассмотрим её практические следствия.

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

SELECT employee_number, name FROM employees WHERE salary > 50000;

И вас только что уволили, потому что ваш начальник заработал больше $50 тыс., но его зарплата отсутствует в БД (в колонке employees.salary стоит NULL), а оператор сравнения не может сравнивать NULL с 50 000.

Может быть, зарплата конфиденциальна. А почему в этой колонке есть NULL? Может быть, это консультант и не получает зарплату. Может быть, информация ещё не поступила. Есть много причин, почему данные могут отсутствовать. Может быть, у него почасовая оплата, а не зарплата.

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

И да, это был глупый пример, но он стал последней каплей.

NULL-значения приводят к логически невозможным ситуациям

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

В той ситуации информация однозначно должна была возвращаться, но этого не происходило. Несколько лет назад я работал в Лондоне на регистратора доменов и пытался понять, почему 80-строчный SQL-запрос возвращает некорректные данные. Стыдно признать, но у меня ушёл день на то, чтобы понять, причиной была такая комбинация условий:

  • Я использовал OUTER JOIN.
  • Они легко могли генерировать NULL-значения.
  • NULL-значения могут привести к тому, что SQL даст некорректный ответ.

Многие разработчики не знают о последнем аспекте, поэтому давайте обратимся к примеру из книги Database In Depth. Простая схема из двух таблиц:

suppliers

parts
Трудно подобрать более простой пример.

Этот код возвращает p1.

SELECT part_id FROM parts;

А что сделает этот код?

SELECT part_id FROM parts WHERE city = city;

Он ничего не вернёт, потому что нельзя сравнивать NULL-значение, даже с другим NULL или тем же самым NULL. Это выглядит странно, потому что город в каждой строке должен быть одним и тем же, даже если мы его не знаем, правильно? Тогда что вернёт следующий код? Попробуйте это понять, прежде чем читать дальше.

SELECT s.supplier_id, p.part_id FROM suppliers s, parts p WHERE p.city <> s.city OR p.city <> 'Paris';

Мы не получили в ответ строки, потому что не можем сравнивать город NULL (p.city), и поэтому ни одна из веток условия WHERE не приведёт к true.

Если это Париж, то первое условие будет истинным (<> 'London'). Однако мы знаем, что неизвестный город либо Париж, либо не Париж. Таким образом, условие WHERE должно быть true, но оно им не является, и в результате SQL генерирует логически невозможный результат. Если это не Париж, то истинным будет второе условие (<> 'Paris').

Каждый раз, когда вы пишете SQL, который может генерировать или содержать NULL-значения, вы рискуете получить ложный результат. Это был баг, с которым я столкнулся в Лондоне. Такое бывает нечасто, но очень трудно выявляется.

  • Используйте имена_с_подчёркиванием вместо CamelCase.
  • Имена таблиц должны быть во множественном числе.
  • Давайте расширенные названия для полей с идентификаторами (item_id вместо id).
  • Избегайте неоднозначных названий колонок.
  • По мере возможности именуйте колонки с внешними ключами так же, как колонки, на которые они ссылаются.
  • По мере возможности добавляйте NOT NULL во все определения колонок.
  • По мере возможности избегайте написания SQL, который может генерировать NULL-значения.

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

Показать больше

Похожие публикации

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Кнопка «Наверх»