Thread: Inheritence woes

Hello All,
  We've been playing with Inheritence and have perhaps not used it
correctly.

Our original intention was to have a generic table called unit, then we
dealing with different types of units, like boats, or cars, or marine
outboards for example, they can then inherit off unit and get a common
set of attributes.

As we've progressed things like the purchase costs for these items needs
to be recorded and we've added in a table like, unit_purchase_cost.
However, because the unit type car inherits unit, unit_purchase_cost
can't use a foreign key that references unit.id because the values are
in car.

Doing a SELECT * FROM ONLY unit; shows no values as they're all in car.

About the only away around this right now we can see is to write our own
foreign key triggers, allowing the insert of the value without the
referential integrity to the parent class?

Any other thoughts on this?

Thanks.
--
Hadley Willan > Systems Development > Deeper Design Limited. +64(7)377-3328
hadley.willan@deeperdesign.co.nz > www.deeperdesign.com > +64(21)-28-41-463
Level 1, 4 Tamamutu St, PO Box 90, TAUPO 2730, New Zealand.

On 24 Apr 2003, Hadley Willan wrote:

>   We've been playing with Inheritence and have perhaps not used it
> correctly.
>
> Our original intention was to have a generic table called unit, then we
> dealing with different types of units, like boats, or cars, or marine
> outboards for example, they can then inherit off unit and get a common
> set of attributes.
>
> As we've progressed things like the purchase costs for these items needs
> to be recorded and we've added in a table like, unit_purchase_cost.
> However, because the unit type car inherits unit, unit_purchase_cost
> can't use a foreign key that references unit.id because the values are
> in car.
>
> Doing a SELECT * FROM ONLY unit; shows no values as they're all in car.
>
> About the only away around this right now we can see is to write our own
> foreign key triggers, allowing the insert of the value without the
> referential integrity to the parent class?
>
> Any other thoughts on this?

Inheritance is a mess? ;)

Seriously, you'll need to do some sort of workaround (or replacement) for
the foreign key triggers, you may need to do a workaround on the primary
key/unique constraint on id (I figure there must be one since otherwise
the foreign key wouldn't work) since that won't prevent you from inserting
a car with id 1 and then a boat with id 1 (if you use a sequence and never
insert manually you don't have to worry about this as much).

Thanks. It's pretty much what we'd thought then. But sometimes you like
to check these things out to avoid the "doh!" that comes later.

Hadley

On Thu, 2003-04-24 at 13:39, Stephan Szabo wrote:
> On 24 Apr 2003, Hadley Willan wrote:
>
> >   We've been playing with Inheritence and have perhaps not used it
> > correctly.
> >
> > Our original intention was to have a generic table called unit, then we
> > dealing with different types of units, like boats, or cars, or marine
> > outboards for example, they can then inherit off unit and get a common
> > set of attributes.
> >
> > As we've progressed things like the purchase costs for these items needs
> > to be recorded and we've added in a table like, unit_purchase_cost.
> > However, because the unit type car inherits unit, unit_purchase_cost
> > can't use a foreign key that references unit.id because the values are
> > in car.
> >
> > Doing a SELECT * FROM ONLY unit; shows no values as they're all in car.
> >
> > About the only away around this right now we can see is to write our own
> > foreign key triggers, allowing the insert of the value without the
> > referential integrity to the parent class?
> >
> > Any other thoughts on this?
>
> Inheritance is a mess? ;)
>
> Seriously, you'll need to do some sort of workaround (or replacement) for
> the foreign key triggers, you may need to do a workaround on the primary
> key/unique constraint on id (I figure there must be one since otherwise
> the foreign key wouldn't work) since that won't prevent you from inserting
> a car with id 1 and then a boat with id 1 (if you use a sequence and never
> insert manually you don't have to worry about this as much).
--
Hadley Willan > Systems Development > Deeper Design Limited. +64(7)377-3328
hadley.willan@deeperdesign.co.nz > www.deeperdesign.com > +64(21)-28-41-463
Level 1, 4 Tamamutu St, PO Box 90, TAUPO 2730, New Zealand.

On 24 Apr 2003, Hadley Willan wrote:

> Thanks. It's pretty much what we'd thought then. But sometimes you like
> to check these things out to avoid the "doh!" that comes later.

Well, one other workaround is to make a table that has only the ids and
then have each of unit, boats, cars, etc... foreign key an id to that.
Then at least you can foreign key to the id table, but that doesn't help
you with the allowing dups between boats and cars.

On Thu, 2003-04-24 at 00:28, Stephan Szabo wrote:
>
> On 24 Apr 2003, Hadley Willan wrote:
>
> > Thanks. It's pretty much what we'd thought then. But sometimes you like
> > to check these things out to avoid the "doh!" that comes later.
>
> Well, one other workaround is to make a table that has only the ids and
> then have each of unit, boats, cars, etc... foreign key an id to that.
> Then at least you can foreign key to the id table, but that doesn't help
> you with the allowing dups between boats and cars.
>

variation on the theme... make one table that holds all of the ids, with
a unique constraint on that tables id column.  then write a trigger
function on each "child" table to insert the id's into the "parent"
table. if your child table tries to insert an existing id it will error
out and not allow the insert. as preventative medicine, use 1 sequence
to drive all of the tables, and set the default id to
nextval(common_seq); that should help keep you from getting duplicates
in the first place.

Robert Treat