CODEWITHSUNDEEP
phpmysql
nikmav
nikmav

Reputation: 375

What is the best way to store user notifications in MySQL?

I am building a noticeboard module in a php/mysql app. The idea is an admin can post notices to a single user, groups of users (by role), perhaps combinations of users/groups/roles, everyone in the system.

My initial thoughts were to have the following schema which solves my send to a single user or single user role:

notices
----------------
notice_id
notice_date
notice_title
notice_text
notice_recipients  <-- linked to user_role_id (from user_roles table)

I'm not sure how to record 2 groups and/or 50 individual recipients without having duplicated rows in the database. Is it possible to store all the user_role_id's in the one notice_recipients field and then somehow read the contents of that field to display in each individual users noticeboard?

Upvotes: 1

Views: 1196

Answers (1)

Eugen Rieck
Eugen Rieck

Reputation: 65274

You need a join table: 

notices
----------------
notice_id
notice_date
notice_title
notice_text

and 

notices_users
----------------
notice_id
user_id

When you create a notice, write it to the notces table. Then for every user, that should receive it, add a row to notices_users. If you have shown it, either delete it from notices_users or add a status field.

Upvotes: 5

Related Questions