CODEWITHSUNDEEP
laraveldatabase-design
chinloyal
chinloyal

Reputation: 1141

Database design for many to many relationship with optional columns?

I have a database relationship with two tables, courses and modules. A course can have many modules and a module can belong to many courses. The admin is able to add modules to a course. But now we have a problem, even though you can have:

- Course 1
     module 1 | 3 credits
     module 2 | 4 credits

There can be a situation where the modules can be optional but a student has to do one of them:

- Course 1
   module 3 | 2 credits OR module 4 | 2 credits

So you'll see in the first scenario the student has to pick both those modules to get the credits for the the course, but in the second scenario the student can pick module 3 or module 4 to earn the 2 credits.

So my problem is I have no idea how to form the the relationship between the courses and the modules now that some modules can be optional but one is at least required.

Upvotes: 0

Views: 52

Answers (1)

Matthias S
Matthias S

Reputation: 3553

You will need an intermediate table to realize the many to many relationship. Within this intermediate table you could add a column to specify whether or not the course is optional.

But in the end it will be up to you, within the application, to make sure that the student chooses at least one module if mandatory.

You could have something like:

courses: id, name

modules: id, name

course_module: id, course_id, module_id, optional

Your Model would then look like this:

class Course extends Model
{
    public function modules()
    {
        return $this->belongsToMany(Module::class)->withPivot();
    }
}

And you would check whether or not a module is mandatory by checking

$course->modules->first()->pivot->optional;

Upvotes: 1

Related Questions