Storing Schedule in Database

Question

How do i store routine or schedule in Database in efficient way.Such as i have 20 rooms and in this rooms 8-10,10-12,... this way classes are held.How do i store this sort of schedule in database in efficient way?

Answer 1

I would have two tables: Rooms and RoomSchedule.

RoomSchedule (ID int primary key, RoomID int /foreign key to rooms/, From int, To int )

This will allow you to easily check for availability and conflicts, later on, and produce reports.

Answer 2

room
--------------
id INT PK<------------
[more data like]     |
room INT/VARCHAR     |
seats INT            |
                 Foreign Key
room_schedule        |
---------------      |
id INT PK            |
room_id INT<----------
start DATETIME
end DATETIME

You can now eaisly attach certain times to a specific room. To have the start/end in a databasespecific format allows you to transform it in other display of the time.

Answer 3

One table for the rooms with their properties.
One table for the bookings, with their properties.

Simple normalization.

Answer 4

It depends on your requironments eg reporting, leagal and so on.

For an introduction to this problem see Developing Time-Oriented Database Applications in SQL (Snodgrass) and Patterns for things that change with time (Fowler)