E-commerce 中订单系统的设计

Schema::create('orders', function (Blueprint $table) {
    $table->increments('id');
    $table->string('number')->nullable()->comment('订单号');
    $table->unsignedInteger('address_id')->nullable()->comment('订单地址');
    $table->unsignedInteger('user_id')->index()->comment('用户id');
    $table->integer('items_total')->default(0)->comment('order每一个item的total的和 unit/分');
    $table->integer('adjustments_total')->default(0)->comment('调整金额 unit/分');
    $table->integer('total')->default(0)->comment('需支付金额 unit/分');

    $table->string('local_code')->comment('语言编号');
    $table->string('currency_code')->comment('货币编号');

    $table->string('state')->comment('主状态 checkout/new/cancelled/fulfilled');
    $table->string('payment_state')->comment('支付状态 checkout/awaiting_payment/partially_paid/cancelled/paid/partially_refunded/refunded');
    $table->string('shipment_state')->comment('运输状态 checkout/ready/cancelled/partially_shipped/shipped');

    $table->ipAddress('user_ip')->comment('用户ip ip2long后的结果');

    $table->timestamp('paid_at')->nullable()->comment('支付时间');
    $table->timestamp('confirmed_at')->nullable()->comment('确认订单时间');
    $table->timestamp('reviewed_at')->nullable()->comment('评论时间');
    $table->timestamp('fulfilled_at')->nullable()->comment('订单完成时间');

    $table->json('rest')->nullable()->comment('非核心字段冗余');

    $table->timestamps();
});Copy

Schema::create('order_items', function (Blueprint $table) {
    $table->increments('id');
    $table->unsignedInteger('order_id')->index()->comment('外键');
    $table->unsignedInteger('variant_id')->comment('variant是国外的称呼,国内通常称为sku. 既库存最小单位');
    $table->unsignedInteger('product_id')->comment('冗余字段');
    $table->unsignedInteger('quantity')->comment('购买数量');

    // adjustment calculate
    $table->integer('units_total')->default(0)->comment('item中每一个unit的和. 单位/分');
    $table->integer('adjustments_total')->default(0);
    $table->integer('total')->default(0)->comment('units_total + adjustments_total');
    $table->integer('unit_price')->default(0)->comment('variant单价,冗余字段');

    $table->json('rest')->nullable()->comment('非核心字段冗余');
    $table->timestamps();
});Copy

Schema::create('order_item_units', function (Blueprint $table) {
    $table->increments('id');
    $table->unsignedInteger('item_id')->index();
    $table->unsignedInteger('shipment_id')->comment();
    $table->integer('adjustments_total')->default(0);
    $table->timestamps();
});Copy

Schema::create('adjustments', function (Blueprint $table) {
    $table->increments('id');

    $table->unsignedInteger('order_id')->nullable();
    $table->unsignedInteger('order_item_id')->nullable();
    $table->unsignedInteger('order_item_unit_id')->nullable();

    $table->string('type')->comment('调整的类型 shipping/promotion/tax等等');

    $table->string('label')->comment('结合type决定');

    $table->string('origin_code')->comment('结合label决定');

    $table->bool('included')->comment('是否会影响最终订单需要支付的价格')
        $table->integer('amount');
    $table->timestamps();

    $table->index('order_id');
    $table->index('order_item_id');
    $table->index('order_item_unit_id');
});Copy

{
    id: 1,
    order_id: 1,
    order_item_id: null,
    order_item_unit_id: null,
    amount: 500,
    type: 'shipping',
    label: 'UPS',
    origin_code: null,
    included: 1,
}Copy

[
    {
        id: 2,
        order_id: null,
        order_item_id: null,
        order_item_unit_id: 1,
        amount: -267,
        type: 'promotion',
        label: '8元代金券',
        origin_code: 'KSDI12K2', // 代金券code
        included: 1
    },
    {
        id: 2,
        order_id: null,
        order_item_id: null,
        order_item_unit_id: 2,
        amount: -267,
        type: 'promotion',
        label: '8元代金券',
        origin_code: 'KSDI12K2', // 代金券code
        included: 1
    },
    {
        id: 2,
        order_id: null,
        order_item_id: null,
        order_item_unit_id: 3,
        amount: -266,
        type: 'promotion',
        label: '8元代金券',
        origin_code: 'KSDI12K2', // 代金券code
        included: 1
    },
]Copy

Schema::create('shipments', function (Blueprint $table) {
    $table->increments('id');
    $table->unsignedInteger('method_id')->comment('运输方式 外键');
    $table->unsignedInteger('order_id')->comment('订单 外键');
    $table->string('state')->comment('运输状态');
    $table->string('tracking_number')->nullable()->comment('订单号码');
    $table->timestamps();

    $table->index('order_id');
});Copy

Schema::create('payments', function (Blueprint $table) {
    $table->increments('id');
    $table->unsignedInteger('method_id')->comment('支付方式');
    $table->unsignedInteger('order_id');7
    $table->string('currency_code', 3)->comment('冗余 货币编码');
    $table->unsignedInteger('amount')->default(0)->comment('支付金额');
    $table->string('state');
    $table->text('details')->nullable();
    $table->timestamps();

    $table->index('order_id');
});Copy

public function calculator(Order $order)
{
    $items = $order->items;
    $items->load('adjustments', 'units.adjustments');
    $order->load('adjustments');

    $items->each(function ($item) {
        $item->units->each(function ($unit) {
            $unit->adjustments_total = $unit->adjustments->sum('amount');
        });

        $item->units()->saveMany($item->units);

        $item->adjustments_total = $item->adjustments->sum('amount');

        $item->units_total = $item->quantity * $item->unit_price + $item->units->sum('adjustments_total');

        $item->total = $item->units_total + $item->adjustments_total;
    });

    $order->items()->saveMany($items);

    $order->adjustments_total = $order->adjustments->sum('amount');
    $order->items_total = $order->items->sum('total');
    $order->total = $order->items_total + $order->adjustments_total;
    $order->save();
}Copy