Ответ 1
Ваш дизайн нарушает Четвертая нормальная форма. Вы пытаетесь сохранить несколько "фактов" в одной таблице, и это приводит к аномалиям.
Таблица Person_Attributes должна выглядеть примерно так: personId jobId houseId restaurantId
Итак, если я свяжусь с одной работой, одним домом, но двумя ресторанами, храню ли я следующее?
personId jobId houseId restaurantId
1234 42 87 5678
1234 42 87 9876
И если я добавлю третий ресторан, я скопирую другие столбцы?
personId jobId houseId restaurantId
1234 123 87 5678
1234 123 87 9876
1234 42 87 13579
Готово! Ой, подождите, что там произошло? Я менял работу одновременно с добавлением нового ресторана. Теперь я неправильно связан с двумя заданиями, но нет возможности различать это и правильно быть связанным с двумя заданиями.
Кроме того, даже если это правильно, чтобы быть связанным с двумя заданиями, не должны ли данные выглядеть так?
personId jobId houseId restaurantId
1234 123 87 5678
1234 123 87 9876
1234 123 87 13579
1234 42 87 5678
1234 42 87 9876
1234 42 87 13579
Он начинает выглядеть как декартово произведение всех различных значений jobId, houseId и restaurantId. Фактически, это потому, что эта таблица пытается хранить несколько независимых фактов.
Для правильной реляционной конструкции требуется отдельная таблица пересечений для каждого отношения "многие ко многим". К сожалению, вы не нашли ярлыка.
(Многие статьи о нормализации говорят, что высшие нормальные формы прошлого 3NF являются эзотерическими, и никогда не приходится беспокоиться о 4NF или 5NF. Пусть этот пример опровергнет это утверждение.)
Повторите свой комментарий об использовании NULL: тогда у вас есть проблема, обеспечивающая уникальность, потому что ограничение PRIMARY KEY требует, чтобы все столбцы были NOT NULL.
personId jobId houseId restaurantId
1234 123 87 5678
1234 NULL NULL 9876
1234 NULL NULL 13579
Кроме того, если я добавлю второй дом или второе задание в приведенную выше таблицу, в какую строку я его вставляю? Вы можете в итоге:
personId jobId houseId restaurantId
1234 123 87 5678
1234 NULL NULL 9876
1234 42 NULL 13579
Теперь, если я disassociate restaurantId 9876, я мог бы обновить его до NULL. Но это оставляет строку всех NULL, которые я действительно должен просто удалить.
personId jobId houseId restaurantId
1234 123 87 5678
1234 NULL NULL NULL
1234 42 NULL 13579
Если бы у меня был отключенный ресторан 13579, я мог бы обновить его до NULL и оставить строку на месте.
personId jobId houseId restaurantId
1234 123 87 5678
1234 NULL NULL 9876
1234 42 NULL NULL
Но не следует ли мне консолидировать строки, перемещая jobId в другую строку, если там есть вакансия в этом столбце?
personId jobId houseId restaurantId
1234 123 87 5678
1234 42 NULL 9876
Проблема заключается в том, что теперь становится все сложнее добавлять или удалять ассоциации, требуя нескольких инструкций SQL для изменений. Вам придется написать много утомительного кода приложения, чтобы справиться с этой сложностью.
Тем не менее, все различные изменения легки, если вы определяете одну таблицу для отношений "много-ко-многим". Вам нужна сложность наличия еще многих таблиц, но, делая это, вы упростите ваш код приложения.
Добавление ассоциации в ресторан - это просто INSERT в таблице Person_Restaurant. Удаление этой ассоциации - это просто DELETE. Неважно, сколько ассоциаций приходится на работу или дома. И вы можете определить ограничение первичного ключа в каждой из этих таблиц пересечений для обеспечения уникальности.