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
> 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
Take, for example, your Volunteers example. This requires at least two
rows: one main row on the `contacts` table and another on the
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
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN