Key problem: Which key strategy should I use in my database?

[ruireeGof]

Problem: When I use an auto-incrementing primary key in my database, this happens all the time:I want to store an Order with 10 Items. The ordered Items belong to the Order. So I store the order, ask the database for the last inserted id (which is dangerous when it comes to concurrency, right?), and then store the 10 Items with the foreign key (order_id).So I always have to do:INSERT ...last_inserted_id = db.lastInsertId();INSERT ...INSERT ...INSERT ...and I believe this prevents me from using transactions in almost all INSERT cases where I need a foreign key.So... here some solutions, and I don't know if they're really good:A) Don't use auto_increment keys! Use a key table?Key Table would have two fields: \[code\]table_name, next_key\[/code\]. Every time I need a key for a table to insert a new dataset, first I ask for the next_key by accessing a special static KeyGenerator class method. This does a SELECT and an UPDATE, if possible in one transaction (would that work?). Of course I would request that for every affected table. Next, I can INSERT my entire object graph in one transaction without playing ping-pong with the database, before I know the keys already in advance.B) Using GUUID / UUID algorithm for keys?These suppose to be really unique worldwide, and they're LARGE. I mean ... L_A_R_G_E. So a big amount of memory would go into these gigantic keys. Indexing will be hard, right? And data retrieval will be a pain for the database - at least I guess - integer keys are much faster to handle. On the other hand, these also provide some security: Visitors can't iterate anymore over all orders or all users or all pictures by just incrementing the id parameter.C) Stick with auto_incremented keys?Ok, if then, what about transactions like described in the example above? How can I solve that? Maybe by inserting a Ghost Row first and then doing an transaction with one UPDATE + n INSERTs?D) What else?