社交网络数据库架构？
用户
- 组
- 链接
- 用户1
- 用户2
- 时间（或其他一些相关链接数据）
- 帖子
- 链接
如果用户所属的链接与帖子属于同一组，则该用户只能看到该帖子。
我想优化数据库以查询允许给定用户查看的所有帖子。构建数据库以实现此目的的最佳方法是什么？
感谢您的帮助！
编辑：上面的模式是帮助您入门的基本想法。再次感谢！
User
- Groups
- Link
- User1
- User2
- Time (or some other relevant link data)
- Posts
- Link
A user can only see a post if the user belongs to a link that belongs to the same group to which the post belongs.
I would like to optimize the database for querying all posts that a given user is allowed to see. What is the best way to structure the database to do this?
Thanks for the help!
EDIT: The above schema is a basic idea to get you started. Thanks again!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这就是我的建模方式：
用户（id 等）
这是用户列表。它仅包含用户数据。 PK 将是
id，因此那里会有一个隐式索引Groups（id 等）
这是组列表。它仅包含组数据。 PK 将是
id，因此那里会有一个隐式索引User_Groups（userId、groupId 等）
这是将用户链接到组的方式。它需要具有用户和组的外键，并且您可以向其中添加任何其他数据，例如用户加入组的日期。这里的 PK 将是
userId和groupId之间的复合键，因此索引将为userId和groupId。您还需要在groupId中添加一个额外的索引，因为“主订单”将由userId给出，而不是由groupId< strong>帖子（id、createdByUserId、belongsToGroupId 等）
这是帖子列表。帖子仅由一名用户创建，并且仅属于一个组。要显示帖子，您需要将createdByUserId 与Users 表连接起来（以显示创建帖子的用户的姓名）。此外，您还需要检查想要查看该帖子的用户是否确实被允许查看该帖子。因此，您将转到 User_Groups 表并使用类似于
$currentUserId = users_groups.userId 和 $currentPostBelongsToGroupId = users_groups.groupId的WHERE子句（或者您可以加入post.belongsToGroupId = user_groups.groupId上的表）。这是总体思路。现在，要专注于“优化数据库以查询允许给定用户查看的所有帖子”，您基本上应该按照我在帖子实体描述中所述进行操作。由于您不是在寻找特定帖子，因此您必须将帖子与 User_Groups 关联起来（此关联将使用在 User_Groups 实体上创建的
groupId索引），然后使用WHERE 子句也在那里说明。该子句将使用在该实体上创建的复合主键。就是这样。这似乎是一个完全索引的路径。现在，为什么我没有添加您在问题中获得的
link实体？因为我不知道它是什么，并且您没有回答我在上面询问它是什么的评论。如果它是 2 个用户之间的链接，那么它不应该属于一个组，因为用户分别属于组，而不是成对的。除此之外，如果链接是我所假设的，那么它根本不会影响用户如何查看他/她自己允许的帖子的性能。可能与link实体相关的问题希望如此帮助或指导您找到更好的解决方案:)
This is how I would model this:
Users(id, etc)
This is the user list. It contains only user data. The PK will be the
id, so there will be an implicit index thereGroups(id, etc)
This is the group list. It contains only group data. The PK will be the
id, so there will be an implicit index thereUser_Groups(userId, groupId, etc)
This is the way to link users to groups. It needs to have the foreing keys to the users and groups and you can add any additional data to it, for instance, date when the user joined the group. The PK here will be a compound key between
userIdandgroupId, so the index will beuserIdandgroupId. You will also want to have an additional index in thegroupId, because the "main order" will be given byuserId, not bygroupIdPosts(id, createdByUserId, belongsToGroupId, etc)
This is the lists of posts. Posts are created by only a user and belong to only one group. To display a post you'll join createdByUserId with Users table (to display the name of the user who created the post). Additionally, you'll want to check if the user who wants to see the post is actually allowed to see it. So, you'll go to the User_Groups table and use a
WHEREclause similar to$currentUserId = users_groups.userId and $currentPostBelongsToGroupId = users_groups.groupId(OR you can join the table onpost.belongsToGroupId = user_groups.groupId).This is the general idea. Now, to focus on "optimize the database for querying all posts that a given user is allowed to see" you should basically do what I stated in the Posts entity description. As you're not looking for a particular post you'll have to join Posts with User_Groups (this join will use the
groupIdindex created on the User_Groups entity) and then use theWHEREclause stated there too. That clause will use the compound primary key created on that entity. And that's it. It seems to be a fully indexed path.Now, why didn't I add the
linkentity you've got on your question? Because I've not idea what it is and you didn't answer my comment on top asking what it was. If it was a link between 2 users then it shouldn't belong to a group because users belong to groups separately, not in pairs. Apart from that, if the link is what I assumed then it wouldn't affect at all the performance on how a user see his/her own allowed posts. Possibly related question to thelinkentityHope this helps or guide you to a better solution :)