CODEWITHSUNDEEP
databasesql-server-2008database-designdata-structuresrelational-database
user635378
user635378

Reputation: 21

How to Implement Business Related Schedules in Relational Databases (e.g. Employee Schedules, Appointments, Courses and etc.)

I wasn't sure how I should word the title; I apologize if it is unclear. I’m developing a relational database for a vocational school. I want to provide students with their class schedules online. I would like to make the design as flexible as possible. I want to store exact dates and times including the term and year. I am interested in learning how any of you would approach this. Also I’m searching for a book, training video, tutorial or discussion on this specific topic.

The part I’m unsure of is how to have the amount of days vary from one schedule to another. I understand it is a one-to-many relationship; however, I’m unsure how I should set it up in this scenario. Also what about leap year?

My Approach is to make a Month/Day Table, Year Table and Term Table with foreign keys in a Schedule Table. To Address Leap Year I would just make the Month/Day Table 366 days. I’m unsure if this idea is overkill, I’m looking for the most elegant solution for handling any practical variation in a schedule.

I apologize if I’m way off, I’ve been trying to teach myself Relational Database Development but I’m just getting started.

I would like the schedule to output partly like this:

Example Class A Schedule:
01/02/2011 1:00pm-3:00pm
01/03/2011 2:00pm-4:00pm
01/04/2011 1:00pm-3:00pm
01/05/2011 2:00pm-5:00pm
01/08/2011 1:00pm-4:00pm

Example Class B Schedule:
01/02/2011 1:00pm-3:00pm
01/03/2011 2:00pm-4:00pm

Upvotes: 1

Views: 567

Answers (3)

user635378
user635378

Reputation: 21

I came up with a design based on what you mentioned, I included it below.

* Primary Key
~ Foreign Key

Classes_Table
*ClassID
 ClassName
 ClassNumber
~ScheduleID

Schedules_Table
*ScheduleID
~ClassID

Class_Schedule_Table (There Would Be Many Of These Tables, 1 For Each ScheduleID)
*DayID
 Date
 StartTime
 EndTime
~TermID
~ClassID
~ScheduleID

Terms_Table
*TermID
 TermName

Students_Table
*StudentID
 StudentName

Student_Schedules_Table
*RegistrationID
~StudentID
~ClassID
~ScheduleID

Upvotes: 0

user635378
user635378

Reputation: 21

Here are some of the business rules; they are similar to those of a college:

  • Class schedules must not be constrained to any pattern
  • Class schedules must store every date and time class is held
  • Class Schedules must include the start and end time
  • Classes may or may-not be associated with a term
  • Schedules may be created and stored in the database long before the class is offered
  • Students must be able to see the schedules for classes offered
  • Students must be able to access schedules for classes they are registered for

Upvotes: 1

Joel Brown
Joel Brown

Reputation: 14408

I may or may not understand your business requirement, so this might be really bad advice. However, what I would say is don't create a table to hold days if the only thing in the table is a calendar, without any other attribution around the individual dates.

If your schedule is based on a rotating schedule of an arbitrary number of days, like "Day 1" to "Day 6" or the like, then this deserves its own table, then the occurences of classes become an intersection between the day of the rotation and the class. Each intersection would have a date and time as well as foreign keys to the class and rotation day.

Depending on your business rules, Terms might plug in either above the class or above the arbitrary day schedule. The Term table should probably include starting and ending dates, in any case.

You don't need to do anything special for leap years or days in a month etc, because SQL is smart enough to be able to work out date-related queries.

Can you tell me more about what your business rules are for setting up a schedule? If so, maybe I can hone my advice.

Upvotes: 0

Related Questions