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