Соединительная таблица и вопрос нормализации

Question

Я с трудом пытаюсь выяснить, приемлем ли следующий шаблон дизайна. У меня есть следующие требования (и некоторые другие) для реляционной модели:

1) Он должен быть в состоянии представлять приложения (такие как AppA, AppB, AppC), каждый со своим набором атрибутов.

2) Каждое приложение может общаться через разные каналы, такие как Internet (Электронная почта, Twitter, Facebook), Phone (SMS, MMS и т. Д.), Чтобы между программами и каналами существовала взаимосвязь "многие ко многим".

3) Существует набор предопределенных идентификаторов (адреса, номера телефонов, учетные записи для входа в систему), которые могут совместно использоваться многими программами, так что, опять же, между программами и идентификаторами существует связь "многие ко многим".

4) Один и тот же идентификатор может отправлять несколько типов сообщений, как и программы (опять же, многие ко многим), но мне нужно иметь возможность ограничивать использование идентификаторов для типа связи для каждого отдельного приложения.

По сути, я создал четыре таблицы, Program, Channel, Ident а также CommunicationType хранить информацию о каждом из них и вместо создания соединительных таблиц для (Program, Channel), (Program, Identifier)и так далее, что только усложнит проект, я создал одну таблицу, состоящую из первичных ключей этих четырех таблиц с уникальным ограничением на (Program, Channel, Ident, CommunicationType), Теперь каждая запись этой таблицы связана с данным сообщением.

Конечно, это решает мою проблему довольно простым способом, но теперь я задаюсь вопросом, является ли это вообще приемлемым, если это противоречит принципам нормализации. Может кто-нибудь, пожалуйста, дайте мне мнение?

Answer 1

По сути, я создал четыре таблицы: Program, Channel, Ident и CommunicationType для хранения информации о каждой из них, и,

Это хорошая идея.

вместо создания таблиц соединений для (Программа, Канал), (Программа, Идентификатор) и т. д., которые могут просто усложнить проектирование, я создал одну таблицу, состоящую из первичных ключей этих четырех таблиц, с уникальным ограничением на (Программа, Channel, Ident, CommunicationType).

При разработке таких таблиц вам нужно быть осторожным с одной вещью. Ваша структура, которая имеет ключ {Program, Channel, Ident, CommunicationType}, допускает любую возможную комбинацию Program и Channel, Channel и Ident, Program и CommunicationType и так далее. Иногда это плохая идея.

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

И это то, что делает это плохой идеей. Похоже, вы говорите, что не каждая комбинация Ident, Program и CommunicationsType действительна.

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

Создайте таблицу с ключом {Program, Ident, CommunicationsType}. Таблица с ключом {Program, Channel, Ident, CommunicationType} может установить ссылку на внешний ключ.

Создайте столько таблиц, сколько потребуется для реализации всех известных вам ограничений. Больше таблиц означает, что проверки целостности данных проще. (Вам может понадобиться больше таблиц, чем я упомянул. Не думайте, что им нужно иметь два столбца; им может понадобиться больше.)

Не совсем понятно, что вам нужна таблица с ключом {Program, Channel}. Но если вы это сделаете, то вам нужно что-то построить таблицы по этим направлениям. (Воздушный кодекс.)

create table pc (
    program_name varchar(10) not null references programs (program_name),
    channel_name varchar(10) not null references channels (channel_name),
    primary key (program_name, channel_name)
);

create table pict (
    program_name varchar(10) not null,
    channel_name varchar(10) not null,
    comm_type varchar(10) not null references communication_type (comm_type),
    primary key (program_name, channel_name, comm_type),
    foreign key (program_name, channel_name) 
        references pc (program_name, channel_name) 
);

create table your-table-name (
    program_name varchar(10) not null,
    channel_name varchar(10) not null,
    comm_type varchar(10) not null,
    ident varchar(10) not null,
    primary key (program_name, channel_name, comm_type, ident),
    foreign key (program_name, channel_name, comm_type) 
        references pict (program_name, channel_name, comm_type),
    foreign key (ident) references ident (ident)
);

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

Я не уверен, что вы подразумеваете под "если это наносит ущерб принципам нормализации". Таблица с первичным ключом из четырех столбцов не нарушает ни одну из нормальных форм только по этой причине, хотя это может быть по другим причинам. Неспособность реализовать все известные ограничения - это, как правило, неоптимальный дизайн, но не потому, что он нарушает любую из нормальных форм.

Answer 2

Я бы не стал этого делать.

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

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

Answer 3

Извините за предоставление вам ответа, который просит дополнительную информацию. Моя репутация на данный момент не позволяет комментировать...

Я не вижу ничего плохого в выбранном дизайне на основе объяснения.

Однако, чтобы действительно ответить на ваш вопрос, было бы полезно понять, почему вы выбрали этот дизайн.

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

Когда вы найдете сообщение, вам все равно придется присоединиться к одной или нескольким другим таблицам, чтобы получить доступ к информации, которая составляет сообщение.

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