Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2859810
  • 博文数量: 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\ntop10+5\r\ntop10+10\r\ntop10+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;

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

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

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

  172.                 }
  173.                 break;
  174.             case ModuleConstants.EVENT_TYPE_RULE_MENTION_POST:
  175.                 source = postService.findOneById(sourceId);
  176.                 break;
  177.             default:
  178.                 _description = description;
  179.             }

  180.             extend.put("description", _description);
  181.             eventRule.setExtend(extend);
  182.             eventRule.setExtendJson(extend.toString());
  183.             event.setEventRule(eventRule);

  184.             event.setSource(source);
  185.             event.setTarget(target);
  186.         } catch (Exception e) {
  187.             logger.error("Fail to setSourceAndTarget event:" + event);
  188.         }
  189.     }

阅读(2063) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~