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