Database Design
This database schema includes the following models:
UserRolesProgramCourseModuleTags
The aforementioned models are organized into the following tables:
-
USERS- Every user in this system will be either an ADMIN, TEACHER, or STUDENT
-
ROLES- ADMIN, TEACHER, or STUDENT are the primary role types for this application
-
PROGRAMS- The parent of the Program > Course > Module curriculum hierarchy
-
COURSES- The middle child of the Program > Course > Module curriculum hierarchy
-
MODULES- The smallest child in the Program > Course > Module curriculum hierarchy
-
TAGS- ADMIN users can create "tags" for any given Program that act as the possible sub-categories for that program
- Each course in that Program will then have the ability to "belong to" one specific Tag that exists in the categories ("ProgramTags") defined for that Program
- Explicit Join Tables:
-
PROGRAM_TAGS— Joins Many PROGRAMS to Many TAGS in a MANY to ONE to MANY relationship- Though not an explicit table, it's worth noting that Courses have a Many COURSES to One PROGRAM_TAG relationship with this table
-
USER_COURSES— Joins Many USERS to Many COURSES in a MANY to ONE to MANY relationship- Users in this table belong to the role of TEACHER or STUDENT, as ADMINS are not attached at the course-level.
-
USER_ROLES— Joins Many ROLES to Many USERS in a MANY to ONE to MANY relationship
-
All tables will have the columns pictured in the schema above; additionally, each table will contain the following auditing fields:
-
CREATED_BY- The user that first created it -
CREATED_DATE— The timestamp for when it was first created -
LAST_MODIFIED_BY— The user that last edited it -
LAST_MODIFIED_DATE— The timestamp for when it was first created