Wating-list corss matching

[max_w1]

Hello everyone, i have been developing an application for a hotel with online bookings. everything went fine until i was asked to develop a waiting list(i don't understand why they need this but they really insist).

 

After scratching my head for 3days i decided to keep my mysql database like this:

 

--------Total Rooms----------

|-----------

| Room No  |

|-----------

| 101      |

| 102      |

| 201      |

| 202      |

| 301      |

| 302      |

| 401      |

| 402      |

|----------------------------|

 

------Vacant Rooms-----------

 

|----------------------------|

| Room No  |  From  |  To    |

|----------------------------|

| 102      | 2-8-11 | 3-8-01 |

| 301      | 2-8-11 | 3-8-01 |

| 401      | 2-8-11 | 3-8-01 |

|----------------------------|

 

---------Waiting List--------

|----------------------------|

| Room No  |  From  |  To    | //also includes other information about the guest.

|----------------------------|

| 202      | 2-8-11 | 3-8-01 |

| 101      | 2-8-11 | 3-8-01 |

| 401      | 2-8-11 | 3-8-01 |

| 402      | 2-8-11 | 3-8-01 |

|----------------------------|

 

Now the confusing part comes in. how do i cross-match vacant rooms and waiting list and allot the room to the guest?

[Psycho]

Hello everyone, i have been developing an application for a hotel with online bookings.

 

There are hundreds of posts on this forum for people building booking sites and most, if not all, are from people working on homework assignments. I have a hard time believing that your request is not a homework assignment due to the fact that you have a "Vacant Rooms" table. That makes no sense. You should have a "Rooms" table and a "booked" table that includes a foreign key to the rooms table and the booked dates. Using that relationship you can easily determine what rooms are available and when. Also, why would your waiting list of guests include a room number? Hotels don't put people on waiting lists by room. If anything they may have a waiting list by room type (e.g. standard, suite, luxury) and the people will be offered the "next available" room, or better, based on type.

 

I don't care if this is a homework assignment or not, but I don't like helping people I believe are dishonest.

 

In any event, looking at your tables you need to go back to the drawing board and start over on your data structure. If you stay with what you have I really don't understand what help you need. The waiting list has a room number and the available rooms has a room number. Just do a JOIN query between the two tables using a clause that includes the room number and includes comparisons of the dates to find matches where the rooms are available.