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?

primary key = Event_id + Role_Id and unique key = Event_id + Rank — Abra, Jul 30 '18 at 01:39

Joel Brown · Accepted Answer · 2018-07-30T01:43:19.797

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.

edited Jul 30 '18 at 01:43

answered Jul 30 '18 at 01:32

Joel Brown

14,123
4
52
64

This. I didn't think about making a unique composite constraint. Thank you – user3466773 Jul 30 '18 at 01:46

Marcus Vinicius Pompeu · Answer 2 · 2018-07-30T01:50:27.430

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

Rank is just a number that identifies the order of priority for a role in a given event. I don't think it needs its own table, any event-specific data can be found using the event and role ids (rank, permissions, etc.) — user3466773, Jul 30 '18 at 01:48
@user3466773, then, I think the second solution fits better. You may, event, rename `RankId` to `RankOrder` — Marcus Vinicius Pompeu, Jul 30 '18 at 01:51

Database Design of a value identified by two Primary Keys

2 Answers2