Design idea for dynamic table event in the most code website

Original article: Design Ideas for dynamic table event in the most code website

To present the operations of the most code users on the whole site, a dynamic type needs to be designed, which can be expanded infinitely according to the business, you can also specify how many coins can be exchanged for some user behaviors, so that the event table is abstracted Based on the scattered table design of the original javaniu.

The table structure is as follows:

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' user registration \ r \ n download code \ r \ n browse share \ r \ n browse seek \ r \ n favorites share \ r \ n favorites seek \ r \ n browsing activity \ r \ n appending rewards \ r \ n joining activity \ r \ n apprentice \ r \ n following user \ r \ n posting mood \ r \ n posting seek \ r \ n comments seek \ r \ n comments Code \ r \ n upload code \ r \ n download code \ r \ n share code \ r \ n follow user \ r \ n browse share \ r \ n browse seek \ r \ n administrator Delete Code \ r \ n favorites share \ r \ n favorites seek \ r \ n get medal \ r \ n worship Master \ r \ n launch activity \ r \ n browse activity \ r \ n join activity \ r \ n append reward \ r \ n posting mood for one week in a row \ r \ n weekly contribution ranking of users \ r \ n monthly contribution ranking of Users \ r \ n annual contribution ranking of Users \ r \ n weekly ranking of code downloads \ r \ n monthly ranking of code downloads \ r \ n annual ranking of code downloads ', '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 Delete 0 pending review 2 normal ', '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

The event_rule Association Table can specify the coin Rule and Its Dynamic Display Information. The structure is as follows:

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 'registration + 1 \ r \ n posting mood + 1 \ r \ n posting mood for one consecutive week + 5 \ r \ n sharing code + 1 \ r \ n sharing code download + n (n is the sharer's custom currency) \ r \ n seek code-2 \ r \ n upload code + 1 \ r \ n upload code downloaded + 1 \ r \ n code set to optimal + n (n is searcher User-Defined coin) \ r \ n Delete Code-1 \ r \ n invalid search-2 \ r \ n Invalid code-2 \ r \ n administrator reward + n \ r \ n administrator penalty-n \ r \ n weeks top 10 + 5 \ r \ n months top 10 + 10 \ r \ n years top 10 + 100 \ r \ n complete information + 1 \ r \ n monthly vip + 100 \ r \ n master gift + n ox coins \ r \ n grant badge + 5 ox coins \ 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

The user behavior data model of event is abstracted as follows:

Model 1: User a generates dynamic 0 through event x = user_id_a 0 0 0
A => x => 0
Model 2: User a generates user a data through event x 1 = user_id_a 0 0 1
A => x => 1
Model 3: User a generates user a's data through event x on user B's data 1 2 = user_id_a B 1 2
A => x + B + 1 => 2
Model 4: User a generates dynamic 0 for user B's data 1 through event x = user_id_a B 1 0
A => x + B + 1 => 0
Model 5: User a generates dynamic 0 for user B through event x = user_id_a B 0 0
A => x + B => 0
 
Arrangement and combination:
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

This includes all the user events that will appear, as long as you perform business conversion at the java layer:

Core event data conversion java class source code:

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 (eventRu Le. 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 (so UrceId);} 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: // This dynamic case ModuleConstants is not found yet. EVENT_TYPE_RULE_GROUP_JOIN_IN: case ModuleConstants. EVENT_TYPE_RULE_GROUP_DELETE_BY_USER: // case // ModuleConstants. EVENT_TYPE_RULE_GROUP_DELETE_BY_ADMIN: // no such dynamic 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 display layer conversion core code:

<#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>

For example, the freemarker macro code of one of the event types is as follows:

<! -- 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 ">  & nbsp; & nbsp ;$ {post. thirdSort} </a> </span> </div> </# macro>

This design conforms to the design idea of High Cohesion and low coupling. In the future, it can be infinitely expanded based on the business. Of course, the price is that the event table is getting bigger and bigger, but the pressure can be shared by database and table sharding, you can leave a message if you have any suggestions.