Re: FKs + partial indexes?

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(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)