Thank you for your concise answer re: intersection tables. Now, how do you insert/update into multiple tables simultaneously? Currently, I am doing multiple inserts in a locked transaction but this seems wrong. It seems there should be the inverse of a joined select statement where I can insert into two+ tables at one shot, so the foreign key gets incremented up with the primary key in the other table.
I am wondering if the reason this never comes up is because a properly designed database would never require it, or if you somehow use cascading updates (which I don't know how to do), or if you simply do sequential separate inserts... Wisdom appreciated.
An "inverse" of a join for an insert is an interesting suggestion. Yes, you have to do separate sequential inserts. No, you do not need transaction locking for the separate inserts. Let me outline the scenario so that my comments will have some context.
The situation arises when inserting related rows in both parent and child tables in a one-to-many relationship. First, we insert a new row in the parent table, perhaps with a statement like:
insert into division ( dvid, dvname ) values ( 'it', 'Information Technology' )
Next, we want to insert one or more rows in the child table, using the value of the newly-inserted parent row's primary key as the value for the foreign keys in the child rows that relate to the parent we just inserted. With natural keys, this isn't a problem. When the parent row has a natural primary key, we simply use its value in the inserts for the child rows:
insert into department ( dvid, dpid, dpname ) values ( 'it', 'dv', 'Development' )
insert into department ( dvid, dpid, dpname ) values ( 'it', 'pr', 'Processing' )
insert into department ( dvid, dpid, dpname ) values ( 'it', 'mg', 'Management' )
Note that transaction locking would not be required for the above. You're probably thinking of the situation where the parent table has an autonumber surrogate key, in which case locking might be required. If you use the SELECT MAX(id) technique for getting the newly-assigned primary key back, then yes, locking is required. However, that's not the best approach, and I describe a different strategy in Retrieving last row inserted (24 April 2002). However, this only serves to acquire the parent's primary key, and we are still left with the task of inserting the child rows.
With two exceptions, we have no choice but to execute child inserts one at a time. The first exception is MySQL, which offers this non-standard but highly appealing syntax:
insert into department ( dvid, dpid, dpname ) values ( 'it', 'dv', 'Development' ) , ( 'it', 'pr', 'Processing' ) , ( 'it', 'mg', 'Management' )
This is one SQL statement, so one communication between our application and the database. Neat, eh?
The other alternative is to denormalize the multiple child rows into one string, and pass the string to a database stored procedure expressly written for this one purpose. The entire process can then be executed in one database call, maybe something like this:
exec InsertDivsAndDepts @div = 'it,Information Technology' , @depts = 'dv,Development,pr,Processing,mg,Management'
The stored proc would require "split" logic to extract the various fields, and it would take care of looping to do the inserts.