company_banner

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

Автор оригинала: Curtis Poe
  • Перевод

Переходя от проекта к проекту, мы сталкиваемся, к сожалению, с отсутствием единообразных стандартов проектирования баз данных, несмотря на то, что 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 > threshold ) {
                   ^
  first type:  String
  second type: int

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

По иронии, базы данных, которые хранят ваши данные — и являются вашей последней линией обороны от повреждения данных — ужасно работают с типами! Просто отвратительно. Например, если в вашей таблице 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
supplier_id
city
s1
London

parts

part_id
city
p1
NULL

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

Этот код возвращает 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-значения.

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

Комментарии 52

    +13
    Замечаете проблему?

    Да на этот листинг кода у меня только один вопрос: что ты такое? ©

    Таблица character джойнится к таблице station по id.
    Если бы id было иначе, то был бы такой же сущий маразм character.character_id = station.station_id

    Когда главный идентификатор называется id, то видя джойн вида t1.entity_id = t2.id
    сразу видно что это N-к-1 соединение. Причём видно это вообще ничего не зная о том что там описывается. А соответственно обратная схема t1.id = t2.entity_id мне сразу говорит что здесь возможно размножение строк.

    А вот t1.entity2_id = t2.entity2_id это вот ещё неизвестно. Вы можете сказать — «используйте алиасы подлиннеее, чтобы было понятно что t2 это и есть entity2, но entity2 может быть в пару десятков символов длиной и смысл алиаса будет потерян.

      +13
      1. Про ID хотелось бы заметить, что если писать нормальные алиасы, то нет необходимости дублировать информацию в названии ключа. Более того — замучаешься эти ключи постоянно писать.
      2. NULL — имеет свои особенности и их надо знать. То что с ними кто то не умеет работать — совсем не повод от них избавляться.
      Поводом служила бы сложная оптимизация моментов связанных с NULL, особенности хранения NULL на дисках или реальные ошибки логики которые придётся с трудом обходить.
      Точно так же можно сказать что надо избегать неявного приведения типов, скобочек, дат, битовых типов и прочего-прочего — при использовании их неподготовленными людьми может произойти трагедия!
      3. NOT NULL в определении колонок нужен там где он нужен, а не где только можно.
      4.По поводу ключей — мне понравилась идея, так же, начинать наименования ключей с pk/fk/bk соответственно, что бы знать, что это не просто поле и что на него наложены ограничения и какие.
      5. Не раскрыта тема использования схем и префиксов в названиях (функция? процедура? таблица? вьюха?). Надо? Не надо? Почему?
      6. Т.е. в названиях не должно быть заглавных букв?

      Тема статьи хорошая, но дюже холиварная. =)
        0
        > 3. NOT NULL в определении колонок нужен там где он нужен, а не где только можно.

        Я для себя определяю так: в отсутствии явных потребностей и/или вводных всегда используй NOT NULL, иначе попробуй найти хотя бы 2 причины почему это поле _должно_ быть nullable и если не найдешь, используй NOT NULL.

        Остальные случаи использования DEFAULT NULL произрастают из моей собственной лени…
          0
          Обычно достаточно причины: этих данных может не быть. По большей части справочной информации такое возможно.
        +1
        COALESCE разве не для null значений?
          0
          Для них, для них… просто проблема в том, чтобы правильно найти все места, где её нужно применить.
          +2
          Почему CamelStyle по имени, а snake_style — нет?
            0

            Всегда в полях с возможностью нахождения NULL использую функцию NVL, очень удобно. Хотелось бы более расширенный вариант статьи, как написано выше. Про id тоже была засаув своё время.

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

              С таким же успехом можно сказать, что проблема — от «кривых» и «лишних» алиасов. Будь они типа

                JOIN star star_origin         ON star_origin.id = email_thread.id
                JOIN star star_destination    ON star_destination.id = station.id

              проблемы тоже не было бы. Но зато в этом случае было бы чётко видно, где первичный, а где не очень.
                +1

                Я для имен таблиц использую множественное число (users, contacts и т.п.), в алиасах использую единственное число, при этом до одной буквы стараюсь не сокращать (не u, а user; не c, а contact).


                Поэтому по мне вполне нормально использовать Id, а не TABLE_ID:


                SELECT
                     Contact.Id
                   , Contact.Name
                FROM dbo.Contacts as Contact
                INNER JOIN dbo.Users as User ON Contact.UserId = User.Id
                WHERE 1 = 1
                AND User.Id = 123
                  +7

                  ИМХО, не важно как называть, главное одинаково для всей БД, т.е. должен быть определено "соглашение об именовании". Если всё в разнобой, конечно, кровь из глаз потечёт)

                    0

                    В статью прямо просится обзор SQL-форматтеров. Например, я бы с удовольствием почитал бы чем народ пользуется, чтобы иметь общее представление (на работе куплен SQL complete, он Crtl+K, Ctrl+D прекрасно форматит (и можно подкрутить в опциях), поэтому особо нет необходимости в выходе из зоны комфорта, а вот любопытство есть)

                      0
                      SQL Workbench/J — www.sql-workbench.eu. Ну или JetBrains IDEA Ultimate / DataGrip, хотя лично мне они нравятся меньше, и платные к тому же.
                        0
                        А чем нравятся меньше, можете рассказать?)
                          0
                          Тут статью писать надо… Вкратце чем мне WB нравится больше
                          • когда SQLWB/J уже был и давно – у JetBrains даже похожего ничего не было. И я к нему привык уже.
                          • подсветка PK / FK в местном аналоге IntelliSense, как и возможность настроить режимы вставки – т.е. прям в DropDown колонок таблицы можно выбрать несколько и вставить одним движением, и в зависимости от настроек – отсортировано по алфавиту или позиции в табличке
                          • JOIN completion по Alt-J
                          • реформат кода, какой нужен мне (настройки)
                          • подсветка в тексте выбранного вхождения – вроде как в FF «Highlight all», только по мере выделения, удобно смотреть, где ещё есть то же поле
                          • выполнение текущего выражения по <Ctrl-Enter>, всех, начиная с текущего, от начала и до текущего
                          • выполнение SQL в виде Prepared Statement (со значками “?”)
                          • конвертация SQL statement в Java snippet и обратно
                          • возможность задать значение переменной и использовать его ниже по скрипту
                          • превосходный менеджер подключений, и возможность иметь открытые вкладки (восстанавливать) в соответствии с выбранным рабочим пространством. Особенно удобно, если есть пяток проектов с разными СУБД, к которым нужно время от времени возвращаться)
                          • авто-генерация Insert и всего остального
                          • настройка визуального отображения NULL в результатах запросов (и не только NULL)
                          • автоматическое обновление результатов выполнения запроса
                          • regexp фильтрация для объектов БД
                          • копирование данных в буфер обмена в читаемом виде (и с выбором того, что и как копировать, включая моноширинный шрифт и соответствующее выравнивание)
                          • глобальный поиск текста в БД и удобное представление – где и что нашлось
                          • макросы
                          • возможность включения SQL файлов друг в друга
                          • импорт БД в Excel, правка и заливка обратно (сильно помогает на тестах)
                          • экспорт во что угодно
                          • удаление с учётом ограничений
                          • сравнение баз данных
                          • копирование данных из одной БД в другую
                          • удобная навигация по зависимостям объектов
                          • GUI и терминальный клиенты
                          • возможность запуска скриптов в batch режиме
                          • ReadOnly режим

                            0
                            Спасибо за комент! Отвечу по пунктам.

                            • подсветка PK / FK в местном аналоге IntelliSense, как и возможность настроить режимы вставки – т.е. прям в DropDown колонок таблицы можно выбрать несколько и вставить одним движением, и в зависимости от настроек – отсортировано по алфавиту или позиции в табличке

                            Это немного непонятно, что значит подсветка в PK / FK IntelliSense? Gпо поводу колонок: в DataGrip можно вставить сразу все (это предлагает автодополнение или можно раскрыть звездочку с учетом всех джоинов). Если нужны только нужные, можно драгндропнуть из дерева (даже запятые проставятся). Выбора нужных ИЗ автодополнения нет.

                            • JOIN completion по Alt-J

                            JOIN completion есть, он автоматический. Понимает форин ключи, просто совпадающие имена или виртуальные связи, которые вы настроите заранее.

                            • реформат кода, какой нужен мне (настройки)

                            Думаю, это тоже есть — несколько версий назад мы добавили очень много настроек.

                            • подсветка в тексте выбранного вхождения – вроде как в FF «Highlight all», только по мере выделения, удобно смотреть, где ещё есть то же поле

                            Это есть и всегда было, если я вас правильно понимаю.

                            • выполнение текущего выражения по <Ctrl-Enter>, всех, начиная с текущего, от начала и до текущего

                            Это есть.

                            • выполнение SQL в виде Prepared Statement (со значками “?”)

                            Это есть.

                            • конвертация SQL statement в Java snippet и обратно

                            Это непонятно, можете раскрыть?)

                            • возможность задать значение переменной и использовать его ниже по скрипту

                            Есть поддержка кастомных переменных.

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

                            В DataGRip есть и проекты и восстановление вообще всего всего, что вы закрыли (кстати еще восстановление всего что в когда-либо напечатали)

                            • авто-генерация Insert и всего остального

                            Это есть.

                            • настройка визуального отображения NULL в результатах запросов (и не только NULL)

                            Это непонятно :)

                            • автоматическое обновление результатов выполнения запроса

                            Этого нет. Имеется в виду, что запрос бомбится постоянно с определенной периодичностью?

                            • regexp фильтрация для объектов БД

                            Это есть.

                            • копирование данных в буфер обмена в читаемом виде (и с выбором того, что и как копировать, включая моноширинный шрифт и соответствующее выравнивание)

                            Это есть.

                            • глобальный поиск текста в БД и удобное представление – где и что нашлось

                            Это есть.

                            • макросы

                            Это есть.

                            • возможность включения SQL файлов друг в друга

                            Этого нет. Имеется в виду что если в файле есть ссылка, при запуске он подтащит файл по ссылкке:

                            • импорт БД в Excel, правка и заливка обратно (сильно помогает на тестах)

                            Этого нет, надо сделать. youtrack.jetbrains.com/issue/DBE-4123

                            • экспорт во что угодно

                            Это есть, можно писать любые скрипты.

                            • удаление с учётом ограничений

                            Это есть (если я правильно понял, генерация CASCADE)

                            • сравнение баз данных

                            Это есть.

                            • копирование данных из одной БД в другую

                            Есть.

                            • удобная навигация по зависимостям объектов

                            Есть.

                            • GUI и терминальный клиенты

                            Terminal есть, что такое GUI клиент?

                            • возможность запуска скриптов в batch режиме

                            Есть.

                            • ReadOnly режим

                            Есть.
                              0
                              Мне в принципе нравится datagrip форматер, но у меня он довольно далеко от стандартного поведения и я, честно говоря, устал уже ловить баги. Процесс репорта довольно утомителен еще потому, что довольно много времени нужно, чтобы понять, что это явно баг.

                              Вот сейчас кстати как раз медитирую над пачкой багов в генераторе схем и не могу понять толи я тупой толи лыжи не едут.
                                0
                                Давайте сконнектимся в телеграме и просто репортите все туда, сомнения и нет)
                                  0
                                  О_О это можно)
                      +1

                      Хех, если имена таблиц во множественном числе snake_case, то orm тоже во множественном числе, чтоб автоматом привязывались и получится
                      val address = new home_adresses("tverskaya", 21). В общем, да, ходиварная тема))

                        0

                        Для этого используют pluralizers. На хибер, например, прекрасно навешивается.

                          0
                          У меня при работе с БД аналогичный API, поэтому поддерживаю
                          — название таблицы в единственном числе
                          — обязательно наличие одинакового PRIMARY KEY id (не обязательно с функцией счетчика), т.к. если последуете рекомендации статьи, то 21 это что? id, home_adresses_id или запросить структуру и посмотреть PRIMARY KEY?
                          0
                          Действительно, на вкус и цвет все фломастеры разные :)

                          Пример с одной из предыдущих работ стажировок (уже 24 года назад, оказывается)
                          • Имя таблицы описывает роль таблицы (т.е. что она хранит)
                          • Таблицы именуются в единственном числе, поскольку обычно 1 строка = 1 запись, но могут быть варианты, когда в одной строке таблицы хранится массив объектов — тогда имя во множественном (t_geopoints например)
                          • Если таблица – это отражение связи многие-ко-многим, то её имя формируется из связываемых таблиц с указанием роли
                          • Все таблицы имеют префикс T_
                          • Все представления имеют префикс V_
                          • Поля, которые совпадают с зарезервированными словами, имеют постфикс _C (ещё в паре мест я видел обязательный префикс С_ и обязательный постфикс _COL)
                          • Имена колонок PK формируются как имя таблицы без префикса, плюс постфикс _ID
                          • Имена колонок FK формируются как имя таблицы, куда ходить, без префикса плюс постфикс _FK

                          Вроде бы простые правила, но случаются и проблемы:
                          • Суровая таблица вроде T_CUSTOMER_CNT_CONTACT_PERSON, и имена колонок, ограничений и индексов уже перестают соответствовать ограничениям БД на длину идентификаторов пофиксено в Ora 12.2. В PgSQL – 63 символа, что неожиданно тоже немного. А если вдруг нужно добавить ещё одну промежуточную таблицу, и то, что написано выше – это только часть нового идентификатора…
                          • Что делать, если в одну и ту же таблицу нужно ходить два и три раза, например, при возможных ролях пользователя: исполнитель, пользователь подтвердивший действие, пользователь подписавший запись – то тут в имя колонки ещё нужно впихнуть роль
                          • А ещё бывают составные первичные и соответственно внешние ключи
                          • Мелочи, но неприятно – при построении схемы БД это вот всё не лезет ни на какой экран и уже нужно клеить 12 (или 28) листов A4 чтобы хоть что-то обсудить

                          Я страюсь использовать более естественную (для меня) схему наименований:
                          • Первичный суррогатный ключ – id, если вдруг натуральный – то честное наименование такового
                          • Колонка FK – роль внешней таблицы в контексте фиксируемого факта, причём по возможности – глагол. Запросто может не совпадать с именем таблицы, куда нужно ходить, для примера из предыдущего пункта – executed_by, approved_by, signed_by. Тогда в запросе получается что-то типа
                          … FROM t_document doc JOIN t_employee signer where doc.signed_by = signer.id JOIN t_employee appr ON doc.approved_by = appr.id…
                          И я бы с удовольствием вообще не писал первичный ключ в запросе, если бы синтаксис SQL такое позволял.
                          • Постфиксы в колонках — только если нельзя придумать загуглить синоним, который точнее, чем чем зарезервированное слово, описывает роль хранимого атрибута. Т.е t_employee.date — плохо, а t_emloyee.birthdate — хорошо. t_payment.timestamp — плохо, потому что непонятно, это дата/время нажатия пользователем кнопки «оплатить», дата/время начала (или окончания ?) обработки в платёжной системе, или вообще дата импорта записи из внешнего лога. Пока правильно назовёшь, десять писем заказчику напишешь. Ну и бонусом — при таких упражнениях немного английский подтягивается :)
                            0
                            Очень часто приходится переписывать запросы, построенные на внешних соединениях на запросы с внутренними соединениями с использованием подзапросов в SELECT. Взял себе за правило, что если вопрос можно решить на подзапросах, то предпочту такой путь, нежели бороться с NULL-результатами в LEFT или OUTER JOIN.
                              0
                              Ну так они и создавались для случая, если из какой-то из таблиц обязательно нужны ВСЕ записи. Используйте INNER JOIN
                                0
                                Подзапрос в SELECT-части — дело очень нездоровое в плане оптимизации. Не надо так, пожалуйста )

                                *MS SQL
                                  0
                                  Это смотря какой запрос. Если аналитический, для отчета на мильён строк — да, не надо. А если тащим десяток записей для веб-странички с пагианцией — уже не так однозначно, какой вариант будет эффективней работать.
                                    0
                                    А можно примеры, когда это «не однозначно»? )
                                      0
                                      Да на каждом шагу. Самое типичное — join десятка таблиц + order by.
                                      www.db-fiddle.com/f/uSjTZ1u5zFZKqBjwLesi6c/1
                                      Запрос #1 в пять раз быстрее #2. Если посмотреть в планы запросов, то понятно, что это еще не предел, с ростом данных и числа join-ов разрыв будет расти.

                                      Понято, что конкретно в данном случае запрос #2 можно переписать в запрос #3. Но, во-первых, на практике вы не всегда будете иметь возможность протащить (order by + limit) вглубь запроса. А во-вторых, план запроса #3 ничем не лучше плана #1. Вот конкретно в данном случае выполняется за сопоставимое время, но все же чуть дольше, чем #1.

                                      Можно пенять, что, мол, планировщик глуповат у PG. Не принимается :) У Оракла та же история. Про современный MS SQL ничего не могу сказать, т.к. не работал с ним уже лет 10.
                                        0
                                        1. MS SQL запрос 2 в два раза быстрее первого. (а CPU сожрал в 20 раз меньше)
                                        2. Я специально написал про MS SQL потому что в постгре планировщик совсем иначе относится к подзапросам )
                                          0
                                          DDL и тексты запросов покажете? Хочу планы запросов посмотреть, для общего развития.
                                            0
                                            *провёл ещё тестов…
                                            Я был не прав — выигрыша в данном случае нет ни в одном из трёх запросов (планы одинаковые), а предыдущий мой комментарий был неверным по причине кэширования запроса.

                                            Заголовок спойлера
                                            DROP TABLE IF EXISTS #tmp
                                            
                                            CREATE TABLE #tmp (
                                               ID INT PRIMARY KEY CLUSTERED
                                              ,[Name] VARCHAR(100)
                                              ,f1 INT
                                              ,f2 INT
                                              ,f3 INT
                                              ,f4 INT
                                              ,f5 INT
                                              ,f6 INT
                                              ,f7 INT
                                              ,f8 INT
                                              ,f9 INT
                                            )
                                            
                                            ;with tmp as (
                                              SELECT 1 AS ID
                                            
                                              UNION ALL 
                                            
                                              SELECT 
                                                 t.ID + 1 AS ID 
                                              FROM tmp AS t
                                              WHERE ID < 100000
                                            )
                                            INSERT INTO #tmp
                                            select 
                                               t.ID
                                              ,concat('rec ', t.ID) as [Name]
                                              ,cast(rand(t.ID) * 10000  AS INT) AS f1
                                              ,cast(rand(t.ID) * 20000  AS INT) AS f2
                                              ,cast(rand(t.ID) * 30000  AS INT) AS f3
                                              ,cast(rand(t.ID) * 40000  AS INT) AS f4
                                              ,cast(rand(t.ID) * 50000  AS INT) AS f5
                                              ,cast(rand(t.ID) * 60000  AS INT) AS f6
                                              ,cast(rand(t.ID) * 70000  AS INT) AS f7
                                              ,cast(rand(t.ID) * 80000  AS INT) AS f8
                                              ,cast(rand(t.ID) * 90000  AS INT) AS f9
                                            FROM tmp as t
                                            OPTION (MAXRECURSION 0)
                                            
                                            SET STATISTICS TIME ON 
                                            
                                            select top 10
                                            tmain.name, t1.name as n1, t2.name as n2,
                                            	t3.name as n3, t4.name as n4, t5.name as n5, t6.name as n6, t7.name as n7, t8.name as n8, t9.name as n9
                                            from #tmp tmain
                                            left join #tmp t1 on tmain.f1 = t1.id
                                            left join #tmp t2 on tmain.f2 = t2.id
                                            left join #tmp t3 on tmain.f3 = t3.id
                                            left join #tmp t4 on tmain.f4 = t4.id
                                            left join #tmp t5 on tmain.f5 = t5.id
                                            left join #tmp t6 on tmain.f6 = t6.id
                                            left join #tmp t7 on tmain.f7 = t7.id
                                            left join #tmp t8 on tmain.f8 = t8.id
                                            left join #tmp t9 on tmain.f9 = t9.id
                                            order by t1.name, t2.name
                                            OPTION (MAXDOP 1) 
                                            
                                            select top 10
                                            tmain.name, t1.name as n1, t2.name as n2,
                                            	(select name from #tmp  as tbl where tbl.id = tmain.f3) as n3,
                                            	(select name from #tmp  as tbl where tbl.id = tmain.f4) as n4,
                                            	(select name from #tmp  as tbl where tbl.id = tmain.f5) as n5,
                                            	(select name from #tmp  as tbl where tbl.id = tmain.f6) as n6,
                                            	(select name from #tmp  as tbl where tbl.id = tmain.f7) as n7,
                                            	(select name from #tmp  as tbl where tbl.id = tmain.f8) as n8,
                                            	(select name from #tmp  as tbl where tbl.id = tmain.f9) as n9
                                            from #tmp tmain
                                            	left join #tmp t1 on tmain.f1 = t1.id
                                            	left join #tmp t2 on tmain.f2 = t2.id
                                            order by t1.name, t2.name
                                            OPTION (MAXDOP 1) 
                                            
                                            
                                            select tmain.name, tmain.n1, tmain.n2,
                                            	t3.name as n3, t4.name as n4, t5.name as n5, t6.name as n6, t7.name as n7, t8.name as n8, t9.name as n9
                                            from (
                                            select top 10 tmain.*, t1.name as n1, t2.name as n2
                                                from #tmp tmain
                                            		left join #tmp t1 on tmain.f1 = t1.id
                                            		left join #tmp t2 on tmain.f2 = t2.id
                                                order by t1.name, t2.name
                                                ) as tmain
                                            	left join #tmp t3 on tmain.f3 = t3.id
                                            	left join #tmp t4 on tmain.f4 = t4.id
                                            	left join #tmp t5 on tmain.f5 = t5.id
                                            	left join #tmp t6 on tmain.f6 = t6.id
                                            	left join #tmp t7 on tmain.f7 = t7.id
                                            	left join #tmp t8 on tmain.f8 = t8.id
                                            	left join #tmp t9 on tmain.f9 = t9.id
                                            OPTION (MAXDOP 1) 
                                            
                                            SET STATISTICS TIME OFF
                                            

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

                                              То, что планы получились одинаковыми — так в идеале и должно быть, поскольку запросы семантически эквивалентные. Но в жизни это происходит, к сожалению, не всегда, особенно с запросами похитрее. Огорчает и то, что третий вариант не всегда доступен, например, когда платформа, под которую пишется запрос, берет на себя сортировку и пагинацию, а программисту дает только базовый запрос написать. Мы с этим наелись в Oracle Application Express в свое время.
                                                0
                                                программисту дает только базовый запрос написать
                                                ужас какой!

                                                Но в жизни это происходит, к сожалению, не всегда, особенно с запросами похитрее.
                                                Что ж, если человек смотрит планы запроса и знает что как их читать — для него не будет преград в построении оптимальных решений)
                                0
                                Хм, более интересно было бы почитать про шаблоны СУБД. На примерах решения
                                1) Дополнительные атрибуты. Пример: есть таблица клиентов с полями id, birthday, firstName, secondName, sex, email. В процессе эксплуатации для небольшой части клиентов возникает необходимость добавить доп реквизиты: должность, степень знания english и т.п.
                                Alter-ить таблицу не всегда допустимо и тут возможны варианты:
                                1.1) Создаем табличку с полями idClient, NameFeature:Text, valString, valDateTime, valNumber — где в поле с именем нужного типа прописываем значение.
                                1.2) Создаем табличку с полями idClient, feature1, feature2… (еще как вариант добавить имя таблицы отдельной колонкой — но в одном месте все держать свои минусы)
                                1.3) Варианты с доп. полем в таблице, заполненным в виде «feature2=..., feature3=», JSON, XML-типах и т. п. еще более ужасны в связи с отходом от реляционности.
                                Плюс 1.1 — один раз создали и наполняем любыми доп атрибутами, минусы — размер, поиск инфы по клиенту чуть сложней
                                Плюсы 1.2 — проще строить запросы, минусы — альтерить и следить за этим + размер еще больше распухнуть может

                                2) Модификация данных по времени. Требуется в запросах выдавать данные с привязкой ко времени.
                                Клиенты могут менять secondname(смена фамилии), email…
                                При подобных изменениях в связанную таблицу кидать новое(в основной оно не меняется) или старое (т.е логируем) значение?
                                Если еще далее делать выносить данные в отдельные архивные таблицы (не выдавливать из БД совсем) — OldClients, то запросы с поиском по всем данным еще более усложнятся (Какой ORM умеет?). Клиенты тут для примера(как таблицы, для которой нельзя делать простые вставки/удаления для данного случая, так как на данные есть внешние ссылки), в реале приходится выносить в old-таблицы с данными по активности (где записей на порядке больше).

                                Опять свои плюсы/минусы.
                                  0
                                  По пункту 2 это всегда попаболь. Есть конечно Hibernate Envers, https://hibernate.org/orm/envers/, но иногда это не то, что нужно.
                                  Если дизайнить такую штуку с нуля — то нужны «срезы» — коллекции непротиворечивых на некоторый момент времени записей, например если госпожа Иванова осуществляла платёж в прошлом году, а в этом — она уже госпожа Петрова, то в отчётах за прошлый год она должна отображаться как по-прежнему как Иванова.
                                  В данном примере можно версионировать данные в той же таблице и выдать клиенту ещё один ключ. Что-то типа натурального — CLID например. А дальше аккуратно смотрим: платежи всё такое, что делал конкретный живой человек, привязываем и к СLID, и к ID, который PK. Агрегаты при этом работают нормально — но считать их нужно исключительно по CLID. В остальных местах — честная связка по PK, чтобы показывать версию записи, актуальную именно на нужный момент времени.
                                    0
                                    1) много где прижились таблицы _meta: id, entity_id, name, value
                                    2) revision_id — при сохранении важных изменений копируем старую строку, сохраняя в revision_id ключ основной записи. Основную запись обновляем, естественно нужны колонки created/modified чтобы можно было достать запись на определенную дату — 1с, wordpress
                                      0
                                      А как для 2) получить при выборке, например, поста за прошлый год — ник автора, который был именно на тот момент? (для случая, если ник обновлять можно)?
                                        0
                                        при условии что для основной записи revision_id=0 как-то так

                                        SELECT id,name,author FROM post
                                        WHERE id=123 OR (revision_id=123 AND modified<'2019-12-31')
                                        ORDER BY revision_id DESC, modified DESC
                                        LIMIT 1


                                        123 | 0 | name1 | author1 | 2020-01-17
                                        144 | 123 | name2 | author2 | 2019-10-16
                                        200 | 123 | name3 | author3 | 2019-12-25


                                        сортировка по revision_id DESC закинет 123 в конец,
                                        а по modified DESC вытянет наверх ближайшую к дате ревизию 200
                                          0
                                          Это немного не то, что я имел в виду — как это будет выглядеть при паре таблиц Author и Post, при том, что данные в Author — версионируются?
                                            0
                                            упс, у меня ошибка в запросе, по данным таблицы, на 2019-12-31 актуальным должен быть author1

                                            но суть думаю ясна
                                            если представить таймлайн с отметками ревизий то проще составить правильный запрос

                                            можно добавить штампы актуальности версий, для упрощения условия
                                            WHERE snapshot_date BETWEEN rev_start AND rev_end

                                            в вашем случае это будут 2 запроса или сложный вложенный запрос
                                          0
                                          На вскидку можно так:

                                          Before update trigger
                                          insert into autors (id, nickname, valid_period) values (100500, 'Vasya', tsrange(lower(old.valid_preiod), now(), '[)'))
                                          set new.valid_period=tsrange(upper(now(), 'infinity', '[)')
                                          


                                          И выборка через

                                          select * from authors where id=100500 and valid_period<@'2019-01-01'
                                          


                                          хотя имхо лучше через after update но тогда доп поле нужно, которое занулять
                                            0
                                            Интервалы мне нравятся тем, что

                                            select at.*,
                                                   ut.name
                                            from articles as at
                                            left join authors as ut
                                              on ut.id = at.author_id and 
                                                 ut.valid_period <@ at.created_at
                                            


                                            Имхо гораздо приятней чем between и, в некоторых случаях, быстрее.
                                      +1
                                      Не увидел совета (для всех очевидно?), что в именовании таблиц стоит хорошенько думать о префиксе, чтобы когда таблиц станет 100+ все они удобно группировались в виде:

                                      users
                                      user_group
                                      user_status

                                      products
                                      product_type
                                      product_status


                                      Иначе, когда таблиц становится много, а названия идут в разнобой — product_type, status_product — становится ужасно неудобно с ними работать.
                                        +1
                                        Когда таблиц становится 100+, можно использовать различные схемы, для чего они и были придуманы.
                                        +1
                                        назвать колонку fahrenheit.

                                        Нет не лучше. Там температура? температура, Так и напишите. Если для вас важно по фаренгейту или цельсию то впишите еще и это в название. Хотя «я живу во франции» это очень слабый аргумент, на уровне «я живу в gmt+1»

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

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

                                        Но когда вы посмотрите определение таблицы

                                        o.person_id=s.person_id это гораздо хуже во многих ситуациях. Не должна схема базы вводить в ступор и догадываться что за person_id тут.
                                        И уж совершенно невозможна ваша идея в таблицах, где есть множество поля связаны по person_id (ответственный, получатель, etc)

                                        По мере возможности добавляйте NOT NULL во все определения колонок

                                        Очень плохой совет. Руководство по проектированию должно давать четкие критерии.
                                        created_at not null но deleted_at вполне себе может и должно в общем случае содержать null. Есть возможность у меня сделать его not null и вписать туда чушь? Есть и примеров таких я навидался.

                                        Я бы еще добавил в обязательные советы:
                                        — Забыть к чертям про префиксы T_ V_/VW_ и пр. Ибо очень сильно напрягают при поиске по списку. Я не хочу помнить view там или table, если вам это важно — пишите в конце имени.
                                        — Выучить на зуб и всегда именовать единообразно имена полей с числительными: количество, сумма, налоги и пр (реально глаза уже кровоточат от valume и пр)
                                        — regexp для автокомплитеров должен выглядеть так: '(^|\s)' ||? и уж точно ни при каких обстоятельствах не '*.'||?||'.*' (ну или '%'||?||'%' для like)
                                        — внешние ключи — маст хев, если по вашей базе нельзя построить диаграмму, в помойку ее, немедленно.
                                        — используйте схемы (что посоветовать ораклистам я даж не знаю) )
                                          0
                                          Набор бредовых измышлений перфекциониста, который не думает, что с его фантазиями ему придётся жить. Видимо, он скинет свои маразматические фантазии на кого-то другого.
                                            +1
                                            *Зануда-mode ON*

                                            То, что в статье названо camelCase, на самом деле является PascalCase
                                            В camelCase первая буква строчная, в PascalCase — заглавная

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

                                              Работа с NULL из примера — ошибка новичка. Мне кажется лучше сразу учиться понимать логику NULL. Она довольно подробно описана в документации и учебниках, в том числе на чистом русском языке.
                                              Чрезмерная боязнь NULL часто приводит новичков к псевдонеизвестным значениям типа ' ','NULL','-',0, -1,-666,100500,01.01.0001,01.01.1900,31.12.3000 из которых возникают совершенно неожиданные последствия.

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

                                              Отличный пример на работе есть (до сих пор), в каталоге устанавливаются значения размерности 1х1х1 мм по умолчанию для каждого товара. Это позволяет рассчитывать объём товара и отображать его во всех связных программах без проблем. Наименований в каталоге больше 1.5 миллионов, треть со значениями по умолчанию. Конечно никто уже не сможет исправить этот каталог. Сделать проверку на габариты во всех связных системах тоже невозможно. Пришлось делать специальный сервис который сообщает о возможной ошибке по кодам которые могут быть где-то актуальны.
                                                +1
                                                Когда делал вот такой плакат по правилам наименований объектов БД для проекта: Naming Convention. Ну, и ссылка на поясняющую статью: Именование объектов в Oracle. Взгляд «со стороны»

                                                Сразу скажу, что часть требований с годами поменялись. Но большинством пользуюсь и по сей день
                                                  0
                                                  «Роль / Цель / Комментарий» лоя таблицы — это, на первый взгляд, жесткач, хотя потом я вспомнил, что сам в своё время вывел зпкономерность, что в любой БД есть центральная таблица (через несколько лет понял, что их может быть несколько), которая представляте из себя нечто вроде журнала, а остальные таблицы являются для неё (них) справочниками.
                                                    0
                                                    ...*для таблицы…

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

                                                  Самое читаемое