How to design the database for merging multiple order invoices
problem description
A rookie. At present, an order automatically generates an invoice, an order form, an invoice form, and an one-to-one relationship. Now the backstage needs to add a function to realize that multiple invoices of a user can be combined to open one.
the environmental background of the problems and what methods you have tried
now I want to add an invoice merge table, an invoice form plus a field merge table id, but the background invoice list query takes the invoice as the main table query, the previous data does not have a merge table id, how to query to bring all the previous data? Or is there any other way
logically controls the relationship between invoices and orders, changing the relationship between invoices and orders to one-to-many. Add an invoice order association table to achieve an one-to-many relationship. Invoices are created when you click generate, and the merged invoices are created based on the order id selected by the user, rather than with the order.
< H2 > title text-sharp-sharp scenario 1: users can choose multiple orders to issue an invoice < / H2 >
you can add the invoice_id field to the order table at this time, and the user can select the order (A, B, C) to merge and open an invoice to create the data in the invoice table, and then update the invoice_id field of the order selected by the user. The design is as follows:
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateOrdersTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('orders', function (Blueprint $table) {
$table->increments('id');
$table->integer('invoice_id');
//
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('orders');
}
}
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateInvoicesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('invoices', function (Blueprint $table) {
$table->increments('id');
//
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('invoices');
}
}
scenario 2: if the system restricts that an order must correspond to an invoice, the user can choose to invoice the combined order. At this point, modify the invoice table under the above logic and add the host_id field to indicate the invoice id to be merged. This design if host_id > 0 both merged invoices, while compatible with multiple orders to open one invoice, multiple invoices combined into one.
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateInvoicesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('invoices', function (Blueprint $table) {
$table->increments('id');
$table->integer('host_id')->default(0);
//
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('invoices');
}
}
the above code is written using laravel data migration.
order table
invoice table
invoice order association table (stores which orders are invoiced. Which invoice was issued)
ID...
IDID/)
pass in the order ID that needs to be invoiced when invoicing.
check whether the invoice has been issued first. If not, you can write the invoice association table in batches
.
