一个电商项目的数据库设计实践（第一部分）（附源码）

1.准备工作

数据库：MySQL5.7（5.5、5.6皆可）；
数据库客户端软件：SQLyog；
源码在Github上：https://github.com/Fateasstring/EB-SQL ，文章整理不易，请赏个star吧。

2.用户实体

用户模块用于管理和维护用户信息。把所有的用户信息放在一个表中，会容易导致：

• 数据插入异常；
• 数据更新异常，比如要修改某一行的值是，不得不修改多行数据；
• 数据删除异常，删除某一数据时不得不同时删除另一数据；
• 数据存在冗余； 数据表过宽，会影响修改表结构的效率。

第三范式（3NF）定义：一个表中的列和其它列之间，即不包含部分函数依赖关系，也不包含传递函数依赖关系，那么这个表的设计就符合第三范式。

设计方式：
用户登陆表：{登录名，密码，用户状态}
用户地址表：{省，市，区编码，地址}
用户信息表：{用户姓名，证件类型，证件号码，手机号，邮箱，性别，积分，注册时间，生日，会员级别，用户余额}

2.1 用户登陆表（customer_login）

CREATE table customer_login(

customer_id int UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '用户ID',

login_name VARCHAR(20) NOT NULL COMMENT '用户登陆名',
password CHAR(32) not NULL COMMENT 'md5加密的密码',

user_stats TINYINT NOT null DEFAULT 1 COMMENT '用户状态',

modified_time timestamp NOT null 
default  CURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',

PRIMARY key pk_customerid(customer_id)

)engine = INNODB COMMENT = '用户登录表';

2.2 用户信息表（customer_inf）

CREATE table customer_inf(

customer_inf_id int UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '自增主键ID',

customer_id int UNSIGNED NOT NULL COMMENT 'customer_login表的自增ID',

customer_name VARCHAR(20) not null COMMENT '用户真实姓名',

identity_card_type TINYINT not null 
DEFAULT 1 COMMENT '证件类型：1 身份证，2 军官证， 3 护照',

identity_card_no VARCHAR(20) COMMENT '证件号码',

mobile_phone int UNSIGNED COMMENT '手机号',

customer_email VARCHAR(50) COMMENT '邮箱',

gender CHAR(1) COMMENT '性别',

user_point int NOT NULL DEFAULT 0 COMMENT '用户积分',

register_time timestamp not null COMMENT '注册时间',
  
birthday datetime COMMENT '会员生日',

customer_level TINYINT NOT null 
DEFAULT 1 COMMENT '会员级别：1.普通会员，2.青铜会员，3.白银会员，4.黄金会员，5.钻石会员',

user_money DECIMAL(8,2) NOT null DEFAULT 0.00 COMMENT '用户余额',

modified_time timestamp NOT null 
DEFAULT CURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',

primary key pk_customerinfid (customer_inf_id)

)ENGINE = INNODB COMMENT '用户信息表';

2.3 用户级别表（customer_level_inf）

CREATE table customer_level_inf(

customer_level TINYINT not NULL auto_increment COMMENT '会员级别ID',

level_name VARCHAR(10) NOT NULL COMMENT '会员级别名称',

min_point int UNSIGNED not null DEFAULT 0 COMMENT '该级别最低积分',

max_point int UNSIGNED not null DEFAULT 0 COMMENT '该级别最高积分',

modified_time timestamp not null 
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',

primary key pk_levelid(customer_level)  

)ENGINE = INNODB COMMENT '用户级别信息表';

2.4 用户地址表（customer_addr）

CREATE TABLE customer_addr(
customer_addr_id int UNSIGNED AUTO_INCREMENTnot null COMMENT'自增主键ID',

customer_id int UNSIGNED not NULL COMMENT 'customer_login表的自增ID',

zip SMALLINT not null COMMENT '邮编',

province SMALLINT not null COMMENT '地区表中省份的id',

city SMALLINT not null COMMENT '地区表中城市的id',

district SMALLINT not null COMMENT '地区表中的区id',

address VARCHAR(200) not null COMMENT '具体的地址门牌号',

is_default TINYINT not null COMMENT '是否默认',

modified_time timestamp not null 
DEFAULT CURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',

primary key pk_customeraddid (customer_addr_id)
		
)ENGINE = INNODB COMMENT '用户地址表';

2.5 用户积分日志表（customer_point_log）

CREATE TABLE customer_point_log(

point_id int UNSIGNED not null AUTO_INCREMENT COMMENT '积分日志ID',

customer_id int UNSIGNED not null COMMENT '用户id',
source TINYINT UNSIGNED not null COMMENT '积分来源：0.订单，1.登陆，2.活动',

refer_number int UNSIGNED not null DEFAULT 0 COMMENT '积分来源相关编号',

change_point SMALLINT not null DEFAULT 0 COMMENT '变更积分数',

create_time timestamp not null COMMENT '积分日志生成时间',

primary key pk_point(point_id)

)ENGINE = INNODB COMMENT '用户积分日志表';

2.6 用户余额变动表（customer_balance_log）

CREATE table customer_balance_log(

balance_id int UNSIGNED not null AUTO_INCREMENT COMMENT '余额日志id',

customer_id int UNSIGNED not null COMMENT '用户id',
source TINYINT UNSIGNED not null DEFAULT 1 COMMENT '记录来源：1.订单，2.退货单',

source_sn int UNSIGNED not null COMMENT '相关单据id',

create_time timestamp not null DEFAULT current_timestamp COMMENT '记录生成时间',

amount DECIMAL(8,2) not null DEFAULT 0.00 COMMENT '变动金额',

primary key pk_balanceid (balance_id)

)ENGINE = INNODB COMMENT '用户余额变动表';

2.7 用户登录日志表（customer_login_log）

```sql
CREATE table customer_login_log(

login_id int UNSIGNED not null AUTO_INCREMENT COMMENT '登录日志id',

customer_id int UNSIGNED NOT null COMMENT '登录用户id',

login_time timestamp not null COMMENT '用户登录时间',

login_ip int UNSIGNED not null COMMENT '登录ip',

login_type TINYINT not null COMMENT '登录类型：0.未成功，1.成功',

primary key pk_loginid(login_id)

)ENGINE = INNODB COMMENT '用户登陆日志表'

3.customer_login_log分区

3.1 customer_login_log表分区

业务使用场景：用户登录日志表主要用于用户每次登录的记录，每次登录时会在表中增加一条日志，所以数据量增长很快。为了尽可能保持生产环境数据库不会因为日志表增长而过快增长，用户登录日志只保存一年。

1）登录日志表的分区类型及分区键

这样的场景使用范围（RANGE）分区比较适合，可以很方便删除分区范围的数据，特别时数据量大的时候会方便很多。
这里以login_time作为分区键。

2）分区后的用户登录日志表

```sql
CREATE table customer_login_log(
customer_id int UNSIGNED NOT null ,
login_time DATETIME not null ,
login_ip int UNSIGNED not null ,
login_type TINYINT not null 
)ENGINE =