How to implement prerequisite table in MySQL

I'm using Laravel and I've a table in database called 'course'. It has some fields like 'title', 'content' and etc...
And also I want a course has some prerequisites (other courses). How can I implement this? I can do this by self reference foreign key but in this way there will be just one prerequisite for a course.

2 Answers
2

As a Course can has many Prerequisites and one Prerequisite belongs to many Courses its an n:m relationship.

Create a pivot table course_prerequisite with the fields id, course_id, prerequisite_id.

course_prerequisite

id

course_id

prerequisite_id

To the Course Model add a prerequisites() method with BelongsToMany relationship like so:

prerequisites()

BelongsToMany

prerequisites() : BelongsToMany
{
return $this->belongsToMany(Course::class, 'course_prerequisite', 'course_id', 'prerequisite_id')
}


If you need a reverse relationship, add a courses method with BelongsToMany:

courses

BelongsToMany

courses() : BelongsToMany
{
return $this->belongsToMany(Course::class, 'course_prerequisite', 'prerequisite_id', 'course_id')
}


There are many passible ways to do this. I mention 2 ways to do this.

Method 1:

You can create your course table like this.

Create table courses(
id int(10) not null primary key,
title varchar(25),
content varchar(255)
);


and now create another table for prerequisites mapping.

create table prerequisites(
course_id int(10),
prerequisite_id int(10),
foreign key (course_id) references courses(id),
foreign key (prerequisite_id) references courses(id)
)


Method 2:

Another way of doing this is by adding new column in courses table for prerequisites courses. Although you can not add foreign key constraint in this way but you can manage that programatically.

Create table course(
id int(10) not null primary key,
prerequisites varchar(250),
title varchar(25),
content varchar(255)
)


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.