FKs + partial indexes?
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
Andrew Dunstan wrote:
I was thinking a bit about a problem that was mentioned in the
discussion TomD, Simon Rigga and I had about enums
er, that's Simon Riggs. apologies for aging fat fingers.
cheers
andrew
Andrew Dunstan <andrew@dunslane.net> writes:
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);
This seems like a solution in search of a problem. Why wouldn't you
just use separate reference tables? Adding such a concept would break
more things than I even want to think about (information_schema for
starters).
regards, tom lane
Tom Lane wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
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);This seems like a solution in search of a problem. Why wouldn't you
just use separate reference tables? Adding such a concept would break
more things than I even want to think about (information_schema for
starters).
Well, I was thinking out loud, more or less. And with luck enums will take
care of many of the cases. I'll try to suppress the overactive imagination
:-)
cheers
andrew
On Wed, Nov 22, 2006 at 01:33:21PM -0500, Andrew Dunstan wrote:
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,
Is this really a problem?
or else we find a table that somehow consolidates them, plus some
sort of homegrown referential integrity checks.
That is the standard EAV mistake, born of fear of committing to do
some things and not to do others.
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).
Isn't this just putting some lipstick on the EAV pig?
Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
On Nov 23, 2006, at 12:05 PM, David Fetter wrote:
On Wed, Nov 22, 2006 at 01:33:21PM -0500, Andrew Dunstan wrote:
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).Isn't this just putting some lipstick on the EAV pig?
EAV?
If we're going to improve the situation of needing lookup tables, I
think the way to do it would be through enums, or allowing user-
configurable settings on TOAST. The latter would allow you to force
any value written into a text field to get toasted. If you also allow
toast to combine multiple identical values into a single row in the
toast table (it might already do that...), you now have your normal
lookup-table scenario, without having to define an extra table, RI,
etc. (Ok, you'd need a check constraint too for "normal" lookup table
behavior).
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
On Sun, Nov 26, 2006 at 04:16:12PM -0600, Jim Nasby wrote:
On Nov 23, 2006, at 12:05 PM, David Fetter wrote:
On Wed, Nov 22, 2006 at 01:33:21PM -0500, Andrew Dunstan wrote:
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).Isn't this just putting some lipstick on the EAV pig?
EAV?
"Entity-Attribute-Value" a frequently-repeated mistake a.k.a. an
"anti-pattern." It's something people do when they fear making design
decisions, so they defer making a decision until later by making no
decision up front. The costs in terms of performance, maintainability
and extensibility grow exponentially over time, but as is frequently
the case with such growth, they start piling up slowly at first.
Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!