Thread: Preventing Multiple Inheritance

Preventing Multiple Inheritance

From
Peter Fein
Date:
Hi-

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.

Sorry if this is unclear...

--
Peter Fein                 pfein@pobox.com                 773-575-0694

Basically, if you're not a utopianist, you're a schmuck. -J. Feldman

Re: Preventing Multiple Inheritance

From
Tom Lane
Date:
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.

            regards, tom lane

Re: Preventing Multiple Inheritance

From
Bruno Wolff III
Date:
On Fri, Jun 03, 2005 at 14:09:32 -0500,
  Peter Fein <pfein@pobox.com> wrote:
> Hi-
>
> 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 is OK to have no value in either D1 or D2, the simple way to do this
is the following. Have a record type value in B, D1 and D2. In D1 and D2 it
should be constained to have exactly the value that corresponds to that record
type. You need to make the PK of B plus the record type a unique key. And
in D1 and D2 you need to use a foreign key reference that uses the normal
PK plus the record type. This wastes a little space, but is easy to use.
If you need exactly one of D1 or D2 to have a value, then you can have
two fields in B that can either have a copy of the primary key or NULL
and a constraint that exactly one of them is NULL. One of these should
reference D1 and the other D2. You will want to make these last two
deferred constraints.

Re: Preventing Multiple Inheritance

From
Peter Fein
Date:
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

Re: Preventing Multiple Inheritance

From
Bruno Wolff III
Date:
On Fri, Jun 03, 2005 at 16:04:26 -0500,
  Peter Fein <pfein@pobox.com> wrote:
>
> 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.

That may be bordering on a religious debate. There are people that say
you shouldn't have NULLs and should use an extra table instead.
I think for most people it is a matter of what will be easier to understand
and to some extent what is more efficient, that should dictate the design.