Thread: Preventing Multiple Inheritance
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
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
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.
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
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.