lundi 23 février 2015

How to modelling a union type in PostgreSQL


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:



  1. 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.



  1. 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