商业软件项目搭建指南（SpringMVC篇）——第六章：数据库表结构设计

本章介绍数据库设计的规范、原则、思路和规则。本项目的数据是以关系型数据保存在MySQL数据库里，因此需要居于MySQL关系型数据库进行表结构设计。

目录

第1节、表结构设计规范和原则

第2节、表结构设计思路

第3节、数据库表名命名规则

第4节、本项目的表结构设计

第4节、本项目的数据库表结构

4.1、用户信息表

4.2、商品信息表

4.3、订单信息表

4.4、购物车信息表

4.5、收货地址信息表

4.6、订单与商品关系表

4.7、商品分类信息表

4.8、商品文件信息表

---

第1节、表结构设计规范和原则

在进行数据库表结构设计时，尽量按照如下规范和原则进行设计：

1. 明确业务需求：在设计数据库表前，需要明确业务需求，明确需要存储的数据类型、数据结构和数据之间的关系。只有在明确业务需求后，才能进行有针对性的设计。

2. 划分表和字段：在确定业务需求后，需要对需要存储的数据进行划分。根据数据之间的关系和访问频率等因素，将数据划分到不同的表中。在确定每个表中字段时，需要根据字段之间的关系、数据类型、存储方式等因素进行综合考虑。

3. 设计主键和索引：主键是每个表中唯一标识一条记录的字段，可以通过主键进行快速定位记录。索引可以加速查询操作。在设计主键和索引时，需要考虑查询频率、唯一性、数据量等因素。

4. 设计关系：如果不同表之间存在关系，如一对多或多对多关系，则需要进行合理设计。在设计关系时，需要考虑数据完整性、查询效率等因素。

5. 设计约束条件：约束条件用于限制插入或更新操作，在保证数据完整性方面起到重要作用。常用约束条件有主键约束、唯一约束、非空约束等。

6. 设计存储引擎：MySQL数据库支持不同类型的存储引擎，如MyISAM、InnoDB等。不同存储引擎具有不同的特点，如性能、事务支持等方面。在设计存储引擎时，需要根据具体业务需求选择合适的存储引擎。

综上所述，设计数据库表需要综合考虑业务需求、数据结构、数据之间的关系等因素。在设计时需要合理划分表和字段、设计主键和索引、设计关系和约束条件等，以实现高效、稳定和安全的数据存储。

第2节、表结构设计思路

如何进行设计数据库表呢？通常的思路是从项目的所有业务逻辑和场景里，进行如下操作：

首先、从中提取出有意义、有实体的重要名称，一个重要名称一般对应一张数据库表；

然后、找出这些名称之间的关系，如果是一对一或一对多关系，那么它们以外键方式进行关联；如果是多对多关系，那么就创建一张关系表，使用一条条记录它们之间的关系。

之后、根据这些重要名称对应的对象或者类，一个一个地去整理出它们的属性，即对应表里的字段；根据这些字段的性质来进行数据类型、存储空间大小长度。同时继续要考虑到一些保存关系的字段加入到表中去。

最后、从这些重要名称做为中心点出发，向外围思考一些附属在它们身边的普通名称，或者与它们有关联的业务或功能，进行相关表设计。

第3节、数据库表名命名规则

在设计数据库表结构时，通常也是一样分模块进行设计的。因此对数据库表进行命名时，主要有表类型、模块名、业务名、关系标识组成，各个组件之间使用下划线进行间隔。

数据库表类型有表（Table）、视图（View）、过程（Procedure）、函数（Function），在命名规则里使用这些类型单词的首字母小写，即表（t）、视图（v）、过程（p）、函数（f）。

关系标识，通常以数字2代表。

综上所述，通常一个数据库业务表命名规则为：t_模块名_业务名，例如我们项目里商品分类信息表，它所在的模块是商品模块，名叫goods，因此它的表名为t_goods_category。

通常一个数据库关系表命名规则为：t_模块名_业务名2业务名，例如我们项目里订单与商品关系表，它所在的模块是订单模块，名叫order，因此它的表名为t_order2goods，其中订单业务名和模块名是同名，因此可以合并为一个order。

第4节、本项目的表结构设计

按照上述设计思路，本项目的数据库表结构设计可以按照如下操作：

1. 首先我们可以找到管理员、网站会员、商品、订单、收货地址这些重要的有实体的名称，找到购物车这样虚拟物体名称。那么很显然管理员和网站会员是同一种数据结构、不同角色，因此它们可以归类为一张用户信息表，商品、订单、购物车收货地址这些重要名称都是独立的对象，因此它们都是独立的一张信息表，对应的是商品信息表、订单信息表、购物车信息表、收货地址信息表。
2. 其次以用户为中心点出发，与它相关的对象有订单、收货地址、购物车，并且它们之间都是1对多关系，因此在订单、购物车和收货地址信息表上都会有一个用户ID字段，即通过外键方式来保持它们之间的关系。
3. 其次以商品为中心点出发，与它相关的对象有订单、购物车；其中商品与订单它们之间都是多对多关系，因此需要创建商品与订单关系表；商品与购物车关系是要建立在一个网站会员角度上去进行关联的，因此在购物车信息表上会有一个商品ID字段，即通过外键方式来保持它们之间的关系。附属在商品身上的普通名称有商品分类、商品文件等，并且它们与商品的关系是多对一的关系，它们对应的表有商品分类信息表、商品文件信息表。
4. 之后以订单为中心点出发，与它相关的对象有用户、商品、收货地址；其中订单与用户是一对一关系，因此只需要在订单信息表里一个用户ID字段，即通过外键方式来保持它们之间的关系；商品与订单它们之间都是多对多关系，因此需要创建订单与商品关系表，它与上步说的商品与订单关系表是同一张表；订单与收货地址是一对一关系，因此只需要在订单信息表里增加收货地址相关字段。
5. 最后我们可以整理出的表有：用户信息表、商品信息表、订单信息表、购物车信息表、收货地址信息表、商品与订单关系表、商品分类信息表、商品文件信息表，根据这些重要名称对应的对象或者类，一个一个地去整理出它们的字段。

第4节、本项目的数据库表结构

本项目的数据库表结构分别如下：

4.1、用户信息表

create table t_user(

   id                   varchar(32) not null comment '用户ID',

   user_name            varchar(64) comment '用户名',

   password             varchar(32) comment '密码,MD5加密保存',

   user_type            tinyint default 0 comment '用户类型 0:管理员 1:网站员工 2:网站会员',

   status               tinyint default 1 comment '用户状态 0:无效  1:有效',

   portrait_uri         varchar(512) comment '用户头像URI',

   cell_num             varchar(11) comment '手机号码',

   email                varchar(128) comment '邮箱',

   qq                   varchar(16) comment 'QQ',

   real_name            varchar(128) comment '用户真实名称',

   nickname             varchar(64) comment '昵称',

   gender               tinyint default 0 comment '性别 0:女 1:男',

   birthday             date comment '出生日期',

   login_password       varchar(256) comment '登录密码,AES加密保存',

   secret_key           varchar(256) comment '秘钥,BASE64加密保存',

   register_time        datetime comment '注册时间',

   last_login_time      bigint unsigned default 0 comment '最后登录时间',

   last_login_ip        varchar(64) comment '最后登录IP',

   create_time          bigint comment '创建时间',

   creator_id           varchar(32) comment '创建者ID',

   last_modifier_id     varchar(32) comment '最后修改者ID',

   last_modify_time     bigint comment '最后修改时间',

   primary key (id)

);

4.2、商品信息表

create table t_goods (

   id                   int(11) not null auto_increment comment '商品ID',

   name                 varchar(512) comment '商品名称',

   large_category_id    int(11) comment '大类ID',

   small_category_id    int(11) comment '小类ID',

   third_category_id    int(11) comment '三级类ID',

   goods_num            varchar(32) comment '商品编号',

   keywords             varchar(256) comment '关键词，多个关键词用,分隔',

   goods_unit           varchar(16) comment '商品单位',

   price                int default 0 comment '价格，单位：分',

   market_price         int default 0 comment '市场价，单位：分',

   supply_amount        int default 0 comment '供货数量',

   min_order_amount     int default 0 comment '最小定量',

   sales_volume         int default 0 comment '销量',

   default_sales        int default 0 comment '默认销量',

   length               varchar(64) comment '长度',

   width                varchar(64) comment '宽度',

   height               varchar(64) comment '高度',

   weight               int default 0 comment '重量，单位：克',

   shelf_number         varchar(32) comment '货架编号',

   main_thumbnail_path  varchar(256) comment '主图缩略图文件路径，相对路径',

   summary              varchar(2048) comment '商品摘要',

   description          mediumtext comment '商品说明',

   is_delete            tinyint default 0 comment '是否删除 0=否 1=是',

   order_num            int default 0 comment '排序序号，从大到小，越大越靠前',

   online_status        tinyint comment '上架状态 0=下架 1=上架',

   creator_id           varchar(32) comment '创建者ID',

   create_time          bigint comment '创建时间',

   last_modifier_id     varchar(32) comment '最后修改者ID',

   last_modify_time     bigint comment '最后修改时间',

   primary key (id));

4.3、订单信息表

create table t_order(

   id                   int(11) unsigned not null auto_increment comment '订单ID',

   user_id              varchar(32) comment '用户ID',

   order_number         varchar(64) comment '订单编号，格式：yyyyMMddHHmmss + MongoDB的ObjectID',

   goods_id             int(11) comment '商品ID，由于可能存在一个订单含有多个商品，因此请使用T_ORDER2GOODS关系表来关联商品信息，但是为了方便查询订单列表时展示一个商品名称作为样例，在该字段里保存订单里第一个商品的ID。',

   buy_num              int default 0 comment '购买总数量',

   trade_price          int default 0 comment '交易总价格(单位：分)',

   freight              int default 0 comment '运费(单位：分)',

   pay_amount           int default 0 comment '支付金额(单位：分)',

   pay_type             tinyint default 0 comment '支付类型 0:微信 1:支付宝 2:银联 3:货到付款',

   pay_status           tinyint default 0 comment '支付状态 0:未支付 1:已支付 2:支付失败 3:支付中 4:转入退款 5:已关闭 6:已撤销',

   pay_time             datetime comment '支付时间',

   out_trade_no         varchar(64) comment '外部交易单号',

   province             varchar(64) comment '省份',

   city                 varchar(64) comment '城市',

   area                 varchar(64) comment '区县',

   address              varchar(1024) comment '收货地址',

   receiver             varchar(32) comment '收货人',

   cell_num             varchar(11) comment '手机号码',

   remark               varchar(1024) comment '备注',

   deliver_status       tinyint default 0 comment '发货状态 0=未发货 1=开始拣货 2=已出库 3=已发货 4=已收货',

   deliver_time         datetime comment '发货时间',

   waybill_number       varchar(64) comment '运单编号',

   status               tinyint default 0 comment '订单状态 -1=已取消 0=未处理 1=配送中 2=已完成 3=已退款 4=退换中 5=已退货 6=已换货',

   create_time          bigint unsigned comment '创建时间，即购买时间',

   last_modify_time     datetime comment '最后修改时间',

   last_modifier_id     varchar(32) comment '最后修改者ID',

   primary key (id));

4.4、购物车信息表

create table t_shopping_cart(

   id                   int(11) not null auto_increment comment '购物车ID',

   goods_id             int(11) comment '被订购的商品ID',

   amount               int default 0 comment '订购数量',

   unit_price           int default 0 comment '单价，单位：分',

   member_user_id       varchar(32) comment '会员用户ID',

   create_time          bigint comment '创建时间',

   primary key (id));

4.5、收货地址信息表

create table t_receive_address(

   id                   int(11) unsigned not null auto_increment comment '收货地址ID',

   user_id              varchar(32) comment '会员用户ID',

   receiver             varchar(32) comment '收货人',

   cell_num             varchar(11) comment '手机号码',

   country              varchar(32) comment '国家',

   province             varchar(64) comment '省份/州',

   city                 varchar(64) comment '城市',

   area                 varchar(64) comment '区/县',

   street               varchar(64) comment '街道',

   detail_address       varchar(128) comment '详细地址',

   is_default           tinyint default 0 comment '是否默认 0:否 1:是',

   create_time          datetime comment '创建时间',

   last_modify_time     datetime comment '最后修改时间',

   last_modifier_id     varchar(32) comment '最后修改者ID',

   primary key (id));

4.6、订单与商品关系表

create table t_order2goods(

   id                   int(11) unsigned not null auto_increment comment 'ID',

   order_id             int(11) comment '订单ID',

   goods_id             int(11) comment '商品ID',

   buy_num              int default 0 comment '购买数量',

   unit_price           int default 0 comment '交易单价，单位：分',

   primary key (id));

4.7、商品分类信息表

create table t_goods_category(

   id                   int(11) not null auto_increment comment '分类ID',

   level                tinyint comment '分类级别 1=大类 2=小类 3=三级类',

   name                 varchar(128) comment '分类名称',

   parent_id            int(11) comment '父分类ID',

   order_num            int default 0 comment '排序序号，从大到小，越大越靠前',

   is_delete            tinyint default 0 comment '是否删除 0=否 1=是',

   creator_id           varchar(32) comment '创建者ID',

   create_time          bigint comment '创建时间',

   last_modifier_id     varchar(32) comment '最后修改者ID',

   last_modify_time     bigint comment '最后修改时间',

   primary key (id));

4.8、商品文件信息表

create table t_goods_file(

   id                   int(11) unsigned not null auto_increment comment '文件ID',

   name                 varchar(256) comment '文件名称',

   file_type            tinyint default 0 comment '文件类型 0=主图片 1=细节图片',

   file_path            varchar(256) comment '文件路径，相对路径',

   thumbnail_path       varchar(256) comment '缩略图文件路径，相对路径',

   order_num            int default 0 comment '排序序号，从大到小，越大越靠前',

   goods_id             int(11) comment '所属商品ID',

   creator_id           varchar(32) comment '创建者ID',

   create_time          bigint comment '创建时间',

   primary key (id));