0

I'm working on a standard relational database that stores options selected by users. I need to store some rules. What is the best way to architect this and keep it normalized?

Examples:

  • If option A and option B are selected, option X must be selected.
  • If option A and option B and option C are selected, option Y must be selected.
  • If option D is selected, option Z cannot be selected.
  • If option E and option F are selected, option Z cannot be selected.

I thought of something like this:

RequiredOptions Table

SelectedOption   RequiredOption
A                X
B                X
A                Y
B                Y
C                Y


ConflictingOptions Table

SelectedOption   ConflictingOption
D                Z
E                Z
F                Z

But one of the problems on the ConflictingOptions table is that if D is selected, it appears that that's allowed unless E and F are selected too. But that's not correct.

I thought about hashing the SelectedOption but wasn't sure if that was necessary.

RequiredOptions Table

SelectedOptionHash   RequiredOption
Hash(A&B)            X
Hash(A&B&C)          Y


ConflictingOptions Table

SelectedOptionHash   ConflictingOption
Hash(D)              Z
Hash(E&F)            Z

This wouldn't be very readable either.

I'm open to any suggestions. If anyone has experience or opinions they'd like to share, it would be greatly appreciated!

mmcfly
  • 834
  • 8
  • 12
  • How are these "rules" used? Are they constraints on tables about selected options, or is your application reproducing functionality of a DBMS & your rules are metadata/constraints in the state of that DBMS you are implementing via a given DBMS? Why not just use DDL/metadata instead of implementing your own? What exactly is the set of possible states that your "examples" are examples of? PS What does "normalized" mean? Without any modifiers it doesn't mean anything in particular, there are many misconceptions about various specific meanings for it, and it doesn't mean "well-designed". – philipxy Dec 03 '19 at 00:40
  • It's similar to selecting options for a car. If user selects a fabric color for the seats, then selects leather, they can't keep the fabric color. Or if they select the technology package, they can't deselect the GPS option. – mmcfly Dec 03 '19 at 18:12
  • Admin users will be able to change these rules through a UI. On normalization, since there can be 1 to N condition combinations that cause a requirement or a conflict, I won't be having repeated columns that contain the condition(s), like: Column1 (RequiredOption), Column2 (Condition1), Column3 (Condition2),... ColumnN (ConditionN-1), where non-needed columns would be null. – mmcfly Dec 03 '19 at 18:25
  • Please clarify via edits, not comments. Please don't insert EDITs/UPDATEs, just make your post the best presentation as of right now. Also you haven't answered all my questions. PS Getting rid of multiple similar columns isn't actually part of "normalize to 1NF"](https://stackoverflow.com/a/40640962/3404097) (or of the other sense of normalize, to higher NFs), although it is a common misconception. PS Entities having shared & non-shared columns is one design pattern for [DB/SQL subtyping/inheritance](https://stackoverflow.com/q/3579079/3404097). – philipxy Dec 04 '19 at 00:23

1 Answers1

0

Its clear there is going to be code to actually implement this, but the tables I believe are this:

First you need a place to store the set, and what they affect

Combination(CombinationID, Description, AffectedOptionID, IsSet)

Then you need the member of the combination

CombinationMember(CombinationID, OptionID)

By using this, you will be able to define any of your defined rules - each of your rules would be a row in the Combination table, including what they affect and how it is effected, and you can have as many steps or members in the rule as you wish.

TomC
  • 2,759
  • 1
  • 7
  • 16