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

标签: 数据库
245人阅读 评论(0) 收藏 举报
分类:

前言

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

表结构

用户表

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_nameproduct_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数据更新时间

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

总结

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

查看评论

web聊天类数据库功能表结构如何设计?

最近在带着团队做项目,有些比较关键的问题,也在找解决方案,这里把一些感觉比较重要的记录一下。 希望看完博客的同事,能踊跃留言,恳请指正。 完 web聊天类数据库功能表结构如何设计? ...
  • u010098331
  • u010098331
  • 2016-05-24 20:03:19
  • 7868

数据库的架构设计与性能优化

本文首发于阿里云&《程序员》杂志联合出品的《凌云》专刊中。 作者: 杭州湖畔网络技术经理 王鑫鹏 杭州湖畔网络技术有限公司是一家专业提供SaaS化电商ERP服务的创业公司,主要用户群体为经...
  • liu870915
  • liu870915
  • 2016-07-05 15:07:16
  • 2860

数据库表结构设计

为什么要学习数据表结构设计 实际开发中,需要根据需求,将实际模型转换成物理表结构,这时需要考虑几个问题,表名称如何命名,表中需要哪些字段,各个字段的命名规范,字段的数据类型,字段的长度,和其他表的联...
  • mozha_666
  • mozha_666
  • 2018-02-13 14:33:02
  • 101

在线问卷调查数据表结构设计

a. 表名称:tb_Survey_Name 作用:保存系统所有的调查问卷名称数据,实现调查问卷的管理。 属性 解释 类型 备注 ...
  • myhuashengmi
  • myhuashengmi
  • 2016-11-09 21:27:46
  • 4107

数据库表结构设计方法及原则

http://www.cnblogs.com/RunForLove/p/5693986.html   数据库设计的三大范式:为了建立冗余较小、结构合理的数据库,设计数据库时必须遵循一定的规则。在...
  • u014470581
  • u014470581
  • 2017-03-23 08:50:27
  • 2117

数据库(表结构)设计技巧及注意事项

库设计: 1、数据库名称要明确,可以加前缀或后缀的方式,使其看起来有业务含义,比如数据库名称可以为Business_DB(业务数据库)。 2、在一个企业中,如果依赖很多产品,但是每个产品都使用同一套用...
  • Truong
  • Truong
  • 2014-06-10 13:39:07
  • 17408

数据库表结构设计浅谈

转载自:http://hi.baidu.com/yzx110/blog/item/0159fadc7b7839a4cd116686.html数据库表结构设计浅谈    这篇文章如题所述,只打算谈一下数...
  • Micky0903
  • Micky0903
  • 2010-10-21 09:31:00
  • 10729

数据库(表结构)设计技巧及注意事项

当您在决定开发一个数据库管理项目时,最先着手的工作就应是数据库表结构的设计了。可以这么说,表结构的设计是开发数据库管理项目的基石,一个糟糕的表结构设计,可能会严重延误您的项目开发周期,使您大量的劳动时...
  • DannyIsCoder
  • DannyIsCoder
  • 2017-12-01 16:51:39
  • 437

关于论坛数据库的设计

一个简单的论坛系统  1:包含下列信息:    2:每天论坛访问量300万左右,更新帖子10万左右。  请给出数据库表结构设计,并结合范式简要说明设计思路。  一. 发帖主题和回...
  • samjustin1
  • samjustin1
  • 2016-10-07 11:04:28
  • 11223

泛微数据表结构设计文档

  • 2015年05月04日 09:35
  • 22KB
  • 下载
    个人资料
    等级:
    访问量: 3582
    积分: 286
    排名: 27万+
    文章分类
    文章存档