mysql - MessageBoardApp 的数据库结构

Question

好吧，我被告知我的最后一个问题太宽泛，所以这次我尝试更精确。

我需要一个留言板应用程序的数据库结构。我有两个想法，但不知道哪个更适合。还有比我的想法更好的另一种可能性吗？

有用户、消息和组。所有消息至少属于一个组，但可以属于多个组。用户订阅至少一个组，然后可以看到他们所属的所有组的所有消息。用户可以随意创建群组。

规模(理论上):用户:数百万消息:数十亿组:多于用户

我现在有两个想法:

想法1:

表用户:

• 身份证
• 所有个人信息...
• GroupsSubscribed(包含所有群组 ID 的字符串？)
• 上次更新(日期)

表消息:

• 身份证
• 图像URI
• 文字
• 创作者
• 日期
• 群组
• Answer_Messages_IDs(包含作为此消息答复的所有消息 ID 的字符串)

表组:

• 身份证
• 组名称
• 上次更新(日期)

想法:

消息获取:

1. 应用程序每 X 秒获取一次 Group-LastUpdate(数据库调用:Group)
2. 如果组-LastUpdate > 用户-LastUpdate ->选择组包含组和日期的所有消息 > LastUpdate(数据库调用:消息)

消息写入:

1. 应用写入属于多个组的消息
2. 将消息保存在消息表中(数据库调用)
3. 更新组表 LastUpdate(数据库调用)

-----------------

想法2:

表用户:

• 身份证
• 所有个人信息...
• GroupsSubscribed(包含所有群组 ID 的字符串？)
• NewMessages(带有 MessageID 的字符串？)

表消息:

• 身份证
• 图像URI
• 文字
• 创作者
• 日期
• 群组
• Answer_Messages_IDs(包含作为此消息答复的所有消息 ID 的字符串)

表组:

• 身份证
• 组名称
• UserID(包含所有用户 ID 的字符串)

想法:

消息获取:

1. 应用程序每 X 秒获取一次 User-NewMessages(数据库调用:Users)
2. 如果 User-NewMessages != ""->选择 ID 在 NewMessages 列表中的所有消息(数据库调用:消息)

消息写入:

1. 应用写入属于多个组的消息
2. 将消息保存在消息表中(数据库调用:消息)
3. 获取每个组的组 - 用户 ID(数据库调用:组)
4. 使用新消息 ID 更新每个用户(数据库调用:用户)

Answer 1

正如@Paul Spiegel 上面指出的那样，这是数据库规范化的一个练习。

您将创建如下内容:

Users

• UserID PK
• ImageURI
• ... personal user informational columns ...

Messages

• MessageID PK
• Text
• UserID FK -> Users(UserID) // Message Author (Creator)
• Date

Replies

• MessageID FK -> Messages(MessageID)
• ReplyID FK -> Messages(MessageID)
• PK (MessageID, ReplyID)

Groups

• GroupID PK
• Name
• Description
• UserID FK -> Users(UserID) // Group Moderator -- I'm just adding this one in for fun.

User_Groups

• UserID FK -> Users(UserID)
• GroupID FK -> Groups(GroupID)
• PK (UserID, GroupID)

Message_Groups

• MessageID FK -> Messages(MessageID)
• GroupID FK -> Groups(GroupID)
• PK (MessageID, GroupID)

我将 ImageID 从 Messages 移至 Users，假设它是用户头像。如果它确实是与消息相关的其他内容，则将其移回原处。

除了已包含的 PK 和 FK 之外，还存在三个应用程序完整性规则。

AIR #1 - Messages 中存在一行意味着 Message_Groups 中至少有一个匹配行。

AIR #2 - Users 中存在一行意味着 User_Groups 中至少有一个匹配行。

AIR #3 - 给定的 ReplyID 只能在回复中出现一次。这保留了邻接列表语义，防止广义的多对多关联并强制执行分层关联。

数据库连接逻辑和应用程序代码留给读者作为练习。