2

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?

APC
  • 144,005
  • 19
  • 170
  • 281
user387478
  • 21
  • 1
  • 2
  • I recently provided an Oracle solution to a simelar general database design question, using three tables. Check it out: http://stackoverflow.com/questions/3193227/what-is-the-best-way-to-optimize-schema-for-capturing-attendance-data/3194711#3194711 – APC Jul 13 '10 at 01:16

4 Answers4

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.

DrColossos
  • 12,656
  • 3
  • 46
  • 67
1

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 Temporal patterns (Fowler)

This can be done with graph databases also, see neo4j example Time-Based Versioned Graphs

oluies
  • 17,694
  • 14
  • 74
  • 117
0

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.

Nick
  • 4,787
  • 2
  • 18
  • 24
0

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

Simple normalization.

MattBianco
  • 1,501
  • 2
  • 20
  • 30