老师好,下面是梳理的情况:

如果采购状态未完成情况,也就是入库时候出现,如果供应商本应根据采购单(001)中的采购数量送100台手机,但是有50台未能及时补齐,只送了50台,所以本次采购单(001)的状态是未完成,那么允许入库本次采购单的商品50台,在这里是需要记录本次商品入库50吗?因为下次入库前是需要核对本次采购单(001)与上次的入库商品数量是否已满100台,可是这几张表中,并没有入库时记录入库了上次入库了多少商品数量,核对逻辑就无从完成了,那么采购状态就不会完成?

t_purchase 采购表
t_productin 入库记录表
t_productin_purchase 采购与入库商品关联表(是否需要在关联表中加入商品入库数量)

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
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = ‘采购与入库商品关联表’ ROW_FORMAT = Dynamic;

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
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = ‘采购表’ ROW_FORMAT = Dynamic;

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) CHARACTER SET utf8 COLLATE utf8_general_ci 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
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = ‘入库信息表’ ROW_FORMAT = Dynamic;