Creating a time and location schedule form

[imolorhe]

I am currently designing an application form that the user fills to use a room in a specified time. The application is to check if the room specified is available at the time period specified. I am stil wondering how to do this such that there is no clash. Any info will be very helpful.

[debasisdas]

You need to store the details of the room in database with a flag for empty/occupied.

[imolorhe]

Does that mean that each room will have its own seperate database and the flags will be per hour?

[debasisdas]

All the rooms will be part of a single table.

The flag needs to be set to available when ever it is available, occupied otherwise.

[imolorhe]

so each row/record shows flag information about the rooms in a given time?

[adam_k]

I believe that the flags system isn't the best way to go with this. If you flag, then you force pre-defined time slots for a resource. This means that if I need the resource for 09:45 - 11:15 I have to book them from 09:00 until 12:00.

Why not go with a table containing resource, date, start time, end time.
When you are looking the availability use a select like this:

select * from calendar 
where date = @meeting_date 
and resource = @resource 
and (start_time < @start_time and end_time > @start_time 
or start_time < @end_time and end_time > @end_time

If this returns records, then the resource is not available.

[imolorhe]

there are about 13 rooms in the system and all of them can be used for a single session if they are all available. so will I have 13 slots for resource?

[debasisdas]

Not necessarily. the status of the room will be availble before 09:45 and after 11:15.

It will be occupied during 09:45 - 11:15 only.

You need to maintain separate tables for rooms and rooms bookings.

[imolorhe]

OK! So each room has its own seperate table...right?

[debasisdas]

No a separate table for booking information , there you need to store room number with booking start and end time with date.

[imolorhe]

I don't understand.

[debasisdas]

You need maintain two separate tables, one for rooms information and another for bookings information.

[imolorhe]

I don't understand.

[imolorhe]

how do we know which rooms are being used at particular times if the booking information is seperate?

[imolorhe]

a sample scenario would be useful

[debasisdas]

you need to join both the tables based on room id.

[adam_k]

No a separate table for booking information , there you need to store room number with booking start and end time with date.

You got me confused debasisdas. Are you still going with the slots and flags approach?
Can you please explain a bit whay you are proposing, as I can't see how a booking start and end time with date scenario will use flags for occupied.

[imolorhe]

yeah...please explain further...