（八）订单表设计

 

 订单表

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

 

 

订单详情表 

CREATE TABLE `t_order_detail`  (
  `order_id` int UNSIGNED NOT NULL COMMENT '订单ID',
  `old_id` int UNSIGNED COMMENT 'SKU_OLD表的ID',
  `sku_id` int UNSIGNED NOT NULL COMMENT '商品ID',
  `price` decimal(10, 2) UNSIGNED NOT NULL COMMENT '原价格',
  `actual_price` decimal(10, 2) UNSIGNED NOT NULL COMMENT '实际购买价',
  `num` int UNSIGNED NOT NULL COMMENT '购买数量',
  PRIMARY KEY (`order_id`, `sku_id`),
  INDEX `idx_old_id`(`old_id`)
) COMMENT = '订单详情表';