1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

INSERT DB

Discussion in 'Databases' started by Karl1710, May 21, 2020.

0
  1. #1
    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
     
    Karl1710, May 21, 2020 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #2
    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.
     
    sarahk, May 21, 2020 IP
  3. Karl1710

    Karl1710 Peon

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #3
    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?
     
    Karl1710, May 21, 2020 IP
  4. JEET

    JEET Notable Member

    Messages:
    3,825
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #4
    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.
     
    JEET, May 21, 2020 IP
  5. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #5
    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.
     
    sarahk, May 21, 2020 IP
  6. Karl1710

    Karl1710 Peon

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #6
    I have full administrator rights
     
    Karl1710, May 21, 2020 IP
  7. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #7
    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.
     
    sarahk, May 21, 2020 IP