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
