Representing a timetable in a database

Question

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, etc.
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 the class_sessions table and class_unit table.

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.

Answer 1

This does what you said, but I'm still not 100% confident that what you said is what you want. :-)

CREATE TABLE rooms (
  room_num VARCHAR(10) NOT NULL PRIMARY KEY
);

INSERT INTO rooms VALUES 
  ('5B'),
  ('5C'),
  ('5D');

CREATE TABLE class_rooms (
  class VARCHAR(15) NOT NULL,
  room_num VARCHAR(10) NOT NULL,
  CONSTRAINT room_nm_fm_rooms FOREIGN KEY (room_num) REFERENCES rooms(room_num),
  PRIMARY KEY (class, room_num)
);

INSERT INTO class_rooms VALUES 
  ('Algebra', '5B'),
  ('Calculus','5C'),
  ('Discrete Math', '5C');

Having done that, one way to get the room number that's not in use is with a query using SELECT...WHERE...NOT IN. This probably isn't the fastest, but in my experience it's the easiest syntax to understand.

SELECT room_num 
FROM rooms 
WHERE room_num NOT IN (SELECT room_num FROM class_rooms);