10分钟掌握数据库建模

快速掌握数据库建模 

文章出处： 
http://netkiller.sourceforge.net 
http://netkiller.github.com 

数据库性能问题，主要来自3三个部分。 
1. 数据库配置 
2. SQL查询语句的性能 
3. 建模的合理性 

客户端性能这里不谈，硬件的影响这里也不讨论，这里只谈数据库服务器本身的问题。 
首先是数据库的配置，这个比较好解决，有经验的DBA都能搞定，无非是连接数，CPU与内存优化等等。 
其次是SQL 查询性能问题，比较要命，不可能每个开发人员都能写出高性能的查询语句，查询与索引息息相关，优化索引是一种手段，通过SQL Review 来解决索引失效的SQL。数据也可能打印出查询性能差的SQL，也是可控的。 
最后是建模，很多企业忽略这块。数据库建模非常重要，数据结构一旦确认，后面变更都会影响整个项目的进行。所以对建模人员要求相当的高。目前招聘到一个合格的建模人员真的很难。 
目前国内企业数据建模都是由开发人员完成。随心所欲建表，加字段，我就发现过一个表有200多个字段的情况，还有字段中存储以逗号分隔的数据。这样的情况非常普遍。 
另外有些企业让DBA负责建模，由于DBA不参与开发，不了解宏观需求，根据开发人员的描述建表，也有很多不合理之处。 

如何建立高性能，可伸缩的数据库呢？ 

经过多年总结与摸索，我找到一个小技巧，请阅读下面文章，当你阅读完后，你就会感觉大彻大悟。 

如果你能掌握这个技巧，在未来开发或数据管理方面会得心应手。 

如何设计User表 

用户帐号表 

用户帐号或通行证系统设计，下面以我的数库为例讲解。 

我一般使用两个表 passport，profile 完成网站会员系统。 

首先说说passport表，你也要以使用user或member等等命名，这个表设计尽可能地简单，不要使用过多字段。仅保存登录所必须用到的字段，如user,password,nickname,email... 登录帐号和密码做复合索引。 

然后是profile表，这个表与passport是1:1关系，保存用户详细信息 

这样设计可以保证海量用户登录时的速度。 


+----------+ 
| user     | 
|----------| 
|id        | <---+ 
|user      |     | 
|passwd    |     | 
|nickname  |     | 
|status    |     | 
+----------+     | 
                1:1 
+----------+     | 
| profile  |     | 
|----------|     | 
|user_id   | o---+ 
|name      | 
|sex       | 
|passwd    | 
|nickname  | 
|status    | 
+----------+ 

如何设计分类表？ 

树形分类表 


+-----------+ 
| category  | 
|-----------| 
|id         | <---+ 
|title      |     | 
|description|    1:n 
|status     |     | 
|parent_id  | o---+ 
+-----------+ 



CREATE TABLE `category` ( 
`id` SMALLINT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
`name` VARCHAR(10) NOT NULL, 
`description` VARCHAR(255) NULL, 
`status` ENUM('enable','desable') NOT NULL DEFAULT 'enable', 
`parent_id` SMALLINT(10) UNSIGNED NOT NULL DEFAULT '0', 
PRIMARY KEY (`id`), 
CONSTRAINT `FK1` FOREIGN KEY (`parent_id`) REFERENCES `category` (`id`) 
) 
COMMENT='goods category' 
ENGINE=InnoDB 
ROW_FORMAT=DEFAULT 


多对多分类 

多对多分类,主要用于满足，一个产品/文章属于多个分类的需求。 


      +------------+ 
      | category   | 
      |------------| 
+--> |id          | <---+ 
|    |title       |     |     +----------------------+ 
1:n   |description |    1:n    | categroy_has_product | 
|    |status      |     |     +----------------------+ 
+--o |parent_id   |     |     | id                   | 
      +------------+     +---o | category_id          | 
                         +---o | product_id           | 
      +------------+     |     +----------------------+ 
      | product    |    1:n 
      +------------+     | 
      |id          | <---+ 
      |price       | 
      |quantity    | 
      |...         | 
      |status      | 
      +------------+ 

快速检索子分类设计 

上面我刚刚讲过怎样实现“不限子树的分类树”，我们可以实现不限层次的无线分类表。 


+-----------+ 
| category  | 
|-----------| 
|id         | <---+ 
|title      |     | 
|description|    1:n 
|status     |     | 
|parent_id  | o---+ 
+-----------+ 


问题出来了，当我需要读取一个分类（任意分类）下的所有子分类，怎样实现，很多人会说用“递归”。 当然“递归”可是现实我们的需求，在几百个分类的项目中，使用递归也不是不可以的，但是当数量非常庞大时怎么办？ 

当然有更好的解决方案，请看下面 


+-----------+ 
| category  | 
|-----------| 
|id         | <---+ 
|title      |     | 
|description|    1:n 
|status     |     | 
|parent_id  | o---+ 
|path       | 
+-----------+ 



+-------------------------------------------------------------------------+ 
| category                                                                | 
+----+-----------+-----------------------+--------+-----------+-----------+ 
| id | name      | description           | status | parent_id | path      | 
+----+-----------+-----------------------+--------+-----------+-----------+ 
|  1 | 中国    | 中华人民共和家                                    | Y      |      NULL | 1/        | 
|  4 | 广东省 | 广东省                                                      | Y      |         1 | 1/4       | 
|  5 | 深圳市 | NULL                      | Y      |         4 | 1/4/5     | 
|  6 | 宝安区 | NULL                      | Y      |         5 | 1/4/5/6   | 
|  7 | 龙华镇 | NULL                      | Y      |         6 | 1/4/5/6/7 | 
+----+-----------+-----------------------+--------+-----------+-----------+ 



CREATE TABLE `category` ( 
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '分类ID', 
`name` VARCHAR(50) NOT NULL COMMENT '分类名称', 
`description` VARCHAR(200) NULL DEFAULT NULL COMMENT '分类描述', 
`status` ENUM('Y','N') NOT NULL DEFAULT 'Y' COMMENT '分类状态有继承性', 
`parent_id` INT(10) NULL DEFAULT '1' COMMENT '分类父ID', 
`path` VARCHAR(255) NOT NULL COMMENT '分类递归路径索引', 
INDEX `PK` (`id`), 
INDEX `relation` (`id`, `parent_id`), 
INDEX `FK_category_category` (`parent_id`), 
INDEX `path` (`path`) 
) 
COMMENT='分类表' 
ENGINE=InnoDB 
ROW_FORMAT=DEFAULT 
AUTO_INCREMENT=0 

insert into category(`name`,`description`,`status`,`parent_id`,`path`) values('中国','中华人民共和家','Y',null,'1/') 



ALTER TABLE `category` 
ADD CONSTRAINT `FK_category_category` FOREIGN KEY (`parent_id`) REFERENCES `category` (`id`) 


抽取广东子树 


select * from category where path like '1/4%'; 



mysql> select * from category where path like '1/4%'; 
+----+-----------+-------------+--------+-----------+-----------+ 
| id | name      | description | status | parent_id | path      | 
+----+-----------+-------------+--------+-----------+-----------+ 
|  4 | 广东省 | 广东省   | Y      |         1 | 1/4       | 
|  5 | 深圳市 | NULL        | Y      |         4 | 1/4/5     | 
|  6 | 宝安区 | NULL        | Y      |         5 | 1/4/5/6   | 
|  7 | 龙华镇 | NULL        | Y      |         6 | 1/4/5/6/7 | 
+----+-----------+-------------+--------+-----------+-----------+ 
4 rows in set (0.00 sec)

文章表设计 
看具体情况，拆分表，可按“日”，“月”，“年”等等 


      +-----------+ 
      | category  | 
      |-----------| 
  +-->|id         | <---+ 
  |   |title      |     | 
  |   |description|    1:n 
  |   |status     |     | 
  |   |parent_id  | o---+ 
  |   +-----------+ 
  | 
1:n 
  | 
  |   +-----------------+            +------------------+ 
  |   | article_2008_01 |            | feedback_2008_01 | 
  |   |-----------------|            |------------------| 
  |   |id               |<--1:n--+   |id                | 
  |   |title            |        |   |title             | 
  |   |content          |        |   |content           | 
  |   |datetime         |        |   |datetime          | 
  |   |status           |        |   |status            | 
  +--o|category_id      |        +--o|news_id           | 
  +--o|user_id          |        +-->|user_id           | 
  |   +-----------------+        |   +------------------+ 
  |                              | 
1:n  +----------+     +---1:n---+ 
  |   | user     |     | 
  |   |----------|     | 
  +-->|id        | <---+ 
      |user      | 
      |passwd    | 
      |nickname  | 
      |status    | 
      +----------+ 


4.1. 分区表设计 

分区表可以通过表空间，等等技术实现，优点是解决了Union查询问题，保证了数据的一致性。 


      +-----------+ 
      | category  | 
      |-----------| 
  +-->|id         | <---+ 
  |   |title      |     | 
  |   |description|    1:n 
  |   |status     |     | 
  |   |parent_id  | o---+ 
  |   +-----------+ 
  | 
1:n 
  | 
  |   +-----------------+            +-----------------+ 
  |   | article         |            | feedback        | 
  |   |-----------------|            |-----------------| 
  |   |id               |<--1:n--+   |id               | 
  |   |title            |        |   |title            | 
  |   |content          |        |   |content          | 
  |   |datetime         |        |   |datetime         | 
  |   |status           |        |   |status           | 
  +--o|category_id      |        +--o|news_id          | 
  +--o|user_id          |        +-->|user_id          | 
  |   +-----------------+        |   +-----------------+ 
  |   | 2007,2008,2009  |        |   | 2007,2008,2009  | 
  |   +-----------------+        |   +-----------------+ 
  |                              | 
1:n  +----------+     +---1:n---+ 
  |   | user     |     | 
  |   |----------|     | 
  +-->|id        | <---+ 
      |user      | 
      |passwd    | 
      |nickname  | 
      |status    | 
      +----------+ 

评论表 


+----------+ 
| user     | 
|----------| 
|id        | <---+ 
|user      |     | 
|passwd    |     | 
|nickname  |     | 
|status    |     | 
+----------+     | 
                1:n 
+-----------+    |      +-----------+ 
| feedback  |    |      | news      | 
|-----------|    |      |-----------| 
|id         |    |  +-->|id         | 
|title      |    |  |   |title      | 
|content    |    |  |   |content    | 
|datetime   |    | 1:n  |datetime   | 
|status     |    |  |   |status     | 
|user_id    |o---+  |   |user_id    | 
|news_id    |o------+   +-----------+ 
+-----------+ 

记录点击率，阅读次数，及评分表 


+--------------+             +--------------+ 
| article      |             | article_rank | 
|--------------|             |--------------| 
|id            | <---1:1---o |article_id    | 
|title         |             |click         | 
|content       |             |read          | 
|datetime      |             |score         | 
|status        |             |...           | 
|category_id   |             |...           | 
|user_id       |             |...           | 
+--------------+             +--------------+ 

产品属性表 

7.1. 简单实现 


+------------+           +--------------------------+           +-----------------------+ 
| product    |           | product_attribute        |           |product_attribute_key  | 
+------------+           +--------------------------+           +-----------------------+ 
|id          | <--1:1--o |product_id                |     +---> |id                     | 
|price       |           |product_attribute_key_id  | o---+     |name                   | 
|quantity    |           |product_attribute_value_id| o---+     +-----------------------+ 
|...         |           +--------------------------+     |     +-----------------------+ 
|category_id |                                           1:n    |product_attribute_value| 
+------------+                                            |     +-----------------------+ 
                                                          +---> |id                     | 
                                                                |name                   | 
                                                                +-----------------------+ 


7.2. 实现属性组管理 

product attribute group 


+------------+           +--------------------------+           +--------------------------+           +-----------------------+ 
| category   |           | product_attribute_group  |           | product_attribute        |           |product_attribute_key  | 
+------------+           +--------------------------+           +--------------------------+           +-----------------------+ 
|id          |     +---> |id                        | <--1:n--o |product_attribute_group_id|     +---> |id                     | 
|title       |     |     |name                      |           |product_attribute_key_id  | o---+     |name                   | 
|description |    1:1    |status                    |           |product_attribute_value_id| o---+     +-----------------------+ 
|status      |     |     +--------------------------+           +--------------------------+     |     +-----------------------+ 
|parent_id   |     |                                                                            1:n    |product_attribute_value| 
|default_pag | o---+                                                                             |     +-----------------------+ 
+------------+                                                                                   +---> |id                     | 
                                                                                                       |name                   | 
                                                                                                       +-----------------------+ 


7.3. 可编辑属表 

product attribute group 

    
    +------------+           +------------------+           +--------------------------+           +---------------------------------+ 
    | category   |           | attribute_group  |           | group_has_attribute      |           |attribute_key                    | 
    +------------+           +------------------+           +--------------------------+           +---------------------------------+ 
+->|id          |     +-->  |id                | <--1:n--o |attribute_group_id        |     +-+-> |id                               | 
|  |title       |     |     |name              |           |attribute_key_id          | o---+ |   |name                             | 
|  |description |    1:1    |status            |           |                          |       |   |type enum('Bool','List','Input') | 
|  |status      |     |     +------------------+           +--------------------------+       |   |default array()                  | 
|  |parent_id   |     |                                                                       |   +---------------------------------+ 
|  |default_pag | o---+                                                                       | 
|  +------------+                                                                             | 
1:n                                                                                            | 
|  +-------------+             +--------------------------+                                   | 
|  | product     |             | product_attribute        |                                   | 
|  +-------------+             +--------------------------+                                   | 
|  |id           |         +-> |product_id                |                                   | 
|  |price        |         |   |attribute_key_id          | o---------------1:n---------------+ 
|  |quantity     |         |   |attribute_value           | 
|  |...          |         |   +--------------------------+ 
+-o|category_id  |         | 
    |attr_group_id| <--1:n--o 
    +-------------+ 
    
    
+--------------------------------------------------+ 
| product_attribute_key                            | 
+--------------------------------------------------+ 
| 1 | color | list | red,green,blue                | 
| 2 | sex   | bool | Female,Male                   | 
| 3 | qty   | input| ''                            | 
+--------------------------------------------------+ 
    
国际化语言表 


      +-----------+             +---------------+ 
      | category  |    .---+    | category_lang | 
      |-----------|   /    |    +---------------+ 
  +-->|id         | <---+  +--o |category_id    | 
  |   |title      |     |       |language_id    | o---+ 
  |   |description|    1:n      |name           |     |      +-------------+ 
  |   |status     |     |       +---------------+     .      | language    | 
  |   |parent_id  | o---+                              \     +-------------+ 
  |   +-----------+                                     >--> |id           | 
1:n                                                   /     |lang         | 
  |   +------------+                                  '      |status       | 
  |   | product    |                                  |      +-------------+ 
  |   +------------+           +--------------+       | 
  |   |id          | <---+     | product_lang |       | 
  |   |price       |     |     +--------------+       | 
  |   |quantity    |     +---o |product_id    |       | 
  |   |...         |           |language_id   | o-----+ 
  +-o |category_id |           |name          | 
      +------------+           +--------------+ 

Workflow 



   +------------+     +---------------+     +-----------+ 
   | user       |     | role_has_user |     | role      | 
   +------------+     +---------------+     +-----------+ 
   |id          |o-+  |id             |  +->|id         |<-+ 
   |node_id     |  +->|user_id        |  |  |name       |  | 
   |up_id       |     |role_id        |o-+  |description|  | 
   +------------+   +---------------+     +-----------+  | 
                                                   | 
   +----------------+     +------------+                   | 
   | workflow       |     | job        |                   | 
   +----------------+     +------------+                   | 
+->|id              |  +->|id          |                   | 
|  |job_id          |o-+  |name        |                   | 
+-o|up_id           |     |role_id     |o------------------+ 
   |                |     |description | 
   +----------------+   +------------+ 

内容版本控制 

主表 

CREATE TABLE `article` ( 
`article_id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT, 
`cat_id` SMALLINT(5) NOT NULL DEFAULT '0', 
`title` VARCHAR(150) NOT NULL DEFAULT '', 
`content` LONGTEXT NOT NULL, 
`author` VARCHAR(30) NOT NULL DEFAULT '', 
`keywords` VARCHAR(255) NOT NULL DEFAULT '', 
PRIMARY KEY (`article_id`), 
INDEX `cat_id` (`cat_id`) 
) 
ENGINE=MyISAM 
ROW_FORMAT=DEFAULT 
AUTO_INCREMENT=1 

本版控制表，用于记录每次变动 

CREATE TABLE `article_history` ( 
`id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT, 
`article_id` MEDIUMINT(8) UNSIGNED NOT NULL, 
`cat_id` SMALLINT(5) NOT NULL DEFAULT '0', 
`title` VARCHAR(150) NOT NULL DEFAULT '', 
`content` LONGTEXT NOT NULL, 
`author` VARCHAR(30) NOT NULL DEFAULT '', 
`keywords` VARCHAR(255) NOT NULL DEFAULT '', 
PRIMARY KEY (`id`), 
INDEX `article_id` (`article_id`) 
) 
ENGINE=MyISAM 
ROW_FORMAT=DEFAULT 
AUTO_INCREMENT=1 

版本控制触发器 

DROP TRIGGER article_history; 

DELIMITER // 
CREATE TRIGGER article_history BEFORE update ON article FOR EACH ROW 
BEGIN 
INSERT INTO article_history SELECT * FROM article WHERE article_id = OLD.article_id; 
END; // 
DELIMITER; 
   
我的建表规则很多是基于Form建模(但不是全部都按form走，这样说让各位好理解)，例如用户登录表单： 
user 
password 
submit 
当做用户认证的时候只需要读user即可。此时profile空闲 
当点击编辑个人信息的时候才读取profile表。 
用户user表只有 user,passwd等几个字段，性能远比一个大user表好。 
像MySQL这样的数据库，有些操作会锁表，将user 分为两个1：1的表可以避开一部分锁表影响 

现在我们来设计个order(订单系统表)，会用到上面的product与user表。 
order 表 
id 
user_id 
sn 订单编号 
... 
created 创建时间 

order_item表 
id 
order_id 
product_id 
.... 

当点击我的订单时查询 order表，当点击定点细节时读order_item表。以此类推，不多举例。 

延伸阅读 
http://netkiller.sourceforge.net 
http://netkiller.github.com