新零售数据库设计(5):购物券和订单

DROP TABLE IF EXISTS `t_voucher`;
CREATE TABLE `t_voucher`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `deno` decimal(10, 2) UNSIGNED NOT NULL COMMENT '面值',
  `condition` decimal(10, 2) UNSIGNED NOT NULL COMMENT '订单满多少钱可以使用',
  `start_date` date NULL DEFAULT NULL COMMENT '起始日期',
  `end_date` date NULL DEFAULT NULL COMMENT '截止日期',
  `max_num` int(11) NULL DEFAULT NULL COMMENT '代金券发放最大数量',
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '购物券表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_voucher
-- ----------------------------
INSERT INTO `t_voucher` VALUES (1, 50.00, 1000.00, '2019-04-25', '2019-05-25', 1000, 0);
INSERT INTO `t_voucher` VALUES (2, 20.00, 500.00, '2019-04-25', '2019-05-25', NULL, 0);

购物卷和客户关联

DROP TABLE IF EXISTS `t_voucher_customer`;
CREATE TABLE `t_voucher_customer`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `voucher_id` int(10) UNSIGNED NOT NULL COMMENT '购物券ID',
  `customer_id` int(10) UNSIGNED NOT NULL COMMENT '客户ID',
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '客户关联购物券数据表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_voucher_customer
-- ----------------------------
INSERT INTO `t_voucher_customer` VALUES (1, 1, 1, 0);
INSERT INTO `t_voucher_customer` VALUES (2, 1, 1, 0);
INSERT INTO `t_voucher_customer` VALUES (3, 1, 1, 0);
INSERT INTO `t_voucher_customer` VALUES (4, 2, 1, 0);
INSERT INTO `t_voucher_customer` VALUES (5, 2, 1, 0);

订单表如下

DROP TABLE IF EXISTS `t_order`;
CREATE TABLE `t_order`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `code` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '流水号',
  `type` tinyint(3) UNSIGNED NOT NULL COMMENT '订单类型：1实体销售,2网络销售',
  `shop_id` int(10) UNSIGNED NULL DEFAULT NULL COMMENT '零售店ID',
  `customer_id` int(10) UNSIGNED NULL DEFAULT NULL COMMENT '会员ID',
  `amount` decimal(10, 2) UNSIGNED NOT NULL COMMENT '总金额',
  `payment_type` tinyint(3) UNSIGNED NOT NULL COMMENT '支付方式：1借记卡,2信用卡,3微信,4支付宝,5现金',
  `status` tinyint(3) UNSIGNED NOT NULL COMMENT '状态：1未付款,2已付款,3已发货,4已签收',
  `postage` decimal(10, 2) UNSIGNED NULL DEFAULT NULL COMMENT '邮费',
  `weight` int(10) UNSIGNED NULL DEFAULT NULL COMMENT '重量（克）',
  `voucher_id` int(10) UNSIGNED NULL DEFAULT NULL COMMENT '购物券ID',
  `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `unq_code`(`code`) USING BTREE,
  INDEX `idx_code`(`code`) USING BTREE,
  INDEX `idx_customer_id`(`customer_id`) USING BTREE,
  INDEX `idx_status`(`status`) USING BTREE,
  INDEX `idx_create_time`(`create_time`) USING BTREE,
  INDEX `idx_type`(`type`) USING BTREE,
  INDEX `idx_shop_id`(`shop_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '订单表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_order
-- ----------------------------
INSERT INTO `t_order` VALUES (1, 'CX0000000120160522', 1, 3, 1, 5998.00, 5, 2, NULL, NULL, NULL, '2019-04-11 01:07:05', 0);
INSERT INTO `t_order` VALUES (2, 'CX0000000120160523', 2, NULL, 1, 5998.00, 1, 2, 60.00, 530, NULL, '2019-04-11 21:22:37', 0);

订单详情表

DROP TABLE IF EXISTS `t_order_detail`;
CREATE TABLE `t_order_detail`  (
  `order_id` int(10) UNSIGNED NOT NULL COMMENT '订单ID',
  `old_id` int(10) UNSIGNED NULL DEFAULT NULL COMMENT 'SKU_OLD表的ID',
  `sku_id` int(10) UNSIGNED NOT NULL COMMENT '商品ID',
  `price` decimal(10, 2) UNSIGNED NOT NULL COMMENT '原价格',
  `actual_price` decimal(10, 2) UNSIGNED NOT NULL COMMENT '实际购买价',
  `num` int(10) UNSIGNED NOT NULL COMMENT '购买数量',
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除',
  PRIMARY KEY (`order_id`, `sku_id`) USING BTREE,
  INDEX `idx_old_id`(`old_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '订单详情表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_order_detail
-- ----------------------------
INSERT INTO `t_order_detail` VALUES (1, NULL, 3, 2999.00, 2999.00, 1, 0);
INSERT INTO `t_order_detail` VALUES (1, NULL, 4, 2999.00, 2999.00, 1, 0);
INSERT INTO `t_order_detail` VALUES (2, NULL, 3, 2999.00, 2999.00, 2, 0);