Inserting data into 3 tables, one after the other

[webdeveloper123]

Hey Guys,

Looking for some advice/logic here.

I am making a hotel room reservation system. I have got to the booking stage. I have 3 tables which I need to insert data into. Guest table (guest details), booking and room_booking. The data dictionary looks like this:

Guest(title, fname, lname, phone, email, address, pcode, city, country)

Booking(booking_id, guest_id, time_booked, numOfAdults, numOfChildren, specialRequest)

Room_booking(room_booking_id, room_id, booking_id, arrivalDate, departureDate)

Now I have to insert the data in that order because the booking table requires a guest_id, so I have to generate that first and then thirdly the room_booking table requires a booking_id. I have made a sql insert form for guest details which works fine but I'm a little stuck on the next step. Shall I just keep it to 3 insert statements, grabbing the required id's as I go along and insert them or should I use Insert Into Select?

Thanks

[requinix]

To make sure I understand, you have two options:

(a) Do three normal INSERT statements, and get the auto-incremented IDs of what you need
(b) Do a normal INSERT and then craft a couple INSERT...SELECT queries based on the raw values you want to insert plus a reference or two to the IDs of the new rows you created that hopefully don't have any duplicates

I'm thinking you should go for the simpler option.

[webdeveloper123]

You mean go for option a?

Sorry I just wanted to be clear

Edited November 15, 2023 by webdeveloper123

[requinix]

That does sound simpler, yes.

[webdeveloper123]

Thanks! 😁

[maxxd]

I agree with the 3 inserts method and highly recommend using a transaction. That way if any of the child inserts fail, the parent inserts are rolled back and there aren't any abandoned and potentially problematic parent records.