How to create a table with data from different tables

I have three tables ( course, student, subject ) and they are structured the following way:

student
  id
  st_number
  name
  department_id

subject
   id
   course_code
   name

course
   id
   name
   department_id

A student should be able to register for one or many subjects. But am confused on where all the data will go to when I register a student.

Which table does the data go to when a student makes a registration?

So that later I can be able to show a list of all student registered of a specific course as well as a list of all students registered on a particular subject.

I am not s strong in developing ERD's yet so I hope someone can help

Thanks in advance

tykus

8 years ago

Best Answer

Level 104

You almost described it yourself... a registrations table would act as a pivot table between students and subjects containing only the student_id and subject_id foreign keys (unless you wish to record some additional data).

This is a many-to-many relationship which will allow you to define on the Student model a subjects relationship as follows:

// Student.php
public function subjects()
{
    return $this->belongsToMany(Subject::class, 'registrations');
}

You can get all of the subjects that a student has registered for using $student->subjects which returns a collection.

Note by convention the pivot table would be called student_subject in a Laravel app, however, you are free to call the pivot table whatever you wish, i.e. something meaningful in the context of your domain. In this case, the non-conventional table name must be specified in the relationship defined as I have done above.

Melodia

8 years ago

Level 5

Thanks @tykus I was quite confused but will try that. Never used many to many relationship so I will first check the laravel docs

Melodia

8 years ago

Level 5

@tykus Is the pivot table to unite two tables only? because I will want to have a status_id from a status table.

tykus

8 years ago

Level 104

Ok, so whenever you find yourself in a situation where the pivot table seems to have its own relationships, and you want to do things with it, then you can actually start to think about it as a model in its own right.

A Registration model would have belongsTo relationships with the Student and Subject models, as well as the Status model. A Student hasMany Registrations, but also belongsToMany Subjects; and vice versa.

Melodia

8 years ago

Level 5

I created the pivot table as per laravel convention and it looks like this:

public function up() { Schema::create('student_subject', function (Blueprint $table) { $table->increments('id'); $table->integer('student_id'); $table->integer('subject_id'); $table->timestamps(); }); }

Then the relationship in the student table

public function subjects(){
    return $this->belongsToMany('App\Subject');
}

But now here I see the issue when I try to register a student:

How will I update the student_subject table? Because I create the pivot table but no model was added.

Please or to participate in this conversation.