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.