设计供应商表

-- 供货商表
DROP TABLE IF EXISTS `t_supplier`;
CREATE TABLE `t_supplier` (
  `id` BIGINT(20) UNSIGNED NOT NULL COMMENT '供货商编码',
  `code` VARCHAR(64) NOT NULL COMMENT '供货商编号',
  `suppname` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '供货商名称',
  `type` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '类型 0：厂家；1：代理商；2：个人',
  `contacts` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '联系人',
  `mobile` VARCHAR(16) NOT NULL COMMENT '联系电话',
  `areaid` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '所在地区',
  `address` VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '联系地址',
  `bankname` VARCHAR(128) NOT NULL COMMENT '开户银行名称',
  `bankaccount` VARCHAR(128) NOT NULL COMMENT '银行账号',
  `status` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '状态 0：启用；1：禁用',
  `isdeleted` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '逻辑删除 0：正常；1：已删除',
  `createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 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
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT = '供货商表';

-- 供货商商品表
DROP TABLE IF EXISTS `t_supplier_sku`;
CREATE TABLE `t_supplier_sku` (
  `suppid` BIGINT(20) UNSIGNED NOT NULL COMMENT '供货商编码',
  `skuid` BIGINT(20) UNSIGNED NOT NULL COMMENT '商品编码',
  `isdeleted` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '逻辑删除 0：正常；1：已删除',
  `createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`suppid`, `skuid`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT = '供货商商品表';

-- 采购表
DROP TABLE IF EXISTS `t_purchase`;
CREATE TABLE `t_purchase` (
  `id` BIGINT(20) UNSIGNED NOT NULL COMMENT '采购编码',
  `skuid` BIGINT(20) UNSIGNED NOT NULL COMMENT '商品编码',
  `numbers` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '数量',
  `whid` BIGINT(20) UNSIGNED NOT NULL COMMENT '仓库编码',
  `price` DECIMAL(10, 2) UNSIGNED NOT NULL COMMENT '采购价格',
  `saleprice` DECIMAL(10, 2) UNSIGNED NOT NULL COMMENT '建议零售价',
  `buyerid` INT(11) UNSIGNED NOT NULL COMMENT '采购员编码',
  `status` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '状态 0：未完成；1：已完成',
  `isdeleted` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '逻辑删除 0：正常；1：已删除',
  `createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_skuid`(`skuid`) USING BTREE,
  INDEX `idx_whid`(`whid`) USING BTREE,
  INDEX `idx_buyerid`(`buyerid`) USING BTREE,
  INDEX `idx_status`(`status`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT = '采购表';

-- 入库信息表
DROP TABLE IF EXISTS `t_productin`;
CREATE TABLE `t_productin` (
  `id` BIGINT(20) UNSIGNED NOT NULL COMMENT '入库信息编码',
  `keeperid` INT(11) UNSIGNED NOT NULL COMMENT '保管员编码',
  `suppid` BIGINT(20) UNSIGNED NOT NULL COMMENT '供货商编码',
  `total` DECIMAL(12, 2) UNSIGNED NOT NULL COMMENT '总金额',
  `amount` DECIMAL(12, 2) UNSIGNED NOT NULL COMMENT '实付金额',
  `channel` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '支付渠道 0：现金；1：支付宝；2：微信',
  `method` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '支付方式 0：扫码；1：小程序；2：其他',
  `invoice` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '是否开票 0：否；1：是',
  `remark` VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT DEFAULT '' COMMENT '备注',
  `isdeleted` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '逻辑删除 0：正常；1：已删除',
  `createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_keeperid`(`keeperid`) USING BTREE,
  INDEX `idx_suppid`(`suppid`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT = '入库信息表';

-- 入库商品表
DROP TABLE IF EXISTS `t_productin_purchase`;
CREATE TABLE `t_productin_purchase` (
  `insid` BIGINT(20) UNSIGNED NOT NULL COMMENT '入库编码',
  `buyid` BIGINT(20) UNSIGNED NOT NULL COMMENT '采购编码',
  `isdeleted` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '逻辑删除 0：正常；1：已删除',
  `createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`insid`, `buyid`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT = '入库商品表';

---

感谢支持

更多内容，请移步《超级个体》。