Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Inpanic4's avatar

Database Design

I have a users table, i also have one to many relationship, one user may belong to one course , one course can have many users when user is student.( i have roles and permissions from spatie)

I also have lecturer role : many to many relationship with course.

I have implemented my relations on models like:

class Course extends Model
{
 public function lecturers()
    {
				// A course can have many lecturers
        return $this->belongsToMany(User::class);
    }

 public function students()
    {																								//Get course's students
        return $this->hasMany(User::class);
    }
class User extends Model
{
  public function courses()
    {
//A lecturer can have many courses
        return $this->belongsToMany(Course::class);
    }

    public function course()
    {
																								//Get student's course
        return $this->belongsTo(User::class);
    }
}

I have two questions:

Is the above logic correct?

Also, i have a user's role , called admin where he is not assigned no any course, currently the course_id which is needed to make the relations ,is stored on users table, admin will never need that column, where should i store it? Leave it nullable is good approach?

0 likes
4 replies
okusax's avatar
okusax
Best Answer
Level 6

hi, @stavrosg4 I think you are good with a many-to-many relationship instead of a one-to-many in this case. To ensure the correct navigation across the models remember that you need to create a pivot table called course_user which should have course_id and user_id columns (maybe you can rename it as course_lecturer to be more explicit and prevent errors with laravel tables name assumptions). You can read more about many-to-many relationships on https://laravel.com/docs/9.x/eloquent-relationships#many-to-many

Using a many-to-many relation will ensure that you don't have a course_id on the users table nor a user_id in the courses table, and you can have users without courses (like the admin one) or users with more than a single course if needed.

1 like
Inpanic4's avatar

@okusax Thank you for your response

Yeah, got it, i understood clearly everything you told me except one thing.

When i want to associate a student with course i will keep the record on many to many pivot table?

I will use many to many for both relationships?

I want to keep records of both students and lecturers on one course,

I believe better approach is to have course_user with course_id and user_id in order to keep both students and lecturers in the pivot. And retireve users from pivot where role = lecturer or student.

If i make course_lecturer it will be explicit for lecturers i want also students to be in courses.

I have read the documentations i got stuck on the logic

okusax's avatar

@StavrosG4 you can have multiple pivot tables or as you said use only one but filter the data in some other way (like checking the role) or annextra colum with the type of relation. In that case you should make the filter added to the relation function so it filter the data when you navigate across the models. Just be careful to not generate a lot of queries to perform the filter.

1 like

Please or to participate in this conversation.