Обсуждение: 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!