数据库设计

目标与目的

学习目标：

掌握日常数据库建表操作
了解表设计的规范

何为表结构设计：

表结构设计需要在正式进行开发之前完成
根据产品需求将复杂的业务模型抽象出来

项目分析

实践课程模型：

类似电商类的网站

用户：

核心业务
非核心业务

核心业务：

交易相关
- 订单详情，订单　　
商品相关
- 商品类别，商品

非核心业务：

物流
供应商信息

业务模型转表

用户： tb_account(用户表)

商品类：tb_goods_category(商品类别表) tb_goods(商品详情表)

交易类：tb_order_item(交易订单详情表) tb_order(交易表)

设计表的时候需要注意哪些

理解各个表的依赖关系
理解各个表的功能特点（字段之间的约束，索引；字段类型，字段长度）

收集表属性

用户表：

自增属性，auto_increment

图片不会直接存在数据库，在数据库中之后存图片的一个url地址，有专门的图片存储系统去存图片

varchar VS text 在存储特别长的字段时

一般从两个方面： 1.性能

2.存储上限

varchar 最大长度65535，如果用utf8及3个字节；

varchar最大为65535/3=21845

65535及2的16次方

网易数据类型-命名规范

所有表名，字段名全部使用小写字母
不同业务，表名使用不同前缀区分
生成环境表名字段名要有实际意义
单个字段尽量使用字段全名；多个字段名之间使用下划线分隔

字段设计规范：

存储空间（尽量选择能满足应用要求的最小数据类型）
前瞻性（尽量使用整型代替字符型）
注释，每个字段必须以COMMENT语句给出字段的作用
经常访问的大字段（如blob，text）等需要单独放到一张表中，避免降低SQL效率，图片，电影等大文件数据禁止存放数据库
新业务统一建议使用utf8mb4字符集（适应苹果手机的imoge表情库）

用户赋权注意：

理解用户到底需要什么权限
- 普通用户只有读写权限　
- 系统管理员具有super权限
权限粒度要做到尽可能的细
- 普通用户不要设置with grant option 属性
- 权限粒度：系统层面>库层面>表层面>字段层面　　
禁止简单密码
- 线上密码要求随机

总结

理解个表间依赖关系以及功能特点
- 字段间约束
- 索引的选取
- 字段类型与长度
自增主键重要性
varchar最大长度
- 65535/3=21845
- 实际还要小点
前瞻性与注释
经常访问的大字段(blob/text)单独存放
赋权限
- 权限最小原则
- 普通用户不要设置with grant option 属性

表结构

--

-- Table structure for table `tb_account`

--

 

DROP TABLE IF EXISTS `tb_account`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `tb_account` (

  `account_id` int(11) NOT NULL AUTO_INCREMENT,

  `nick_name` varchar(20) DEFAULT NULL,

  `true_name` varchar(20) DEFAULT NULL,

  `sex` char(1) DEFAULT NULL,

  `mail_address` varchar(50) DEFAULT NULL,

  `phone1` varchar(20) NOT NULL,

  `phone2` varchar(20) DEFAULT NULL,

  `password` varchar(30) NOT NULL,

  `create_time` datetime DEFAULT NULL,

  `account_state` tinyint(4) DEFAULT NULL,

  `last_login_time` datetime DEFAULT NULL,

  `last_login_ip` varchar(20) DEFAULT NULL,

  PRIMARY KEY (`account_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/*!40101 SET character_set_client = @saved_cs_client */;

 

--

-- Table structure for table `tb_goods`

--

 

DROP TABLE IF EXISTS `tb_goods`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `tb_goods` (

  `goods_id` bigint(20) NOT NULL AUTO_INCREMENT,

  `goods_name` varchar(100) NOT NULL,

  `pic_url` varchar(500) NOT NULL,

  `store_quantity` int(11) NOT NULL,

  `goods_note` varchar(800) DEFAULT NULL,

  `producer` varchar(500) DEFAULT NULL,

  `category_id` int(11) NOT NULL,

  PRIMARY KEY (`goods_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/*!40101 SET character_set_client = @saved_cs_client */;

 

--

-- Table structure for table `tb_goods_category`

--

 

DROP TABLE IF EXISTS `tb_goods_category`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `tb_goods_category` (

  `category_id` int(11) NOT NULL AUTO_INCREMENT,

  `category_level` smallint(6) NOT NULL,

  `category_name` varchar(500) DEFAULT NULL,

  `upper_category_id` int(11) NOT NULL,

  PRIMARY KEY (`category_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/*!40101 SET character_set_client = @saved_cs_client */;

 

--

-- Table structure for table `tb_order`

--

 

DROP TABLE IF EXISTS `tb_order`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `tb_order` (

  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,

  `account_id` int(11) NOT NULL,

  `create_time` datetime DEFAULT NULL,

  `order_amount` decimal(12,2) DEFAULT NULL,

  `order_state` tinyint(4) DEFAULT NULL,

  `update_time` datetime DEFAULT NULL,

  `order_ip` varchar(20) DEFAULT NULL,

  `pay_method` varchar(20) DEFAULT NULL,

  `user_notes` varchar(500) DEFAULT NULL,

  PRIMARY KEY (`order_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/*!40101 SET character_set_client = @saved_cs_client */;

 

--

-- Table structure for table `tb_order_item`

--

 

DROP TABLE IF EXISTS `tb_order_item`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `tb_order_item` (

  `order_item_id` bigint(20) NOT NULL AUTO_INCREMENT,

  `order_id` bigint(20) NOT NULL,

  `goods_id` bigint(20) NOT NULL,

  `goods_quantity` int(11) NOT NULL,

  `goods_amount` decimal(12,2) DEFAULT NULL,

  PRIMARY KEY (`order_item_id`),

  UNIQUE KEY `uk_order_goods` (`order_id`,`goods_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/*!40101 SET character_set_client = @saved_cs_client */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;