From: Jan Steinman Date: January 7 2012 5:58am Subject: Re: Common Pattern for parent-child INSERTs? List-Archive: http://lists.mysql.com/mysql/226567 Message-Id: MIME-Version: 1.0 (Apple Message framework v1084) Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable 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= >=20 > If you use InnoDB for your tables, you can use the ON UPDATE CASCADE = option for your foreign key constraints. >=20 > However, your habit is indeed a nasty one :-p It forces you to do = joins where none are necessary, thus needlessly slowing down operations. >=20 > 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). >=20 >=20 >=20 >=20 > ----- Original Message ----- >> From: "Jan Steinman" >> To: mysql@stripped >> Sent: Thursday, 5 January, 2012 1:12:15 AM >> Subject: Common Pattern for parent-child INSERTs? >>=20 >> 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. >>=20 >> For example: >>=20 >> 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 >>=20 >> CREATE TABLE addresses TYPE InnoDB >> names_id INT NOT NULL REFERENCES names (id) >> street VARCHAR(255) NOT NULL, >> city VARCHAR(255) NOT NULL >>=20 >> CREATE TABLE phones TYPE InnoDB >> names_id INT NOT NULL REFERENCES names (id) >> phone VARCHAR(255) NOT NULL >>=20 >> (Keyed in from memory for schematic purposes, may contain errors. >> CREATE syntax is not what I'm here about.) >>=20 >> Now how do I go about INSERTing or UPDATEing two or three tables at >> once in a way that maintains referential integrity? >>=20 >> 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. >>=20 >> 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? >>=20 >> (BTW: MySQL version 5.0.92, if that matters...) >>=20 >> Thanks in advance for any help offered! >>=20 >=20 > --=20 > 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 ::::