How to represent a timetable in a database?

[Charles Ndethi]

Hi,

I want to represent a timetable on a mysql database.
I had the idea that i should have three tables:
1.a classdetails table- containing class capacity, classroom name e.t.c
2.a class_sessions table with: start_time and end_time of session,
3.a class_unit table with: the name of the course (MAT003. et.c)

there would also be appropriate foreign keys in class_sessions table and class_unit tables

eventually i want to be able to query for a 'free' class ( one that does not have a class presently-at the time of running of the query)

and return its name e.g ( Room 5b)

Will the tables i have listed be sufficient for the query at hand?

any ideas how to make this better will be appreciated.

Thank you.

[Jerry Winston]

I don't know if this is an improvement but here's a different way to look at class organization:

CourseTable: name of class, course code, Description("Ma th for DB's", MATH, "Database math")

CourseSectionTa ble: course section information (like your details table) meeting times, locations instructors ect.

There's alot of detail associated with large course management systems(like at colleges and universities) that we haven't began to touch. It would help to know how much detail you're looking for and how you want to use the database.

(It just so happens I work at a university)

[Charles Ndethi]

Thanks,
I have left out a lot of detail because of how i intend to use the database. Good thing you work at a university so this may sound piece of cake ish.
Heres is the problem for which i intend to use the database:

In my institution there are several computer labs. These labs have classes e.g in lab1 there is C# programming, lab2 introduction to Unix e.t.c,

When there are no classes in these labs, students can use these 'free' labs for homework, research, twitter e.t.c.

I want to make a simple SMS application using php that returns the name of the 'free' lab when a user sends the keyword 'free' to a certain number.

I am stuck at the logic of representing the timetable in a mysql database.

Just to give you an overview of the detail I have in my database:

-- phpMyAdmin SQL Dump
-- version 3.3.2deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 24, 2011 at 04:51 PM
-- Server version: 5.1.41
-- PHP Version: 5.3.2-1ubuntu4.5

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `CompLabTimetable`
--

-- --------------------------------------------------------

--
-- Table structure for table `labdetails`
--

CREATE TABLE IF NOT EXISTS `labdetails` (
  `LabId` int(15) unsigned NOT NULL AUTO_INCREMENT,
  `LabName` varchar(20) NOT NULL,
  `LabOpeningTime` time DEFAULT NULL,
  `LabClosingTime` time DEFAULT NULL,
  PRIMARY KEY (`LabId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `labdetails`
--

INSERT INTO `labdetails` (`LabId`, `LabName`, `LabOpeningTime`, `LabClosingTime`) VALUES
(1, 'Menengai', '08:00:00', '20:00:00'),
(2, 'Kiambere', '08:00:00', '20:00:00'),
(3, 'Suswa', '08:00:00', '20:00:00'),
(4, 'Elgon', '08:00:00', '20:00:00'),
(5, 'Aberdare', '08:00:00', '20:00:00'),
(6, 'Longonot', '08:00:00', '20:00:00');

-- --------------------------------------------------------

--
-- Table structure for table `labunit`
--

CREATE TABLE IF NOT EXISTS `labunit` (
  `UnitId` int(11) NOT NULL AUTO_INCREMENT,
  `UnitName` varchar(45) DEFAULT 'LabLecture',
  `UnitFaculty` varchar(45) DEFAULT 'UnNamed Lecture Faculty',
  `IdSessionTime` int(15) unsigned DEFAULT NULL,
  PRIMARY KEY (`UnitId`),
  UNIQUE KEY `IdSessionTime_UNIQUE` (`IdSessionTime`),
  KEY `IdSessionTime` (`IdSessionTime`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='The Class Table contains information about the session to be' AUTO_INCREMENT=4 ;

--
-- Dumping data for table `labunit`
--

INSERT INTO `labunit` (`UnitId`, `UnitName`, `UnitFaculty`, `IdSessionTime`) VALUES
(1, 'Advanced Networking', 'FIT', NULL),
(2, 'Sage', 'Fcom', NULL),
(3, 'Pastel', 'SOA', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `lab_sessiontime`
--

CREATE TABLE IF NOT EXISTS `lab_sessiontime` (
  `IdSessionTime` int(15) unsigned NOT NULL AUTO_INCREMENT,
  `Start_time` time NOT NULL,
  `End_time` time NOT NULL,
  `Day_Of_Week` varchar(45) NOT NULL,
  `LabID` int(15) unsigned NOT NULL,
  PRIMARY KEY (`IdSessionTime`,`Start_time`,`End_time`,`Day_Of_Week`),
  KEY `Lab_ID` (`LabID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `lab_sessiontime`
--

INSERT INTO `lab_sessiontime` (`IdSessionTime`, `Start_time`, `End_time`, `Day_Of_Week`, `LabID`) VALUES
(5, '14:30:00', '15:00:00', 'Saturday', 1),
(3, '11:15:00', '12:15:00', 'Tuesday', 3),
(1, '08:15:00', '10:15:00', 'Monday', 5),
(2, '11:15:00', '01:15:00', 'Monday', 5);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `labunit`
--
ALTER TABLE `labunit`
  ADD CONSTRAINT `IdSessionTime` FOREIGN KEY (`IdSessionTime`) REFERENCES `lab_sessiontime` (`IdSessionTime`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `lab_sessiontime`
--
ALTER TABLE `lab_sessiontime`
  ADD CONSTRAINT `Lab_ID` FOREIGN KEY (`LabID`) REFERENCES `labdetails` (`LabId`) ON DELETE NO ACTION ON UPDATE NO ACTION;