List:General Discussion« Previous MessageNext Message »
From:Jan Steinman Date:January 7 2012 5:58am
Subject:Re: Common Pattern for parent-child INSERTs?
View as plain text  
Okay, I'm seeking enlightenment here.

In my trivial case, one can save storage by not having a record extension for people
without phones. Big deal.

In my real-world case, I have a contacts database with your typical name, address, phone,
email, etc. info. Then I have extensions for people who are particular type of contacts
that have more information than the general case. If I have several thousand records in
my contacts database, but only ten in the "dairy customers" database, I'm saving a ton of
storage by not having every single record in the general-purpose contacts database contain
stuff like "desired_milk_pickup_day" or SET "dairy_products_of_interest".

But now I have a different extension, "Volunteers", with extra fields like
"special_skills", "dietary_restrictions", etc. I don't want those fields in the general
contact list. And there's another extension, "Advisory", that holds extra information for
contacts who are on our advisory council.

In normalizing databases, I was taught to do exactly what I've done, separate out the
special cases and put them in a separate table. But as you note, that creates a bit of a
mess for INSERT while simplifying SELECT.

ON UPDATE CLAUSE does not help on INSERT, does it? I mean, how does it know the
auto-increment value of the parent record before it's been INSERTed?

It appears that anything I do must be wrapped in a transaction, or there's the chance
(however unlikely) that something will get in between the INSERT of the parent and that
of the child.

On 5 Jan 12, at 05:51, Johan De Meersman wrote:

> http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
> 
> If you use InnoDB for your tables, you can use the ON UPDATE CASCADE option for your
> foreign key constraints.
> 
> However, your habit is indeed a nasty one :-p It forces you to do joins where none
> are necessary, thus needlessly slowing down operations.
> 
> The oo "extends" idea does not exactly match what you're doing, as the "child" table
> you create does not inherit the parent's "attributes", it merely has it's own column and a
> reference to the parent. This is good for 1:n relations, but for 1:1 there are only
> downsides (except for a very few edge cases).
> 
> 
> 
> 
> ----- Original Message -----
>> From: "Jan Steinman" <Jan@stripped>
>> To: mysql@stripped
>> Sent: Thursday, 5 January, 2012 1:12:15 AM
>> Subject: Common Pattern for parent-child INSERTs?
>> 
>> Having been steeped in object-orientation, I have a nasty habit of
>> creating parent-child tables that have a 1:1 relationship where the
>> child extends the parent, sometimes to a depth of three or more.
>> 
>> For example:
>> 
>> CREATE TABLE names TYPE InnoDB
>>   id INT NOT NULL AUTO INCREMENT PRIMARY KEY,
>>   name_first VARCHAR(255) NOT NULL,
>>   name_last VARCHAR(255) NOT NULL
>> 
>> CREATE TABLE addresses TYPE InnoDB
>>   names_id INT NOT NULL REFERENCES names (id)
>>   street VARCHAR(255) NOT NULL,
>>   city VARCHAR(255) NOT NULL
>> 
>> CREATE TABLE phones TYPE InnoDB
>>   names_id INT NOT NULL REFERENCES names (id)
>>   phone VARCHAR(255) NOT NULL
>> 
>> (Keyed in from memory for schematic purposes, may contain errors.
>> CREATE syntax is not what I'm here about.)
>> 
>> Now how do I go about INSERTing or UPDATEing two or three tables at
>> once in a way that maintains referential integrity?
>> 
>> I've tried making a VIEW, but I wasn't able to INSERT into it. I
>> don't think I was violating the restrictions on VIEWs as stated in
>> the manual.
>> 
>> Is there a generalized pattern that is used for INSERTing and
>> UPDATEing these parent-child tables? Does it require a TRIGGER in
>> order to propagate the foreign key?
>> 
>> (BTW: MySQL version 5.0.92, if that matters...)
>> 
>> Thanks in advance for any help offered!
>> 
> 
> -- 
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel

----------------
An idea that is not dangerous is unworthy of being called an idea at all. -- Oscar Wilde
:::: Jan Steinman, EcoReality Co-op ::::




Thread
Common Pattern for parent-child INSERTs?Jan Steinman5 Jan
  • Re: Common Pattern for parent-child INSERTs?Claudio Nanni5 Jan
    • Re: Common Pattern for parent-child INSERTs?Jan Steinman5 Jan
      • Re: Common Pattern for parent-child INSERTs?Claudio Nanni5 Jan
  • Re: Common Pattern for parent-child INSERTs?Johan De Meersman5 Jan
    • Re: Common Pattern for parent-child INSERTs?Jan Steinman7 Jan
      • Re: Common Pattern for parent-child INSERTs?MySQL)7 Jan