多种模型类型的数据库消息传递-用户，组，全局 [英] Database messaging for multiple model types - user, group, global

问题描述

我想在网站中实施一个警报消息系统，该系统将向多个用户，多个组(不同的数据库表示形式)或<对所有用户均是强"(全局).

I want to implement an alert messaging system, in a website, that will show a message to either multiple users, multiple groups (different DB representation), or globally to all users.

对于这种行为，良好的数据库关系会是什么样?

What would a good database relationship for this behavior look like?

推荐答案

这是我根据您提供的信息开始的地方...

This is where I would start based on the information you've given...

要发送警报消息的表...

A table for the alert messages to be sent out...

alerts
    id      unsigned int(P)
    message text

+----+---------------------+
| id | message             |
+----+---------------------+
|  1 | The sky is falling! |
|  2 | Some other message  |
| .. | ................... |
+----+---------------------+

用于保存有关所有组的信息的表...

A table to hold information about all the groups...

groups
    id          unsigned int(P)
    name        varchar(20)

+----+---------+
| id | name    |
+----+---------+
|  1 | Group A |
|  2 | Group B |
| .. | ....... |
+----+---------+

用于关联组和警报的表.每列都是其各自表的外键，它们共同构成一个主键-这将确保没有任何一个组与同一警报相关联.在我的示例数据中，我们看到A组已针对天塌下来！"进行了注册.和其他消息"消息，而B组仅注册了其他消息"警报.

A table to associate groups and alerts. Each column is a foreign key to it's respective table and together they form a primary key - this will make sure no group is every associated with the same alert more than once. In my example data we see that Group A is signed up for both "The sky is falling!" and "Some other message" messages while Group B is only signed up for "Some other message" alerts.

groups_alerts
    alert_id        unsigned int(F alerts.id)--\_(P)
    group_id        unsigned int(F groups.id)--/

+----------+----------+
| alert_id | group_id |
+----------+----------+
|        1 |        1 |
|        1 |        2 |
|        2 |        2 |
| ........ | ........ |
+----------+----------+

用户信息表...

users
    id              unsigned int(P)
    username        varchar(32)
    password        varbinary(255)
    ...

+----+----------+----------+-----+
| id | username | password | ... |
+----+----------+----------+-----+
|  1 | bob      | ******** | ... |
|  2 | mary     | ******** | ... |
|  3 | john     | ******** | ... |
| .. | ........ | ........ | ... |
+----+----------+----------+-----+

将用户与警报相关联的表.键遵循与 groups_alerts 表中相同的逻辑.在我的示例数据中，我们看到 john 已签署天塌下来！"消息.

A table that associates users with alerts. The keys follow the same logic as those in the groups_alerts table. In my example data we see that john is signed up for the "The sky is falling!" message.

users_alerts
    user_id     unsigned int(F users.id)---\_(P)
    alert_id    unsigned int(F alerts.id)--/

+---------+----------+
| user_id | alert_id |
+---------+----------+
|       3 |        1 |
| ....... | ........ |
+---------+----------+

最后，我们需要将用户与组相关联.键遵循与 groups_alerts 表中相同的逻辑.在我的示例数据中，我们看到 bob 是 Group A 的成员，而 mary 是 Group B 的成员

And finally we need to associate users with groups. The keys follow the same logic as those in the groups_alerts table. In my example data we see that bob is a member of Group A and mary is a member of Group B.

users_groups
    user_id     unsigned int(F users.id)
    group_id    unsigned int(F groups.id)

+---------+----------+
| user_id | group_id |
+---------+----------+
|       1 |        1 |
|       2 |        2 |
| ....... | ........ |
+---------+----------+

这篇关于多种模型类型的数据库消息传递-用户，组，全局的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！