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