So, i have a project on which the client can order food. The products are stored in a cart and then are inserted in a database. I want to design the Orders and Products tables but i also want to store the quantity and the subtotal price for each product.Orders and Products have N:N relation. Here are my table migrations:
Schema::create('orders', function (Blueprint $table) {
$table->increments('id');
$table->integer('user_id');
$table->integer('product_id');
$table->enum('status',['open','closed']);
$table->timestamps();
});
Schema::create('products', function (Blueprint $table) {
$table->increments('id');
$table->string('name');
$table->string('description')->nullable();
$table->float('price',8,2);
$table->string('image_path');
$table->timestamps();
});
The pivot table:
Schema::create('order_product', function (Blueprint $table) {
$table->integer('order_id')->unsigned()->nullable();
$table->foreign('order_id')->references('id')->on('orders')->onDelete('cascade');
$table->integer('product_id')->unsigned()->nullable();
$table->foreign('product_id')->references('id')->on('products')->onDelete('cascade');
$table->timestamps();
});
The ways I see the solution are:
- add the quantity and subtotal attributes to the pivot table( which i am not sure if it is good or bad)
- insert rows for each of the specific product quantity (which will create a lot of rows for the same order and i'm not sure if this is good too).
Can you point me to a better solution? I am new to web development.