Re: Table inheritance

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.
---------------------------------------------------------------