数据库建表 —— 静态数据 和 动态数据 的 拆分

背景

基本表 news、user、news_reply

简单关系：user 创建 news ，user 可以查看 news，并且 回复 news

业务需求

简单来说，具有发布、查询、查看、回复功能。基本表的增删改查OK。

但实际上的需求：要查看 一条新闻的总回复数 、 浏览次数 、 最后一次更新时间。

最简单的做法：在 news 表 加上三列

基于数据的准确性，我们可能用 select count(*) from news_reply where news_id = :news_id 来获取 总回复数

每次 getNews(id) 的时候 news.pageView ++ news， replyNews 的时候 last_upd_time = NOW()

但实际上这样做应该更合理：create table news_ext

news_ext 包含 news_id、page_view、last_upd_time、reply_count 四列

每当 get 的时候 更新 news_ext 的 page_view 和 last_upd_time，reply 的时候 更新 reply_count 的数据

好处：

如果 news 表有 20列属性，那么如果你每次 getNews(id) 的时候 去执行更新，这个代价还是很坑的。

如果 reply 表有 十万条数据，select count() 的 消耗也是很坑的

相对来说， news 表的 title content user_id news_id 等数据都是 比较静态的

page_view、reply_count、last_upd_time 则是 非常动态的。

这就是我理解的所谓 静态数据 和 动态数据 的 拆分。感觉这样做还是具有相当的合理性的。

更新效率 比 直接在 news 表中加 列 快很多。

查询效率 比 直接 select count() 简单很多。

等于是把复杂的问题 拆分成简答的问题处理掉了。

隐患

如果每张表都有个 ext，其实还是挺坑的，多了很多表，不过我自己也只是总结了一下理论，实际应用还没实验，也许不会像想象中那么麻烦吧。

还有个隐患就是：这些数据不是算出来的，而是写出来的，人手动写的错误率比电脑算要高非常多。

/*
Navicat MySQL Data Transfer

Source Server         : Seraph_fd
Source Server Version : 50616
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50616
File Encoding         : 65001

Date: 2014-08-07 23:46:03
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for news
-- ----------------------------
DROP TABLE IF EXISTS `news`;
CREATE TABLE `news` (
  `news_id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `content` text,
  `create_time` datetime DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`news_id`),
  KEY `fk_u_n` (`user_id`),
  CONSTRAINT `fk_u_n` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for news_ext
-- ----------------------------
DROP TABLE IF EXISTS `news_ext`;
CREATE TABLE `news_ext` (
  `news_id` int(11) NOT NULL,
  `page_view` int(11) NOT NULL,
  `last_upd_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `reply_count` int(11) NOT NULL,
  PRIMARY KEY (`news_id`),
  CONSTRAINT `fk_n_n_e` FOREIGN KEY (`news_id`) REFERENCES `news` (`news_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for news_reply
-- ----------------------------
DROP TABLE IF EXISTS `news_reply`;
CREATE TABLE `news_reply` (
  `reply_id` int(11) NOT NULL AUTO_INCREMENT,
  `news_id` int(11) DEFAULT NULL,
  `content` varchar(255) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`reply_id`),
  KEY `fk_u_n_r` (`user_id`),
  KEY `fk_n_n_r` (`news_id`),
  CONSTRAINT `fk_n_n_r` FOREIGN KEY (`news_id`) REFERENCES `news` (`news_id`),
  CONSTRAINT `fk_u_n_r` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) DEFAULT NULL,
  `pass_word` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- View structure for view_news
-- ----------------------------
DROP VIEW IF EXISTS `view_news`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`  VIEW `view_news` AS SELECT
`user`.user_name,
news.news_id,
news.title,
news.content,
news.create_time,
news.user_id,
news_ext.page_view,
news_ext.last_upd_time,
news_ext.reply_count
FROM
news
INNER JOIN `user` ON news.user_id = `user`.user_id
INNER JOIN news_ext ON news_ext.news_id = news.news_id ;