数据库设计为“追随者”和“追随者”？ [英] database design for 'followers' and 'followings'?

问题描述

亲爱的数据库专家/程序员：

Dear database experts/programmers:

我有一个包含用户信息的mysql表，例如

I have a mysql table with users information, such as

id      user_id         name etc.
1       userA            
2       userB   
3       userC
..      ..
..      ..

我想创建一个像'follow'等用户喜欢twitter的功能。用户A可以跟随用户B，或者用户B可以跟随用户A，或者两者可以彼此跟随。
为了这个目的，我应该创建1个表，让说跟随者

I want to create a feature like 'follow' other users something like twitter. A userA can follow userB, or userB can follow userA, or both can follow each other. For that purpose, should I create 1 table, lets say followers

id      user_id     follower_id
1           userA       userB
2           userC       userA
3           userA       userC
4           userB       userC
5           userX       userA

现在我想找到谁在关注userA。我会这样sth喜欢：
选择*从关注者where user_id = userA
这将选择userB和userC。这是我需要的。

Now I want to find who is following a userA. I'd so sth like: Select * from followers where user_id = userA This will select userB and userC. Thats what I need.

现在我想找到userA正在关注的人员（例如在上表中，userA正在关注userC和userX，像
从关注者中选择* follower_id = userA。

Now I want to find, which persons userA is following (for example in above table, userA is following userC and userX. Then I should run something like Select * from followers where follower_id=userA.

我的问题是，这个数据库设计是否正确这个问题（考虑到数据库冗余和优化

My question is that, is this database design correct for this problem (considering in mind database redundancy and optimization?) Or there can be better approach than this? Thanks.

推荐答案

一般来说，你的设计是正确的。

In general, your design is correct.

但是，如果user_id在表users中是唯一的，那么在users中不需要列id（包含唯一id em>和唯一的user_id是很不寻常的。）您也不需要在表追踪者中的列id。

But, if user_id is unique in the table "users", you don't need the column "id" in "users". (A single table containing a unique "id" and a unique "user_id" is pretty unusual.) You also don't need the column "id" in the table "followers".

关键字中的主键应为（user_id，follower_id），并确保每个列都有引用users中的user_id的外键。

Primary key in "followers" should be (user_id, follower_id), and make sure each of those columns has a foreign key referencing "user_id" in "users".

这篇关于数据库设计为“追随者”和“追随者”？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！