CoCalc Logo Icon
StoreFeaturesDocsShareSupportNewsAboutSign UpSign In
jackfrued

CoCalc provides the best real-time collaborative environment for Jupyter Notebooks, LaTeX documents, and SageMath, scalable from individual users to large groups and classes!

GitHub Repository: jackfrued/Python-100-Days
 Path: blob/master/Day36-45/code/order_sys.sql
Views: 729
1
-- 交易表

2
CREATE TABLE `transaction` (

3
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

4
  `order_sn` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '交易单号',

5
  `member_id` bigint(20) NOT NULL COMMENT '交易的用户ID',

6
  `amount` decimal(8,2) NOT NULL COMMENT '交易金额',

7
  `integral` int(11) NOT NULL DEFAULT '0' COMMENT '使用的积分',

8
  `pay_state` tinyint(4) NOT NULL COMMENT '支付类型 0:余额 1:微信 2:支付宝 3:xxx',

9
  `source` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '支付来源 wx app web wap',

10
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '支付状态 -1：取消 0 未完成 1已完成 -2:异常',

11
  `completion_time` int(11) NOT NULL COMMENT '交易完成时间',

12
  `note` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',

13
  `created_at` timestamp NULL DEFAULT NULL,

14
  `updated_at` timestamp NULL DEFAULT NULL,

15
  PRIMARY KEY (`id`),

16
  KEY `transaction_order_sn_member_id_pay_state_source_status_index` (`order_sn`(191),`member_id`,`pay_state`,`source`(191),`status`)

17
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

18


19
-- 交易记录表

20
CREATE TABLE `transaction_record` (

21
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

22
  `order_sn` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,

23
  `events` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '事件详情',

24
  `result` text COLLATE utf8mb4_unicode_ci COMMENT '结果详情',

25
  `created_at` timestamp NULL DEFAULT NULL,

26
  `updated_at` timestamp NULL DEFAULT NULL,

27
  PRIMARY KEY (`id`)

28
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

29


30


31
-- 订单表

32
CREATE TABLE `order` (

33
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

34
  `order_no` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '订单编号',

35
  `order_sn` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '交易号',

36
  `member_id` int(11) NOT NULL COMMENT '客户编号',

37
  `supplier_id` int(11) NOT NULL COMMENT '商户编码',

38
  `supplier_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '商户名称',

39
  `order_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单状态 0未付款,1已付款,2已发货,3已签收,-1退货申请,-2退货中,-3已退货,-4取消交易',

40
  `after_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '用户售后状态 0 未发起售后 1 申请售后 -1 售后已取消 2 处理中 200 处理完毕',

41
  `product_count` int(11) NOT NULL DEFAULT '0' COMMENT '商品数量',

42
  `product_amount_total` decimal(12,4) NOT NULL COMMENT '商品总价',

43
  `order_amount_total` decimal(12,4) NOT NULL DEFAULT '0.0000' COMMENT '实际付款金额',

44
  `logistics_fee` decimal(12,4) NOT NULL COMMENT '运费金额',

45
  `address_id` int(11) NOT NULL COMMENT '收货地址编码',

46
  `pay_channel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '支付渠道 0余额 1微信 2支付宝',

47
  `out_trade_no` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '订单支付单号',

48
  `escrow_trade_no` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '第三方支付流水号',

49
  `pay_time` int(11) NOT NULL DEFAULT '0' COMMENT '付款时间',

50
  `delivery_time` int(11) NOT NULL DEFAULT '0' COMMENT '发货时间',

51
  `order_settlement_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单结算状态 0未结算 1已结算',

52
  `order_settlement_time` int(11) NOT NULL DEFAULT '0' COMMENT '订单结算时间',

53
  `is_package` enum('0','1') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0' COMMENT '是否是套餐',

54
  `is_integral` enum('0','1') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0' COMMENT '是否是积分产品',

55
  `created_at` timestamp NULL DEFAULT NULL,

56
  `updated_at` timestamp NULL DEFAULT NULL,

57
  `deleted_at` timestamp NULL DEFAULT NULL,

58
  PRIMARY KEY (`id`),

59
  UNIQUE KEY `order_order_sn_unique` (`order_sn`),

60
  KEY `order_order_sn_member_id_order_status_out_trade_no_index` (`order_sn`,`member_id`,`order_status`,`out_trade_no`(191))

61
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

62


63
-- 售后申请表

64
CREATE TABLE `order_returns_apply` (

65
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

66
  `order_no` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '订单单号',

67
  `order_detail_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '子订单编码',

68
  `return_no` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '售后单号',

69
  `member_id` int(11) NOT NULL COMMENT '用户编码',

70
  `state` tinyint(4) NOT NULL COMMENT '类型 0 仅退款 1退货退款',

71
  `product_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '货物状态 0:已收到货 1:未收到货',

72
  `why` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '退换货原因',

73
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '审核状态 -1 拒绝 0 未审核 1审核通过',

74
  `audit_time` int(11) NOT NULL DEFAULT '0' COMMENT '审核时间',

75
  `audit_why` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '审核原因',

76
  `note` text COLLATE utf8mb4_unicode_ci COMMENT '备注',

77
  `created_at` timestamp NULL DEFAULT NULL,

78
  `updated_at` timestamp NULL DEFAULT NULL,

79
  PRIMARY KEY (`id`)

80
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

81


82
-- 售后记录表

83
CREATE TABLE `order_returns` (

84
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

85
  `returns_no` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '退货编号 供客户查询',

86
  `order_id` int(11) NOT NULL COMMENT '订单编号',

87
  `express_no` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '物流单号',

88
  `consignee_realname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '收货人姓名',

89
  `consignee_telphone` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '联系电话',

90
  `consignee_telphone2` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '备用联系电话',

91
  `consignee_address` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '收货地址',

92
  `consignee_zip` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '邮政编码',

93
  `logistics_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '物流方式',

94
  `logistics_fee` decimal(12,2) NOT NULL COMMENT '物流发货运费',

95
  `order_logistics_status` int(11) DEFAULT NULL COMMENT '物流状态',

96
  `logistics_settlement_status` int(11) DEFAULT NULL COMMENT '物流结算状态',

97
  `logistics_result_last` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '物流最后状态描述',

98
  `logistics_result` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '物流描述',

99
  `logistics_create_time` int(11) DEFAULT NULL COMMENT '发货时间',

100
  `logistics_update_time` int(11) DEFAULT NULL COMMENT '物流更新时间',

101
  `logistics_settlement_time` int(11) DEFAULT NULL COMMENT '物流结算时间',

102
  `returns_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0全部退单 1部分退单',

103
  `handling_way` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'PUPAWAY:退货入库;REDELIVERY:重新发货;RECLAIM-REDELIVERY:不要求归还并重新发货; REFUND:退款; COMPENSATION:不退货并赔偿',

104
  `returns_amount` decimal(8,2) NOT NULL COMMENT '退款金额',

105
  `return_submit_time` int(11) NOT NULL COMMENT '退货申请时间',

106
  `handling_time` int(11) NOT NULL COMMENT '退货处理时间',

107
  `remark` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '退货原因',

108
  PRIMARY KEY (`id`)

109
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

110


111
-- 评价表

112
CREATE TABLE `order_appraise` (

113
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

114
  `order_id` int(11) NOT NULL COMMENT '订单编码',

115
  `info` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '评论内容',

116
  `level` enum('-1','0','1') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '级别 -1差评 0中评 1好评',

117
  `desc_star` tinyint(4) NOT NULL COMMENT '描述相符 1-5',

118
  `logistics_star` tinyint(4) NOT NULL COMMENT '物流服务 1-5',

119
  `attitude_star` tinyint(4) NOT NULL COMMENT '服务态度 1-5',

120
  `created_at` timestamp NULL DEFAULT NULL,

121
  `updated_at` timestamp NULL DEFAULT NULL,

122
  PRIMARY KEY (`id`),

123
  KEY `order_appraise_order_id_index` (`order_id`)

124
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

125