You must Sign In to post a response.
  • Category: SQL Server

    How To Add Days And Time For Instructor Schedule


    I need to make design database(ERD) for displaying schedule for every instructor


    day of course(Saturday or Monday,etc)

    And which time it will give course(period FROM 12 PM TO 03 PM ) .

    And what course it will give(c# or SQL) .


    Instructor martin give course c# in Monday and Saturday for period 12 pm - 03 pm

    And SQL in Sunday and Wednesday for period 12 pm - 03 pm .


    So that i designed the following tables :

    Instructors table (InstractorID,InstractorName)

    Courses table (CourseID,CourseName)

    Instructors_courses table (instcourseID,InstractorID,CourseID)

    And relation between instructors table and Courses table many to many so that I do another table is Instructors_Courses table .

    My questions are

    1- How to represent days and time 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 table .

    I can added but it will repeated with every course are this correct or what .
  • #768236
    Hi ,

    In table => Instructors_courses , you can add 3 columns for Course Start Date, StartTime and EndTime.Then you can make join between these these three tables.

    Anjali Bansal

    ~Give your best and lead the world

  • #768281
    you can implement a CHECK constraint to enforce the logic to make design database(ERD) for displaying schedule for every instructor
    CREATE FUNCTION [dbo].[CheckScheduledCourseDoesNotOverlap](
    @course INT,
    @instructor INT,
    @startOn DATETIME2(0),
    @endOn DATETIME(0)
    DECLARE @overlaps BIT = 0;
    IF EXISTS(SELECT * FROM [dbo].[ScheduledInstructor]
    WHERE [Course] = @course
    AND [Instructor] = @instructor
    AND(@startOn BETWEEN [StartOn] AND [EndOn] OR @endOn BETWEEN [StartOn] AND [EndOn]))
    SET @overlap = 1;
    RETURN @overlap;

  • Sign In to post your comments