Chinaunix首页 | 论坛 | 博客

Arvil to dreamiforeverlove.blog.chinaunix.net

首页　| 　博文目录　| 　关于我

nba76ers

  • 博客访问： 2856766
  • 博文数量： 471
  • 博客积分： 7081
  • 博客等级： 少将
  • 技术积分： 5369
  • 用 户 组： 普通用户
  • 注册时间： 2012-01-04 21:55
文章分类

全部博文（471）

文章存档

2014年（90）

2013年（69）

2012年（312）

我的朋友
最近访客
推荐博文
相关博文
持续关注最代码数据库设计

分类： Mysql/postgreSQL

2014-09-11 15:22:29

最代码的数据库设计结合了javaniu的经验，将所有主业务表设计为project表，所有业务都由该表来体现，通过字段type来区别是什么业务，对于业务间高度相同的属性就加到project表中，对于某个业务特有的属性则通过extend_json字段来表达，该这段是json字符串的，可以随意定义。project的表结构如下：

CREATE TABLE `zuidaima_project` (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `create_time` datetime NOT NULL,

  `update_time` datetime DEFAULT NULL,

  `user_id` bigint(20) NOT NULL,

  `type` int(11) NOT NULL DEFAULT '0' COMMENT '1寻求代码\r\n2分享代码\r\n3发起活动\r\n4活动日志\r\n5分享经验\r\n6发表博客',

  `title` varchar(1000) NOT NULL,

  `content` longtext NOT NULL,

  `status` int(2) NOT NULL COMMENT '-1删除\r\n0待审核\r\n2正常\r\n11寻求中31活动中',

  `extend_json` varchar(2000) DEFAULT '' COMMENT '扩展信息json，如寻求的代码数,浏览数,评论数,来源',

  `topics` varchar(500) NOT NULL DEFAULT '' COMMENT '所属话题json,如[{id:1},{id:2}]',

  `source_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '活动日志则为活动id',

  `first_sort` int(11) NOT NULL DEFAULT '0',

  `second_sort` int(11) NOT NULL DEFAULT '0',

  `third_sort` int(11) NOT NULL DEFAULT '0',

  `publish_time` datetime DEFAULT NULL,

  `from_url` varchar(500) DEFAULT NULL,

  `uuid` bigint(20) NOT NULL,

  `tags` varchar(500) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=2860 DEFAULT CHARSET=utf8

对于以后数据量大的问题，完全可以通过拆库拆表来做到，所以不用担心这个问题，这样在java mvc层实现代码就很简单了

拆表 拆库 
拆表就是将一张表复制N多张，里面分别存放不用内容的数据，数据的存放是用HASH算法来决定放入哪张表。 
例如用户表user,传统情况就是一张表，拆表就是将表复制为user_01,user_02等里面都存放了格式一样的不同用户数据。 

拆库和拆表类似，就是库的复制。 

拆表或拆库有很多的HASH算法，主要目的就是减少表的数据量，用算法保证每个表的数据量平均，请求，读写操作被分摊降低压力，而且安全，出了问题最多是一部分用户受影响。缺点就是检索不方便，需要另想办法。 

为了能将最代码整站用户的操作都展现出来，需要设计一种动态类型，既可以根据业务无限扩展，也可以指定某些用户行为是可以产生多少牛币交换的，这样就在原先javaniu的零散的表设计基础上抽象出event表

最代码网站中关于动态表event的设计思路

为了能将最代码整站用户的操作都展现出来，需要设计一种动态类型，既可以根据业务无限扩展，也可以指定某些用户行为是可以产生多少牛币交换的，这样就在原先javaniu的零散的表设计基础上抽象出event表

表结构如下：

01

CREATE TABLE `javaniu_event` (

02

  `id` bigint(20) unsigned NOT NULL auto_increment,

03

  `create_time` datetime NOT NULL,

04

  `update_time` datetime default NULL,

05

  `event_rule_id` bigint(20) NOT NULL default '0' COMMENT '用户注册\r\n下载代码\r\n浏览分享\r\n浏览寻求\r\n收藏分享\r\n收藏寻求\r\n浏览活动\r\n追加悬赏\r\n加入活动\r\n拜师\r\n关注用户\r\n发表心情\r\n发表寻求\r\n评论寻求\r\n评论代码\r\n上传代码\r\n下载代码\r\n分享代码\r\n关注用户\r\n浏览分享\r\n浏览寻求\r\n管理员删除代码\r\n收藏分享\r\n收藏寻求\r\n获取勋章\r\n拜师傅\r\n发起活动\r\n浏览活动\r\n加入活动\r\n追加悬赏\r\n连续一周发表心情\r\n用户周贡献排行\r\n用户月贡献排行\r\n用户年贡献排行\r\n代码下载周排行\r\n代码下载月排行\r\n代码下载年排行',

06

  `user_id` bigint(20) NOT NULL default '0',

07

  `source_user_id` bigint(20) NOT NULL default '0',

08

  `source_id` bigint(20) NOT NULL default '0',

09

  `target_id` bigint(20) NOT NULL default '0',

10

  `status` int(2) NOT NULL default '0' COMMENT '-1删除0待审核2正常',

11

  `type` int(2) NOT NULL default '0',

12

  PRIMARY KEY  (`id`),

13

  KEY `create_time` (`create_time`),

14

  KEY `userid_status` (`user_id`,`status`),

15

  KEY `event_rule_id_source_id` (`event_rule_id`,`source_id`),

16

  KEY `event_rule_id_status` (`event_rule_id`,`status`),

17

  KEY `type_source_id` (`type`,`source_id`)

18

) ENGINE=InnoDB DEFAULT CHARSET=utf8

关联表event_rule可以指定牛币规则及其动态显示信息，结构如下：

01

CREATE TABLE `javaniu_event_rule` (

02

  `id` bigint(20) unsigned NOT NULL auto_increment,

03

  `create_time` datetime NOT NULL,

04

  `update_time` datetime default NULL,

05

  `type` int(1) NOT NULL COMMENT '注册+1\r\n发表心情+1\r\n连续一周发表心情+5\r\n分享代码+1\r\n分享代码被下载+n(n为分享者者自定义牛币)\r\n寻求代码-2\r\n上传代码+1\r\n上传代码被下载+1\r\n代码被设为最佳+n(n为寻求者者自定义牛币)\r\n删除代码-1\r\n无效寻求-2\r\n无效代码-2\r\n管理员奖赏+n\r\n管理员惩罚-n\r\n周top10+5\r\n月top10+10\r\n年top10+100\r\n信息完善+1\r\n包月vip+100\r\n师傅赠送+n牛币\r\n授予徽章+5牛币\r\n',

06

  `name` varchar(100) NOT NULL,

07

  `niubi` int(11) NOT NULL,

08

  `extend_json` varchar(1000) NOT NULL,

09

  PRIMARY KEY  (`id`)

10

) ENGINE=InnoDB DEFAULT CHARSET=utf8

event的用户行为数据模型抽象如下：

 模型一：用户a通过事件x产生动态0=user_id_a 0 0 0
 a=>x=>0
 模型二：用户a通过事件x产生产生用户a的数据1=user_id_a 0 0 1
 a=>x=>1
 模型三：用户a通过事件x对用户b的数据1产生用户a的数据2=user_id_a b 1 2
 a=>x+b+1=>2 
 模型四：用户a通过事件x对用户b的数据1产生动态0=user_id_a b 1 0
 a=>x+b+1=>0
 模型五：用户a通过事件x对用户b产生动态0=user_id_a b 0 0
 a=>x+b=>0
 
 排列组合:
 user_id source_user_id source_id target_id
 user_id
 user_id source_user_id
 user_id source_user_id source_id
 user_id source_user_id source_id target_id

这样就囊括了所有会出现的用户event，只要在java层做业务转换即可：


  1. package com.zuidaima.core.service.impl;

  3.     private void setSourceAndTarget(Event event, EventRule _eventRule) {
  4.         try {
  5.             EventRule eventRule = new EventRule();
  6.             eventRule.setCreateTime(_eventRule.getCreateTime());
  7.             eventRule.setExtendJson(_eventRule.getExtendJson());
  8.             eventRule.setId(_eventRule.getId());
  9.             eventRule.setName(_eventRule.getName());
  10.             eventRule.setNiubi(_eventRule.getNiubi());
  11.             eventRule.setType(_eventRule.getType());
  12.             eventRule.setUpdateTime(_eventRule.getUpdateTime());
  13.             BaseEntity source = null;
  14.             BaseEntity target = null;
  15.             long sourceId = event.getSourceId();
  16.             long targetId = event.getTargetId();
  17.             JSONObject extend = eventRule.getExtend();
  18.             extend = eventRule.getExtend();
  19.             String description = (String) extend.get("description");
  20.             String _description = null;
  21.             Answer answer = null;
  22.             Project project = null;
  23.             switch (eventRule.getType()) {
  24.             case ModuleConstants.EVENT_TYPE_RULE_PROJECT_CREATE:
  25.             case ModuleConstants.EVENT_TYPE_RULE_PROJECT_DELETE_BY_USER:
  26.             case ModuleConstants.EVENT_TYPE_RULE_PROJECT_DELETE_BY_ADMIN:
  27.             case ModuleConstants.EVENT_TYPE_RULE_PROJECT_VIEW:
  28.             case ModuleConstants.EVENT_TYPE_RULE_PROJECT_COLLECT:
  29.             case ModuleConstants.EVENT_TYPE_RULE_PROJECT_REWARD:
  30.                 if (sourceId > 0) {
  31.                     source = projectService.findOneById(sourceId);
  32.                 }
  33.                 if (targetId > 0) {
  34.                     target = projectService.findOneById(targetId);
  35.                 }
  36.                 project = (Project) target;
  37.                 if (source != null) {
  38.                     project = (Project) source;
  39.                 }
  40.                 if (project == null) {
  41.                     return;
  42.                 }
  43.                 _description = String.format(
  44.                         description,
  45.                         ModuleConstants.PROJECT_TYPE_DESC_MAP.get(
  46.                                 project.getType()).getDesc());
  47.                 break;
  48.             case ModuleConstants.EVENT_TYPE_RULE_POST_CREATE:
  49.             case ModuleConstants.EVENT_TYPE_RULE_POST_DELETE_BY_USER:
  50.             case ModuleConstants.EVENT_TYPE_RULE_POST_DELETE_BY_ADMIN:
  51.                 if (sourceId > 0) {
  52.                     source = postService.findOneById(sourceId);
  53.                 }
  54.                 if (targetId > 0) {
  55.                     target = postService.findOneById(targetId);
  56.                 }
  57.                 Post post = (Post) target;
  58.                 if (source != null) {
  59.                     post = (Post) source;
  60.                 }
  61.                 _description = String.format(description,
  62.                         ModuleConstants.POST_TYPE_DESC_MAP.get(post.getType()));
  63.                 break;
  64.             // case ModuleConstants.EVENT_TYPE_RULE_GROUP_CREATE://暂时没有这种动态
  65.             case ModuleConstants.EVENT_TYPE_RULE_GROUP_JOIN_IN:
  66.             case ModuleConstants.EVENT_TYPE_RULE_GROUP_DELETE_BY_USER:
  67.                 // case
  68.                 // ModuleConstants.EVENT_TYPE_RULE_GROUP_DELETE_BY_ADMIN://暂时没有这种动态
  69.                 if (sourceId > 0) {
  70.                     source = groupService.findOneById(sourceId);
  71.                 }
  72.                 if (targetId > 0) {
  73.                     target = groupService.findOneById(targetId);
  74.                 }
  75.                 Group group = (Group) source;

  77.                 _description = String
  78.                         .format(description,
  79.                                 ModuleConstants.GROUP_TYPE_DESC_MAP.get(group
  80.                                         .getType()));
  81.                 break;
  82.             case ModuleConstants.EVENT_TYPE_RULE_COMMENT_CREATE:
  83.             case ModuleConstants.EVENT_TYPE_RULE_COMMENT_DELETE_BY_USER:
  84.             case ModuleConstants.EVENT_TYPE_RULE_COMMENT_DELETE_BY_ADMIN:
  85.                 target = commentService.findOneById(targetId);
  86.                 Comment comment = (Comment) target;
  87.                 int commentType = comment.getType();
  88.                 if (commentType == ModuleConstants.COMMENT_TYPE_ANSWER) {
  89.                     source = answerService.findOneById(sourceId);
  90.                     answer = (Answer) source;
  91.                     project = (Project) answer.getTarget();
  92.                     _description = String.format(
  93.                             description,
  94.                             ModuleConstants.PROJECT_TYPE_DESC_MAP.get(
  95.                                     project.getType()).getDesc());
  96.                 } else if (commentType == ModuleConstants.COMMENT_TYPE_PROJECT) {
  97.                     source = projectService.findOneById(sourceId);
  98.                     project = (Project) source;
  99.                     _description = String.format(
  100.                             description,
  101.                             ModuleConstants.PROJECT_TYPE_DESC_MAP.get(
  102.                                     project.getType()).getDesc());
  103.                 } else if (commentType == ModuleConstants.COMMENT_TYPE_POST) {
  104.                     source = postService.findOneById(sourceId);
  105.                     post = (Post) source;
  106.                     _description = String.format(description,
  107.                             ModuleConstants.POST_TYPE_DESC_MAP.get(post
  108.                                     .getType()));
  109.                 } else {

  111.                 }
  112.                 break;
  113.             case ModuleConstants.EVENT_TYPE_RULE_ANSWER_CREATE:
  114.             case ModuleConstants.EVENT_TYPE_RULE_ANSWER_BEEN_SET_PERFECT:
  115.                 source = projectService.findOneById(sourceId);
  116.                 target = answerService.findOneById(targetId);
  117.                 project = (Project) source;
  118.                 _description = String.format(
  119.                         description,
  120.                         ModuleConstants.PROJECT_TYPE_DESC_MAP.get(
  121.                                 project.getType()).getDesc());
  122.                 break;
  123.             case ModuleConstants.EVENT_TYPE_RULE_ANSWER_GET:
  124.             case ModuleConstants.EVENT_TYPE_RULE_ANSWER_DELETE_BY_USER:
  125.             case ModuleConstants.EVENT_TYPE_RULE_ANSWER_DELETE_BY_ADMIN:
  126.                 source = answerService.findOneById(sourceId);
  127.                 answer = (Answer) source;
  128.                 Project _project = (Project) answer.getTarget();
  129.                 _description = String.format(
  130.                         description,
  131.                         ModuleConstants.PROJECT_TYPE_DESC_MAP.get(
  132.                                 _project.getType()).getDesc());
  133.                 break;
  134.             case ModuleConstants.EVENT_TYPE_RULE_REPUTATION_CREATE:
  135.                 if (sourceId > 0) {
  136.                     source = reputationService.findOneById(sourceId);
  137.                 }
  138.                 if (targetId > 0) {
  139.                     target = reputationService.findOneById(targetId);
  140.                 }
  141.                 break;
  142.             case ModuleConstants.EVENT_TYPE_RULE_USER_FOLLOW:
  143.                 source = userService.findOneById(sourceId);
  144.                 User _user = (User) source;
  145.                 _description = String
  146.                         .format(description,
  147.                                 "" + _user.getName() + "");
  148.                 break;
  149.             case ModuleConstants.EVENT_TYPE_RULE_MENTION_COMMENT:
  150.                 target = commentService.findOneById(targetId);
  151.                 comment = (Comment) target;
  152.                 commentType = comment.getType();
  153.                 if (commentType == ModuleConstants.COMMENT_TYPE_ANSWER) {
  154.                     source = answerService.findOneById(sourceId);
  155.                     answer = (Answer) source;
  156.                     project = (Project) answer.getTarget();
  157.                     _description = String.format(
  158.                             description,
  159.                             ModuleConstants.PROJECT_TYPE_DESC_MAP.get(
  160.                                     project.getType()).getDesc());
  161.                 } else if (commentType == ModuleConstants.COMMENT_TYPE_PROJECT) {
  162.                     source = projectService.findOneById(sourceId);
  163.                     project = (Project) source;
  164.                     _description = String.format(
  165.                             description,
  166.                             ModuleConstants.PROJECT_TYPE_DESC_MAP.get(
  167.                                     project.getType()).getDesc());
  168.                 } else if (commentType == ModuleConstants.COMMENT_TYPE_POST) {
  169.                     source = postService.findOneById(sourceId);
  170.                     post = (Post) source;
  171.                     _description = String.format(description,
  172.                             ModuleConstants.POST_TYPE_DESC_MAP.get(post
  173.                                     .getType()));
  174.                 } else {

  176.                 }
  177.                 break;
  178.             case ModuleConstants.EVENT_TYPE_RULE_MENTION_POST:
  179.                 source = postService.findOneById(sourceId);
  180.                 break;
  181.             default:
  182.                 _description = description;
  183.             }

  185.             extend.put("description", _description);
  186.             eventRule.setExtend(extend);
  187.             eventRule.setExtendJson(extend.toString());
  188.             event.setEventRule(eventRule);

  190.             event.setSource(source);
  191.             event.setTarget(target);
  192.         } catch (Exception e) {
  193.             logger.error("Fail to setSourceAndTarget event:" + event);
  194.         }
  195.     }

阅读(2061) | 评论(0) | 转发(0) |
0

上一篇：mysql经纬度查询并且计算2KM范围内附近用户的sql查询性能优化实例教程

下一篇：linux网卡设置

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6