macOS Need help with room booking sql

[raymondu999]

Hey all. Not sure if this was the right place to look. I'm building a room booking system and we're using SQL server (I know!!).

I have a "reservations" table where I store the details of each reservation (date of booking, start/end timings, which room). I have a static list of rooms that are bookable.

I'd like to show it on a schedule (a la iCal's weekly view). But I'm not sure how to do this as I'm not sure what records to "select" given that I won't have any records to signify which rooms are empty. Just which rooms are full at particular times, and I don't think I will be able to sort this table either.

Thanks. Any and all help will be appreciated.

 

[balamw]

I have a "reservations" table where I store the details of each reservation (date of booking, start/end timings, which room). I have a static list of rooms that are bookable.

I'd like to show it on a schedule (a la iCal's weekly view).

I would suggest reading up on the NSCalendar format and trying to stuff the info from your SQL into NSCalendar items, e.g. http://developer.apple.com/library/...esAndTimes.html#//apple_ref/doc/uid/10000039i

If you want to show availability, rather than bookings, you'd just have to find all the bookings in the period you want to view and step through and create calendar items for each room when bookings do not exist. (EDIT: Lee explained what I was thinking better than I).

B

 

[lee1210]

It seems to me that you would have a timeframe (say 1 week) that you want to display at once... so you'd have something like:
select room_key,startdate,enddate from reservations where enddate >= '2011-01-09 00:00' and startdate < '2011-01-16 00:00' group by room_key;
If you needed the list of rooms for your display, you could grab that in a separate query. The rest seems like you'd just implement in code. I'd imagine each room having available blocks (down to the hour seems fine, 168 in a week per room, you can go to a smaller resolution if needed). You'd then go through the list of reservations marking a room used whenever it is reserved, plus perhaps an hour in advance in case the guest arrives early, plus at least 1-2 hours afterwards for cleaning, "make-ready", etc. Once you'd gone through your result set from the above query, each room would have a schedule laid out of busy/available times. If you're just needing to have a schedule that shows when you're fully booked vs. times at least one room is available it should be fairly straight-forward to merge the results of all of your rooms. It might be worthwhile to visually indicate times where you're below a threshold, say 2 empty rooms, so you know those are "busy" times, but rooms are still available.

-Lee

 

[jared_kipe]

Why not keep all of your "rooms" in a "Room" table and then have some kind of flag to specify if it is booked. Then you can search for ones that are not booked.

Also for doing the bookings you should probably implement a Booking table with a foreign key "RoomID"

 

[balamw]

Is the db structure set in stone or can it still be modified and just has to be stored in SQL?

I'm sure folks here could give more concrete help constructing the queries if they could see how the db is structured.

B

 

[lee1210]

Why not keep all of your "rooms" in a "Room" table and then have some kind of flag to specify if it is booked. Then you can search for ones that are not booked.

I would disagree a little because booked is a transient, time-based thing. Unless you want some process checking every minute, 15 minutes, whatever checking if a room is booked "right now" and updating the "booked" field, that thing is not going to reflect things "right now". In any event, even if this was true this helps you say if a room is available "right now" but not help you lay out availability.

Also for doing the bookings you should probably implement a Booking table with a foreign key "RoomID"

This is absolutely required, and i just assumed a foreign key in reservations to room when i wrote my response.

-Lee

 

[balamw]

Unless you want some process checking every minute, 15 minutes, whatever checking if a room is booked "right now" and updating the "booked" field, that thing is not going to reflect things "right now".

That's basically why I was asking about the db structure. If you wanted to do what jared_kipe suggests, you could just store the booked/free data instead of (or on top of) the start/stop dates? You would only need to modify it when a booking is created/changed/deleted.

I believe that's what MS Mail/Exchange did/does.

B

 

[raymondu999]

I'm using MS SQL, and Visual Studio (Not my idea ) and C# (again, not my idea) the db design can be changed around

 

[timbos]

I would disagree a little because booked is a transient, time-based thing. Unless you want some process checking every minute, 15 minutes, whatever checking if a room is booked "right now" and updating the "booked" field, that thing is not going to reflect things "right now". In any event, even if this was true this helps you say if a room is available "right now" but not help you lay out availability.

Couldn't you use a stored procedure to return whether a room is booked rather than a table?

 

[lee1210]

Couldn't you use a stored procedure to return whether a room is booked rather than a table?

There are lots of ways to skin a cat. You could probably do this with a view, too. I guess I prefer my application logic in my application code and my data in my data store. I still don't know how knowing if a room is booked at one specific time makes this a much easier problem. You're going to need to know for a whole week. I wouldn't want to ask the database about this 168 times, though I guess you could have a procedure return 168 rows showing if a room is available each hour of the week. But again, that seems like app/business logic to me. I guess it's giving you a "view of the data"... i don't know. Whatever floats one's boat, i suppose.

Scheduling is not a problem domain I've worked in, so this is all off the cuff. Maybe putting a lot of the logic in the database makes things much better, but we have more devs than DBAs so I'd rather have it in the app than the DB. We also have multiple RDBMSs to support, which means 2x the work for putting it in the DB.

-Lee

 

[jared_kipe]

.. checking every minute, 15 minutes, whatever checking if a room is booked "right now"..

Wait what kind of hotel is this??

When I originally read the OP I assumed it was more about booking, and less about displaying in cal format. I didn't really think about booking at any moment in the future.

There are some assumptions that could help.
Lets assume that all rooms are equal and the database is laid out like this Oh and Room is indexed from 0-num of rooms without any interruptions and all rooms are valid rooms.

Room
|ID|

Booking
|ID|RoomID|StartDateTime|EndDateTime|

Psudocode

function NextRoomForTimeRange(start, end) {
     //booked rooms during that time, not so sure about datetime syntax on this
     booked = SELECT RoomID FROM Booking WHERE  StartDateTime < end AND EndDateTime > start ORDER BY RoomID
     // this means they overlap and thus these returned RoomID's are booked durring this date range
     for ( i=0; i < #ofRooms; i++ ) {
          if (booked does not contain i) return i;
     }
     return NoRoomAtTheInn = -1;
}

You may have noticed that Room doesn't seem to do anything. That is correct in this, probably oversimplified, model. But it could be useful to get things like the number of rooms total, or to get all the rooms and check them off one by one as to wether or not they are in the booking list, and return the first one that isn't. This was an assumption I worked around by assuming rooms started at 0 and went to #ofRooms


Laying out the bookings is relatively trivial as well, just do
SELECT * FROM Booking WHERE EndDateTime > BeginingOfDay AND StartDateTime < EndOfDay ORDER BY StartDateTime
For each day of the week and find a way to display them. Maybe make a CSV from them and have Exel display it for you.

 

[jared_kipe]

Sooo what happened with this?