Stfw.RuПравила именования объектов базы данных
"Всякое приказание должно быть отдано в должное время, в должном месте, и в выражениях, исключающих двоякое толкование" Алексей Михайличенко
🕛 09.10.2006, 12:10
"Всякое приказание должно быть отдано в должное время, в должном месте,
и в выражениях, исключающих двоякое толкование"
(Из Устава Петровских времен)
Обсуждаемый вопрос заключается в следующем. Если мы начали разработку базы данных для некоторой задачи, определились с набором схем, таблиц, полей, внешних ключей, то как нам следует называть все эти объекты, и зачем вообще нужно говорить о какой-то особой системе наименований?
Проблема чем-то похожа на выбор имен для переменных при написании программного кода. Но разница в том, что в написании процедур мир давно уже пришел к идее локальных переменных, и их имена остаются на совести автора-программиста - согласуются только параметры вызова. А вот база данных - штука глобального характера. Имена таблиц и полей используются в процедурах обработки, в клиентских формах ввода, в отчетах. Стройная система имен позволит "протащить" логику организации данных сквозь весь проект, сделать все его части более читабельными, за что вы получите немало благодарностей как от коллег по команде, так и от себя лично, когда вернетесь к задаче эдак через полгодика. С другой стороны, полтысячи разношерстных огрызков вроде 'trb_zrplt_mumu', по десятку на одно и то же понятие, выведут из себя кого угодно.
Итак, какова бы ни была система имен, она должна решить следующие задачи:
Единство именования программных единиц во всем проекте. Другими словами, если у нас есть, скажем "документ" (doc), или "номер документа" (docnum), то именно так должны называться таблица БД, ее соответствующее поле, соответствующие поля всевозможных VIEW, поля клиентских наборов данных, локальные переменные в процедурах обработки, классы, представляющие эту сущность на клиенте, поля (свойства) в этих классах, объекты во всех отчетах, и вообще все, что содержит в себе документ (номер). Другими словами, нельзя допускать размножения сущностей без необходимости. Нередко проектировщики свято соблюдают этот принцип Оккама при моделировании "внешних" данных, но напрочь игнорирую его во внутренней "кухне".
Самодокументируемость структуры базы данных. Сформулировать это зыбкое понятие трудно, но без него работать еще труднее.
Единообразие подачи информации пользователю. Если вернуться к тому же 'номеру документа', то с единством названия становится легче проследить и за единством подписей полей ввода, кнопок, шапок отчетов, содержащих этот реквизит, чтобы пользователь не терялся.
О конкретной цели статьи. Описываемая схема не является ни единственно правильной, ни вообще оптимальной в каком-либо смысле. Скорее это исторически сложившийся взгляд автора, применяемый лишь потому, что лучше хоть какая-то система, чем никакой. Основная цель - обсудить основные принципы и проблемы "именования", и предложить один из вариантов. Если кто-то предложит лучше - буду рад.
Выбор имени сущности
Обычно в результате нормализации каждая таблица ассоциируется с неким объектом или событием реального мира - человеком, документом, посещением и т.д., то есть "сущностью". Основой для дальнейших рассуждений будет "имя сущности" - идентификатор из латинских букв и цифр, например 'man', 'doc', 'visit'. При выборе имени сущности разумно придерживаться таких правил:
Имя должно быть существительным (полным, сокращенным либо аббревиатурой) в единственном числе.
Имя должно быть как можно короче. Оптимально - 2-4 буквы, максимум до 10.
Имя должно быть уникальным в пределах базы данных.
Имя должно быть мнемонически понятным проектантам без заглядывания в словарь (но словарь такой хорошо бы составить).
Желательно, чтобы имена не начинались и не заканчивались на другие имена сущностей.
Обсудим это.
Нетрудно заметить, что все эти правила противоречат друг другу. И в инженерной практике это скорее правило, чем исключение. Чего стоит хотя бы идея плавающего танка... Так что придется выкручиваться.
Итак, почему как можно короче? Потому что на основании имени сущности мы будем "лепить" другие идентификаторы. Они будут встречаться целыми списками в секциях FROM, WHERE. И если названия таблицы и пяти соединенных справочников будут несокращенными словами в 16-20 символов, то это порадует лишь поклонников языка Шекспира, но успешно затуманит смысл простейших SQL-запросов.
Требование уникальности понятно само собой. Стоит лишь заметить, что здесь нужно максимально учесть перспективы роста БД. Возможно, стоит набросать список сущностей, которые могут войти в БД в следующих версиях системы, даже если эта работа отдаленного будущего.
Чтобы сделать имена понятными, нужно определиться - "salary" или "zarplata" ? То есть в качестве имен сущностей можно использовать английские слова и сокращения, либо русские (кстати, почему бы и не на других языках? Лишь бы... см. Правило 4 ;-) Кстати, неплохо использовать комбинации из двух понятий. Например "группа пользователей" - "ugroup", "группа домов" - "hgroup".
Последнее, пятое правило говорит, что если у вас уже есть таблица 'документ' (doc), то для 'докторов' и 'доктрин' нужно выбрать что-то другое, а не 'doctor' и 'doctrine'. Океан тоже нехорошо называть 'oc' (иначе документ будет интуитивно восприниматься как нечто связанное с океаном - doc). Для решения такой проблемы можно изменить либо удлинить проблемный идентификатор, например 'dcm', 'docum',
Наименование таблиц
Многие проектировщики дают таблицам непосредственно имена сущностей (в единственном числе), например street, city. Другие же ратуют за то, что таблицы нужно называть множественным числом - streets, cities. И единого мнения быть не может. Дело в том, что если говорить об удобочитаемости SQL-запросов, вроде следующего:
SELECT *
FROM cities, streets
WHERE streets.cityid = cities.cityid AND streets.name LIKE "%тупик Третьего Интернационала%"
то нетрудно заметить, что секция FROM грамматически подразумевает множественное число - "ВЫБРАТЬ.. ИЗ городов, улиц". В то же время секция WHERE "читается" с единственным числом - "ВЫБРАТЬ ту запись, ГДЕ название улицы ПОХОЖЕ ...", и это понятно - ведь условие под WHERE применяется к каждой записи набора по отдельности, то есть к каждой улице.
Если вспомнить, что мы хотим использовать имя сущности также в программах обработки данных, где объектом работы становится именно "единица" информации (например в названии класса для хранения одной улицы TStreet, а затем в названии переменой-объекта objStreet1), то разумнее остановиться на единственном числе как на стандарте. Но ведь в таблице все-таки живет множество сущностей! Как это выразить? Для таблиц примем следующее волюнтаристское решение:
Имя таблицы состоит из служебного префикса и имени сущности. Например.
Префикс Имя сущности Имя таблицы Название таблицы в целом
(как множества сущностей)
C STRTYPE (тип улицы) CSTRTYPE Каталог типов улиц
S STREET (улица) SSTREET Справочник улиц
R ABON (абонент) RABON Реестр абонентов
J CALCOP (выч. операция) JCALCOP Журнал вычислительных операций
Таким образом, благодаря мысленной расшифровке префикса мы можем воспринимать таблицу как множество сущностей со своим нравом - ведь таблица таблице рознь. Я предлагаю такие префиксы:
C - Catalog, список. Самая безобидная форма жизни информации. Представляет собой простейший справочник из десятка пунктов чего-то неизменного, или почти неизменного. Структурирует некую фундаментальную природу вещей. Например, перечисляет типы улиц - "улица", "переулок", "проезд", "тупик", "овраг". Через пару лет построят в деревне первый проспект - добавим "проспект".
Можно ввести дополнительные соглашения, приятные для программистов. Например, что записи из таблиц типа 'C' не удаляются, а только иногда добавляются. Что в интерфейсе для работы с таким списком будет достаточно унифицированной формы с полным просмотром содержимого и простейшим поиском-фильтром, а для выбора значения - выпадающего списка (ComboBox).
S - Справочник. Относительно большая таблица, выходящая за пределы простого каталога. Могут требовать инкрементального поиска, времязависимости, древовидных показов и чего угодно.
R - Реестр. Таблица объектов, для поголовного учета которых собственно предназначена система, например Абонентов, Приборов. Объекты интенсивно добавляются, редактируются, удаляются (или иным образом помечаются как выведенные из учета). Таблицы, как правило, времязависимы. Объем таблиц может быть велик. Просмотр всей таблицы обычно не имеет смысла, из-за чего применяются специальный поиск и фильтрация, зависящие от прикладной специфики. Обычно такие таблицы слегка растут со временем, скажем, на несколько процентов в месяц.
J - Журнал. Таблица фиксирования событий, например журнал бухгалтерских операций, журнал регистрации пользователей в системе. Растет линейно со временем жизни системы (поэтому при больших объемах может понадобиться сбрасывание части информации в архив). Как правило, просмотр всей таблицы не имеет смысла, а нужен только в контексте какого-либо объекта в связи один-ко-многим, например - операции начисления данного абонента, отчет о вчерашних похождениях конкретного пользователя. Также используются для расчетов, отчетов.
Разумеется, можно придумать и другие префиксы.
Чем еще привлекает прибавление префиксов к единственному числу по сравнению с множественным числом - своей однозначностью. Ведь нередко в английском языке образование множественного числа не сводится к простому прибавлению 's' (как в последнем примере с city - cities). А если вы захотите поиском найти по всем файлам проекта фрагменты, где завязаны города, придется вспоминать этот нюанс.
Не будем также забывать, что в качестве имен сущностей мы разрешили себе использовать сокращения и аббревиатуры. А в этом случае добавление "s" или "es" вряд ли добавит ясности и читабельности.
Будем считать, что с правилом именования таблиц мы определились. Разумеется, из любого правила есть исключения. Скажем, как именовать таблицы, отражающие связи "много-ко-многим"?, например вхождение пользователей (сущность user) в группы (сущность ugroup)? Предлагаю использовать специальный префикс "nn_", и получится nn_user_ugroup. Возможны и другие исключения.
Наименование полей
Имена полей таблицы, как правило, составляются из имени сущности (city), смыслового суффикса (id, name, area), и необязательного дополнительного суффикса (применяется для полей внешних ключей в случае множественных ссылок, рассмотрим это ниже). Например
scity - Справочник городов
cityid Ид (PK)
cityname Название
cityarea Площадь (км2)
citypopulcnt Население
cstrtype - Список типов улиц
strtypeid Ид (PK)
strtypename Название
sstreet - Справочник улиц городов
1 streetid Ид (PK)
2 cityid Город.Ид (FK scity )
3 strtypeid Тип улицы.Ид (FK cstrtype)
4 streetname Название
В зале слышны крики ревнителей минимализма и экономии символов. Мы видим, что в имени каждого поля таблицы участвует название сущности, то есть практически повторяется имя таблицы (без префикса). Оправдано ли это? Почему бы всегда не называть идентификаторы просто ID, названия - Name? Ведь в SQL-запросах есть возможность полной нотации имен, с указанием таблицы: WHERE scity.name LIKE "%МОСКВА%" AND sstreet.name LIKE "%ЛЕНИНА%" ?
Первое, на чем споткнется такой подход - внешние ключи. Пусть в списке типов улиц cstrtype ключ мы назвали ID, и ключ улицы тоже назвали ID. Как прикажете назвать поле номер 3 в таблице улиц sstreet, которое ссылается на тип улицы cstrtype? Ах, вот теперь вы хотите назвать его strtype_id? Но тогда получается, что в БД есть поля, содержащие одну и ту же информацию, а названные по-разному. Кто здесь хотел не плодить сущности без необходимости? Для единообразия придется вернуться в cstrtype, и переименовать тамошний ключ ID тоже в strtype_id. К той же печке и пританцевали.
Ну хорошо, не унимаются минималисты. С ключами понятно. А названия, и прочие неключевые поля? Их-то можно назвать просто Name?
Хорошо. Назовем названия города, улицы и типа улицы просто Name, и составим запрос, выводящий "Улица Ленина, Москва".
SELECT cstrtype.name, sstreet.name, scity.name.
Хм.
FROM scity, cstrtype, sstreet
WHERE ... связи между таблицами и условия поиска...
Три поля с одинаковыми названиями в результате запроса. Какое безобразие! Интересно, что на большинстве SQL-серверов ошибки выдано не будет, и строки данных будут правильны. Но имена столбцов! Некоторые SQL-сервера молча переименуют поля, вроде name_1, name_2, или name_a, name_b (кстати, я не припомню, чтобы порядок такого переименовывания где-то документировался). Другие издевательски вернут набор данных с тремя одинаковыми полями - типа вам из погреба виднее, разбирайтесь. Редкий ClientDataSet не выдаст Access Violation от такого зрелища, не говоря уже о безнадежности задачи FieldByName().
В этом месте обычно вспоминают о возможности SELECT cstrtype.name AS strtypename, долго сопят, размышляя о бритве Оккама, а затем лезут в поля таблиц дописывать имя сущности. Опять пританцевали.
Ну хорошо, умоляют последние минималисты. Обозначать принадлежность поля таблице надо. Но зачем же полное имя сущности повторять? Можно ведь сократить - 'STName', 'StrName', 'CName'?...
Нет, господа минималисты! Ведь зачем мы добавляем имя сущности в имя поля? Обеспечить уникальность полей в результатах запросов! А уникальность эта в общем виде должна работать в масштабах базы данных. Вот завтра выйдете на международный рынок, появится у вас понятие стран (Country), и имя страны как сократите - опять "CName"? Поэтому ставим вопрос ребром. Если вы можете сократить имя сущности до удобочитаемой и в то же время уникальной величины - так это сокращение и используйте изначально как имя сущности. Нет - используйте полное. Но везде одно и то же.
С именем сущности в составе названия поля вроде бы разобрались. А что там за смысловой префикс, все эти -id, -name, -label, -area?
Это собственно обозначения содержимого полей. Опять же произвольные английские либо латинизированно-русские слова и сокращения, минимальной длины и максимальной понятности, уникальные теперь уже в пределах таблицы. Но некоторые из них хорошо бы канонизировать, и заключить по ним дополнительные соглашения.
id - внутренний идентификатор, суррогатный ключ. Автоинкрементный. От пользователя скрывается. Никогда не модифицируется.
code - пользовательский идентификатор, уникальный ключ. Например, табельный номер сотрудника. Как правило, неизменный; но при необходимости его можно менять (когда у отдела кадров сносит крышу), причем без каскадных обновлений БД.
name - имя чего-либо (скорее идентификатор, или нечто каноническое)
label - название (обычно человеческое удобное название)
notes - поле типа TEXT, для примечаний
num - номер чего-либо (может быть числовой либо текстовый, например Письмо N "1234/56-789")
...разумеется, список можно продолжать.
Что еще сказать по названиям полей. Как уже было видно из примеров, при построении внешних ключей поля-ссылки на справочники (cityid) рекомендуется называть так же, как они называются в самих справочниках (опять же за исключением множественных ссылок, рассмотренных ниже).
Ставить ли подчеркивание между сущностью и смысловым суффиксом, вроде street_name - дело вкуса. Это красиво выглядит в статье, но отвратительно в программном коде, потому что в точечной нотации objStreet.Street_Name подчеркивание выглядит как разделитель более высокого уровня, чем точка, и сбивает с толку. Кроме того, подчеркивания плохо видны на некоторых шрифтах и на бумажных распечатках. Поэтому подчеркивания я использую редко. В Pascal достаточно придерживаться общепринятой венгерской нотации - начинать каждое слово с заглавной буквы: StreetName, а в SQL чаще всего пишут ключевые слова большими буквами, идентификаторы - в венгерской нотации или просто маленькими буквами.
Иногда в нескольких таблицах встречаются служебные поля, не связанные с какой-либо сущностью, а используемые программистом либо базой данных для служебных целей, например поля вроде Row_ID в Oracle. Здесь нужно следить, чтобы имена сущностей не пересекались с названиями таких полей, иначе служебные поля будут ошибочно "мысленно привязаны" к какой-то нашей таблице.
Множественные ссылки
Иногда в базах данных встречается ситуация, требующая дополнения описанных правил. Рассмотрим пример - точки и вектор, имеющий начало и конец:
SPOINT - Справочник точек
pointid Ид
pointX X
pointY Y
pointName Имя
pointColor Цвет точки
SVECTOR - Справочник векторов
vectorid Ид
pointid_start Начало (FK SPOINT )
pointid_end Конец (FK SPOINT )
vectorColor Цвет вектора
В ситуации, когда из одной таблицы есть более одной ссылки на один и тот же справочник, приходится использовать дополнительный суффикс (_start, _end) в наименованиях полей-ссылок. Это тот редкий случай, когда я использую подчеркивание.
Кроме того, те же дополнительные суффиксы придется использовать и при переобозначении выходных полей запроса, если придется объединять оба экземпляра справочника. Например, выведем список векторов с указанием координат начала и конца:
SELECT vectorid,
ps.pointX AS pointX_start, ps.pointY AS pointY_start,
pe.pointX AS pointX_end, pe.pointY AS pointY_end
FROM svector v, spoint ps, spoint pe
WHERE v.pointid_start = ps.pointid AND v.pointid_end = pe.pointid
Другими словами, если справочник используется в двух вариантах (как _start и как _end), то мы должны быть готовы представить все его данные в этих двух вариантах одновременно добавлением соответствующих дополнительных суффиксов к именам полей, а для пользователя - добавлением слов "Начало", "Конец" к стандартным наименованиям шапок отчета.
Коварство ситуации заключается в том, что помимо очевидного случая (две ссылки из одной таблицы на другую), мы можем прийти к той же проблеме на внешне благополучной схеме БД, если, начав путешествие из какой-либо таблицы по стрелкам внешних ключей, имеем возможность попасть в какой-либо справочник более чем одним путем. Если в каком-либо запросе мы захотим объединения всех промежуточных таблиц, опять придется разруливать варианты справочника.
Небольшое замечание. Если в процессе проектирования наклевывается ситуация с множественными ссылками, особенно более чем в двух вариантах - проверьте, не лучше ли заменить эту схему таблицей один-то-многим. Например, если у города есть руководитель мэрии, и председатель обкома партии:
SCITY - справочник городов
cityid
manid_mer FK sman - люди
manid_partyboss FK sman- люди
...
то это можно выразить иначе:
SCITY- справочник городов
cityid
...
SBOSS - справочник начальников (один город - много начальников)
bossid
cityid FK scity
bstypeid FK cbstype - виды начальников
manid FK sman - люди
и дополнительный справочник, отражающий, в сущности, понятия _mer и _partyboss первоначального варианта:
CBSTYPE - виды начальников
bstypeid
bstypename Название (мэр, партийный лидер и т.д.)
Когда же разумно применять такое преобразование? Например, трудно представить себе, что у вектора появится третья вершина, или что у бухгалтерской проводки появится что-то кроме дебета и кредита - следовательно, таблица 1-N не нужна. А вот новые начальники в городе могут появиться запросто - например, военный комендант, если произойдет военный переворот, и командир партизанского военного округа, возглавляющий ему сопротивление. Баррикады, стрельба, переделка структуры БД... А с 1-N таблицей нам и структуру базы данных менять не надо, только дополнить справочник CBSTYPE!
Другие объекты базы данных
Разумное наименование других объектов базы данных, таких как первичные, уникальные и внешние ключи, помогает прежде всего в отладке программ и импорте внешних данных, когда имя нарушенного ключа выводится в сообщении об ошибке. Текст ошибки станет понятен программисту, даже если ему продиктуют его по телефону. Кроме того, в перехвате ошибок можно отловить известные строки, и снабдить их понятными комментариями. Поэтому:
Первичные ключи называем так: PK_имя_таблицы.
Уникальные: UN_имя_таблицы_имена_всех_полей (если хватит терпения и допустимой длины), или просто UN_имя_тфблицы_номер_ключа_по_порядку.
Внешние ключи: FK_имя_таблицы_имя_поля_ссылки_имя_таблицы-справочника
Заключение
Указанные правила выглядят более естественно при выполнении некоторых дополнительных условий, которые выходят за рамки проблемы наименования, но о которых стоит упомянуть. Как вы уже заметили, в рассмотренных примерах в каждой таблице есть первичный ключ, причем ключ суррогатный. В этом вопросе я являюсь поклонником мнения Анатолия Тенцера, высказанного в статье "Естественные ключи против искусственных ключей". Лучше всего, когда в первичном ключе всего одно поле. Разумные дополнения к этому правилу - это территориально распределенные базы данных, где в первичном ключе участвует еще одно поле - идентификатор узла БД, уже упоминавшиеся таблицы "много-ко-многим" (nn_), и различные реализации времязависимых таблиц с указанием сроков действия каждой записи - эти реквизиты тоже участвуют в PK.
Еще один вопрос, который напрашивается после "упорядочения" названий в БД - как автоматизировать использование этого богатства? У нас есть названия сущностей, в том числе с русской расшифровкой, названия и русская расшифровка названий полей - нельзя ли сохранить эту информацию в БД и использовать ее непосредственно в программе, ее диалогах, фильтрах, отчетах, независимо используемых компонентов и языков? К сожалению, не существует общепринятого способа сохранения таких метаданных для использования различными средами разработки. Все движения в этом направлении намертво завязаны на конкретные продукты (Репозиторий в Delphi, системный словарь в PowerBuilder, конфигуратор в 1C, метаданные в Visual FoxPro), и все они обладают определенными ограничениями и неудобствами. Так что здесь есть о чем помечтать.