Несколько таблиц соединений между одними и теми же объектами

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

Итак, проблема в том, что у меня есть два объекта с множественными отношениями "многие ко многим" между ними. Две таблицы — «Команды» и «Люди»; Команда состоит из многих Людей, и Человек может иметь одну или несколько ролей в Команде. Роли включают в себя лидера команды, члена команды, последователя команды и т. д. У человека может быть более одной роли для конкретной команды, но в идеале подмножество этих ролей являются взаимоисключающими, а остальные — нет.

Вот решения, которые я рассмотрел:

1) Создайте отдельную соединительную таблицу для каждой роли. Наличие строки в каждой таблице означает, что один человек принадлежит к одной команде, а конкретная таблица указывает роль человека в команде. Взаимоисключающие роли должны быть реализованы на уровне приложений.

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

3) Создайте единую соединительную таблицу и сохраните в ней список логических флагов, по одному для каждой роли. Каждая комбинация человек-команда имеет одну строку в таблице, а флаги определяют роли пользователя в этой команде. Взаимная исключительность может быть реализована на уровне базы данных, поскольку все взаимоисключающие роли могут совместно использовать одно перечисляемое поле в таблице.

4) Создайте две соединительные таблицы. Это своего рода комбинация (2) и (1), которая позволяет обеспечить взаимную исключительность на уровне базы данных. Будет одна соединительная таблица с перечислением взаимоисключающих ролей, а другая соединительная таблица (с перечислением) будет обрабатывать все неисключающие роли.

Я что-то забыл? Какой вариант кажется наиболее естественным?

Спасибо


database database-schema relational-database database-design database-agnostic
person Aaron    schedule 09.05.2015    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Вы наткнулись на модель вечеринки! :)

Просто храните людей и организации в одной таблице. Это называется наследованием одной таблицы. Если вы используете базу данных, например PG, то нули не занимают места.

То же самое с ролями. Храните их в одной таблице, если она не становится громоздкой.

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

person Neil McGuigan    schedule 09.05.2015

arrow_upward
1
arrow_downward

Каждая таблица People и Team представляет сущности. Каждая из таблиц пересечения или соединения представляет отношения между сущностями. Если у вас есть несколько возможных отношений, то имеет смысл иметь несколько соединительных таблиц.

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

create table CaptainOrMember(
    PersonID  int not null,
    TeamID    int not null,
    C_or_M    char( 1 ) not null,
    constraint PK_CaptainOrMember primary key( PersonID, TeamID ),
    constraint FK_CaptainOrMember_Person foreign key( PersonID )
        references People( ID ),
    constraint FK_CaptainOrMember_Team foreign key( TeamID )
        references Team( ID ),
    constraint CK_CaptainOrMember_OneOrOther check( C_or_M in( 'C', 'M' )
);

Это определяет отношения капитана и члена. Можно сделать только одну запись «Человек ‹-> Команда», и она должна быть обозначена буквой «C» или «M». Таким образом, человек может быть капитаном команды или членом команды, но не тем и другим одновременно.

Дополнительным преимуществом этого метода является использование одной соединительной таблицы для двух отношений.

То есть, если у вас нет данных только для капитана и только для члена, которые вы хотите добавить в кортеж отношений. Затем вы можете добавить две таблицы «подсоединения». Вот они для иллюстрации.

alter table CaptainOrMember add constraint UQ_CaptainOrMember_OneOrOther unique( PersonID, TeamID, C_or_M );

create table Captains(
    PersonID  int not null,
    TeamID    int not null,
    C_or_M    char( 1 ) not null,
    ...,
    ...,    <Captain related data>
    ...,
    constraint PK_Captains primary key( PersonID, TeamID ),
    constraint CK_Captains_OneOrOther check( C_or_M = 'C' ),
    constraint FK_Captains_Captain foreign key( PersonID, TeamID, C_or_M )
        references CaptainOrMember( PersonID, TeamID, C_or_M )
);

create table Members(
    PersonID  int not null,
    TeamID    int not null,
    C_or_M    char( 1 ) not null,
    ...,
    ...,    <Member related data>
    ...,
    constraint PK_Members primary key( PersonID, TeamID ),
    constraint CK_Members_OneOrOther check( C_or_M = 'M' ),
    constraint FK_Members_Member foreign key( PersonID, TeamID, C_or_M )
        references CaptainOrMember( PersonID, TeamID, C_or_M )
);

Если запись в таблице CaptainOrMember определяет конкретное лицо как капитана определенной команды, то комбинация этого человека и этой команды не может быть вставлена ​​в таблицу Members. Он может существовать только в таблице Captains. И наоборот.

person TommCatt    schedule 10.05.2015