I was thinking a bit about a problem that was mentioned in the
discussion TomD, Simon Rigga and I had about enums and foreign keys,
namely that often we find dozens of tiny little reference tables
littering the data model, or else we find a table that somehow
consolidates them, plus some sort of homegrown referential integrity
checks. I wondered if we could improve on that situation by using
partial unique indexes on the consolidated table, and providing a
mechanism to specify which index the FK must refer to (or else allow
allow an optional predicate expression which would have to match the
predicate expression of the partial index).
So we would have something like:
create table constants (type text, id int primary key, value text);
create unique index x_const_idx on constants(id) where type = 'x';
create table client (id serial primary key; xid int references
constants(id) using x_const_idx);
Of course, this is a blue sky idea, and I haven't thought out any
details at all, but it struck me that it might be a way to make designs
a bit cleaner.
cheers
andrew
