Help with Database for hotel reservation

[HB25]

Dear Friends

I have created the attached database for hotel reservation system and I don’t know if it is correct or there is something wrong with it. I would be very grateful if someone could have a look at it and give me some feedback on it.

If there is anything wrong with it then some advice will be highly appreciated.

Notes: I have not included payment as its not part of the requirements.

Kind Regards
HB25

[pclfw]

You include ROOM_PRICE in the ROOMS table and the ROOM_TYPE table. It should only appear in one of these.

The choice is yours. ROOM gives precise control over room pricing, ROOM_TYPE gives easier maintenance when chaning room prices.

[nathanjeynes]

Dear Friends

I have created the attached database for hotel reservation system and I don’t know if it is correct or there is something wrong with it. I would be very grateful if someone could have a look at it and give me some feedback on it.

If there is anything wrong with it then some advice will be highly appreciated.

Notes: I have not included payment as its not part of the requirements.

Kind Regards
HB25

Hi i am new with databases i was wondering how you were gunna check the database to see what rooms are free beetween two dates? what query should i use? any help would be great i am trying to build a simular database for a project in college, many thanks, nathan

[bigakis]

Using the given schema, the query is:

select *
from tblRooms r
where not exists
      (select '0'
         from tblBookings b, tblLINLK_Bookings t
        where t.BookingID = b.BookingID
          and t.RoomID    = r.RoomID
          and (BookingStartDate  <= :date_to
               or BookingEndDate >= :date_from
      )

I found the schema correct if:
1)When reservation applies to more than one rooms, the dates are the same for all rooms for all reservations (quite unlikely. the dates have to be moved to the tblLink_booking table)
2)all rooms are available 365 days per year. No maintenance, no damages.
3)all reservations are valid or deleted if cancelled

[taneval23]

Hi
Just a couple quick questions. That might point out ways to improve or change the database.
1. Does more than 1 person ever stay in a room that you would need to keep track of?
2. Is it always the person staying in the room that is paying for the room? Would you ever have a company pay for their employees type idea?
3. Perhaps I am not seeing it but are you needing to record if/when the booking is paid, prepaid, places a deposit, etc.
I hope this helps as I am working on doing the same type of thing right now.

[MALM14]

Hi Taneval23 and HB25,

I am working on a similar system but found that the schema needs change due to

1. advance reservation made by guests. so query should also consider advance reservation.

2. Blocking of rooms (similar to advance reservation) by Hotel management for some renovation etc but in advance of 1 mnth or so.

So how can we design a database for such business rules?

Thanks
MALM

[tesuji]

hi hb25

I think it s a good idea to draw an ERM of your hotel reservation. A very useful tool doing so seems to be mysql workbench (though i don't like to work with that database)

-- tes

[tesuji]

Sorry, i didn't notice that this thread is older than dirt ...