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();
});