新零售-进销存（供货商、采购、入库）相关数据表

1、进销存流程

（1）采购员通过进销存系统下订单给供货商
（2）供货商收到采购单后，送货到采购单上的指定仓库
（3）仓库保管员进行入库操作
（4）当一个采购单上的所有商品都送货完毕，仓库保管员将该采购单状态标识为已完成
2、数据表

-- 供货商表
DROP TABLE IF EXISTS `supplier`;
CREATE TABLE `supplier`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '供货商ID',
  `supplier_code` varchar(200) NOT NULL COMMENT '供货商编号',
  `supplier_name` varchar(200) NOT NULL COMMENT '供货商名称',
  `type` tinyint(3) UNSIGNED NOT NULL COMMENT '供货商类型 1-厂家 2-代理商 3-个人',
  `link_person` varchar(20) NOT NULL COMMENT '联系人',
  `tel` varchar(20) NOT NULL COMMENT '联系电话',
  `address` varchar(200) NOT NULL COMMENT '联系地址',
  `bank_name` varchar(200) NOT NULL DEFAULT '' COMMENT '开户银行名称',
  `bank_account` varchar(200) NOT NULL DEFAULT '' COMMENT '银行账号',
  `status` tinyint(3) UNSIGNED NOT NULL COMMENT '状态 1-正常 2-停用',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `unq_supplier_code`(`supplier_code`) USING BTREE,
  INDEX `idx_type`(`type`) USING BTREE,
  INDEX `idx_status`(`status`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COMMENT = '供货商表';

-- 供货商与商品关联表
DROP TABLE IF EXISTS `supplier_sku_relation`;
CREATE TABLE `supplier_sku_relation`  (
  `supplier_id` int(10) UNSIGNED NOT NULL COMMENT '供货商ID，关联supplier表',
  `sku_sn` int(10) UNSIGNED NOT NULL COMMENT '商品唯一标识sn，关联product_sku',
  UNIQUE KEY (`supplier_id`, `sku_sn`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COMMENT = '供货商与商品关联表';

-- 采购表
DROP TABLE IF EXISTS `purchase`;
CREATE TABLE `purchase`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `sku_sn` varchar(256) NOT NULL COMMENT '商品唯一标识sn，关联product_sku表',
  `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，关联staff表',
  `status` tinyint(3) UNSIGNED NOT NULL COMMENT '状态 1-未完成 2-已完成',
  `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  `finish_time` timestamp(0) COMMENT '完成时间',
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否删除 0-未删除 1-已被删除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_sku_id`(`sku_sn`) 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 CHARACTER SET = utf8 COMMENT = '采购表';

-- 入库信息表
DROP TABLE IF EXISTS `into_warehouse`;
CREATE TABLE `into_warehouse`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '入库ID',
  `keeper_id` int(10) UNSIGNED NOT NULL COMMENT '仓管员ID，关联staff表',
  `purchase_id` int(10) UNSIGNED NOT NULL COMMENT '采购ID，关联purchase表',
  `amount` decimal(15, 2) UNSIGNED NOT NULL COMMENT '总金额',
  `supplier_id` int(10) UNSIGNED NOT NULL COMMENT '供应商ID',
  `actual_amount` decimal(15, 2) UNSIGNED NOT NULL COMMENT '实付金额，可能这次没有全送到，所以先支付了一部分钱',
  `payment_type` tinyint(3) UNSIGNED NOT NULL COMMENT '支付方式 1-借记卡 2-信用卡 3-微信 4-支付宝 5-现金',
  `is_invoiced` tinyint(1) NOT NULL COMMENT '是否开了发票 0-没有开发票 1-有开发票',
  `remark` varchar(200) NOT NULL DEFAULT '' COMMENT '备注',
  `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否删除 0-未删除 1-已被删除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_keeper_id`(`keeper_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 CHARACTER SET = utf8 COMMENT = '入库信息表';
打赏赞