Обсуждение: Table inheritance

Hi,
I'm new to inheritance. Does anyone have a good link about it?

I have just finished an application using parent and child tables.
Tables look a bit strange. :)

Inheritance seems a good replacement for foreign keys. One can
completely forget about them which simplifies programming.
A great idea, this inheritance.

Regards, Zdravko

On Wednesday 24 March 2010, Zdravko Balorda <zdravko.balorda@siix.com>
wrote:
> Hi,
> I'm new to inheritance. Does anyone have a good link about it?
>
> I have just finished an application using parent and child tables.
> Tables look a bit strange. :)
>
> Inheritance seems a good replacement for foreign keys. One can
> completely forget about them which simplifies programming.
> A great idea, this inheritance.

It ... doesn't. In fact, it makes them harder, as you need to create them
and associated indexes on each child table.

--
"No animals were harmed in the recording of this episode. We tried but that
damn monkey was just too fast."

Inheritance is not the replacement for foreign keys. Inheritance
mechanism is a sort of "create like" thing. Created table inherits
column names and types. Indexes and constraints are not inherited.
Foreign keys still have to be used to guarantee the logical consistency.

Zdravko Balorda wrote:
>
> Hi,
> I'm new to inheritance. Does anyone have a good link about it?
>
> I have just finished an application using parent and child tables.
> Tables look a bit strange. :)
>
> Inheritance seems a good replacement for foreign keys. One can
> completely forget about them which simplifies programming.
> A great idea, this inheritance.
>
> Regards, Zdravko
>


--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

>> A great idea, this inheritance.
>
> It ... doesn't. In fact, it makes them harder, as you need to create them
> and associated indexes on each child table.

Hmm ... But:

SELECT parent_field FROM child_table WHERE child_field = 'something';
does the job. For this one needs foreign key, but if child_table inherits
from parent_table it works without it.

Am I wrong?

Zdravko

On Fri, March 26, 2010 6:25 am, Zdravko Balorda wrote:
>
>>> A great idea, this inheritance.
>>
>> It ... doesn't. In fact, it makes them harder, as you need to create
>> them
>> and associated indexes on each child table.
>
> Hmm ... But:
>
> SELECT parent_field FROM child_table WHERE child_field = 'something';
> does the job. For this one needs foreign key, but if child_table inherits
> from parent_table it works without it.
>
> Am I wrong?

Not for this specific case, but that's not really a foreign key use-case.
It's just a basic select, in essence.

Foreign keys are where you have two tables holding _different_ data sets
and types, that are only linked by the foreign key.  As a basic example,
let's use this table structure:

customer:
  ID
  first_name
  last_name
  birthday

address:
  ID
  street
  city
  state
  zip
  type

One customer can have multiple addresses (home, work, delivery...).  To
send out birthday cards, you'd need a select something like this:

SELECT first_name, last_name, street, city, state, zip FROM customer JOIN
address USING ('ID') WHERE birthday = tomorrow AND type = 'HOME';

To send a present, you'd use:

SELECT first_name, last_name, street, city, state, zip FROM customer JOIN
address USING ('ID') WHERE birthday = tomorrow AND type = 'DELIVERY';

Now, you could bastardize child tables to do this, but really you'd be
creating 'foreign keys by another name', and probably a maintenance
headache as you would likely do dumb things like create a separate child
table for each type of address...  (Which pulls data out of the table and
into the schema, among other sins.)

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------