MySql Insert to multiple tables based on the ID generated in the first table

I have three tables in my database. A users table, StoreA and StoreB

StoreA and StoreB both have a unique key which is the user ID value.

What I want is; When I create a user and insert them into the database, how can I Insert a row into the other two tables without too many additional queries.

I figure I can do 开发者_StackOverflow社区this by inserting the user in one query, then in another return the newly created user ID, then in another, using said ID, create rows in StoreA and StoreB

Can I cut out the middle query?

Can I cut out the middle query?

YES

START TRANSACTION;
INSERT INTO user (id, name, other) 
          VALUES (null, 'John','rest of data');
INSERT INTO storeA (id, user_id, other) 
            VALUES (null, @user_id:= LAST_INSERT_ID(), 'rest of data');
INSERT INTO storeB (id, user_id, other) 
            VALUES (null, @user_id, 'rest of data');
COMMIT;

See: http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

It's a good idea to do this in a transaction, you you're not stuck with just a user with no other data if something goes wrong.
It's not a DB requirement though.

Yes - there should be a function available to get the last inserted ID (assuming it's an autoincrement field) without another query. In PHP, it's mysql_insert_id(). Just call that after the first query.

YES

 Q1: insert into table1 values (...);
 Q2: insert into table2 values (last_insert_id(), ...);

last_insert_id is the default mysql build-in function

Most of the mysql libraries in various programming language did support return last insert id.
But You did not mention what sort of language you are using to connect to mysql.,
so cannot provide any example

I just wanted to share a php solution.

If you're using mysqli, first execute your insert query. Then do

$db_id = $this->db->insert_id;

Why don't you use their username as the primary key instead of creating an arbitrary user_id field thats auto incremented? Their user names are unique, right?