供应商与入库表设计

如果说快递表、退货表与评价表是售后类相关表，那么供应商与入库表则是售前准备相关表，比较有货物才能售卖。

设计供应商相关表

供应商表

CREATE TABLE t_supplier  (
    id int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
    `code` varchar(150) NOT NULL COMMENT '供货商编号',
    `name` varchar(150) NOT NULL COMMENT '供货商名称',
    type tinyint(3) UNSIGNED NOT NULL COMMENT '类型：1厂家,2代理商,3个人',
    link_man varchar(20) NOT NULL COMMENT '联系人',
    tel varchar(20) NOT NULL COMMENT '联系电话',
    address varchar(200) NOT NULL COMMENT '联系地址',
    bank_name varchar(200) NULL DEFAULT NULL COMMENT '开户银行名称',
    bank_account varchar(200) NULL DEFAULT NULL COMMENT '银行账号',
    `status` tinyint(3) UNSIGNED NOT NULL COMMENT '状态:1可用,2不可用',
    is_deleted tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除',
    PRIMARY KEY (id) USING BTREE,
    UNIQUE INDEX unq_code(code) USING BTREE,
    INDEX idx_code(code) USING BTREE,
    INDEX idx_type(type) USING BTREE,
    INDEX idx_status(status) USING BTREE
) COMMENT = '供货商表';

供货商关联商品表

CREATE TABLE t_supplier_sku  (
    supplier_id int(10) UNSIGNED NOT NULL COMMENT '供货商ID',
    sku_id int(10) UNSIGNED NOT NULL COMMENT '商品ID',
    is_deleted tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除',
    PRIMARY KEY (supplier_id, sku_id) USING BTREE
) COMMENT = '供货商关联商品表';

设计采购与入库表

采购表设计

CREATE TABLE t_purchase  (
    id int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
    sku_id int(10) UNSIGNED NOT NULL COMMENT '商品ID',
    num int(10) UNSIGNED NOT NULL COMMENT '数量',
    warehouse_id int(10) UNSIGNED NOT NULL COMMENT '仓库ID',
    in_price decimal(10, 2) UNSIGNED NOT NULL COMMENT '采购价格',
    out_price decimal(10, 2) UNSIGNED NULL DEFAULT NULL COMMENT '建议零售价',
    buyer_id int(10) UNSIGNED NOT NULL COMMENT '采购员ID',
    `status` tinyint(3) UNSIGNED NOT NULL COMMENT '状态：1未完成,2已完成',
    create_time timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
    is_deleted tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除',
    PRIMARY KEY (id) USING BTREE,
    INDEX idx_sku_id(sku_id) USING BTREE,
    INDEX idx_warehouse_id(warehouse_id) USING BTREE,
    INDEX idx_buyer_id(buyer_id) USING BTREE,
    INDEX idx_status(status) USING BTREE,
    INDEX idx_create_time(create_time) USING BTREE
) COMMENT = '采购表';

入库信息表

CREATE TABLE t_productin  (
    id int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
    storekeeper_id int(10) UNSIGNED NOT NULL COMMENT '保管员ID',
    amount decimal(15, 2) UNSIGNED NOT NULL COMMENT '总金额',
    supplier_id int(10) UNSIGNED NOT NULL COMMENT '供应商ID',
    payment decimal(15, 2) UNSIGNED NOT NULL COMMENT '实付金额',
    payment_type tinyint(3) UNSIGNED NOT NULL COMMENT '支付方式',
    invoice tinyint(1) NOT NULL COMMENT '是否开票',
    remark varchar(200) NULL DEFAULT NULL COMMENT '备注',
    create_time timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
    is_deleted tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除',
    PRIMARY KEY (id) USING BTREE,
    INDEX idx_storekeeper_id(storekeeper_id) USING BTREE,
    INDEX idx_supplier_id(supplier_id) USING BTREE,
    INDEX idx_payment_type(payment_type) USING BTREE,
    INDEX idx_create_time(create_time) USING BTREE
) COMMENT = '入库信息表';

采购入库关系表

CREATE TABLE t_productin_purchase  (
  productin_id int(10) UNSIGNED NOT NULL COMMENT '入库ID',
  purchase_id int(10) UNSIGNED NOT NULL COMMENT '采购ID',
  is_deleted tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除',
  PRIMARY KEY (productin_id, purchase_id) USING BTREE
) COMMENT = '入库商品表';