在MySQL中存储用户通知的最佳方法是什么？

Question

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?

Answer 1

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.