INSERT's in a multi-user environment

[Anon]

Hello,

I am creating a database, which contains about 5 tables. The database is designed using a many-to-one relationship. What I would like to know is what is the best way to insert data into the "main" table and then have the other 4 tables point that specific record? I would like to implement this is a multi-user environment.

I am able to submit information using the "LAST_INSERT_ID()" to relate to the foreign key but what if someone executes an insert at the exact same time or before I am able to do my insert/s?

One idea had was to enclose the inserts into a transaction but I was not sure if "LAST_INSERT_ID()" was specific to the transaction or to the system as a whole.

I just wanted to know be practices for this type of thing. Does anyone have any idea, websites or books that deal with this?

Thanks
- Justin

[Anon]

Sorry i forgot to mention the database server i was using... i am using mySQL.. i have though about using a db transaction to do something like.

$fname = "john";
$lname = "simpson";

insert into users ('fname', 'lname') values ('$fname', '$lname');

select id from users where fname = "john" and lname = "simpson";

and then use that id as a key to complete the inserts to the other tables?

Does this sound like a good idea or is there a better way?

• Justin

[brandonschnell]

LAST_INSERT_ID() is specific to the users session. it won't be affected by what other users are doing.

using transactions might be a good idea if you can take the performance hit. with transactions it should help keep the tables in sync and prevent problems if only half the query executes.

[Anon]

thanks man

• Justin