Database design for a booking application e.g. hotel

Question

I've built one, but I'm convinced it's wrong.

I had a table for customer details, and another table with the each date staying (i.e. a week's holiday would have seven records).

Is there a better way?

Many thanks.

• I code in PHP with MySQL

Answer 1

What's wrong with that? logging each date that the customer is staying allows for what I'd imagine are fairly standard reports such as being able to display the number of booked rooms on any given day.

Answer 2

Here you go

I found it at this page: A list of free database models.

Answer 3

The answer heavily depends on your requirements... But I would expect only storing a record with the start and stop date for their stay is needed. If you explain your question more, we can give you more details.

Answer 4

Some questions you need to ask yourself:

• Is there a reason you need a record for each day of the stay?
• Could you not just have a table for the stay and have an arrival date and either a number of nights or a departure date?
• Is there specific bits of data that differ from day to day relating to one customer's stay?

Answer 5

A tuple-per-day is a bit overkill, I think. A few columns on a "stay" table should suffice.

stay.check_in_time_scheduled
stay.check_in_time_actual
stay.check_out_time_scheduled
stay.check_out_time_actual

Answer 6

Is creating a record for each day a person stays neccessary? It should only be neccessary if each day is significant, otherwise have a Customer/Guest table to contain the customer details, a Booking table to contain bookings for guests. Booking table would contain room, start date, end date, guest (or guests), etc.

If you need to record other things such as activities paid for, or meals, add those in other tables as required.

Answer 7

One possible way to reduce the number of entries for each stay is, store the time-frame e.g. start-date and end-date. I need to know the operations you run against the data to give a more specific advice.

Generally speaking, if you need to check how many customers are staying on a given date you can do so with a stored procedure.

For some specific operations your design might be good. Even if that's the case I would still hold a "visits" table linking a customer to a unique stay, and a "days-of-visit" table where I would resolve each client's stay to its days.

Asaf.

Answer 8

I think the tuple per day approach is really elegant.

Answer 9

You're trading off database size with query simplicity (and probably performance)

Your current model gives simple queries, as its pretty easy to query for number of guests, vacancies in room X on night n, and so on, but the database size will increase fairly rapidly.

Moving to a start/stop or start/num nights model will make for some ... interesting queries at times :)

So a lot of the choice is to do with your SQL skill level :)

Answer 10

Some things that may break your model. These may not be a problem, but you should check with your client to see if they may occur.

• Less than 1 day stays (short midday stays are common at some business hotels, for example)
• Late check-outs/early check-ins. If you are just measuring the nights, and not dates/times, you may find it hard to arrange for these, or see potential clashes. One of our clients wanted a four hour gap, not always 10am-2pm.

Answer 11

I don't care for the schema in the diagram. It's rather ugly.

Schema Abstract

Table: Visit

The Visit table contains one row for each night stayed in a hotel.

Note: Visit contains

• ixVisit
• ixCusomer
• dt
• sNote

Table: Customer

• ixCustomer
• sFirstName
• sLastName

Table: Stay

The Stay table includes one row that describes the entire visit. It is updated everytime Visit is udpated.

• ixStay
• dtArrive
• dtLeave
• sNote

Notes

A web app is two things: SELECT actions and CRUD actions. Most web apps are 99% SELECT, and 1% CRUD. Normalization tends to help CRUD much more than SELECT. You might look at my schema and panic, but it's fast. You will have to do a small amount of extra work for any CRUD activity, but your SELECTS will be so much faster because all of your SELECTS can hit the Stay table.

I like how Jeff Atwood puts it: "Normalize until it hurts, denormalize until it works"

For a website used by a busy hotel manager, how well it works is just as important as how fast it works.

Answer 12

I work in the travel industry and have worked on a number of different PMS's. The last one I designed had the row per guest per night approach and it is the best approach I've come across yet. Quite often in the industry there are particular pieces of information to each night of the stay. For example you need to know the rate for each night of the stay at the time the booking was made. The guest may also move room over the duration of their stay.

Performance wise it's quicker to do an equals lookup than a range in MySQL, so the startdate/enddate approach would be slower. To do a lookup for a range of dates do "where date in (dates)".

Roughly the schema I used is:

Bookings (id, main-guest-id, arrivaltime, departime,...)

BookingGuests (id, guest-id)

BookingGuestNights (date, room, rate)

Answer 13

Wow, thanks for all the answers.

I had thought long and hard about the schema, and went with a record=night approach after trying the other way and having difficulty in converting to html.

I used CodeIgniter with the built in Calendar Class to display the booking info. Checking if a date was available was easier this way (at least after trying), so I went with it. But I'm convinced that it's not the best way, which is why I asked the question.

And thanks for the DB answers link, too.

Best,

Mei