6

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?

Adding an example schema:

CREATE SEQUENCE s_rulea_id;
CREATE TABLE rulea (id BIGINT PRIMARY KEY default nextval('s_rulea_id'));
CREATE SEQUENCE s_ruleb_id;
CREATE TABLE ruleb (id BIGINT PRIMARY KEY default nextval('s_ruleb_id'));
CREATE SEQUENCE s_rulec_id;
CREATE TABLE rulec (id BIGINT PRIMARY KEY default nextval('s_rulec_id'));
CREATE SEQUENCE s_rulen_id;
CREATE TABLE rulen (id BIGINT PRIMARY KEY default nextval('s_rulen_id'));

CREATE SEQUENCE s_rule_id;
CREATE TABLE rule (id BIGINT PRIMARY KEY default nextval('s_rule_id'),
                  name CHARACTER VARYING(128) NOT NULL,
                  ruleAid BIGINT DEFAULT NULL references rulea(id),
                  ruleBid BIGINT DEFAULT NULL references ruleb(id),
                  ruleCid BIGINT DEFAULT NULL references rulec(id),
                  ruleNid BIGINT DEFAULT NULL references rulen(id));

INSERT INTO ruleb (id) VALUES (nextval('s_ruleb_id')); 
INSERT INTO ruleb (id) VALUES (nextval('s_ruleb_id')); 
INSERT INTO ruleb (id) VALUES (nextval('s_ruleb_id')); 

INSERT INTO ruleb (id) VALUES (nextval('s_ruleb_id')); 
INSERT INTO ruleb (id) VALUES (nextval('s_ruleb_id')); 
INSERT INTO ruleb (id) VALUES (nextval('s_ruleb_id')); 

INSERT INTO rulec (id) VALUES (nextval('s_rulec_id')); 
INSERT INTO rulec (id) VALUES (nextval('s_rulec_id')); 
INSERT INTO rulec (id) VALUES (nextval('s_rulec_id')); 


INSERT INTO rule (name,ruleBid) VALUES ('B 1',1); 
INSERT INTO rule (name,ruleBid) VALUES ('B 2',2); 
INSERT INTO rule (name,ruleBid) VALUES ('B 4',3); 
INSERT INTO rule (name,ruleBid) VALUES ('B 4',4); 
INSERT INTO rule (name,ruleBid) VALUES ('B 5',5); 
INSERT INTO rule (name,ruleBid) VALUES ('B 6',6); 

INSERT INTO rule (name,ruleCid) VALUES ('c 1',1); 
INSERT INTO rule (name,ruleCid) VALUES ('C 2',2); 
INSERT INTO rule (name,ruleCid) VALUES ('C 3',3); 

select * from rule
8
  • I tried and failed to understand your situation from the given description. Actual CREATE TABLE scripts for your options might help to clarify. And, as always, your version of Postgres. – Erwin Brandstetter Feb 24 '15 at 1:33
  • Ahh sorry, just seen this now. I'll amend in a moment. – Jonoabroad Feb 24 '15 at 6:48
  • Postgres version 9.x – Jonoabroad Feb 24 '15 at 7:06
  • 1
    "9.x" covers four different major versions (and in fact is the only "version" that is still maintained) – a_horse_with_no_name Feb 24 '15 at 7:32
  • o_O wouldn't they be minor versions? 9 being the major version? – Jonoabroad Feb 25 '15 at 0:40

Your Answer

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

Browse other questions tagged or ask your own question.