0

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

5
  • Are the parameters for state 'quux' fixed (property of the status) or a property of the entity?
    – bbaird
    Aug 6 '20 at 1:30
  • Which is to say, can I have 'quux' with any value, or only allowed values?
    – bbaird
    Aug 6 '20 at 1:43
  • The parameter is a property of the state. It can be anything, but it will be a fixed(ish) set in reality... If it helps, my entities are “files” and the states are “deleted”, “archived” and “warned”; “warned” is the parametrised state, that comes with a “T-minus” (e.g., warning of T-24hrs until deletion), the set of which is defined by the application. It’ll be used to drive a notification system, so each status record will also need a notified field. Aug 6 '20 at 12:00
  • 1
    Ok, I think I understand. I'll try and put something together later today.
    – bbaird
    Aug 6 '20 at 12:29
  • I think I've come up with a better solution...but I'm not convinced :P Aug 7 '20 at 14:36
1

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

I think this is better:

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

create table quux_values (
  status integer primary key references status(id),
  state state_enum default 'warned' check (state = 'warned'),
  param integer not null,
  foreign key (status, state) references status(id, state)
);

i.e., The quux parameters exist in a separate table, in which a "dummy" state field exists that is forced to be quux and the tuple of the ID and state is used as a foreign key with status (i.e., so only quux status records can be added to quux_values).

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.