1

Aloha DBA's,

I've got major DB Designers-block and I can't wrap my head around the proper way to design the database to meet my web application requirements. This is strictly a DB design question and I thank anybody who can help me out with this.

I'm building this web application in ASP.NET MVC and using MSSQL 2012 as my data back-end, but that's probably irrelevant for this design question.

The application will be a training/course tracker of sorts that will allow any employee with an account to keep track of their mandatory training. Each course should be able to target all employees or specific groups (all, group a, group b, group c). These courses can be one-time training or recurring (weekly, monthly, annually, etc...). Most of the courses will be online that provide certifications upon completion, but we also have on-site training that only requires acknowledgement that you were there, kind of like a check-in. For online training, users will upload their certificate and fill in a DateCompleted field.

So now that the description of the requirements is out of the way, I can discuss my major design areas that are giving me problems.

My current database schema looks like the following:

current database schema

The problem with this schema is that I don't think it's flexible enough for me to be able to properly handle the requirements and I'm not sure how or if I should set up a training plan of sorts (e.g. add courses to a training plan table, then assign the training plan to a user?). And then there's handling offsite vs onsite, and the recurring courses that require re-certification at a specific interval. I'm not sure where I should be storing this...

Again, thank you for any help you can provide. I'm very appreciative. If there's anything else you need to know about my requirements, please let me know.

Mahalo!

Improve this question
3
  • If you are intending to craft a training plan curriculum using SQL Server tools, you will need to flesh out your design first.
    – RLF
    Jan 5, 2017 at 20:21
  • Yeah for sure, I understand that. However, the design itself is what I'm having trouble figuring out. I can try a million different things, but I'm curious on what experts think might work best for my situation. Also, I'm not actually using any SQL Server tools for crafting the training plan curriculum DB, I'm coding everything using ASP.NET MVC Entity Framework, using the code-first approach. Just turning my C# classes into a relational DB.
    – Element808
    Jan 5, 2017 at 21:05
  • 1
    If I include db2's advice, then my other concerns would be about limiting 1:1 affiliations. In my experience, there will always be an exception to these relationships (even when everyone guarantees there won't be, eventually there will be). I would bet that a person will eventually be in both group a and group b, and a course will eventually want to target everyone except those in group c, even if they are in both group a and group c. Designing to accommodate these situations will be more complicated, but will save you from ugly band-aids later.
    – SqlZim
    Jan 7, 2017 at 13:54

1 Answer 1

Reset to default
2

My knee-jerk reaction would be to add a CourseSchedule table between Course and PersonTranscript. The same course could obviously be offered at different times and places, with different instructors, and maybe some sessions are online vs. on-site, etc. and you need a way to separate those properties from those that are universal for the whole Course.

Then going back to the idea of adding training plans, you could have a PersonTrainingPlan table that joins Person to Course directly (they're just planning which courses to take, not actually scheduling them), whereas PersonTranscript (and possibly a PersonSchedule table if the application warrants) joins a Person to a specific instance of a CourseSchedule.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.