I'm designing a database and can't figure out how to model referential integrity.
I have the following tables
CREATE TABLE Groups ( GroupId INT PRIMARY KEY, GroupName VARCHAR(50) ) CREATE TABLE GroupMembers ( GroupId INT NOT NULL, MemberId INT NOT NULL, MemberName VARCHAR(50), CONSTRAINT pk_GroupMember PRIMARY KEY (GroupId, MemberId) ) CREATE TABLE Missions ( MissionId INT PRIMARY KEY, GroupId INT NOT NULL, MissionName VARCHAR(50) ) CREATE TABLE MissionRollAssignments ( MissionId INT NOT NULL, MemberId INT NOT NULL, MemberRoll VARCHAR(50) --This will probably become RollId and move details to another table )
Every mission will have assignments for some/all members of the corresponding group. There will be several missions associated with each group, but only one mission per group is active at a given time.
My question is:
Is it possible to enforce referenciay integrity for roll assignments such that only members of the corresponding group (given by the MissionId) are selected? I know I can filter this from the GUI, but I'd feel more comfortable if I could create a FK constraint from MissionRollAssignments to GroupMembers while considering the GroupId indicated in the Mission.
A second question would be if you guys think this is a good way to model my domain, or maybe I should try a different approach.
Thanks in advance for any help on this.