We have the generic idea of a rule with specific implementation details stored in their own table. We currently have the following schema:
Rule
* id
* name
* ruleAId
* ruleBId
* ....
* ruleNId
* created
* updated
RuleA
* id
* ...
RuleB
* id
* ...
....
RuleC
* id
* ...
The part we are not enamoured with is having a nullable column for each of the specific rule implementations.
We have two possibly solutions, but both have significant short comings:
- Move the relationship to specific implementation - DOS the database
Store the rule Id on the specific implementation table, so our schema becomes:
RuleA
* id
* ruleId
* ...
Rule
* id
* name
* created
* updated
This as the title suggests, would mean having N queries each time you needed to look up a rule implementation. Presumably bad for performance.
- Forgo referential integrity - Yolo integrity
If we remove the need for referential integrity from the Rule table, we'd only require a single column to store the specific implementation primary key and an enumeration to tell us which table the id is for, giving us:
Rule
* id
* name
* implementationType
* implementationId
* created
* updated
RuleA
* id
...
Ideally we'd be able to have option two WITH Referential integrity. Where instead of implementationId being a bigInt it could be the FK or RuleA or RuleB or ... RuleN.
This can be encoded in the application itself, but it is nice for the database to keep us honest.
We are heading towards option two, but am interested if people think I have missed a more idiomatic SQL solution?
Aucun commentaire:
Enregistrer un commentaire