3

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.

  • 1
    1) need to support recurring events? 2) need to support "weird" dates (last friday of every month) 3) multiple instructors? 4) reschedule or cancel events? 5) partial event instances? (could only complete 80% before instructor died) – Neil McGuigan Apr 12 '17 at 21:56
  • 1
    Good input. Recurring events - no. That would really increase the scope. An event can be scheduled more than once by setting up a new instances with a customized schedule. "Wierd" dates - no - That would really tie into the recurring side of things. Multiple Instructors - POSSIBLY ... GREAT THINKING I need to answer that question ASAP. Reschedule / Cancel again, GREAT THINKING need to make a way for that. Partial event - Probably not. Can handle with custom schedule. Please put this in an answer so that I can vote on it. – pStan Apr 13 '17 at 1:34
  • This is a great question, but I don't think there is really ever going to be a single 'best' answer. – Wes Apr 13 '17 at 14:50
2
+50

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.

1

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!

  • Thank you Michael for the tips. I will have to query based on those dates for classes within a range. Also, good thoughts on naming. I'm already rethinking my naming convention since "Event" is has a specific meaning in c# and it's forcing me to use fully qualified names like Namespace.Entities.Event. BTW I'm using EF 6 code first which generates the DDL from c# classes. – pStan Apr 6 '17 at 13:45
1

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!

  • Good input on the keys. I have that covered. I think I can handle all of the schedule times stuff by allowing them to customize the EventInstanceSchedule start and end times... they can do multiple per day. Instructors - yes - I just didn't include, since my question was getting long and it seemed easy. Students will be handled by a "EventRegistrations" table (xref of an event schedule to a user / person) – pStan Apr 13 '17 at 1:40

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.