我应该如何构建这个组/角色架构？
我正在编写一个应用程序，它具有类似于 Google Circle/FB 好友列表的某些功能。
- 用户可以将他们认识的人分组（家人、同事、朋友等......） （目前组不能嵌套）
- 用户可以向组发送消息、设置每个组的隐私设置等
- 当帖子在组内共享时，这些组的用户可以评论并查看其他人的评论，无论他们与其他人（在该组内）的关系
目前，由于时间和资源的限制，我们正在使用关系数据库（mysql）。无论如何，我试图找到构建数据库的最佳方法，以平衡性能和清晰度。这是我们目前所拥有的：
users:
user_id
default_group_id
friend_group_id
groups:
group_id
groups_to_users:
user_id
group_id
messages:
message_id
messages_to_groups:
message_id
group_id
galleries_to_groups:
gallery_id
group_id
当第一次创建用户时，他/她将有 2 个基本组：
- 默认组仅包含该单个用户
- 朋友组将包含与他/她成为朋友的每个人
我们将简单地使用group_id 来确定“权限”，而不是使用 user_id。这样我们就可以跳过查询 2 个表的复杂性。
同时，使用上面的结构，我们在查询用户收到的所有消息时也遇到了障碍，因为如果该用户有 100 个好友，我们可能需要查询至少 100 个群组。所以现在我们通过这种相当hacky的方法来解决这个问题：
如果用户向一个组发送消息，那么我们会遍历该组中的成员列表并为每个用户保存一条记录（message_id，（default_）group_id）。问题是，如果这个组有 1000 多个成员，那么我们必须为发送到该组的每条新消息插入 1000 多个记录，而且当该用户对该组的成员进行任何更改时，我们也必须更新大量的记录。
我想知道是否有更好的方法来构建我们的数据库以提高性能？
I'm writing an application which has certain features that are similar to Google Circle/FB friend list.
- A user can put people they know into group (family, coworker, friends, etc...)
(for now groups cannot be nested) - A user can send messages to group(s), set privacy settings per group, etc
- When a post is shared within a group, the users of those groups can comment and see the others' comment regardless of the relationship they have with the others (within that group)
For now, we are using relational database (mysql) because of some limitation with time and resource. In any case, I'm trying to find the best way to structure our database to balance performance and clarity. Here is what we currently have:
users:
user_id
default_group_id
friend_group_id
groups:
group_id
groups_to_users:
user_id
group_id
messages:
message_id
messages_to_groups:
message_id
group_id
galleries_to_groups:
gallery_id
group_id
When a user is first created, he/she will have 2 base groups:
- the default group which will contain only that single user
- the friend group which will contain everyone he/she is friend with
We will simply use group_id to determine the "permission" instead of using user_id. That way we can skip the complexity of querying 2 tables.
At the same time, with the structure above we also hit a roadblock with querying all the messages a user received since we may have to query at least 100 groups if this user has 100 friends. So right now we get around this by this rather hacky method:
if a user sends a message to a group, then we go through the list of member in this group and save a record for each user (message_id, (default_)group_id). The problem is that, if this group has 1000+ members then we will have to insert 1000+ records for each new message sent to this group, and also when this user makes any change to the member of the group we will also have to update a massive number of records.
I wonder if there is any better way to structure our database to improve performance?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您的“黑客方法”违背了建立群组的目的，因为您实际上是在编写指向个人的链接，而不是使用他们的群组成员身份来合理化您的交易（即消息）。如果您关心的是性能，那么您可能不会通过将写入乘以 100 或 1000 倍来获得巨大的读取提升。
我认为您应该坚持原始设计并确保您的表已正确索引，以便DBMS 可以完成其设计目的 - 快速有效地连接数据集。
如果您将表设计为具有正确类型的主键和外键，并且如果您设计查询以便它们利用 PK/FK 索引，那么这就是您优化性能的方式。
Your "hacky method" defeats the purpose of having groups since you are in effect writing links to individuals instead of using their group memberships to rationalize your transactions (i.e. messages). If your concern is performance then you are probably not going to get a huge boost for your reads by multiplying your writes by a factor of 100 or 1000.
I think you should stick with the original design and make sure that your tables are properly indexed so that the DBMS can do what it is built to do - which is join data sets quickly and efficiently.
If you design your tables to have the right kind of primary and foreign keys and if you design your queries so that they take advantage of the PK/FK indexes then that is how you will optimize your performance.
树结构适合表示这种分层数据
例如uid1 ; <消息>msgid2 <用户>uid2 <组>groupid1 ; <成员>uid1 <组>groupid2 ; <成员>uid1 <成员>uid2 <消息>msgid1
{
<用户>
}
因此，数据模型可以灵活地找到发送
Tree structure is the suitable fit to represent this kind of hierarchical data
For example
{
<user> <guid>uid1</guid> <message>msgid2</message> </user> <user> <guid>uid2</guid> </user> <group> <guid>groupid1</guid> <member>uid1</member> </group> <group> <guid>groupid2</guid> <member>uid1</member> <member>uid2</member> <message>msgid1</message> </group>
}
So that the data model can be flexible to find