简化数据库关系设计

Question

I have this little Laravel project where members can pay to attend to a class, pay insurance, event, or pay to pass a special type of exams.

So I have 5 tables members, classes, insurance, events and exams, and I need to add the payments table so I can track members payments for each one of those tables.

So for the moment the payments table is going to be like this :

id      date            amount      member_id   class_id    insurance_id    event_id    exam_id
1       2019-01-01      150         2           1           NULL            NULL        NULL
2       2019-01-01      250         11          NULL        14              NULL        NULL
3       2019-01-01      220         15          NULL        NULL            6           NULL
4       2019-01-01      350         32          NULL        NULL            NULL        8

Is there a better way to this without having : class_id, insurance_id, event_id, exam_id fields in the payments table, because this is going to make the relationship more complexe I guess, and to also maybe simply DB queries using Laravel as a PHP Framework.

Answer 1

I think that the best way to go is a one to many polymorphic relationship.

Database/migrations setup

To do so you would need:

• A members table to hold members data.
• One table for each type of payable entity in your system (classes, insurance, events and exams)
• A payments table structured with the following migration:

Schema::create('payments', function (Blueprint $table) {
    $table->increments('id');

    // Payment related fields like date and amount

    $table->unsignedInteger('member_id');
    // Assuming id is the primary key on the members table
    $table->foreign('member_id')->references('id')->on('members');

    // This one will create two fields:
    //  - payable_type
    //  - payable_id
    $table->morphs('payable');
});

This way each record would hold the member that made the payment (through member_id) and the paid entity (through payable_type and payable_id).

Eloquent Models Relationships

You then have to setup morphing relationships on your payment model like:

// app\Payment.php

class Payment extends Model
{
    // ...
    public function payable()
    {
        return $this->morphTo();
    }

    public function member()
    {
        return $this->belongsTo(Member::class);
    }
}

Your member model as:

// app\Member.php

class Member extends Model
{
    // ...    
    public function payments()
    {
        return $this->hasMany(Payment::class);
    }
}

And for each payable entity:

// app\Insurance.php

class Insurance extends Model
{
    // ...

    public function payments()
    {
        return $this->morphMany(Payment::class, 'payable');
    }
}

// app\Event.php

class Event extends Model
{
    // ...

    public function payments()
    {
        return $this->morphMany(Payment::class, 'payable');
    }
}

// app\Exam.php

class Exam extends Model
{
    // ...

    public function payments()
    {
        return $this->morphMany(Payment::class, 'payable');
    }
}

// app\Course.php

// I renamed "Class" payable entity to "Course"
// as "class" is a reserved keyword in PHP and
// you can't give a class the name "Class".
class Course extends Model
{
    // ...

    public function payments()
    {
        return $this->morphMany(Payment::class, 'payable');
    }
}

Usage

If you would need to retrive all the payments (of different types) made by a user you would just have to query the member model like that:

$member = Member::with('payments.payable')->first();
dd($member->payments);

If you set up model relations accordingly, Laravel will resolve the different correct Eloquent model instances based on the payable_type and payable_id of each record with a matching member_id.

As a result, $member->payments will be a collection of instances of Payment each with a relationship to one of Insurance, Exam, Course and Event classes (based on the payable_type stored in database).

For reference and a more detailed usage, you could look at the official Laravel docs here.