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

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

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

表结构如下：

CREATE TABLE `javaniu_event` (  `id` bigint(20) unsigned NOT NULL auto_increment,  `create_time` datetime NOT NULL,  `update_time` datetime default NULL,  `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代码下载年排行',  `user_id` bigint(20) NOT NULL default '0',  `source_user_id` bigint(20) NOT NULL default '0',  `source_id` bigint(20) NOT NULL default '0',  `target_id` bigint(20) NOT NULL default '0',  `status` int(2) NOT NULL default '0' COMMENT '-1删除0待审核2正常',  `type` int(2) NOT NULL default '0',  PRIMARY KEY  (`id`),  KEY `create_time` (`create_time`),  KEY `userid_status` (`user_id`,`status`),  KEY `event_rule_id_source_id` (`event_rule_id`,`source_id`),  KEY `event_rule_id_status` (`event_rule_id`,`status`),  KEY `type_source_id` (`type`,`source_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

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

CREATE TABLE `javaniu_event_rule` (  `id` bigint(20) unsigned NOT NULL auto_increment,  `create_time` datetime NOT NULL,  `update_time` datetime default NULL,  `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',  `name` varchar(100) NOT NULL,  `niubi` int(11) NOT NULL,  `extend_json` varchar(1000) NOT NULL,  PRIMARY KEY  (`id`)) 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层做业务转换即可：

最核心的event数据转换java类源码：

package com.zuidaima.core.service.impl;private void setSourceAndTarget(Event event, EventRule _eventRule) {try {EventRule eventRule = new EventRule();eventRule.setCreateTime(_eventRule.getCreateTime());eventRule.setExtendJson(_eventRule.getExtendJson());eventRule.setId(_eventRule.getId());eventRule.setName(_eventRule.getName());eventRule.setNiubi(_eventRule.getNiubi());eventRule.setType(_eventRule.getType());eventRule.setUpdateTime(_eventRule.getUpdateTime());BaseEntity source = null;BaseEntity target = null;long sourceId = event.getSourceId();long targetId = event.getTargetId();JSONObject extend = eventRule.getExtend();extend = eventRule.getExtend();String description = (String) extend.get("description");String _description = null;Answer answer = null;Project project = null;switch (eventRule.getType()) {case ModuleConstants.EVENT_TYPE_RULE_PROJECT_CREATE:case ModuleConstants.EVENT_TYPE_RULE_PROJECT_DELETE_BY_USER:case ModuleConstants.EVENT_TYPE_RULE_PROJECT_DELETE_BY_ADMIN:case ModuleConstants.EVENT_TYPE_RULE_PROJECT_VIEW:case ModuleConstants.EVENT_TYPE_RULE_PROJECT_COLLECT:case ModuleConstants.EVENT_TYPE_RULE_PROJECT_REWARD:if (sourceId > 0) {source = projectService.findOneById(sourceId);}if (targetId > 0) {target = projectService.findOneById(targetId);}project = (Project) target;if (source != null) {project = (Project) source;}if (project == null) {return;}_description = String.format(description,ModuleConstants.PROJECT_TYPE_DESC_MAP.get(project.getType()).getDesc());break;case ModuleConstants.EVENT_TYPE_RULE_POST_CREATE:case ModuleConstants.EVENT_TYPE_RULE_POST_DELETE_BY_USER:case ModuleConstants.EVENT_TYPE_RULE_POST_DELETE_BY_ADMIN:if (sourceId > 0) {source = postService.findOneById(sourceId);}if (targetId > 0) {target = postService.findOneById(targetId);}Post post = (Post) target;if (source != null) {post = (Post) source;}_description = String.format(description,ModuleConstants.POST_TYPE_DESC_MAP.get(post.getType()));break;// case ModuleConstants.EVENT_TYPE_RULE_GROUP_CREATE://暂时没有这种动态case ModuleConstants.EVENT_TYPE_RULE_GROUP_JOIN_IN:case ModuleConstants.EVENT_TYPE_RULE_GROUP_DELETE_BY_USER:// case// ModuleConstants.EVENT_TYPE_RULE_GROUP_DELETE_BY_ADMIN://暂时没有这种动态if (sourceId > 0) {source = groupService.findOneById(sourceId);}if (targetId > 0) {target = groupService.findOneById(targetId);}Group group = (Group) source;_description = String.format(description,ModuleConstants.GROUP_TYPE_DESC_MAP.get(group.getType()));break;case ModuleConstants.EVENT_TYPE_RULE_COMMENT_CREATE:case ModuleConstants.EVENT_TYPE_RULE_COMMENT_DELETE_BY_USER:case ModuleConstants.EVENT_TYPE_RULE_COMMENT_DELETE_BY_ADMIN:target = commentService.findOneById(targetId);Comment comment = (Comment) target;int commentType = comment.getType();if (commentType == ModuleConstants.COMMENT_TYPE_ANSWER) {source = answerService.findOneById(sourceId);answer = (Answer) source;project = (Project) answer.getTarget();_description = String.format(description,ModuleConstants.PROJECT_TYPE_DESC_MAP.get(project.getType()).getDesc());} else if (commentType == ModuleConstants.COMMENT_TYPE_PROJECT) {source = projectService.findOneById(sourceId);project = (Project) source;_description = String.format(description,ModuleConstants.PROJECT_TYPE_DESC_MAP.get(project.getType()).getDesc());} else if (commentType == ModuleConstants.COMMENT_TYPE_POST) {source = postService.findOneById(sourceId);post = (Post) source;_description = String.format(description,ModuleConstants.POST_TYPE_DESC_MAP.get(post.getType()));} else {}break;case ModuleConstants.EVENT_TYPE_RULE_ANSWER_CREATE:case ModuleConstants.EVENT_TYPE_RULE_ANSWER_BEEN_SET_PERFECT:source = projectService.findOneById(sourceId);target = answerService.findOneById(targetId);project = (Project) source;_description = String.format(description,ModuleConstants.PROJECT_TYPE_DESC_MAP.get(project.getType()).getDesc());break;case ModuleConstants.EVENT_TYPE_RULE_ANSWER_GET:case ModuleConstants.EVENT_TYPE_RULE_ANSWER_DELETE_BY_USER:case ModuleConstants.EVENT_TYPE_RULE_ANSWER_DELETE_BY_ADMIN:source = answerService.findOneById(sourceId);answer = (Answer) source;Project _project = (Project) answer.getTarget();_description = String.format(description,ModuleConstants.PROJECT_TYPE_DESC_MAP.get(_project.getType()).getDesc());break;case ModuleConstants.EVENT_TYPE_RULE_REPUTATION_CREATE:if (sourceId > 0) {source = reputationService.findOneById(sourceId);}if (targetId > 0) {target = reputationService.findOneById(targetId);}break;case ModuleConstants.EVENT_TYPE_RULE_USER_FOLLOW:source = userService.findOneById(sourceId);User _user = (User) source;_description = String.format(description,"<a href='/user/n/" + _user.getName()+ ".htm'>" + _user.getName() + "</a>");break;case ModuleConstants.EVENT_TYPE_RULE_MENTION_COMMENT:target = commentService.findOneById(targetId);comment = (Comment) target;commentType = comment.getType();if (commentType == ModuleConstants.COMMENT_TYPE_ANSWER) {source = answerService.findOneById(sourceId);answer = (Answer) source;project = (Project) answer.getTarget();_description = String.format(description,ModuleConstants.PROJECT_TYPE_DESC_MAP.get(project.getType()).getDesc());} else if (commentType == ModuleConstants.COMMENT_TYPE_PROJECT) {source = projectService.findOneById(sourceId);project = (Project) source;_description = String.format(description,ModuleConstants.PROJECT_TYPE_DESC_MAP.get(project.getType()).getDesc());} else if (commentType == ModuleConstants.COMMENT_TYPE_POST) {source = postService.findOneById(sourceId);post = (Post) source;_description = String.format(description,ModuleConstants.POST_TYPE_DESC_MAP.get(post.getType()));} else {}break;case ModuleConstants.EVENT_TYPE_RULE_MENTION_POST:source = postService.findOneById(sourceId);break;default:_description = description;}extend.put("description", _description);eventRule.setExtend(extend);eventRule.setExtendJson(extend.toString());event.setEventRule(eventRule);event.setSource(source);event.setTarget(target);} catch (Exception e) {logger.error("Fail to setSourceAndTarget event:" + event);}}

freemarker显示层转换核心代码：

<#switch event.eventRule.type><#case event_type_rule_post_create><@event_post_macro event.target/><#break><#case event_type_rule_project_create><@event_project_macro event event.target/><#break><#case event_type_rule_project_view><#case event_type_rule_project_collect><#case event_type_rule_project_reward><@event_project_macro event event.source/><#break><#case event_type_rule_comment_create><@event_comment_macro event event.target/><#break><#case event_type_rule_answer_create><@event_answer_macro event event.target/><#break><#case event_type_rule_answer_get><#case event_type_rule_answer_been_set_perfect><@event_answer_macro event event.source/><#break><#case event_type_rule_mention_comment><@event_comment_macro event event.target/><#break><#case event_type_rule_mention_post><@event_post_macro event.source/><#break></#switch>

比如其中一种event type的freemarker macro代码如下：

<!--event post--><#macro event_post_macro post><div class="content margin_top5">${post.contentExt}<span class="comments_count"><a target="_blank" href="/mood/${post.id}/comment.htm" rel="nofollow"><img alt="${post.thirdSort}个评论" src="/resource/img/comment.gif">&nbsp;&nbsp;${post.thirdSort}</a></span></div></#macro>

这样的设计符合高内聚低耦合的设计思路，未来可以根据业务实现无限扩张，当然代价就是event表越来越大，但可以通过分库分表来分担压力，大家可以参考下，有好的意见可以留言。