Need ideas/advices about a database structure

Let's think we have 100+ hotels, and each hotel has at least more than 3 room types. I want to hold hotel's capacity for one year in the past and one year in the future. How should i design the database for easiest use.

Example:

A hotel has 30 rooms. 10 x "Standard room", 10 x "Duplex Room", 10 x "Delux room" I will keep this example on standard rooms. Today is: 13.01.2011 I want to keep records from 13.01.2010 to 13.01.2012 What i will store in database is available rooms. Something 开发者_Go百科like this(for standard room):

13.01.2011: 10

14.01.2011: 9 (means 1 standard room sold for this day)

15.01.2011: 8 (means 2 standard rooms sold for this day)

16.01.2011: 10 (all available for this day)

17.01.2011: 7 (means 3 standard rooms sold for this day)

18.01.2011: 10

etc...

Thanks in advance.

Let me try to summarize your question to see if I understand it properly:

You have a set of Hotels. Each Hotel has a set of Rooms. Each Room belongs to one of a number of possible Room Types. The lowest level of detail we're interested in here is a Room.

This suggests a table of Hotels, a lookup table of Room Types, and a table of Rooms: each Room will have a reference to its associated Hotel and Room Type.

For any given day, a room is either booked (sold) or not booked (let's leave off partial days for simplicity at this point). For each day in the year before and the year after the current day, you wish to know how many rooms of each type were available (non-booked) at each hotel.

Now, since hotels need to be able to look at bookings individually, it's likely you would maintain a table of bookings. But these would typically be defined by a Room, a Start Date, and a number of Nights, which isn't ideal for your stated reporting purposes: it isn't broken down by day.

So you may wish to maintain a "Room Booking Log" table, which simply contains a record for each room booked on each day: this could be as simple as a datestamp column plus a Room ID.

This sort of schema would let you generate the output you're describing relatively easily via aggregate queries (displaying the sum of rooms booked per day, grouped by hotel and room type, for example). The model also seems like it would lend itself to an OLAP cube.

I did a homework question like this once. Basically you need at least 3 tables: one which holds the rooms, one which holds the reservations, and another table that links the too because its not a specific room that is reserved at a given time, its a specific type of room.