Please I need a little help, I am only learning SQL. I need t o INSERT into the DB, a new hotel called 'Beach Resort' at location 'Mexico'. This is what i have: (Note that the value '8' is because 'hotelNo' in all tables is NOT AUTO_INCREMENT. but it is a int(11) NOT NULL INSERT INTO hotel VALUES ('8','Beach Resort','Mexico') 'hotelNo' is the PRIMARY KEY in all tables. If I manually update hotelNo with above script in Hotel table, do i need to update it manually in all tables? This is MySQL DB in WorkBench Hotel (hotelNo, hotelName, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo,guestNo, dateFrom, dateTo, roomNo) Guest (guestNo, guestName, guestAddress) -Hotel contains hotel details and hotelNo is the primary key -Room contains room details for each hotel and (roomNo, hotelNo) forms the primary key -Booking contains details of bookings and (hotelNo,guestNo,dateFrom) forms the primary key -Guest contains guest details and guestNo is the primary key Any help appreciated
Lets start with a bit of terminology A database is like a big spreadsheet file, it does very little and is akin to a bucket A table is like each tab or worksheet held within the spreadsheet Once you've put your hotel into the hotels table you'll get an ID. You will NEVER change that but you will use it when you go on and insert values into Rooms, Bookings, and Guests So what I would normally do is something like this. I use PHP, whatever language you use will have similarities. foreach hotel insert into the hotels table get the id of the most recent insert foreach room insert into the rooms table using the hotel id then foreach Guest insert into the guests table get the id of the most recent insert Are you adding bookings through the script or are you writing an interface for that? if you have a list you'll need a way to identify the hotel and room and tie to each guest. Primary Keys These don't need to be auto-incremented, they could be a code or a combination of two fields. Your hotels might have a code, a bit like airports have SYD, LAX, JFK and that might be the primary key Alternatively they might have a number but have another field that holds the hotels abbreviated or code name and that could be indexed.
There was no mention of room numbers in the DB information. So unsure how to process roomNo. I guess as long as I add a new hotel. Will it then be available to select as a booking, or do i need to manually add the Hotel to booking and Room also?
First, You will need a "booking no" as an autoincrement value in your booking table, otherwise I don't know how you are accessing that table's individual bookings to edit or delete them. Second, When you add a new hotel, there is no need to update any other table at that time. Once a hotel is made, only then new rooms can be created for that hotel. I am assuming your code does ask the user to select a hotel when they go to add a room in room table, right? So when a hotel is selected, room details filled in the form, only then room table gets a new insert, not at the time when you added a hotel.
How much authority do you have with this project? It seems a really big task for a newbie. It really doesn't matter what you get given, you can manipulate it to be normalised and fit in with a modern website approach. Room numbers will be like hotel names. There will be an internal id and there will be a plain English version. If you have more than one hotel you will have more than one room 3012 (for example) so that room needs to have an internal, kinda secret, id number that is only used by the programs you write.
Since you're starting fresh I'd begin by finding some simple tutorials on building forms to add and edit records. Be prepared to work on something as a learning exercise, your finished work will look different but the satisfaction of each achievement is to be savoured. If you try to go all out initially you'll get bogged down in a heap of different aspects that will make it seem like you're getting nowhere.