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:
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!