CODEWITHSUNDEEP
mysqldatabase-designnormalizationdatabase-performance
Shajeel Afzal
Shajeel Afzal

Reputation: 5953

Database design for the Lectures Scheduling for a Single Course

I want to store the Lectures Timing information in the 3rd Normalized Database. The ER Diagram of the Lecture Info is as follows:

ER Diagram of the Lecture Info.

Since the Lecture_Day is multiple value attribute so i have designed the database as follows:

LectureTimeTable:

_ID | Lecture Start Time | Lecture End Time | Lecture Loaction | Course ID FK

LectureDayTable:

_ID | Lecture Time ID FK | Course ID FK | Lecture Day

The Queries for creating these tables are as follow:

CREATE TABLE lectureTimeTable (
_id INTEGER, 
startTime TEXT NOT NULL, 
endTime TEXT NOT NULL,
course_ID_FK INTEGER, 
FOREIGN KEY (course_ID_FK) REFERENCES CourseTable(_id), 
PRIMARY KEY (_id, course_ID_FK) 
);

CREATE TABLE lectureDayTable (
_id INTEGER, 
classDay INTEGER NOT NULL, 
classTime_ID_FK INTEGER, 
course_ID_FK INTEGER, 
FOREIGN KEY (course_ID_FK, classTime_ID_FK) 
REFERENCES lectureTimeTable(course_ID_FK, _id),
PRIMARY KEY (_id, classTime_ID_FK, course_ID_FK)
);

Now the user add new schedule for the course by giving Lecture Location, Start Time, End Time and Lecture Day(s). I am separately adding this information in the tables as follows:

INSERT INTO lectureTimeTable VALUES (1, "12:00", "1:00", 1);

// and for inserting the days i am using a for loop as follows, Here the
for(i=0; i<=selectedDays.length();i++){
INSERT INTO lectureDayTable VALUES (1, selectedDays[i], 1, 1);
}

I am troubling with adding/retrieving data from these tables so i just want to ensure that my database design is not bad.

Thanks.

Upvotes: 1

Views: 2787

Answers (1)

DevZer0
DevZer0

Reputation: 13525

Database Design

You can design a table called timetable this table can hold your weekly schedule. You can fill it up completely with a 2 dimensional array of time blocks and class rooms. This allows you to print the schedule on a screen without having to calculate a grid. grid is already preloaded to the table. Then from a table of lecture you can assign them to the timetable

Upvotes: 2

Related Questions