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

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

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

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

CamelCase имён или с_подчёркиванием?

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

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

Множественное или единственное число в именах таблиц?

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

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

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

Не называйте колонку с ID как «id»

Я сам грешил этим годами. Однажды работал с клиентом в Париже, и администратор БД на меня пожаловался, когда я дал колонке с идентификаторами название 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.

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

Избегайте значений NULL

Этот совет известен многим опытным разработчикам баз данных, но, к сожалению, говорят о нём недостаточно часто: без уважительной причины не допускайте наличия в БД 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-м. В те годы компьютеры были медленными, и всё, что усложняло реализацию, несомненно замедляло и базы данных.

И тут на сцене появляются NULL-значения. SQL-стандарт правильно реализовал их только в одном месте, в предикатах IS NULL и IS NOT NULL. Поскольку NULL-значение по определению неизвестно, у вас не может быть разработанных для него операторов. И поэтому существуют IS NULL и IS NOT 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'). Если это не Париж, то истинным будет второе условие (<> 'Paris'). Таким образом, условие WHERE должно быть true, но оно им не является, и в результате SQL генерирует логически невозможный результат.

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

Итог

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

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

Автор: AloneCoder

Источник