List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:January 7 2012 2:31pm
Subject:Re: Common Pattern for parent-child INSERTs?
View as plain text  
Hello Jan,

On 1/7/2012 00:58, Jan Steinman wrote:
> 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.
>

Once you have inserted the 'parent' row (the one to the Contacts table) 
you know the ID of the parent. This cannot change and no other contacts 
will be given the same ID. You include this ID with the other INSERT 
commands you need for your 'child' rows.

You do have two options to handle rollback scenarios:
1) run with only InnoDB tables and wrap all of the related INSERTs with 
a single transaction

2) use any tables you like and keep track of the auto_increment values 
issued for each row you are INSERTING in your application, too. This 
allows you to implement a manual rollback in the event of some kind of 
problem.

Take, for example, your Volunteers example. This requires at least two 
rows: one main row on the `contacts` table and another on the 
`volunteers` table.

   INSERT `contacts` (name, ...) VALUES ('Bob the Volunteer',...);
   SET @contact_id = LAST_INSERT_ID();
   INSERT `volunteers` (contact_id, skill, diet_restrict, ...) VALUES 
(@contact_id, 'carpentry', 'hates fish', ...);
   SET @volunteer_id = LAST_INSERT_ID();
   ...

If you don't want to track the ID values in user variables, you can 
query them and draw them back into application-based variables. If you 
want to track lists of values, you can add them to temporary tables to 
build each list or query them into application-side arrays. At this 
point how you handle those numbers is up to you.

Remember, though, that LAST_INSERT_ID() can only return one value. This 
means that you cannot use it for batch processing reliably unless you 
manually lock the table and guarantee a specific sequence of numbers. 
The other option is to build an association table of (id, name) based on 
the newly-inserted data (or something similar based on some unique 
combination of identifiers in your original data instead of just 'name' 
) so that each of your child rows can be assigned their proper parent id 
values.
http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id

-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
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