Hotel Under Construction!

[prabha_friend]

Is the Foundation Okay?

 

[The_Doc_Man]

There is a path leading from Room Types through EITHER Rooms or Tariff and leading through EITHER Bookings or Reservations and leading to Guests_Temp to Payments. The path from Tariff Types avoids the Rooms/Tariff dual path but not the Bookings/Reservations dual path.

Dual paths typically nail you by generating combinations of matching items, and several of your pathways are one/many, indicating a chance for you to have Cartesian (permutation) JOIN problems. (I.e. usually you get too many records because something in the middle of the path gives you fits.) This would be REALLY troublesome if you tried to do a study that involved Room Types and Payment Modes. BUT it could also vex you if you tried a study on guests.

 

[Pat Hartman]

shaky foundation.

1. If you are going to do this, at least have a hotel table so you can handle multiple hotels.
2. Bookings and Reservations are the same thing. Maybe you mean Booking and Occupancy because a guest can book a room but cancel. I would use the same table. Just include a Check in date/time.
3. Your FK names are inconsistent
4. Room rates are more complicated than your schema allows. Not only are they dependent on room type but they are dependent on week day and season. So, at a minimum the tariff table needs a start and end date.

 

[Solo712]

shaky foundation.

1. If you are going to do this, at least have a hotel table so you can handle multiple hotels.
2. Bookings and Reservations are the same thing. Maybe you mean Booking and Occupancy because a guest can book a room but cancel. I would use the same table. Just include a Check in date/time.
3. Your FK names are inconsistent
4. Room rates are more complicated than your schema allows. Not only are they dependent on room type but they are dependent on week day and season. So, at a minimum the tariff table needs a start and end date.

ad 2) Yes, bookings and reservations are the same thing. Room occupancy should have its own table, based on actual check-in (and check-out). Tariff (?, rate) in that table is a duplicate. The rate is negotiated beforehand, whether by a reservation agent or by reception. The payment table does not make much sense - it relates to reservation and it is complicated if a part of the payment, or the entire amount, is made through a third-party agent and effected as bank transfers. A reservation can be extended and/or shortened, ie. feature credits and additional charges to say nothing about security deposit which is now common. I know for a fact that hotels using international booking agents (like Booking.com, and kayak) get paid by them in lump payments in a period. At least that is an option.

ad 4) true and there is more. There are promotions. There are walk-in rates with no reservations. Some advertised rates feature optional extras. Further, like airlines, hotels modify their rates, based on a booked volume vs capacity. I would rework the whole rate (tariff) structure.

Best,
Jiri

 

[tvanstiphout]

Let's not forget that the OP is in India, and the business rules would likely be different than they are in the USA. Prabha, is this for an Indian hotel?

 

[gemma-the-husky]

Let's not forget that the OP is in India, and the business rules would likely be different than they are in the USA. Prabha, is this for an Indian hotel?

Irrespective you would surely want to design the system flexibly to be able to cope with all the different potential scenarios.

 

[prabha_friend]

Give me some time. I explain everything. I have an habit of automating things what I do regularly. My friend just want an front office management tool. He just needs a sampling not an entire Banyan tree right now...

 

[The_Doc_Man]

He just needs a sampling not an entire Banyan tree right now..

The diagram you gave us for starters was ALREADY a banyan tree because of the multi-path problems.