1

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.

1
  • 2
    The problem is that a foreign key needs to reference all of the columns in the key. You are trying to reference part of a candidate key. Obviously that doesn't make sense. If your intersection table is truly referencing just the group IDs, then you need to normalize the rule and action tables to make their group IDs unique without reference to other columns. – Joel Brown Jul 14 '14 at 10:52
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.