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