Проектирование базы данных значения, идентифицируемого двумя первичными ключами

Question

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

   scenario    Event_Id    Role_Id    Rank
      1            1           1        1    good
      2            1           2        1    bad
      3            2           1        1    good

Event_Id - это первичный ключ таблицы Event.
Role_Id - это первичный ключ таблицы Role.
Используя событие и роль, мы находим ранг, связанный с этой ролью для данного события.

Роль может использоваться в нескольких событиях с разными рангами (сценарии 1 и 3).
Однако две роли не должны иметь одинаковый ранг для одного и того же события. (сценарии 1 и 2)

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

Answer 1

Вам нужно иметь два уникальных ограничения.

Первый - это ваш "естественный" ключ-кандидат Event_Id плюс Role_Id, Это будет означать, что у вас есть только одно возможное значение для Rank для любой комбинации событий и ролей.

Теперь, чтобы применить ограничение на то, что любое событие может иметь только одну роль с данным рангом, вам нужно второе уникальное ограничение на комбинацию Event_Id плюс Rank,

Answer 2

Зачем Rank имеет один и тот же кандидатный первичный ключ для разных значений?

Rank = 1 значит или плохо или хорошо...

Если Rank была ли сущность описана набором вроде:

• 1 -> Плохо
• 2 -> Хорошо

Тогда вы можете разработать:

create table Events(EventId int, primary key(EventId))
create table Roles(RoleId int, primary key(RoleId))
create table Ranks(RankId int, Title varchar(...), primary key(RankId))    
create table EventRoleRank(
    EventId int,
    RoleId int,
    RankId int,
    primary key(EventId, RoleId, RankId)
)

В этом дизайне RankId ДОЛЖЕН означать либо Bad или же Goodне оба.

Если иначе, RankId = 1 ДОЛЖЕН означать Bad в одном контексте и Good в другом, то:

create table Events(EventId int, primary key(EventId))
create table Roles(RoleId, primary key(RoleId))
create table EventRoleRank(
    EventId int,
    RoleId int,
    RankId int,
    RankTitle varchar(...),
    primary key(EventId, RoleId)
)

insert into EventRoleRank(1, 1, 1, 'good')
insert into EventRoleRank(1, 2, 1, 'bad')