Enforce referential integrity between two groups of data I have two sets of records:


A collection of rules.
A collection of actions to perform based on those rules.


What I'm trying to model is something like this:


Multiple rules can be grouped together to form a composite rule.
Multiple actions can be grouped together to form a composite action.
When a rule(set) matches the input the corresponding action(s) should be performed.


Both the set of rules and the set of actions change very infrequently (once a year, if that). Because the data is pretty much read-only I came up with the following design.

A short note:
I explictly avoided adding extra lookup tables to define the group relationships, in the hope that doing so would both simplify and speed up my SELECT query (it will be run often). I'm open to the suggesting of adding them, but even with them in a design I'm still struggling to get the referential consistency I'm after:

CREATE TABLE Rules
(
  ID INT NOT NULL IDENTITY(1,1),
  GroupID INT NOT NULL,

  ... columns required by a rule

  CONSTRAINT PK_Rules_ID PRIMARY KEY (ID),
  CONSTRAINT UK_Rules UNIQUE(GroupID, other columns...)
)

CREATE TABLE Actions
(
  ID INT NOT NULL IDENTITY(1,1),
  GroupID INT NOT NULL,

  ... columns required by an action

  CONSTRAINT PK_Actions_ID PRIMARY KEY (ID),
  CONSTRAINT UK_Actions UNIQUE(GroupID, other columns...)
)

CREATE TABLE RuleToActionMappings
(
  RuleGroup_ID INT NOT NULL,
  ActionGroup_ID INT NOT NULL

  -- Note: These constrains are invalid, but I wished they weren't!
  CONSTRAINT FK_RTAM_RuleGroupID_ON_Rules_ID FOREIGN KEY (RuleGroup_ID) REFERENCES Rules(GroupID) ON DELETE CASCADE,
  CONSTRAINT FK_RTAM_ActionGroupID_ON_Actions_ID FOREIGN KEY (ActionGroup_ID) REFERENCES Actions(GroupID) ON DELETE CASCADE
)


In which I expect to insert multiple entries into both the Rules and Actions tables grouped by a common GroupID value. I could later query these tables in the following manner:

SELECT Actions.* FROM Actions
JOIN RuleToActionMappings As M ON M.ActionGroup_ID = Actions.GroupID
JOIN Rules ON M.RuleGroup_ID = Rules.GroupID
WHERE Rules.SomeColumn = 'SomeValue'


The problem with this design is there's no way to place the foreign key constraints I want/need on the RuleToActionMappings table, and the Rules and Actions tables are relying on a convention rather than something more concrete to ensure uniqueness.
Thanks @Joel Brown, your comment pointed me in the direction I needed to go. This is what I ended up with, which satisfies all of my requirements:

CREATE TABLE RuleGroups
(
    ID INT NOT NULL IDENTITY(1,1)
    CONSTRAINT pk_rulegroups PRIMARY KEY (ID)
)

CREATE TABLE Rules
(
    ID INT NOT NULL IDENTITY(1,1),
    GroupID INT NOT NULL,

    RequiredKeyInSourceData INT not null,
    RequiredValue int null

    CONSTRAINT pk_rules primary key (ID),
    CONSTRAINT fk_rules_groupID_on_rulegroups_ID FOREIGN KEY (GroupID) REFERENCES RuleGroups(ID) ON DELETE CASCADE
)

CREATE TABLE ActionGroups
(
    ID INT NOT NULL IDENTITY(1,1)
    CONSTRAINT pk_actiongroups PRIMARY KEY (ID)
)

CREATE TABLE Actions
(
    ID INT NOT NULL IDENTITY(1,1),
    GroupID INT NOT NULL,

    SomeColumn INT,
    SomeOtherColumn INT
    -- ... more columns

    CONSTRAINT pk_actions primary key (ID),
    CONSTRAINT fk_actions_groupID_on_actiongroups_ID FOREIGN KEY (GroupID) REFERENCES ActionGroups(ID) ON DELETE CASCADE
)

CREATE TABLE RuleToActionMappings
(
    RuleGroup_ID INT NOT NULL,
    ActionGroup_ID INT NOT NULL

    CONSTRAINT pk_mappings PRIMARY KEY (RuleGroup_ID, ActionGroup_ID),
    CONSTRAINT uc_mappings_rulegroup_ID UNIQUE (RuleGroup_ID),
    CONSTRAINT uc_mappings_actiongroup_ID UNIQUE (ActionGroup_ID),
    CONSTRAINT fk_rulegroup_ID_on_rulegroups_ID FOREIGN KEY (RuleGroup_ID) REFERENCES RuleGroups(ID) ON DELETE CASCADE,
    CONSTRAINT fk_actiongroup_ID_on_actiongroups_ID FOREIGN KEY (ActionGroup_ID) REFERENCES ActionGroups(ID) ON DELETE CASCADE
)


How I'm using it:
Get the rule groups from the DB and match the input data against them:

select Rules.GroupID, rules.RequiredKeyInSourceData, rules.RequiredValue from Rules


Then in the application code:


partition the rules into sets (by GroupID)
match the sets against the input data


stop when a complete set matches or all rulesets have been analyzed
exec a select query to find the corresponding action set





select Actions.* from Actions
join RuleToActionMappings as m on m.ActionGroup_ID = Actions.GroupID
where m.RuleGroup_ID = @MatchedRuleGroupID