Блог

Главная - Вопросы по программированию - Взаимно однозначное отношение в базе данных, внешний ключ должен быть в TableA или TableB?

Взаимно однозначное отношение в базе данных, внешний ключ должен быть в TableA или TableB?

#sql #database

#sql #База данных

Вопрос:

Пример сценария: у 1 сотрудника будет только 1 адрес, а 1 адрес будет принадлежать только этому 1 сотруднику. Ниже приведены 2 варианта, которые являются правильными или ни тем, ни другим? Больше всего меня смущает, должен ли я поместить первичный ключ сотрудника в таблицу адресов или поместить первичный ключ адреса в таблицу сотрудников?

Я использую PostgreSQL, поскольку некоторым из вас нужно знать, какую базу данных я использую.

Вариант 1

 Employee 
EmployeeId (PK) 
EmployeeName 
AddressId (FK)

Address 
AddressId (PK) 
AddressLine1

Вариант 2

 Employee 
EmployeeId (PK) 
EmployeeName

Address 
AddressId (PK) 
AddressLine1 
EmployeeId (FK)

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

1. Что, если два сотрудника живут по одному адресу? Что происходит, когда сотрудник перемещается?

2. Если два сотрудника живут по одному адресу, это было бы хорошо, потому что первичный ключ в таблице адресов всегда будет разным. Если сотрудник перемещается, конкретный AddressID также должен быть обновлен.

3. Какую СУБД вы используете? На самом деле невозможно иметь истинные отношения 1: 1, если ваша СУБД не поддерживает отложенные ограничения, и, к сожалению (например) MS SQL Server их не поддерживает. Альтернативный подход заключается в использовании a VIEW , который представляет допустимые парные данные, в то время как базовая таблица скрыта от кода приложения и запросов.

4. Я думаю, вы слишком жестки — когда дело доходит до хранения почтовых адресов для других объектов в 1:1 отношениях, проще просто сделать Address таблицу простым хранилищем данных, которое не содержит собственных FK, что также позволяет использовать Addresses таблицу для хранения адресов, не Employee относящихся к записям, например, клиенты.

5. Объясните, почему вы считаете, что необходимы две таблицы.

Ответ №1:

Есть ли у сотрудника адрес или у адреса есть сотрудник?

Я бы сказал, у сотрудника есть адрес. Я бы подумал, что сотрудники имеют первичный ключ и атрибуты сотрудника.

таблица сотрудников:

  • идентификатор сотрудника
  • атрибуты сотрудника
  • …

Нужен ли адресу первичный ключ — это другой вопрос. Но, если вы затем захотите предоставить сотруднику дополнительный атрибут «address», который находится в отдельной таблице, я бы сначала подумал о добавлении внешнего ключа в таблицу адресов, чтобы указать, какому сотруднику принадлежит этот адрес:

адресная таблица:

  • идентификатор сотрудника
  • атрибуты адреса
  • …

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

Если вы хотите, чтобы несколько сотрудников использовали один и тот же адрес, вы могли бы, например, ввести первичный ключ в таблицу адресов и использовать промежуточную таблицу:

адресная таблица:

  • идентификатор адреса
  • атрибуты адреса
  • …

таблица employee_address:

  • идентификатор сотрудника
  • идентификатор адреса

В этом случае оба поля в таблице employee_address являются внешними ключами. Не имеет смысла, что это будут первичные ключи. Здесь, я думаю, вы согласитесь, первичные ключи находятся в таблицах employee и address .

Тогда, отвечая на ваш первоначальный вопрос, я бы, естественно, поместил первичный ключ сотрудника в таблицу адресов, чтобы показать, что адрес принадлежит сотруднику. Но это может быть не так просто. Вы должны учитывать варианты использования, например, имеют ли два сотрудника один и тот же адрес и могут ли сотрудники иметь несколько адресов. Или у сотрудников может не быть адреса, или у адреса может не быть сотрудников?

Ответ №2:

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

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

 Employee 
EmployeeId (PK) (FK)
EmployeeName 

Address 
AddressId (PK) (FK)
AddressLine1

Чтобы вставлять, обновлять или изменять данные, оба FK должны быть отложены. Отсрочка — это стандартная функция SQL, которую, как я знаю, по крайней мере, реализуют PostgreSQL и Oracle.

Если вы хотите пойти еще дальше, имя ПК в address таблице может быть EmployeeId вместо AddressId , чтобы подчеркнуть, что это одно и то же.

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

1. Другой подход заключается в том, чтобы просто встроить поля адресов в Employee таблицу — это единственный способ гарантировать требуемые 1:1 связи в системах, которые не поддерживают переносимые ограничения, без использования таких трюков, как VIEW s .

2. @Dai Верно, если только каждая таблица (employee и address) не имеет своих собственных отношений с другими таблицами. Если они становятся единой таблицей, то все отношения объединяются в единую супер-сущность, что может быть или не быть желательным. Но да, если нет возможности отсрочки, это, вероятно, лучший вариант.

3. Я очень расстраиваюсь из-за сложности и нелогичности, связанных с моделированием бизнес-данных / данных домена и отношений в базах данных SQL, теперь, когда я был избалован такими языками, как Rust и TypeScript, которые позволили мне написать краткий и чрезвычайно выразительный код, а также проверить правильность — но верно обратноес реляционными базами данных: я не видел каких-либо фундаментальных улучшений в способности моделирования данных (помимо отложенных ограничений). Этого почти достаточно, чтобы я захотел уйти и написать свой собственный сервер базы данных хранилища объектов…

4. @TheImpaler не могли бы вы взглянуть на свой ответ с ответом Кази? Он предположил, что одной таблицы должно быть достаточно, в то время как вы и Dai также предлагаете иметь 2 таблицы с отложенным FK.

5. @AnonymousProgrammer Оба подхода (одна таблица. против нескольких таблиц) допустимы — мы не можем сказать, какой подход лучше другого, не зная больше о требованиях вашего приложения и проекта (а также пытаясь предвидеть вероятные будущие проектные решения).

Ответ №3:

Для взаимно однозначного отношения нет причин иметь несколько таблиц. Добавьте столбец address и AddressLine1 в таблицу Employee, как показано ниже.

Employeeid (PK) Employeeename Адрес AddressLine1

Это упростит ваш дизайн и дизайн пользовательского интерфейса. И взаимно однозначное отношение будет принудительно поддерживаться без каких-либо ограничений.

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

1. Что, если у меня много взаимно однозначных отношений, не будет ли добавлена основная таблица со многими столбцами?

2. @AnonymousProgrammer Нет такого понятия, как «слишком много столбцов», если вы не скажете нам, что означает «слишком много».

3. @AnonymousProgrammer Я видел приложения HR с 50 столбцами в таблице Employee вместе со многими отношениями «один ко многим». И это приложение работает бесперебойно в более чем 40 средних и крупных компаниях.

4. У меня также есть таблица из 500 столбцов, а размер этой таблицы составляет 2,8 терабайта. У меня все работает нормально. Итак, ваша цель покажет вам, как вы должны поддерживать свою таблицу. Но слишком много ненужных таблиц может увеличить сложность.

5. @Dai Я не совсем уверен, что «слишком много» — это сколько, потому что я никогда раньше не сталкивался с огромной базой данных. Я просто предположил, что слишком много столбцов может вызвать проблемы с производительностью, а также моя предыдущая практика — это то, что я показал в вопросе, это плохая практика??