Tom Lane wrote:
> Peter Fein <pfein@pobox.com> writes:
>
>>Let's say I have a base table B (with a PK id, say) and two derived
>>tables D1 & D2 (with different cols). For a given B.id, I'd like to
>>allow only a corresponding row in *either* D1 or D2, but not both. Any
>>suggestions on how to do this? Should I not be using inheritance at all?
>
>
>>My thought was to add a column inherits_to to B with a value indicating
>>whether that row is really a D1 or a D2 and enforce it with appropriate
>>CHECK constraints on each of the derived tables.
>
>
> If it can only be one or the other, I think you're misdesigning the
> thing. Use *one* table with all the columns needed to describe either a
> D1 or D2. Leave the unneeded columns NULL in any given row. (If
> needed, you can have a check constraint that specifies that certain
> columns are not null when it's a D1, etc.) Null entries are cheap.
Ok, this makes a lot of sense & is just cleaner. Would you continue to
do it this way if there were around a dozen derived tables (most with
one or two columns)? I remember reading somewhere (perhaps the PG
docs?) that a table with most of its columns NULL was a sign of
misdesign as well... FWIW, most of the columns are small - varchar,
ints, an array or two.
It's going to be one rather long CHECK constraint... ;)
When the heck should one use inheritance?
--
Peter Fein pfein@pobox.com 773-575-0694
Basically, if you're not a utopianist, you're a schmuck. -J. Feldman