【实战】3 数据表结构设计

前言

以前自己跟着一点一点的小教程搞得数据库感觉就是半吊子水平，完全野路子···需要学习一下正规军的做法，如何合理的设计数据库，我也尽可能得把老师讲的精华内容记下来。

表结构

用户表

CREATE TABLE `happymall_user` (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户表id',  `username` varchar(50) NOT NULL COMMENT '用户名',  `password` varchar(50) NOT NULL COMMENT '用户密码，MD5加密',  `email` varchar(50) DEFAULT NULL,  `phone` varchar(20) DEFAULT NULL,  `question` varchar(100) DEFAULT NULL COMMENT '找回密码问题',  `answer` varchar(100) DEFAULT NULL COMMENT '找回密码答案',  `role` int(4) NOT NULL COMMENT '角色0-管理员,1-普通用户',  `create_time` datetime NOT NULL COMMENT '创建时间',  `update_time` datetime NOT NULL COMMENT '最后一次更新时间',  PRIMARY KEY (`id`),  UNIQUE KEY `user_name_unique` (`username`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;

用户id不解释

用户名，这里要求用户名唯一，可以通过业务代码里加锁查询实现，不过不利于分布式实现，这里使用数据库自带功能，给用户名加唯一索引（利用BTREE）

密码使用加盐值之后MD5加密，可以有效防破解，或者说破解成本不划算

创建时间和更新时间就是所谓的时间戳。

其他字段和内容直接看建表SQL语句

分类表

就是商品类别的表

CREATE TABLE `happymall_category` (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '类别Id',  `parent_id` int(11) DEFAULT NULL COMMENT '父类别id当id=0时说明是根节点,一级类别',  `name` varchar(50) DEFAULT NULL COMMENT '类别名称',  `status` tinyint(1) DEFAULT '1' COMMENT '类别状态1-正常,2-已废弃',  `sort_order` int(4) DEFAULT NULL COMMENT '排序编号,同类展示顺序,数值相等则自然排序',  `create_time` datetime DEFAULT NULL COMMENT '创建时间',  `update_time` datetime DEFAULT NULL COMMENT '更新时间',  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=100032 DEFAULT CHARSET=utf8;

注意这里有一列叫parent_id就是为了分类能够递归，递归结束条件就是parent_id为0，这么就能实现无限层级设计。parent_id为0代表的就是根节点

sort_order是为了以后扩展分类排序而预留的字段

产品表

CREATE TABLE `happymall_product` (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品id',  `category_id` int(11) NOT NULL COMMENT '分类id,对应happymall_category表的主键',  `name` varchar(100) NOT NULL COMMENT '商品名称',  `subtitle` varchar(200) DEFAULT NULL COMMENT '商品副标题',  `main_image` varchar(500) DEFAULT NULL COMMENT '产品主图,url相对地址',  `sub_images` text COMMENT '图片地址,json格式,扩展用',  `detail` text COMMENT '商品详情',  `price` decimal(20,2) NOT NULL COMMENT '价格,单位-元保留两位小数',  `stock` int(11) NOT NULL COMMENT '库存数量',  `status` int(6) DEFAULT '1' COMMENT '商品状态.1-在售 2-下架 3-删除',  `create_time` datetime DEFAULT NULL COMMENT '创建时间',  `update_time` datetime DEFAULT NULL COMMENT '更新时间',  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;

main_image使用相对地址，这样程序中拿到img服务器地址拼接即可，而且方便图片服务器的迁移，只需要该程序就行，不需要清洗数据。
业务逻辑中取子图的第一张图作为主图

detail中会存HTML富文本，包括加粗等标签，图片链接，外链等

price decimal(20,2)，20代表数字总位数，2表示两位小数，在java中使用BigDecimal处理，要小心计算中丢失精度的问题，后续课程中细说。

购物车表

CREATE TABLE `happymall_cart` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `user_id` int(11) NOT NULL,  `product_id` int(11) DEFAULT NULL COMMENT '商品id',  `quantity` int(11) DEFAULT NULL COMMENT '数量',  `checked` int(11) DEFAULT NULL COMMENT '是否选择,1=已勾选,0=未勾选',  `create_time` datetime DEFAULT NULL COMMENT '创建时间',  `update_time` datetime DEFAULT NULL COMMENT '更新时间',  PRIMARY KEY (`id`),  KEY `user_id_index` (`user_id`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=146 DEFAULT CHARSET=utf8;

购物车也把它持久化，不过我自己总觉得购物车以这种形式存储有些古怪，希望有淘宝的大神来说说。之前了解了一点点非关系型数据库，感觉可以用在这，不过还是跟着老师做好了，以后慢慢改。

为user_id创建索引是因为经常会使用这个字段来搜索

支付信息表

CREATE TABLE `happymall_pay_info` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `user_id` int(11) DEFAULT NULL COMMENT '用户id',  `order_no` bigint(20) DEFAULT NULL COMMENT '订单号',  `pay_platform` int(10) DEFAULT NULL COMMENT '支付平台:1-支付宝,2-微信',  `platform_number` varchar(200) DEFAULT NULL COMMENT '支付宝支付流水号',  `platform_status` varchar(20) DEFAULT NULL COMMENT '支付宝支付状态',  `create_time` datetime DEFAULT NULL COMMENT '创建时间',  `update_time` datetime DEFAULT NULL COMMENT '更新时间',  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8;

对账、退款等都会用到这个表

platform_status会存储支付平台原生的回调状态

订单表

CREATE TABLE `happymall_order` (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单id',  `order_no` bigint(20) DEFAULT NULL COMMENT '订单号',  `user_id` int(11) DEFAULT NULL COMMENT '用户id',  `shipping_id` int(11) DEFAULT NULL,  `payment` decimal(20,2) DEFAULT NULL COMMENT '实际付款金额,单位是元,保留两位小数',  `payment_type` int(4) DEFAULT NULL COMMENT '支付类型,1-在线支付',  `postage` int(10) DEFAULT NULL COMMENT '运费,单位是元',  `status` int(10) DEFAULT NULL COMMENT '订单状态:0-已取消-10-未付款，20-已付款，40-已发货，50-交易成功，60-交易关闭',  `payment_time` datetime DEFAULT NULL COMMENT '支付时间',  `send_time` datetime DEFAULT NULL COMMENT '发货时间',  `end_time` datetime DEFAULT NULL COMMENT '交易完成时间',  `close_time` datetime DEFAULT NULL COMMENT '交易关闭时间',  `create_time` datetime DEFAULT NULL COMMENT '创建时间',  `update_time` datetime DEFAULT NULL COMMENT '更新时间',  PRIMARY KEY (`id`),  UNIQUE KEY `order_no_index` (`order_no`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=118 DEFAULT CHARSET=utf8;

order_no添加了唯一索引，因为订单号也不能重复，而且跟我以前想的不一样，订单号尽然也是再搞一个字段，而不是直接使用订单id。bigint对应java中的long类型。

payment这个值不能跟产品价格做成联动的，因为商家会经常更改价格···

postage目前免运费，预先留一个字段，以后可以对接物流的接口。

status这里合理设置数值可以通过比较数值大小就可以判断状态，后续会引入状态机

payment_time放入支付宝回调的成功支付的时间

close_time表示下单后超时未付款而导致交易关闭的时间

订单明细表

CREATE TABLE `happymall_order_item` (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单子表id',  `user_id` int(11) DEFAULT NULL,  `order_no` bigint(20) DEFAULT NULL,  `product_id` int(11) DEFAULT NULL COMMENT '商品id',  `product_name` varchar(100) DEFAULT NULL COMMENT '商品名称',  `product_image` varchar(500) DEFAULT NULL COMMENT '商品图片地址',  `current_unit_price` decimal(20,2) DEFAULT NULL COMMENT '生成订单时的商品单价，单位是元,保留两位小数',  `quantity` int(10) DEFAULT NULL COMMENT '商品数量',  `total_price` decimal(20,2) DEFAULT NULL COMMENT '商品总价,单位是元,保留两位小数',  `create_time` datetime DEFAULT NULL,  `update_time` datetime DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `order_no_index` (`order_no`) USING BTREE,  KEY `user_id_order_no_index` (`user_id`,`order_no`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=135 DEFAULT CHARSET=utf8;

将user_id在这里多存一份，可以提高SQL查询效率，不用通过联表去order表进行查询

product_name和product_image在这里相当于快照，这里不能通过product_id去临时获取，因为商家会改变这个商品的名称和图片。
current_unit_price也是同理，表示当时的价格，不同通过id去获取当前价格

total_price由生成订单时直接计算填进去，不用每次都去

两个索引，通过order_no进行查询；通过user_id和order_no进行查询，这里我根据后面索引的顺序把老师索引的名字调换了一下顺序。

收货地址表

CREATE TABLE `happymall_shipping` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `user_id` int(11) DEFAULT NULL COMMENT '用户id',  `receiver_name` varchar(20) DEFAULT NULL COMMENT '收货姓名',  `receiver_phone` varchar(20) DEFAULT NULL COMMENT '收货固定电话',  `receiver_mobile` varchar(20) DEFAULT NULL COMMENT '收货移动电话',  `receiver_province` varchar(20) DEFAULT NULL COMMENT '省份',  `receiver_city` varchar(20) DEFAULT NULL COMMENT '城市',  `receiver_district` varchar(20) DEFAULT NULL COMMENT '区/县',  `receiver_address` varchar(200) DEFAULT NULL COMMENT '详细地址',  `receiver_zip` varchar(6) DEFAULT NULL COMMENT '邮编',  `create_time` datetime DEFAULT NULL,  `update_time` datetime DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8;

表关系

这里用Navicat直接从表生成模型，如上图所示。箭头大致表示了一个用户购物生成数据的过程。

这里没有使用外键，因为在以后分库分表、清洗数据时会非常麻烦；同理，数据库内置的触发器也不建议使用。不使用外键可以通过增加一些字段的冗余，以及程序内的逻辑来保证。

唯一索引

唯一索引unique，保证数据的唯一性

单索引和组合索引、

都是为了查询更加快速，这里我理解为mysql中所谓的辅助索引（secondary index），当然这里用的都是b+tree索引，没有用哈希索引。

时间戳

create_time数据创建时间，一经插入就不再变化
update_time数据更新时间

也是为了业务出问题了且日志没有打印等等情况下可以查询解决问题。
分析数据时也是比较有用的。

总结

这个更接近实战的项目使我了解到现实商业环境需要数据表的复杂性，可能在大公司搞这个东西都有一堆流程要走了···其实这些表的设计会跟产品的各个方面打交道，需要考虑周全，也不是一个程序员能够确定的···