Блог

У меня есть одна таблица [Пользователи] и другая таблица [Администраторы], связанные 1: 0 .. 1 . Лучше ли объединять эти таблицы? Я прочитал много ответов на SO, в которых указано, что разделение таблиц необходимо только для отношений «один ко многим».

Я обосновываю их разделение тем, что я могу ссылаться на администраторов с помощью AdministratorId, а не общего userId. В других таблицах у меня есть поля, которые должны содержать только имя администратора, поэтому это действует как проверка ссылок.

Существует эмпирическое правило, согласно которому таблица либо моделирует сущность / класс, либо отношения между сущностями / классами, но не оба. Однако это всего лишь эмпирическое правило: никогда не говори «никогда»!

В SQL обычно возникает проблема с выделенными таблицами отношений 1: 1, поскольку единственными обычно встречающимися ограничениями между таблицами являются внешние ключи. Однако для FK не требуется, чтобы значение существовало в ссылающейся таблице. Это делает отношение 1: 0 ..1 («один к нулю или единица»), что обычно приемлемо.

Строгое соотношение 1: 1 требует обходного пути. Поскольку в SQL отсутствует множественное присвоение, обходной путь обычно включает обращение к процедурному коду, например, к двум отложенным «двунаправленным» FK; триггерам; принудительному обновлению через хранимые в CRUD процедуры; и т.д.

Напротив, моделировать отношения 1: 1 в одной таблице легко: объявите оба столбца как NOT NULL !

Я думаю, что лучший вариант — иметь две таблицы для двух разных объектов, Users и Administrators , возможно, с одним и тем же Primary Key .

 CREATE TABLE User
( UserId int
, ... other data               --- data for all users
, PRIMARY KEY (UserId)
) ;

CREATE TABLE Administrator
( AdministratorId int
, ... other data               --- data for administrators only
, PRIMARY KEY (AdministratorId)
, FOREIGN KEY AdministratorId
    REFERENCES User(UserId)
) ;
  

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

 CREATE TABLE OtherTable
( OtherTableId int
, AdministratorId int
, ... other data
, ...
, FOREIGN KEY AdministratorId
    REFERENCES Administrator(AdministratorId)
) ;
  

Преимущества:

• ссылочная целостность реализована тривиально.
• соответствующие данные (для пользователей и администраторов) можно хранить в соответствующих таблицах, чтобы в таблицах было меньше столбцов и меньше NULL данных.

• любой запрос, которому требуется таблица JOIN to Administrator , должен будет искать всего несколько строк по сравнению с (возможно, огромным) количеством строк User таблицы. Если у вас есть только одна таблица, вы получите код типа:

  WHERE User.admin = True

  которые, возможно, нелегко оптимизировать.

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

Позвольте мне сначала придерживаться названия вашего вопроса.

Да, отношения «один к одному» могут быть разделены на разные таблицы, когда вам нужно следующее:

1. Модульность
2. Безопасность / Абстракция данных

Я думаю, что бит модульности вполне ясен.

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

Например, давайте предположим, что у вас есть приведенный ниже сценарий:

При этом у клиента есть фиктивный идентификатор для passport, а в таблице Passport нет соответствующей информации о клиенте. Следовательно, человек, имеющий доступ к обеим таблицам, может сопоставить идентификатор паспорта только с клиентом и увидеть всю картину.

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

Ключевой элемент настройки отношения 1-1 заключается в том, чтобы принудительно использовать его как 1-1. Самый простой способ сделать это — сделать поле FK также полем PK во второй таблице.