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" <j...@bytesmiths.com>
>> To: mysql@lists.mysql.com
>> 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 ::::





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql

Reply via email to