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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
