CODEWITHSUNDEEP
sql-serverdatabasedatabase-designmany-to-many
Conor8630
Conor8630

Reputation: 345

SQL Server triple many to many relationship

I am trying to map out a database for a training website for employees.

It works as follows:

  1. Each Employee will be assigned modules, one or more.

  2. Each Module will be assigned Courses, again either one or more.

The Same module can be assigned to multiple employees, and the same course can be assigned to multiple modules.

Each Course has a completed Status and each Module would also have a completed status based on when all the courses are completed for that module for that employee.

So Would this be a Tertiary relationship? Or a triple many to many relationship? How would the db be set up?

Here is what I'm thinking so far.

Employee

 EmployeeID 
 Name

Module

 ModuleID
 ModuleName
 Status

Course

 CourseID
 CourseName
 Status

EmployeesModules

 EmployeeID
 ModuleID

ModulesCourses

 ModuleID
 CourseID

I don't think this is correct as Courses would not be unique to each employee but it would be to each module which is not the aim.

How do I improve this design?

Upvotes: 0

Views: 472

Answers (1)

Joel Brown
Joel Brown

Reputation: 14388

Consider the following ERD:

ERD

First some explanation of the diagram...

In this ERD the blue entity types are like master tables. The green entity types are like transaction tables. For attributes, the underlined ones are the candidate key. You may be one who likes surrogate keys for every table - if so - go right ahead, or you may use the (sometimes compound) keys that are shown in the diagram. As long as the keys in the diagram are unique, you're good to go.

Now, what am I talking about and how does it help you?...

Your concern is that you want to track the progress of each employee against each course they're assigned, and, if I understand correctly, you also want to track status against the entire module per employee.

To do this, you need to separate the definition of courses and modules (blue entity types) from tracking who's taking them (green entity types). For this reason, the tables you proposed in your question aren't quite enough to do what you need. In particular, you can't have a Course.status field because the status will vary depending on who's taking the course, obviously.

Your situation doesn't really call for a triple many to many. Instead, what I'm suggesting is that for each employee who is assigned to a module, you create a set of enrollment records for that specific employee and module. Note that this makes it a one-to-many, not a many-to-many, between module (enrollment) and course (enrollment) because once the employee is enrolled in the module, the child courses of that module are known. What's going on in other modules and how a course might be shared between modules doesn't matter as far as this one employee is concerned, if you follow me.

Now you can track an employee's progress against each course in the Employee_Course_Status table. The only potential issue you have to manage in your code is the Module_Enrolment.EmployeeModuleStatus value. Depending on your business rules, this might be calculated based on the many status values at the course level. This creates a little bit of redundancy and the opportunity for an update anomaly (wherein your course status values and your module status values get out of whack).

This is a risk you're going to have to manage. Your schema can prevent many types of data inconsistencies using database normalization and declarative referential constraints, but sometimes you just have to resort to code-based controls.

Upvotes: 1

Related Questions