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

Dirk313's avatar

Laravel Relationships with pivot table

Hi all, I have a made 3 migration tables "Customers", "Products", "Invoices" with a pivot table, and not sure if i understand the workings behind the relationships correctly, if one has 2 columns with related data then a pivot table should be created then one would reference the pivot tables by foreignId , like 2022_12_19_100107_create_invoice_product_table.php,

I just want to share my code just to know if I do understand it correctly or if there might be something I'm doing wrong, any advise and guidance will be appreciated

This is the Customers table

	Schema::create('customers', function (Blueprint $table) {
        $table->id();
        $table->unsignedBigInteger('products_id')
            ->references('id')
            ->on('products')
            ->onDelete('cascade')
            ->comment('reference to products');

        $table->unsignedInteger('created_by_admin_user_id');
        $table->foreign('created_by_admin_user_id')
            ->references('id')
            ->on('admin_users')
            ->onDelete('cascade');

        $table->string('company');
        $table->string('name');
        $table->string('email');
        $table->string('contact');
        $table->string('address');
        $table->string('company_reg');
        $table->string('vat_no');
        $table->boolean('enabled')->default(false);
        $table->timestamps();
    });

This is the products table

		Schema::create('products', function (Blueprint $table) {
        $table->id();
        $table->unsignedInteger('customers_id')
            ->references('id')
            ->on('customers')
            ->onDelete('cascade')
            ->comment('references to customers');

        $table->foreignId('customers')
            ->references('id')
            ->on('customers')
            ->onDelete('cascade');

        $table->unsignedInteger('created_by_admin_user_id');
        $table->foreign('created_by_admin_user_id')
            ->references('id')
            ->on('admin_users')
            ->onDelete('cascade');

        $table->string('product_name');
        $table->string('description');
        $table->string('brand');
        $table->string('catagory');
        $table->string('unit_price');
        $table->timestamps();
    });

this is the invoices table

		Schema::create('invoices', function (Blueprint $table) {
        $table->id();
        $table->unsignedBigInteger('customers_id');
        $table->unsignedBigInteger('products_id');
        $table->unsignedInteger('created_by_admin_user_id');
        $table->foreign('created_by_admin_user_id')
            ->references('id')
            ->on('admin_users')
            ->onDelete('cascade');

        $table->date('published_at')->nullable();
        $table->date('due_date')->nullable();
        $table->string('payment_type')->default('EFT');
        $table->boolean('paid')->default(false);
        $table->string('tax');
        $table->string('subtotal');

        $table->timestamps();
    });

This is the pivot table

		Schema::create('invoice_product', function (Blueprint $table) {
        $table->id();
        $table->foreignId('products_id')
            ->references('id')
            ->on('products')
            ->comment('reference to products');

        $table->foreignId('invoices_id')
            ->references('id')
            ->on('invoices')
            ->comment('reference to invoices');

        $table->foreignId('customers_id')
            ->references('id')
            ->on('customers')
            ->comment('reference to customers');

        $table->timestamps();
    });
0 likes
1 reply
aleahy's avatar
aleahy
Best Answer
Level 25

Before trying to define the relationships in the database, I always find it best to try to define the relationships between the actual real life objects. These relationships would define an interaction between two models, not a three-way interaction.

Eg: A customer can have many invoices, while an invoice belongs to a single customer (1 to many). An invoice has many products, while a product can be on many invoices (many to many). When we look at the relationship between customer and product, it doesn't make sense to leave the invoice out of it. But we should look at that as more of a pathway, than a direct relationship. Eg: customer -> invoice -> product.

Now that the relationships are defined, this helps us determine how to set up the database.

  • Since an invoice belongs to a customer, the invoice needs a foreign key to the customer. Not the other way round.
  • Since the relationship between products and invoices is many to many, we need a pivot table (invoice_product) between them (and only them).

Please or to participate in this conversation.