Weibo focuses on the design of user tables

Source: Internet
Author: User
The query needs are as follows:
1. Query which users are concerned.
2. Which users are concerned about the query.
3. Query and a common list of concerns for a user.
4. Whether the query concerns each other.

The above functions can be achieved with a table in the form of user_id follow_user_id add_time columns.
But the attention volume is larger, need to according to user ID to divide the table. I don't know how to design a table structure well.

Ask your friends, please give me a lot of advice. Thank you


Reply to discussion (solution)

Partition with User ID

User ID, with a user ID between each table

Partition with User ID



Moderator I want to use the user ID to divide the table, but the table after the above function is not good to achieve. For example, I want to check which users follow me, after the table, I am not sure which table to check. It is only possible to find out all the sub-tables once.

Save a watchlist on your own
Or will it be possible to save your user's attention to the user's table?

It is not possible to partition a table, because it is not sure which table to check after the table is divided.
The partitioned table is still an entire table for you (although it is divided into multiple files), the actual query is that the file is determined by MySQL rather than you decide

If you still want to divide your own table, then you need to Exchange user_id, follow_user_id columns of content to form a new table, and then the two table sub-table


Partition with User ID



Moderator I want to use the user ID to divide the table, but the table after the above function is not good to achieve. For example, I want to check which users follow me, after the table, I am not sure which table to check. It is only possible to find out all the sub-tables once.


Do not divide the table ah, directly write the SQL statement can be

1. Query which users are concerned: Select ' follow_user_id ' from ' table ' where ' user_id ' = ' Your id '
2. Query by which users are concerned: Select ' user_id ' from ' table ' where ' follow_user_id ' = ' Your id '
3. Query and a common list of concerns for a user. SELECT * from ' table ' where ' user_id ' in (' Your id ', ' A user ID ')
4. Whether the query concerns each other: SELECT * from ' table ' where ' user_id ' = ' your id ' and ' follow_user_id ' = ' A user ID ' user_id ' in (select ' follow_user_id ' fr Om ' table ' where ' user_id ' = ' a user ID ')

Of course, there are better formulations, especially 4th.

SELECT * from ' table ' where ' user_id ' = ' your id ' and ' follow_user_id ' = ' A user ID ' and ' user_id ' in (select ' follow_user_id ' from ' table ') where ' user_id ' = ' a user ID ')
4th, one less and, re-write.

It is not possible to partition a table, because it is not sure which table to check after the table is divided.
The partitioned table is still an entire table for you (although it is divided into multiple files), the actual query is that the file is determined by MySQL rather than you decide

If you still want to divide your own table, then you need to Exchange user_id, follow_user_id columns of content to form a new table, and then the two table sub-table



Thanks for the answer, I'll take a look.

It is not possible to partition a table, because it is not sure which table to check after the table is divided.
The partitioned table is still an entire table for you (although it is divided into multiple files), the actual query is that the file is determined by MySQL rather than you decide

If you still want to divide your own table, then you need to Exchange user_id, follow_user_id columns of content to form a new table, and then the two table sub-table



What is the concept of moderator partitioning? I only know the computer division.

Can you tell me more about this sentence?

"Then you need to swap the contents of the user_id, follow_user_id columns to form a new table, and then divide the table by two"

Thank you!

See Http://www.baidu.com/s?ie=utf-8&bs=mysql%E5%88%86%E5%8C%BA%E8%A1%A8&f=8&rsv_bp=1&wd= for table partitioning mysql%e5%88%86%e5%8c%ba&rsv_sug3=1&rsv_sug=1&rsv_sug1=1&rsv_sug4=26&inputt=796

Attention can be one-way, like this.

user_id follow_user_id1       4

By user_id the table, you check all the tables to get the user_id connected to the follow_user_id.
So you need a different set of tables that are grouped in follow_user_id
follow_user_id user_id 2              1

See Http://www.baidu.com/s?ie=utf-8&bs=mysql%E5%88%86%E5%8C%BA%E8%A1%A8&f=8&rsv_bp=1&wd= for table partitioning mysql%e5%88%86%e5%8c%ba&rsv_sug3=1&rsv_sug=1&rsv_sug1=1&rsv_sug4=26&inputt=796

Attention can be one-way, like this.

user_id follow_user_id1       4

By user_id the table, you check all the tables to get the user_id connected to the follow_user_id.
So you need a different set of tables that are grouped in follow_user_id
follow_user_id user_id 2              1



Moderator
The "follow_user_id" table is also a separate table.
Then this program can support the search, who is concerned about me?
For example, my user ID is 1 by follow_user_id the mantissa of the table.


Follow_user_tbl_1

follow_user_id user_id
1 1
11 1
21 1

Follow_user_tbl_2

follow_user_id user_id
2 1
12 1
22 1

If user_id follow_user_id says USER_ID is follow_user_id concerned
So follow_user_id User_ID said follow_user_id was concerned about USER_ID

You've identified the subject, and it's clear.

Save a watchlist on your own
Or will it be possible to save your user's attention to the user's table?



Saving a watchlist alone is not feasible because the data is too large.

It's not reasonable to follow my user's Save to user table

If user_id follow_user_id says USER_ID is follow_user_id concerned
So follow_user_id User_ID said follow_user_id was concerned about USER_ID

You've identified the subject, and it's clear.



Moderator I listened to you, and began to understand your thoughts. Thank you, sir. It's probably the only way to divide the table.

Ollow_user_tbl_1

follow_user_id user_id
1 1
11 1
21 1

Follow_user_tbl_2

follow_user_id user_id
2 1
12 1
22 1

Isn't that a user association table upstairs? Why the two? One can implement this function.

  • Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.