Three-way pivot table or complex relationship in Laravel

Question

I have two tables, shipments and customers. In the real world, a customer can be related to a shipment in three ways: as the biller, the destination and/or the origin.

So my question here is, do I have a pivot table with three columns, one for the shipment_id, one for the customer_id, and one for the relationship_type id? Or do I have separate tables? I'm not sure how best to approach this as it's the first of it's kind that I've run up against.

Answer 1

Here is how I would design your project.

I don't think you even need a pivot table or many-to-many relationship.

Note: For clarity and avoiding confusion with the User, I will use Account to refer to what you call a Customer. At the end you used customer account in your comments.

You have a shipment that relates to three different entities. However, those entities are represented by the same data model in your database: the Account model.

A basic one-to-many relationship will suffice.

An account can have many shipments. And the shipment belongs to one account.

Now, how to add the "Type" of the relationship? We don't need a pivot table, we just add another one-to-many relationship.

• An Account as biller may have many shipments, and the shipment belongs to one biller.

• An Account as origin may have many shipments, and the shipment belongs to one origin.

• An Account as destination may have many shipments, and the shipment belongs to one origin.

To explain, here is an example code:

We have three models: User, Account, and Shipment

Let's start with the schema:

    Schema::create('accounts', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->timestamps();
    });

    Schema::create('shipments', function (Blueprint $table) {
        $table->increments('id');
        $table->string('from');
        $table->string('to');

        $table->unsignedInteger('biller_id');
        $table->unsignedInteger('origin_id');
        $table->unsignedInteger('destination_id');

        $table->foreign('biller_id')
            ->references('id')->on('accounts');

        $table->foreign('origin_id')
            ->references('id')->on('accounts');

        $table->foreign('destination_id')
            ->references('id')->on('accounts');

        $table->timestamps();
    });

We have three columns referencing the id on the accounts table.

For the models and the relationships:

Account Model:

class Account extends Model
{
    public function billerShipments()
    {
        return $this->hasMany(Shipment::class, 'biller_id');
    }

    public function originShipments()
    {
        return $this->hasMany(Shipment::class, 'origin_id');
    }

    public function destinationShipments()
    {
        return $this->hasMany(Shipment::class, 'destination_id');
    }

    public function users()
    {
        return $this->belongsToMany(User::class);
    }

}

Shipment Model:

class Shipment extends Model
{
    public function billerAccount()
    {
        return $this->belongsTo(Account::class, 'biller_id');
    }

    public function originAccount()
    {
        return $this->belongsTo(Account::class, 'origin_id');
    }

    public function destinationAccount()
    {
        return $this->belongsTo(Account::class, 'destination_id');
    }

}

Example to create a shipment

    $billerAccount = \App\Account::create(['name' => 'account b']);
    $originAccount = \App\Account::create(['name' => 'account a']);
    $destinationAccount = \App\Account::create(['name' => 'account c']);

    $newShipment = \App\Shipment::create([
        'from'           => 'city 1',
        'to'             => 'city 2',
        'biller_id'      => $billerAccount->id,
        'origin_id'      => $originAccount->id,
        'destination_id' => $destinationAccount->id,
    ]);

    echo $billerAccount->billerShipments()->count(); // 1
    echo $originAccount->originShipments()->count(); // 1
    echo $destinationAccount->destinationShipments()->count(); // 1

    echo $newShipment->billerAccount->name === $billerAccount->name; // 1
    echo $newShipment->originAccount->name === $originAccount->name; // 1
    echo $newShipment->destinationAccount->name === $destinationAccount->name; // 1

For the account-user relationships, it can be many-to-many or one-to-many depending on your requirements.

Answer 2

I faced this couple weeks ago and I came up with a solution.

Assuming that one customer can have different relations to different shipments.

First of all you need a new model for customer roles obviously that model it will be Relation model.

First approach: You could solve this by using more than one pivot table which works but its not a good database design. I solved it first like this but realized its not optimal choice when it comes to db.

Second approach: You could solve this by defining pivot table as a model, but I havent tried that way even though I know it works and its a solution.

Better approach: use one pivot table for three models. In that case you have to define pivot table when you define a relationship example :

Customer model:

public function relations()
{
    return $this->belongsToMany(Relation::class, 'customer_relation_shippment');
}

Relation model:

public function customers()
{
    return $this->belongsToMany(Relation::class, 'customer_relation_shippment');
}

and the other model as well.

now lets say you want to add a relation to a customer. Lets grab first customer and first shipment and say we want to add a relation as a biller:

$customer = Customer::first();

$shipment = Shipment::first();

$relation = Relation::where('name','biller')->get();

$customer->relations()->attach($shipment->id, ['relationship_type'=>$relation->id]);

By using only one pivot table of course its a bit more complex to perform operations towards those models like CRUD, but when it comes to database design/optimazation of course it is the right choice! Note that I came to this conclusion after dealing with a similar real world issue and it turned way more faster db interaction then using more than one pivot.