Comments 53
UFO just landed and posted this here
1. Про ID хотелось бы заметить, что если писать нормальные алиасы, то нет необходимости дублировать информацию в названии ключа. Более того — замучаешься эти ключи постоянно писать.
2. NULL — имеет свои особенности и их надо знать. То что с ними кто то не умеет работать — совсем не повод от них избавляться.
Поводом служила бы сложная оптимизация моментов связанных с NULL, особенности хранения NULL на дисках или реальные ошибки логики которые придётся с трудом обходить.
Точно так же можно сказать что надо избегать неявного приведения типов, скобочек, дат, битовых типов и прочего-прочего — при использовании их неподготовленными людьми может произойти трагедия!
3. NOT NULL в определении колонок нужен там где он нужен, а не где только можно.
4.По поводу ключей — мне понравилась идея, так же, начинать наименования ключей с pk/fk/bk соответственно, что бы знать, что это не просто поле и что на него наложены ограничения и какие.
5. Не раскрыта тема использования схем и префиксов в названиях (функция? процедура? таблица? вьюха?). Надо? Не надо? Почему?
6. Т.е. в названиях не должно быть заглавных букв?
Тема статьи хорошая, но дюже холиварная. =)
2. NULL — имеет свои особенности и их надо знать. То что с ними кто то не умеет работать — совсем не повод от них избавляться.
Поводом служила бы сложная оптимизация моментов связанных с NULL, особенности хранения NULL на дисках или реальные ошибки логики которые придётся с трудом обходить.
Точно так же можно сказать что надо избегать неявного приведения типов, скобочек, дат, битовых типов и прочего-прочего — при использовании их неподготовленными людьми может произойти трагедия!
3. NOT NULL в определении колонок нужен там где он нужен, а не где только можно.
4.По поводу ключей — мне понравилась идея, так же, начинать наименования ключей с pk/fk/bk соответственно, что бы знать, что это не просто поле и что на него наложены ограничения и какие.
5. Не раскрыта тема использования схем и префиксов в названиях (функция? процедура? таблица? вьюха?). Надо? Не надо? Почему?
6. Т.е. в названиях не должно быть заглавных букв?
Тема статьи хорошая, но дюже холиварная. =)
> 3. NOT NULL в определении колонок нужен там где он нужен, а не где только можно.
Я для себя определяю так: в отсутствии явных потребностей и/или вводных всегда используй NOT NULL, иначе попробуй найти хотя бы 2 причины почему это поле _должно_ быть nullable и если не найдешь, используй NOT NULL.
Остальные случаи использования DEFAULT NULL произрастают из моей собственной лени…
Я для себя определяю так: в отсутствии явных потребностей и/или вводных всегда используй NOT NULL, иначе попробуй найти хотя бы 2 причины почему это поле _должно_ быть nullable и если не найдешь, используй NOT NULL.
Остальные случаи использования DEFAULT NULL произрастают из моей собственной лени…
COALESCE разве не для null значений?
Почему CamelStyle по имени, а snake_style — нет?
Всегда в полях с возможностью нахождения NULL использую функцию NVL, очень удобно. Хотелось бы более расширенный вариант статьи, как написано выше. Про id тоже была засаув своё время.
Замечаете проблему? Если бы 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проблемы тоже не было бы. Но зато в этом случае было бы чётко видно, где первичный, а где не очень.
Я для имен таблиц использую множественное число (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
ИМХО, не важно как называть, главное одинаково для всей БД, т.е. должен быть определено "соглашение об именовании". Если всё в разнобой, конечно, кровь из глаз потечёт)
В статью прямо просится обзор SQL-форматтеров. Например, я бы с удовольствием почитал бы чем народ пользуется, чтобы иметь общее представление (на работе куплен SQL complete, он Crtl+K, Ctrl+D прекрасно форматит (и можно подкрутить в опциях), поэтому особо нет необходимости в выходе из зоны комфорта, а вот любопытство есть)
SQL Workbench/J — www.sql-workbench.eu. Ну или JetBrains IDEA Ultimate / DataGrip, хотя лично мне они нравятся меньше, и платные к тому же.
А чем нравятся меньше, можете рассказать?)
Тут статью писать надо… Вкратце чем мне 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 режим
• когда 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 режим
Спасибо за комент! Отвечу по пунктам.
• подсветка 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 режим
Есть.
• подсветка 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 режим
Есть.
Мне в принципе нравится datagrip форматер, но у меня он довольно далеко от стандартного поведения и я, честно говоря, устал уже ловить баги. Процесс репорта довольно утомителен еще потому, что довольно много времени нужно, чтобы понять, что это явно баг.
Вот сейчас кстати как раз медитирую над пачкой багов в генераторе схем и не могу понять толи я тупой толи лыжи не едут.
Вот сейчас кстати как раз медитирую над пачкой багов в генераторе схем и не могу понять толи я тупой толи лыжи не едут.
Хех, если имена таблиц во множественном числе snake_case, то orm тоже во множественном числе, чтоб автоматом привязывались и получится
val address = new home_adresses("tverskaya", 21). В общем, да, ходиварная тема))
Для этого используют pluralizers. На хибер, например, прекрасно навешивается.
У меня при работе с БД аналогичный API, поэтому поддерживаю
— название таблицы в единственном числе
— обязательно наличие одинакового PRIMARY KEY id (не обязательно с функцией счетчика), т.к. если последуете рекомендации статьи, то 21 это что? id, home_adresses_id или запросить структуру и посмотреть PRIMARY KEY?
— название таблицы в единственном числе
— обязательно наличие одинакового PRIMARY KEY id (не обязательно с функцией счетчика), т.к. если последуете рекомендации статьи, то 21 это что? id, home_adresses_id или запросить структуру и посмотреть PRIMARY KEY?
Действительно, на вкус и цвет все фломастеры разные :)
Пример с одной из предыдущихработ стажировок (уже 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. Тогда в запросе получается что-то типа
• Постфиксы в колонках — только если нельзяпридумать загуглить синоним, который точнее, чем чем зарезервированное слово, описывает роль хранимого атрибута. Т.е t_employee.date — плохо, а t_emloyee.birthdate — хорошо. t_payment.timestamp — плохо, потому что непонятно, это дата/время нажатия пользователем кнопки «оплатить», дата/время начала (или окончания ?) обработки в платёжной системе, или вообще дата импорта записи из внешнего лога. Пока правильно назовёшь, десять писем заказчику напишешь. Ну и бонусом — при таких упражнениях немного английский подтягивается :)
Пример с одной из предыдущих
• Имя таблицы описывает роль таблицы (т.е. что она хранит)
• Таблицы именуются в единственном числе, поскольку обычно 1 строка = 1 запись, но могут быть варианты, когда в одной строке таблицы хранится массив объектов — тогда имя во множественном (t_geopoints например)
• Если таблица – это отражение связи многие-ко-многим, то её имя формируется из связываемых таблиц с указанием роли
• Все таблицы имеют префикс T_
• Все представления имеют префикс V_
• Поля, которые совпадают с зарезервированными словами, имеют постфикс _C (ещё в паре мест я видел обязательный префикс С_ и обязательный постфикс _COL)
• Имена колонок PK формируются как имя таблицы без префикса, плюс постфикс _ID
• Имена колонок FK формируются как имя таблицы, куда ходить, без префикса плюс постфикс _FK
Вроде бы простые правила, но случаются и проблемы:
• Суровая таблица вроде T_CUSTOMER_CNT_CONTACT_PERSON, и имена колонок, ограничений и индексов уже
• Что делать, если в одну и ту же таблицу нужно ходить два и три раза, например, при возможных ролях пользователя: исполнитель, пользователь подтвердивший действие, пользователь подписавший запись – то тут в имя колонки ещё нужно впихнуть роль
• А ещё бывают составные первичные и соответственно внешние ключи
• Мелочи, но неприятно – при построении схемы БД это вот всё не лезет ни на какой экран и уже нужно клеить 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…• Постфиксы в колонках — только если нельзя
Очень часто приходится переписывать запросы, построенные на внешних соединениях на запросы с внутренними соединениями с использованием подзапросов в SELECT. Взял себе за правило, что если вопрос можно решить на подзапросах, то предпочту такой путь, нежели бороться с NULL-результатами в LEFT или OUTER JOIN.
Ну так они и создавались для случая, если из какой-то из таблиц обязательно нужны ВСЕ записи. Используйте INNER JOIN
Подзапрос в SELECT-части — дело очень нездоровое в плане оптимизации. Не надо так, пожалуйста )
*MS SQL
*MS SQL
Это смотря какой запрос. Если аналитический, для отчета на мильён строк — да, не надо. А если тащим десяток записей для веб-странички с пагианцией — уже не так однозначно, какой вариант будет эффективней работать.
А можно примеры, когда это «не однозначно»? )
Да на каждом шагу. Самое типичное — 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.
www.db-fiddle.com/f/uSjTZ1u5zFZKqBjwLesi6c/1
Запрос #1 в пять раз быстрее #2. Если посмотреть в планы запросов, то понятно, что это еще не предел, с ростом данных и числа join-ов разрыв будет расти.
Понято, что конкретно в данном случае запрос #2 можно переписать в запрос #3. Но, во-первых, на практике вы не всегда будете иметь возможность протащить (order by + limit) вглубь запроса. А во-вторых, план запроса #3 ничем не лучше плана #1. Вот конкретно в данном случае выполняется за сопоставимое время, но все же чуть дольше, чем #1.
Можно пенять, что, мол, планировщик глуповат у PG. Не принимается :) У Оракла та же история. Про современный MS SQL ничего не могу сказать, т.к. не работал с ним уже лет 10.
1. MS SQL запрос 2 в два раза быстрее первого. (а CPU сожрал в 20 раз меньше)
2. Я специально написал про MS SQL потому что в постгре планировщик совсем иначе относится к подзапросам )
2. Я специально написал про MS SQL потому что в постгре планировщик совсем иначе относится к подзапросам )
DDL и тексты запросов покажете? Хочу планы запросов посмотреть, для общего развития.
*провёл ещё тестов…
Я был не прав — выигрыша в данном случае нет ни в одном из трёх запросов (планы одинаковые), а предыдущий мой комментарий был неверным по причине кэширования запроса.
Я был не прав — выигрыша в данном случае нет ни в одном из трёх запросов (планы одинаковые), а предыдущий мой комментарий был неверным по причине кэширования запроса.
Заголовок спойлера
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
Спасибо. А то я уже начал переживать, каким образом MSSQL придумал выполнить второй запрос эффективнее первого на таких исходных данных.
То, что планы получились одинаковыми — так в идеале и должно быть, поскольку запросы семантически эквивалентные. Но в жизни это происходит, к сожалению, не всегда, особенно с запросами похитрее. Огорчает и то, что третий вариант не всегда доступен, например, когда платформа, под которую пишется запрос, берет на себя сортировку и пагинацию, а программисту дает только базовый запрос написать. Мы с этим наелись в Oracle Application Express в свое время.
То, что планы получились одинаковыми — так в идеале и должно быть, поскольку запросы семантически эквивалентные. Но в жизни это происходит, к сожалению, не всегда, особенно с запросами похитрее. Огорчает и то, что третий вариант не всегда доступен, например, когда платформа, под которую пишется запрос, берет на себя сортировку и пагинацию, а программисту дает только базовый запрос написать. Мы с этим наелись в Oracle Application Express в свое время.
Хм, более интересно было бы почитать про шаблоны СУБД. На примерах решения
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-таблицы с данными по активности (где записей на порядке больше).
Опять свои плюсы/минусы.
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-таблицы с данными по активности (где записей на порядке больше).
Опять свои плюсы/минусы.
По пункту 2 это всегда попаболь. Есть конечно Hibernate Envers, https://hibernate.org/orm/envers/, но иногда это не то, что нужно.
Если дизайнить такую штуку с нуля — то нужны «срезы» — коллекции непротиворечивых на некоторый момент времени записей, например если госпожа Иванова осуществляла платёж в прошлом году, а в этом — она уже госпожа Петрова, то в отчётах за прошлый год она должна отображаться как по-прежнему как Иванова.
В данном примере можно версионировать данные в той же таблице и выдать клиенту ещё один ключ. Что-то типа натурального — CLID например. А дальше аккуратно смотрим: платежи всё такое, что делал конкретный живой человек, привязываем и к СLID, и к ID, который PK. Агрегаты при этом работают нормально — но считать их нужно исключительно по CLID. В остальных местах — честная связка по PK, чтобы показывать версию записи, актуальную именно на нужный момент времени.
Если дизайнить такую штуку с нуля — то нужны «срезы» — коллекции непротиворечивых на некоторый момент времени записей, например если госпожа Иванова осуществляла платёж в прошлом году, а в этом — она уже госпожа Петрова, то в отчётах за прошлый год она должна отображаться как по-прежнему как Иванова.
В данном примере можно версионировать данные в той же таблице и выдать клиенту ещё один ключ. Что-то типа натурального — CLID например. А дальше аккуратно смотрим: платежи всё такое, что делал конкретный живой человек, привязываем и к СLID, и к ID, который PK. Агрегаты при этом работают нормально — но считать их нужно исключительно по CLID. В остальных местах — честная связка по PK, чтобы показывать версию записи, актуальную именно на нужный момент времени.
1) много где прижились таблицы _meta: id, entity_id, name, value
2) revision_id — при сохранении важных изменений копируем старую строку, сохраняя в revision_id ключ основной записи. Основную запись обновляем, естественно нужны колонки created/modified чтобы можно было достать запись на определенную дату — 1с, wordpress
2) revision_id — при сохранении важных изменений копируем старую строку, сохраняя в revision_id ключ основной записи. Основную запись обновляем, естественно нужны колонки created/modified чтобы можно было достать запись на определенную дату — 1с, wordpress
А как для 2) получить при выборке, например, поста за прошлый год — ник автора, который был именно на тот момент? (для случая, если ник обновлять можно)?
при условии что для основной записи revision_id=0 как-то так
сортировка по revision_id DESC закинет 123 в конец,
а по modified DESC вытянет наверх ближайшую к дате ревизию 200
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 1123 | 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
Это немного не то, что я имел в виду — как это будет выглядеть при паре таблиц Author и Post, при том, что данные в Author — версионируются?
упс, у меня ошибка в запросе, по данным таблицы, на 2019-12-31 актуальным должен быть author1
но суть думаю ясна
если представить таймлайн с отметками ревизий то проще составить правильный запрос
можно добавить штампы актуальности версий, для упрощения условия
WHERE snapshot_date BETWEEN rev_start AND rev_end
в вашем случае это будут 2 запроса или сложный вложенный запрос
но суть думаю ясна
если представить таймлайн с отметками ревизий то проще составить правильный запрос
можно добавить штампы актуальности версий, для упрощения условия
WHERE snapshot_date BETWEEN rev_start AND rev_end
в вашем случае это будут 2 запроса или сложный вложенный запрос
На вскидку можно так:
Before update trigger
И выборка через
хотя имхо лучше через after update но тогда доп поле нужно, которое занулять
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 но тогда доп поле нужно, которое занулять
На больших объёмах это боль. Преимущество по сравнению с nosql-вариантом (id, entity_id, data) только в возможности нормализации по name. Зато если бд умеет индексировать поиск по типу data (jsonb в postgresql), получаем вменяемую скорость на больших объёмах.
Не увидел совета (для всех очевидно?), что в именовании таблиц стоит хорошенько думать о префиксе, чтобы когда таблиц станет 100+ все они удобно группировались в виде:
users
user_group
user_status
…
products
product_type
product_status
…
Иначе, когда таблиц становится много, а названия идут в разнобой — product_type, status_product — становится ужасно неудобно с ними работать.
users
user_group
user_status
…
products
product_type
product_status
…
Иначе, когда таблиц становится много, а названия идут в разнобой — product_type, status_product — становится ужасно неудобно с ними работать.
назвать колонку 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)
— внешние ключи — маст хев, если по вашей базе нельзя построить диаграмму, в помойку ее, немедленно.
— используйте схемы (что посоветовать ораклистам я даж не знаю) )
Набор бредовых измышлений перфекциониста, который не думает, что с его фантазиями ему придётся жить. Видимо, он скинет свои маразматические фантазии на кого-то другого.
*Зануда-mode ON*
То, что в статье названо camelCase, на самом деле является PascalCase
В camelCase первая буква строчная, в PascalCase — заглавная
*Зануда-mode OFF*
То, что в статье названо camelCase, на самом деле является PascalCase
В camelCase первая буква строчная, в PascalCase — заглавная
*Зануда-mode OFF*
Желательно чтобы в одном проекте было одно соглашение о наименованиях, остальные уточнения — полная вкусовщина. Настолько это не принципиально, что даже не знаю зачем статья нужна.
Работа с NULL из примера — ошибка новичка. Мне кажется лучше сразу учиться понимать логику NULL. Она довольно подробно описана в документации и учебниках, в том числе на чистом русском языке.
Чрезмерная боязнь NULL часто приводит новичков к псевдонеизвестным значениям типа ' ','NULL','-',0, -1,-666,100500,01.01.0001,01.01.1900,31.12.3000 из которых возникают совершенно неожиданные последствия.
Устанавливая ограничение NOT NULL, как и типы данных и размерность в колонке нужно понимать, что без этих данных (данных этого формата) запись действительно не имеет смысла или точно имеет значащее значение по умолчанию.
Отличный пример на работе есть (до сих пор), в каталоге устанавливаются значения размерности 1х1х1 мм по умолчанию для каждого товара. Это позволяет рассчитывать объём товара и отображать его во всех связных программах без проблем. Наименований в каталоге больше 1.5 миллионов, треть со значениями по умолчанию. Конечно никто уже не сможет исправить этот каталог. Сделать проверку на габариты во всех связных системах тоже невозможно. Пришлось делать специальный сервис который сообщает о возможной ошибке по кодам которые могут быть где-то актуальны.
Работа с NULL из примера — ошибка новичка. Мне кажется лучше сразу учиться понимать логику NULL. Она довольно подробно описана в документации и учебниках, в том числе на чистом русском языке.
Чрезмерная боязнь NULL часто приводит новичков к псевдонеизвестным значениям типа ' ','NULL','-',0, -1,-666,100500,01.01.0001,01.01.1900,31.12.3000 из которых возникают совершенно неожиданные последствия.
Устанавливая ограничение NOT NULL, как и типы данных и размерность в колонке нужно понимать, что без этих данных (данных этого формата) запись действительно не имеет смысла или точно имеет значащее значение по умолчанию.
Отличный пример на работе есть (до сих пор), в каталоге устанавливаются значения размерности 1х1х1 мм по умолчанию для каждого товара. Это позволяет рассчитывать объём товара и отображать его во всех связных программах без проблем. Наименований в каталоге больше 1.5 миллионов, треть со значениями по умолчанию. Конечно никто уже не сможет исправить этот каталог. Сделать проверку на габариты во всех связных системах тоже невозможно. Пришлось делать специальный сервис который сообщает о возможной ошибке по кодам которые могут быть где-то актуальны.
Когда делал вот такой плакат по правилам наименований объектов БД для проекта: Naming Convention. Ну, и ссылка на поясняющую статью: Именование объектов в Oracle. Взгляд «со стороны»
Сразу скажу, что часть требований с годами поменялись. Но большинством пользуюсь и по сей день
Сразу скажу, что часть требований с годами поменялись. Но большинством пользуюсь и по сей день
«Роль / Цель / Комментарий» лоя таблицы — это, на первый взгляд, жесткач, хотя потом я вспомнил, что сам в своё время вывел зпкономерность, что в любой БД есть центральная таблица (через несколько лет понял, что их может быть несколько), которая представляте из себя нечто вроде журнала, а остальные таблицы являются для неё (них) справочниками.
...*для таблицы…
Sign up to leave a comment.
Стандарты проектирования баз данных