How to map different many to manys with same constraint

Question

I have a data model that i have some concerns over. Here it is:

My concern is that it's possible to assign an application to a member, and then assign a role that is from a different application to the member.

Now, I know I can put a constraint on this to ensure that doesn't happen, but that seems like a bandaid. I would rather design the model so that a constraint was not required.

Can anyone suggest how to alter the model to ensure that a member can only be assigned roles from an application it is assigned to?

Answer 1

Usually, you run into this kind of problem when you've split a key. Fixing that split key, then using overlapping foreign key constraints is usually what you're looking for.

create table cmember (
  cmemberid integer primary key,
  username varchar(15) not null,
  emailaddress varchar(64) not null
);

create table application (
  applicationid integer primary key,
  description varchar(50) not null
);

create table member_application (
  cmemberid integer not null references cmember (cmemberid),
  applicationid integer not null references application (applicationid),
  primary key (cmemberid, applicationid)
);

create table role (
  roleid integer primary key,
  rolename varchar(25) not null
);

create table crole (
  croleid integer not null references role (roleid),
  -- Include the application id in this table . . .
  applicationid integer not null references application (applicationid),
  -- and make it part of the primary key.
  primary key (croleid, applicationid)
);

create table member_role (
  cmemberid integer not null references cmember (cmemberid),
  croleid integer not null,
  applicationid integer not null,
  primary key (cmemberid, croleid, applicationid),
  -- Note the overlapping foreign key constraints.
  foreign key (croleid, applicationid) references crole (croleid, applicationid),
  foreign key (cmemberid, applicationid) references member_application (cmemberid, applicationid)
);

insert into cmember values (1, 'A', 'A@b.com');
insert into cmember values (2, 'B', 'B@b.com');

insert into application values (1, 'App 1');
insert into application values (2, 'App 2');

insert into member_application values (1, 1);
insert into member_application values (2, 2);

insert into role values (1, 'Admin');

insert into crole values (1, 1);
insert into crole values (1, 2);

insert into member_role values (1, 1, 1);
insert into member_role values (2, 1, 2);

Member 1 is assigned only to application 1. So trying to insert a row that references application 2 should fail.

insert into member_role values (1,1,2);
ERROR:  insert or update on table "member_role" violates foreign key constraint "member_role_cmemberid_fkey1"
DETAIL:  Key (cmemberid, applicationid)=(1, 2) is not present in table "member_application".