Hotel Database Design Theory

[bensubes]

Hi All,

My 1st post so excuse any obvious short comings, and I am just learning Access so I imagine you may have to be patient with me!

Basically I am trying to design a hotel style db for my business but am getting very confused on the initial table and relationship design that I should be using.

For example for customer orders I have the following design:

tblOrder
tblReservation
tblExtras

tblOrder
Holds the 'header' information customer ID, salesperson, orderdate, etc.
tblReservation
Contains the specific room booked, checkIn, CheckOut date, price
tblExtras
Contains additional charges which may be incurred for the reservation, eg. airport collection, cleaning, etc

Since the final invoice will have to collate the price from tblReservation (which is the base accommodation cost) plus any additional charges from tblExtras and then calculate taxes is this the best design to go with? Or would it be better to try to have just one tblProduct which included both accommodation costs and extras even though both are quite different beasts (eg accommodation prices are calculated using dates and have a range of price bands, and extras do not).

Hopefully someone can point me in the right direction.

 

[jdraw]

Here is a free, generic model that may help.

It has a few associated business facts here.

If you want a tutorial to help you with tables and relationships, then I recommend you work through this one from RogersAccessLibrary.

Good luck.

 

[bensubes]

jdraw many thanks I will take a look at these examples and post back,

 

[Solo712]

Hi All,

My 1st post so excuse any obvious short comings, and I am just learning Access so I imagine you may have to be patient with me!

Basically I am trying to design a hotel style db for my business but am getting very confused on the initial table and relationship design that I should be using.

For example for customer orders I have the following design:

tblOrder
tblReservation
tblExtras

tblOrder
Holds the 'header' information customer ID, salesperson, orderdate, etc.
tblReservation
Contains the specific room booked, checkIn, CheckOut date, price
tblExtras
Contains additional charges which may be incurred for the reservation, eg. airport collection, cleaning, etc

Since the final invoice will have to collate the price from tblReservation (which is the base accommodation cost) plus any additional charges from tblExtras and then calculate taxes is this the best design to go with? Or would it be better to try to have just one tblProduct which included both accommodation costs and extras even though both are quite different beasts (eg accommodation prices are calculated using dates and have a range of price bands, and extras do not).

Hopefully someone can point me in the right direction.

The reservation model that jdraw has linked you to is useful. I noted that you are mixing actual charges with items that would be in the "reservation system". As I have some background in hotel systems business, I can tell you that all viable commercial systems would keep booking data separate from occupancy or consumption data (where extras like taxes, minibar, meals, valet service and other invoicing items would be stored. Pricing tables will also have to be maintained against currency exchange rates table. So, to answer your question I would say if you are trying to put together a viable design you need a larger overview of the business operations and maintain a disciplined approach to them. (In practice that means separate them into functional modules).

Best,
Jiri

 

[jdraw]

Good points, Jiri. Nothing like experience in the field to understand the details and practicality.

 

[bensubes]

Hi Jiri,

Many thanks for your posting. Since you have experience in this field could you take a look at my latest design effort attached.

I have taken tblOrders out to be replaced by a larger tblReservations, as you suggest this keeps all the booking data in one place it also acts as the basis for a customer quotation and then invoice.

Attached to tblReservations are the consumables in the tblExtras (cleaning, food etc) is this how you would link these charges. The invoice report would use the reservationID to fill in a subtable of these charges to link them to the customer reservation.

I am now looking at the supplier end of the chain as each reservation and extra will incur a purchase order which must be paid to each individual supplier. The reservation will only link to one supplier but the extras may be provided by one or more. Also some suppliers have end of month payment terms so I would like to combine any outstanding debt owed from both reservations and extras in one supplier payment. Any tips on how this could be achieved.

Any feedback greatly appreciated still feel like I am swimming against a strong tide and making little progress!

Thanks in advance to everyone using the forum.

 

[Solo712]

Hi Jiri,

Many thanks for your posting. Since you have experience in this field could you take a look at my latest design effort attached.

I have taken tblOrders out to be replaced by a larger tblReservations, as you suggest this keeps all the booking data in one place it also acts as the basis for a customer quotation and then invoice.

Attached to tblReservations are the consumables in the tblExtras (cleaning, food etc) is this how you would link these charges. The invoice report would use the reservationID to fill in a subtable of these charges to link them to the customer reservation.

I am now looking at the supplier end of the chain as each reservation and extra will incur a purchase order which must be paid to each individual supplier. The reservation will only link to one supplier but the extras may be provided by one or more. Also some suppliers have end of month payment terms so I would like to combine any outstanding debt owed from both reservations and extras in one supplier payment. Any tips on how this could be achieved.

Any feedback greatly appreciated still feel like I am swimming against a strong tide and making little progress!

Thanks in advance to everyone using the forum.

Hi bensubes,
I did not mean to give you the impression that I was ready to dive into a detailed design of your project with you. For one I am too busy. Looking at your overall approach (and insofar as I can even read the tables - I don't speak Spanish) I would say if I were you I would want to get more practical experience with both DBMS design and the hotel administration before attempting to build my own system. A good way to start would be to get more diagrams from web sites of hotel management software, or email the vendors asking them for detailed functional overview of their product. It's the old system of "beg-steal-borrow" that everyone in the business follow. You don't reinvent the wheel, you put better-designed tires on existing wheels, if you get my meaning. At any rate, I wish you good luck with your project.

Best,
Jiri