A brief thought on the design of a microblog database

Http://www.blogjava.net/kalman03/archive/2010/07/19/326558.html

In the microblog system, the three roles of current users, followers (that is, fans) and followers (objects of worship) are indispensable. The seemingly simple relationship between them, but how the database tables will be designed, it makes me very difficult to figure out, in the following solution, you will choose Which? Why choose this? Is there a better solution?

Solution One:

Table name	User Information table
Field name	field code	Field type		Describe
User name	user_id	Varchar (20)		Primary key
Login Password	Password	Varchar (20)
......	......	......
Table name	Table of concerns and followers
Field name	field code		Field type		Describe
User name	user_id		Varchar (20)		Primary key
Followers	Funs		Text
People who are concerned	Wasfuns		Text

This is my first thought of a design, where the "followers" and "followers" are used to splice some special characters split storage, such as a user has only followers B, C, D, E, then the data stored in the Database followers field will be B; C;d; E (for the moment, divide the word identifier;).

Based on the above scenario, I asked a question: What would be a string of characters when the user's "followers" or "followers" are large (for example, 100,000 followers)? And when we need to query "followers" or "followers" of the recent blog information, will face and post information table some time sorting query, processing difficulty is to waste performance.

Solution Two:

Based on the above problems, I have been provided with a scalable solution, but also a good solution to a field of massive data problems. Break down the concerns and followers tables in scenario one into two tables, as follows:

Table name	Followers table
Field name	field code	Field type	Describe
Number	Id	Number	Primary key
User name	user_id	Varchar (20)
Followers number	funs_id	Varchar (20)
Table name	List of people to be followed
Field name	field code	Field type	Describe
Number	Id	Number	Primary key
User name	user_id	Varchar (20)
Number of people being followed	wasfuns_id	Varchar (20)

I am surprised to see this design, imagine, if I a user to have 1W followers, then the user will have 10,000 records in the followers table, this is not a serious data redundancy? This does not even conform to the design specifications of the database. But it turns out that this kind of design is good for large data volumes, so if the relationship between users and users is not confined to concerns and concerns, is it a new form?

Solution Three:

The words "long must be divided, divided long will be", the design of the above further modified, so the two tables of the scheme are merged into one, as follows:

Table name	Table of concerns and followers
Field name	field code	Field type	Describe
Number	Id	Int	Primary key
User name	user_id	Varchar (20)
Target Object	Operate_object	Varchar (20)
State	Status	Number	When the target object is the followers, the mark is 1; When the target object is the person of concern, the mark is 2; When the two sides concern each other, marked as 3; When the target object is Oo, it is marked as XX.

OK, this design not only solves a considerable part of the data redundancy, but also can represent a variety of relationships between users, to facilitate future expansion of the system. But the problem comes out again, it is obvious that the design of the maintenance of the State is also questionable, with a table instead of multiple tables, the data is sure to multiply exponentially, whether it does not conform to the current commonly said "demolition of the table" Strategic approach (as if the state is generally used to "Mark men and women" or "whether delete", It seems to be used for less of this kind of occasion).

In the above user relations solution, can be very simple to boil down to is a pair of many, many to one, many to many relationship well, then exactly how to design, what kind of better, I difficult to understand, look forward to everyone to shoot bricks!

A brief thought on the design of a microblog database