Postgresql – Schema to generalise potentially parametrised status

database-designpostgresqlschema

Let’s say I have some entity:

create table entities (
  id serial primary key,
  foo text
);

…and I want to assign it some state, which has N possible values. Some of said states also come with a parameter. For example:

  • foo
  • bar
  • quux, 10
  • quux, 20
  • quux, 100

I original set this up as a separate table, with the states and parameters populated by the application at runtime. Then this is just simply referenced in the status table:

create table status (
  entity integer references entities(id),
  state integer references states(id),
  primary key (entity, state)
);

The problem with this is that the semantics of the states are now lost, so it’s up to the application to understand things.

As an alternative, I created an enumeration type of the states and an optional parameter key:

create table status (
  id serial primary key,
  entity integer references entities(id),
  state state_enum not null,
  param integer default null,
  unique (entity, state),
  unique (entity, state, param)
);

I haven’t written it out here, but I also put an elaborate check constraint on param to ensure it’s only non-null when the state is “quux”.

(Rather than using an enum, I could have a lookup table with the states as hard-codes records; not the parameters as they have application-specific meaning. Either way, it’s equivalent.)

Now the states are explicitly enumerated by the schema, one doesn’t have to rely on the application to define semantics. However, it’s really messy in my opinion.

Is there a more elegant way to design this?

(If it makes a difference, in terms of features, I’m using PostgreSQL.)

Best Answer

Define a StatusType that will migrate through to a StatusLevel and provide a mechanism for a CHECK constraint.

enter image description here

DML (I'm doing this freehand, there may be syntax errors lurking):

CREATE TABLE StatusType
(
  StatusTypeCd  TEXT     NOT NULL
 ,Description   TEXT     NOT NULL
 ,CONSTRAINT PK_StatusType PRIMARY KEY (StatusTypeCd)
)
;
INSERT INTO StatusType VALUES ('N','Non-parameterized'),('P','Parameterized');

CREATE TABLE Status
(
  StatusCd      TEXT  NOT NULL
 ,StatusTypeCd  TEXT  NOT NULL
 ,Description   TEXT  NOT NULL
 ,CONSTRAINT FK_Status_Classified_By_StatusType FOREIGN KEY (StatusTypeCd) REFERENCES StatusType (StatusTypeCd)
 ,CONSTRAINT PK_Status PRIMARY KEY (StatusCd,StatusTypeCd)
 ,CONSTRAINT AK_Status UNIQUE (StatusCd)
)
;
INSERT INTO Status VALUES ('Foo','N','Foo description'),('Bar','N','Bar description'),('Quux','P','Quux description');

CREATE TABLE StatusLevel
(
  StatusCd      TEXT  NOT NULL
 ,Level         INT   NOT NULL
 ,StatusTypeCd  TEXT  NOT NULL
 ,CONSTRAINT FK_StatusLevel_Level_Of_Status FOREIGN KEY (StatusCd, StatusTypeCd) REFERENCES Status (StatusCd, StatusTypeCd) 
 ,CONSTRAINT PR_StatusLevel PRIMARY KEY (StatusCd,Level)
 ,CONSTRAINT CK_StatusLevel_Is_Valid CHECK (StatusTypeCd = 'P' OR Level = 0)
)
;
INSERT INTO StatusLevel VALUES ('Foo','0','N'),('Bar','0','N'),('Quux',10,'P'),('Quux',20,'P'),('Quux',30,'P');

From there you would migrate StatusCd,Level directly to entity or to an optional attribute entity_status: enter image description here

Notes:

  1. There should be unique constraints on names/descriptions.
  2. Three tables? Yes. But it keeps you from doing something dumb and forces you to think about what you're doing.
  3. There are no Id columns for StatusType/Status/StatusLevel as they are unnecessary.
Related Question