Database Design of a value identified by two Primary Keys

Question

I have a table of event_id, role_id, and rank. The following table should help visualize the various outcomes we expect with regards to our constraints:

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

Event_Id is the primary key of the Event table.
Role_Id is the primary key of the Role table.
Using Event and Role we find the rank associated to that role for that given event.

A role can be used in multiple events with different ranks (scenarios 1 and 3)
However, a 2 roles should not have the same rank for the same event. (scenarios 1 and 2)

How can we establish these constraints so that we can identify a rank for a given role in a given event, but the rank of a role is only unique within that event?

Answer 1

You need to have two unique constraints.

The first is your "natural" candidate key of Event_Id plus Role_Id. This will mean you have only one possible value for Rank for any given combination of Event and Role.

Now, to enforce the constraint that any given Event can only have one Role with a given Rank, you need a second unique constraint on the combination of Event_Id plus Rank.

Answer 2

Why Rank has the same candidate primary key for different values?

Rank = 1 means either bad or good...

If Rank was an entity described by a set like:

• 1 -> Bad
• 2 -> Good

Then you could design:

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)
)

In this design, RankId MUST means either Bad or Good, not both.

If, otherwise, RankId = 1 MUST means Bad in one context and Good in another, then:

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')