设计订单表

-- 订单表
DROP TABLE IF EXISTS `t_order`;
CREATE TABLE `t_order` (
  `id` BIGINT(20) UNSIGNED NOT NULL COMMENT '订单编码',
  `code` VARCHAR(256) NOT NULL DEFAULT '' COMMENT '流水号',
  `type` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '订单类型 0：线下销售；1：线上销售',
  `customerid` BIGINT(20) UNSIGNED NOT NULL COMMENT '会员编码',
  `voucherid` BIGINT(20) UNSIGNED NULL DEFAULT NULL COMMENT '购物券编码',
  `amount` DECIMAL(10, 2) UNSIGNED NOT NULL COMMENT '总金额',
  `quantity` INT(11) UNSIGNED NOT NULL DEFAULT '1' COMMENT '总数量',
  `channel` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '支付渠道 0：余额；1：积分；2：支付宝；3：微信；4：现金；5：借记卡；6：信用卡；7：信用赊账',
  `method` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '支付方式 0：扫码；1：小程序；2：其他',
  `status` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '订单状态 0：未付款；1：已付款；2：待发货；3：已发货；4：已签收',
  `weight` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '重量（克）',
  `isdeleted` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '逻辑删除 0：正常；1：已删除',
  `createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `paytime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '付款时间',
  `delivertime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发货时间',
  `confirmtime` 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_customerid`(`customerid`) USING BTREE,
  INDEX `idx_status`(`status`) 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_order_snapshot`;
CREATE TABLE `t_order_snapshot` (
  `id` BIGINT(20) UNSIGNED NOT NULL COMMENT '快照编码',
  `orderid` BIGINT(20) UNSIGNED NOT NULL COMMENT '订单编码',
  `code` VARCHAR(256) NOT NULL DEFAULT '' COMMENT '流水号',
  `type` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '订单类型 0：线下销售；1：线上销售',
  `customerid` BIGINT(20) UNSIGNED NOT NULL COMMENT '会员编码',
  `voucherid` BIGINT(20) UNSIGNED NULL DEFAULT NULL COMMENT '购物券编码',
  `amount` DECIMAL(10, 2) UNSIGNED NOT NULL COMMENT '总金额',
  `quantity` INT(11) UNSIGNED NOT NULL DEFAULT '1' COMMENT '总数量',
  `channel` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '支付渠道 0：余额；1：积分；2：支付宝；3：微信；4：现金；5：借记卡；6：信用卡；7：信用赊账',
  `method` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '支付方式 0：扫码；1：小程序；2：其他',
  `status` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '订单状态 0：未付款；1：已付款；2：待发货；3：已发货；4：已签收',
  `weight` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '商品重量（克）',
  `createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `paytime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '付款时间',
  `delivertime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发货时间',
  `confirmtime` 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_customerid`(`customerid`) USING BTREE,
  INDEX `idx_status`(`status`) 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_order_detail`;
CREATE TABLE `t_order_detail` (
  `orderid` BIGINT(20) UNSIGNED NOT NULL COMMENT '订单编码',
  `skuid` BIGINT(20) UNSIGNED NOT NULL COMMENT '商品编码',
  `shopid` BIGINT(20) UNSIGNED NOT NULL COMMENT '门店编码',
  `spuid` BIGINT(20) UNSIGNED NOT NULL COMMENT '产品编码',
  `price` DECIMAL(10, 2) UNSIGNED NOT NULL COMMENT '原价格',
  `saleprice` DECIMAL(10, 2) 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 (`orderid`, `skuid`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT = '订单详情表';

-- 订单详情历史表
DROP TABLE IF EXISTS `t_order_detail_history`;
CREATE TABLE `t_order_detail_history` (
  `id` BIGINT(20) UNSIGNED NOT NULL COMMENT '流水主键',
  `orderid` BIGINT(20) UNSIGNED NOT NULL COMMENT '订单编码',
  `skuid` BIGINT(20) UNSIGNED NOT NULL COMMENT '商品编码',
  `shopid` BIGINT(20) UNSIGNED NOT NULL COMMENT '门店编码',
  `spuid` BIGINT(20) UNSIGNED NOT NULL COMMENT '产品编码',
  `price` DECIMAL(10, 2) UNSIGNED NOT NULL COMMENT '原价格',
  `saleprice` DECIMAL(10, 2) UNSIGNED NOT NULL COMMENT '销售价',
  `quantity` INT(11) UNSIGNED NOT NULL DEFAULT '1' 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
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT = '订单详情历史表';

---

感谢支持

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