2

Problem

I need to make a database design (ERD) to display a schedule for every instructor, including the following:

  • day of course (e.g. Saturday or Monday)
  • time it will give course (period from 12 PM TO 03 PM)
  • what course it will give (C# or SQL)

Example

Instructor Martin gives course C#, Monday and Saturday, for period 12 pm - 03 pm, and course SQL on Sunday and Wednesday for period 12 pm - 03 pm.

Details

I designed the following tables:

  • Instructors table (InstructorID, InstructorName)
  • Courses table (CourseID, CourseName)
  • Instructors_courses table (instcourseID, InstructorID, CourseID)

Relationship between Instructors table and Courses table is many to many so I do another table Instructors_Courses.

Questions

  1. How to represent days and times for every course added

    Can I add table for time and table for days and make relation with Instructors_courses table by adding day id and time id (one to many)

    OR do it programming from user interface.

  2. course start date and course end date these two fields how to represent in table Instructors_courses.

    I can added but it will repeated with every course are this correct or what.

0

Try making the instructor_course table like this

CREATE TABLE instructor_course (
    InstructorCourseID int IDENTITY (1,1) CONSTRAINT pk_instructor_course PRIMARY KEY CLUSTERED NOT NULL,
    DayName char(10) NOT NULL,
    CourseID int CONSTRAINT fk_CourseID_instructor_course REFERENCES Course(CourseID) NOT NULL,
    InstructorID ...
    StartTime time NOT NULL,
    EndTime time NOT NULL,
    StartDate date NOT NULL,
    EndDate date NULL);

Then you could also add some constraint to the table if you want to enforce a rule that two courses can't be timetabled at the same time. If a course has multiple sessions during the week each will be a row in this table.

| improve this answer | |
  • Ah, I made an assumption here, which may not be valid, that we were after some sort of timetable that would repeat weekly between the start date and end date. If the timetable is made up of one-off sessions, then the answer by @Mr.Brownstone would be superior. – mendosi Nov 5 '16 at 4:51
-1

How about doing something like this:

CREATE TABLE [dbo].[ScheduledCourses]
(
    [Course] INT NOT NULL REFERENCES [dbo].[Course]([Id]),
    [Instructor] INT NOT NULL REFERENCES [dbo].[Instructor]([Id]),
    [StartOn] DATETIME2(0) NOT NULL,
    [EndOn] DATETIME2(0) NOT NULL,
    PRIMARY KEY ([Course],[Instructor],[StartOn],[EndOn])
);

Now you need to ensure that not only do the start and end dates for a course and instructor not be the same, you need to ensure that they don't overlap as well. For instance I could do this:

INSERT [dbo].[ScheduleCourses]
VALUES (1,1,'01/01/2016 12:00:00', '01/01/2016 15:00:00');
INSERT [dbo].[ScheduleCourses]
VALUES (1,1,'01/01/2016 13:00:00', '01/01/2016 16:00:00');

And as per the normal key rules, it would be valid - but logically it is not. They may not have the same start and end date and time but they do overlap - to get around this you can implement a CHECK constraint to enforce the logic:

CREATE FUNCTION [dbo].[CheckScheduledCourseDoesNotOverlap](
    @course INT, 
    @instructor INT, 
    @startOn DATETIME2(0), 
    @endOn DATETIME(0)
)
RETURNS BIT
AS
BEGIN
DECLARE @overlaps BIT = 0;
IF EXISTS(SELECT * FROM [dbo].[ScheduledCourses] 
           WHERE [Course] = @course 
             AND [Instructor] = @instructor 
             AND(@startOn BETWEEN [StartOn] AND [EndOn] 
              OR @endOn BETWEEN [StartOn] AND [EndOn]))
BEGIN
    SET @overlap = 1;
END
RETURN @overlap;
END
GO

This function checks that a new record does not overlap and existing record. Then, you can apply a check constraint to your table like so:

ALTER TABLE [dbo].[ScheduledCourses] 
ADD CONSTRAINT [CK_ScheduledCourses] 
CHECK ([dbo].[CheckScheduledCourseDoesNotOverlap]([Course],[Instructor],[StartOn],[EndOn]);
GO

This should meet your requirements. I wouldn't personally store the day name within the table, instead I would use the DATEPART function to return it in the query instead.

I haven't had chance to test the code and this is just off the top of my head so be aware when you give it a go.

| improve this answer | |
  • 1
    Please be careful when using scalar UDFs in computed columns and check constraints. brentozar.com/archive/2016/04/… – Erik Darling Nov 5 '16 at 2:00
  • I agree - though the question looks like homework so I though going into detail about the impact on parallelism was overkill. That being said, implementing the same logic within a stored procedure or any other insert method would be pretty easy to do. – World Wide DBA Nov 5 '16 at 2:05
  • 1
    Yeah, the tough thing is that people other than the one asking will see your answer and not give a second thought to doing something that can really barf on performance down the road. – Erik Darling Nov 5 '16 at 3:34
  • Valid point. However, I don't believe that we should stop using scalar functions in check constraints on a whole - I believe that we should be mindful and assess the impact and tradeoffs accordingly based on the scenario we are working with. Being a fellow Chicagoan I'll have a thorough conversation on the topic with Brent the next time I see him. :) – World Wide DBA Nov 5 '16 at 6:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.