Many Web projects will be related to the design of the collection function, for users to save their favorite text, pictures, links, multimedia and other information, this article is mainly about how to design a multi-modular Web site collection function of the database.
Suppose to be a social networking system similar to Sina Weibo, and we want to implement a collection of microblogging functions, we may need to design the following favorites Pre_collect_feed
Collect_id,collect_user,collect_feed,collect_time;
Primary key ID, collection user, collection of items, collection time;
Now because of the user needs, we also want to implement a collection of photos, we may need to design the following collection table Pre_collect_photo
Collect_id,collect_user,collect_photo,collect_time;
Primary key ID, collection user, collection of items, collection time;
Now because of the user needs, we also want to implement a collection activities, we may need to design the following collection table Pre_collect_event
Collect_id,collect_user,collect_event,collect_time;
Primary key ID, collection user, collection of items, collection time;
Now because of the user needs, we also want to implement a collection of topic functions, we may need to design the following collection table Pre_collect_topic
Collect_id,collect_user,collect_topic,collect_time;
Primary key ID, collection user, collection of items, collection time;
。
。
。
As the project grows, we need to design a collection of more and more items, this time to find a design method to integrate the above categories of collections.
Solution: First of all, it is clear that the user's collection of items corresponds to a record in the database table, and this record is unique in the corresponding table, so in the Favorites table, storing only the primary keys in the corresponding table is not going to work, because the values of the primary keys in the different tables are stored in a single collection table. This value is likely to be the same, such as the user published a micro-blog in the microblogging table the primary key is 1, the user publishes a picture in the picture table the primary key is also 1, assumes the user also collects this micro-blog and this photograph, then the collection item ID is 1 words, cannot differentiate. as follows:
Collect_id,collect_user,collect_item,collect_time
1,1,1,141414142
2,1,1,141414146
As a result, you can add fields to the table structure above to record the name of the table in which the item is stored, or even the module name of the item, as shown below
Collect_id,collect_user,collect_module,collect_table,collect_row,collect_time
1,1,feed,pre_feed,1,141414142
2,1,photo,pre_photo,1,141414146
Finish.