How to make a 'one OF many relationships' relationship in MySQL/Laravel

user_12016002 0
Viewed 91

Before I get into the question, I am not asking about a one to many relationship.

I have a table called enrollments, an enrollment can either be a lecture, test, or aptitude. The table enrollments has a one to many relationship with the lectures table, the tests table and the aptitudes table.

My question is: how can I make it so an enrollment is allowed to be linked to only one of these three tables? Currently the database allows the enrollment to be both a lecture and a test for example, which shouldn't be possible.

Visual representation of the problem: enter image description here

I googled for a good few hours but could not find anyone with a similar problem, please help. Thanks in advance.

laravel relationship mysql php database
2 Answers

Since you are using laravel, this seems like a good time to try using the many-to-one polymorphic relationship.

So the idea will be that your enrollments table will have a relationship called enrollable which can point to either of the 3 tables. Note: the name enrollable is just following laravel's example naming convention.

To implement this relationship you need to first create the migration, where you can use the convenient morphs method like so

// This will make 2 columns called 'enrollable_type' (VARCHAR) and 'enrollable_id' (UNSIGNED BIGINT)

Schema::table('enrollments', function (Blueprint $table) {
    ...
    $table->morphs('enrollable'); 
});

And then you just have to setup the relationship in your models

// In the Enrollment model you define the enrollable relationship which will be either of 3 model

public function enrollable()
{
     return $this->morphTo('enrollable','enrollable_type','enrollable_id');
}

// in your 3 models you can specify the opposite relationship to refer back to the enrollment

public function enrollments()
{
     return $this->morphMany('App\Enrollment', 'enrollable', 'enrollable_type', 'enrollable_id');
}

When this is setup properly you can use this new relationship like the standard one-to-many relationship. such as $lecture->enrollments to get all enrollments to a lecture.

user_129228810

Since enrollment is only one among the three, the other 2 relationships will be empty and you do not have to worry about them.

Let's say a student is enrolling for a lecture then the table row will have NULL value for test_id and aptitude_id and thus those will be empty relationships.

user_4949070
Related