My first database schema design

Question

I'm new to programming and databases and working on my first application using .Net, Entity (for SQL Server) and Identity.

My application will allow users to create html pages for online courses. Think of it like a course content management system. Instead of users (instructors) having to know HTML markup to create pages for their online courses they will use form fields to create html elements paragraphs<p>, headers<h1> etc. Then they can preview and export these pages for use in their online courses on another system.

A user in the application must have an overall role which will have permissions to do admin things like add users, create courses etc. Users need to be assigned to Courses to access them and can have different roles across Courses. E.g. Person A can be an "Instructor" in Course A, and "Observer" in Course B.

So there are 2 sets of roles and permissions - One at the organization level to do admin tasks and one at the Course level to create and manage content.

Since course development is a collaborative effort with multiple people working on a course, there will be a commenting system next to html elements that users have created.

Schema Design

Am I on the right track? Does it look normalized and are the relationships correct?

Answer 1

I think you're one the right track. Make your database model the process. Try to think of People, Security Roles, and Courses as separate entities that have relationships - not always directly.

In your scenario, you're describing a many to many relationship between roles, courses, and people.

In one table you have people - all the demographical data like name, address, and phone number. A second table that has different roles a person may have with in the system - instructor, student, auditor, teaching assistant, etc. The third table has courses - all the demographical data course name, department, cost, etc. The final table has Security permissions - by web pages, functions, or whatever makes sense in the context of your application.

In between you're going to have tables that break these relationships into 1 to many relationships. A person with the Role of Instructor has multiple courses assigned to them, a table with a surrogate key for a specific instructor and the surrogate key for their course load.

I would like at building something like this - https://en.wikipedia.org/wiki/Swim_lane before building your schema. When you understand who does what, and in what order, you get a better understanding of what your data model should look like.