新零售-订单、退货、评价相关数据表设计

-- 主订单表
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `order_code` varchar(200) NOT NULL COMMENT '流水号，用于给业务人员定义使用',
  `order_type` tinyint(1) UNSIGNED NOT NULL COMMENT '订单类型 1-实体销售 2-网络销售',
  `user_id` int(10) UNSIGNED NULL DEFAULT NULL COMMENT '用户ID，关联user',
  `order_amount` decimal(10, 2) UNSIGNED NOT NULL COMMENT '订单总金额（优惠前的订单金额）',
  `actual_amount` decimal(10, 2) UNSIGNED NOT NULL COMMENT '订单实付金额（优惠后的实付金额）',
  `payment_type` tinyint(1) UNSIGNED NOT NULL COMMENT '支付方式 1-借记卡 2-信用卡 3-微信 4-支付宝 5-现金',
  `status` tinyint(1) UNSIGNED NOT NULL COMMENT '订单状态 1-未付款 2-已付款 3-已发货 4-已签收',
  `total_postage` decimal(10, 2) UNSIGNED NULL DEFAULT NULL COMMENT '订单总邮费',
  `weight` int(10) UNSIGNED NULL DEFAULT NULL 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,
  UNIQUE INDEX `unq_code`(`order_code`) USING BTREE,
  INDEX `idx_user_id`(`user_id`) USING BTREE,
  INDEX `idx_status`(`status`) USING BTREE,
  INDEX `idx_create_time`(`create_time`) USING BTREE,
  INDEX `idx_type`(`order_type`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COMMENT = '主订单表';

-- 订单详情表
DROP TABLE IF EXISTS `order_detail`;
CREATE TABLE `order_detail`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `order_id` int(10) UNSIGNED NOT NULL COMMENT '订单ID，关联order表',
  `sku_sn` varchar(256) NOT NULL COMMENT '商品唯一标识sn，关联product_sku表',
  `store_id` int(10) UNSIGNED NULL DEFAULT NULL COMMENT '零售店ID，关联retail_store，如果是网络销售，该值为0',
  `num` int(10) UNSIGNED NOT NULL COMMENT '购买数量',
  `total_price` decimal(10, 2) UNSIGNED NOT NULL COMMENT '商品总价',
  `actual_total_price` decimal(10, 2) UNSIGNED NOT NULL COMMENT '实际总结算价',
  `Postage` decimal(10, 2) UNSIGNED NOT NULL COMMENT '该笔订单运费',
  `coupon_id_array` varchar(512) NOT NULL DEFAULT '' COMMENT '该笔子订单使用到的优惠ID-Array，如同时使用店铺优惠和平台会员等级优惠',
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否删除 0-未删除 1-已被删除',
  UNIQUE KEY (`order_id`, `sku_sn`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COMMENT = '订单详情表';

-- 订单快递表
DROP TABLE IF EXISTS `delivery`;
CREATE TABLE `delivery`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `order_id` int(10) UNSIGNED NOT NULL COMMENT '订单ID',
  `sku_info` varchar(512) NOT NULL COMMENT '记录该快递单发出的商品sku_sn、num json',
  `qa_id` int(10) UNSIGNED NOT NULL COMMENT '质检员ID，关联staff表',
  `de_id` int(10) UNSIGNED NOT NULL COMMENT '发货员ID，关联staff表',
  `tracking_id` varchar(25) NOT NULL COMMENT '快递单号',
  `price` decimal(10, 0) UNSIGNED NOT NULL COMMENT '快递费',
  `ecp` tinyint(3) UNSIGNED NOT NULL COMMENT '快递公司编号',
  `address_id` varchar(200) NOT NULL COMMENT '收货地址ID，关联user_address表',
  `warehouse_id` int(10) UNSIGNED NOT NULL COMMENT '仓库ID，关联warehouse表',
  `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_order_id`(`order_id`) USING BTREE,
  INDEX `idx_qa_id`(`qa_id`) USING BTREE,
  INDEX `idx_de_id`(`de_id`) USING BTREE,
  INDEX `idx_tracking_id`(`tracking_id`) USING BTREE,
  INDEX `idx_warehouse_id`(`warehouse_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COMMENT = '快递表';

-- 退货表
DROP TABLE IF EXISTS `back_inventory`;
CREATE TABLE `back_inventory`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `order_id` int(10) UNSIGNED NOT NULL COMMENT '订单ID，关联order表',
  `sku_info` varchar(512) COMMENT '记录退货商品sku_sn、num信息',
  `reason` varchar(200) NOT NULL DEFAULT '' COMMENT '退货原因',
  `qa_id` int(10) UNSIGNED NOT NULL COMMENT '质检员ID，关联staff表',
  `refund_amount` decimal(10, 2) UNSIGNED NOT NULL COMMENT '退款金额（有时会部分退款，比如外包装损毁等）',
  `payment_type` tinyint(1) UNSIGNED NOT NULL COMMENT '退款方式 1-借记卡 2-信用卡 3-微信 4-支付宝 5-现金',
  `status` tinyint(3) UNSIGNED NOT NULL COMMENT '退货状态 1-退货成功 2-无法退货',
  `remarks` varchar(256) NOT NULL DEFAULT '' COMMENT '备注',
  `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_order_id`(`order_id`) USING BTREE,
  INDEX `idx_qa_id`(`qa_id`) USING BTREE,
  INDEX `idx_status`(`status`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COMMENT = '退货表';

-- 评价表
DROP TABLE IF EXISTS `evaluation`;
CREATE TABLE `evaluation`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '评价ID',
  `order_id` int(10) UNSIGNED NOT NULL COMMENT '订单ID，关联order表',
  `sku_sn` varchar(256) NOT NULL COMMENT '商品唯一标识sn，关联product_sku表',
  `image` varchar(512) NOT NULL DEFAULT '' COMMENT '买家晒图json',
  `rating` varchar(128) NOT NULL COMMENT '评分，几项评分的json串',
  `comment` varchar(512) NOT NULL DEFAULT '' COMMENT '买家评论',
  `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_order_id`(`order_id`) USING BTREE,
  INDEX `idx_sku_sn`(`sku_sn`) USING BTREE,
  INDEX `idx_create_time`(`create_time`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COMMENT = '评价表';
打赏赞