Design for CE Events / Training Database

database-agnosticdatabase-design

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.

Best Answer

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.