CODEWITHSUNDEEP
databasedatabase-designormdjango-modelsrelational-database
Dor
Dor

Reputation: 902

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?

Upvotes: 0

Views: 103

Answers (1)

Benny Hill
Benny Hill

Reputation: 6240

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 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 |
| ........ | ........ |
+----------+----------+

A table for user information...

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

+----+----------+----------+-----+
| id | username | password | ... |
+----+----------+----------+-----+
|  1 | bob      | ******** | ... |
|  2 | mary     | ******** | ... |
|  3 | 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 |
| ....... | ........ |
+---------+----------+

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 |
| ....... | ........ |
+---------+----------+

Upvotes: 1

Related Questions