Limit the relationship of foreign keys to strings of related subtypes

Question

Overview. I try to imagine several types of objects in a database that have a total number of common fields, and then each has additional fields that are not shared with other types of entities. The workflow often involved joining objects together, so I decided to have a table with their common fields, and then each object would have its own table with its additional fields.

For implementation: there is a common field, "status", which has all the entities; however, some organizations will only support a subset of all possible statuses. I also want each entity type to use its own subset of statuses. Finally, I also want to include this field when listing entities together, so the exclusion from the set of common fields seems incorrect, because this will require the union of specific type tables and the absence of an "implementation interface" in SQL that the inclusion of this field will be conditional.

Why Im here: Below is a solution that is functional, but I'm wondering if there is a better or more general way to solve the problem. In particular, the fact that this decision requires me to make an excessive restriction unique, and the excess state field feels inelegant.

create schema test;

create table test.statuses(
    id      integer     primary key
);
create table test.entities(
    id      integer     primary key,
    status  integer,
    unique(id, status),
    foreign key (status) references test.statuses(id)
);

create table test.statuses_subset1(
    id      integer     primary key,
    foreign key (id) references test.statuses(id)
);
create table test.entites_subtype(
    id integer primary key,
    status integer,
    foreign key (id) references test.entities(id),
    foreign key (status) references test.statuses_subset1(id),
    foreign key (id, status) references test.entities(id, status) initially deferred
);

Some data:

insert into test.statuses(id) values
    (1),
    (2),
    (3);
insert into test.entities(id, status) values
    (11, 1),
    (13, 3);
insert into test.statuses_subset1(id) values
    (1), (2);
insert into test.entites_subtype(id, status) values
    (11, 1);

-- Test updating subtype first
update test.entites_subtype
    set status = 2
    where id = 11;
update test.entities
    set status = 2
    where id = 11;

-- Test updating base type first
update test.entities
    set status = 1
    where id = 11;
update test.entites_subtype
    set status = 1
    where id = 11;

/* -- This will fail
insert into test.entites_subtype(id, status) values
    (12, 3);
*/

Answer 1

Simplify the construction of MATCH SIMPLEfk constraint behavior

If at least one column has a multi-column external constraint with MATCH SIMPLEdefault behavior NULL, the constraint is not met. You can build on this to greatly simplify your design.

CREATE SCHEMA test;

CREATE TABLE test.status(
   status_id  integer PRIMARY KEY
  ,sub        bool NOT NULL DEFAULT FALSE  -- TRUE .. *can* be sub-status
  ,UNIQUE (sub, status_id)
);

CREATE TABLE test.entity(
   entity_id  integer PRIMARY KEY
  ,status_id  integer REFERENCES test.status  -- can reference all statuses
  ,sub        bool      -- see examples below
  ,additional_col1 text -- should be NULL for main entities
  ,additional_col2 text -- should be NULL for main entities
  ,FOREIGN KEY (sub, status_id) REFERENCES test.status(sub, status_id)
     MATCH SIMPLE ON UPDATE CASCADE  -- optionally enforce sub-status
);

NULL ( ):

• NULL postgresql DB?

BTW, :

refcolumn , reftable.

-:

INSERT INTO test.status VALUES
  (1, TRUE)
, (2, TRUE)
, (3, FALSE);     -- not valid for sub-entities

INSERT INTO test.entity(entity_id, status_id, sub) VALUES
  (11, 1, TRUE)   -- sub-entity (can be main, UPDATES to status.sub cascaded)
, (13, 3, FALSE)  -- entity  (cannot be sub,  UPDATES to status.sub cascaded)
, (14, 2, NULL)   -- entity  (can    be sub,  UPDATES to status.sub NOT cascaded)
, (15, 3, NULL)   -- entity  (cannot be sub,  UPDATES to status.sub NOT cascaded)

SQL Fiddle ( ).

FK

(status_id, sub) status ( 2 status_id) fk:

CREATE TABLE test.status(
   status_id  integer
  ,sub        bool DEFAULT FALSE
  ,PRIMARY KEY (status_id, sub)
);

CREATE TABLE test.entity(
   entity_id  integer PRIMARY KEY
  ,status_id  integer NOT NULL  -- cannot be NULL in this case
  ,sub        bool NOT NULL     -- cannot be NULL in this case
  ,additional_col1 text
  ,additional_col2 text
  ,FOREIGN KEY (status_id, sub) REFERENCES test.status
     MATCH SIMPLE ON UPDATE CASCADE  -- optionally enforce sub-status
);

INSERT INTO test.status VALUES
  (1, TRUE)       -- can be sub ...
  (1, FALSE)      -- ... and main
, (2, TRUE)
, (2, FALSE)
, (3, FALSE);     -- only main

Etc.

:

• MATCH FULL vs MATCH SIMPLE
• , NOT NULL
• ,

, - dba.SE:

• " "

... , . . :

• PostgreSQL