Design for CE Events / Training Database

Question

I'm working on a design for a continuing education training database. Users will register for a CE course/event that could be one day or multiple days. Courses are taught multiple times per year.

The part I wanted some review on is the calendar part of the DB.

Here is the design criteria:

• Multiple events taught on varying schedules throughout the year.
• Classes/Events can be part of a day, one day or multiple days.
• Times can be different across days for the same Instance. Ex. "Summer xyz Training" held 9am-5pm Monday, 9-noon Tuesday, etc.

Here is my design for this part of the DB so far:

Event

Class, event or course... can be scheduled multiple times... taught at various times, dates, and locations repeating throughout the year.

• Event_ID - Could be called Class_ID. Could be class or event.
  I went with Event_ID to handle either.
• Name - Ex. "xyz Training"
• Description -
• Objectives - yes - hidden
• Outline - yes - hidden
• Duration_hours ( Break up by Class Schedule )
• Notes - Hidden from user

Event_Instance

Instance of an event. Can be across multiple days. Event_Instance_Schedule for the Instance must add up to Event.Duration_Hours

• Event_Instance_ID
• Location_ID - Tie to Location table for where (could be physical or on line)
• Description - Ex. "Summer xyz training"

Event_Instance_Schedule

Block of time as part of Event_Instance that adds to total Duration_Hours.

• Event_ID
• Event_Instance_ID -
• Event_Instance_Schedule_ID
• Instructor_ID?
• Start_Date_Time - Ex. "1/1/2017 8am"
• End_Date_Time - Ex. "1/1/2017 5pm"
• Notes

I don't really need to setup Google Calendar type re-occurrence, thankfully. My thinking was that the user chooses a class, gives that instance a title like "Summer xyz training", and then schedules times until they reach the Duration_hours number.

I think it's fairly simple. Just looking for feedback or "have you thought about this... " feedback, or "gotchas" I'm not considering.

I have reviewed a number of event / class schedule DB designs, but none quite fit what I'm doing...too much or too little. The question is meant to be at a high level regardless of the DB technology (MySQL, SQL Server, etc.)

The DDL is the easy part in my mind, but I should probably do that based on a good design. I just wondered if anyone had done an Event DB kind of thing before and if they had feedback based on my initial concept.

Answer 1

My wife is a nurse educator who had no even management platform. I developed one in a VERY similar fashion that you outlined here. The logic and thought process was the same.

You should be safe with this overall schema.

Answer 2

One tip would be to make sure you partition/index the Start_Date_Time and End_Date_Time. If you want to (for example) query if room x is free on the 11th of march then you're going to want to have that partitioned/indexed.

If I'm being really critical perhaps the name of the two notes and descriptions columns should be a bit more specific for clarity. Perhaps a column naming convention would sort that out.

Good luck!

Answer 3

I think you should add the Event_ID to the table Event_Instance as a Foreign Key as (for my understanding) an Event_Instance is related to an Event.

Creating indexes on the foreign key fields should be helpful and improve select statements with joins or delete statements.

I'm not sure how you will implement the schedule times. Maybe you need to consider coffee or lunch breaks. Checking for the duration hours should be done easier on application side.

How about additional tables like students or instructors?

I hope you get it done!