Help with MySQL database structure - user notification system

Question

I'd like to send global notifications to my users (1000+ users) and allow them to close the notification box once they have read the message.

Basically I may send one notification per week globally ie/ each user get the same message and they are not personal in nature.

What is the best way to achieve this?

Create 2 tables:

**tb_messages**
message_id
massage_title
message_content

**tb_read_messages**
user_id
message_id
is-read

That way i can just show each user the current notifications that are not read?

select * from tb_read_messages WHERE user_id = $user_id AND is-read = no

OR is there a more efficient way?

Thanks!!!

That's what we do for our 10k+ users and it seems to work out just fine. This will allow you to create global or user specific alerts easily enough. — jasonbar
– jasonbar, Commented Jan 9, 2011 at 16:37
Do you need to know which messages are already read by your users or you just need to track the unread only? — Lachezar Balev
– Lachezar Balev, Commented Jan 9, 2011 at 16:49

Jaydee · Accepted Answer · 2011-01-09 16:47:55Z

2

I think your two table solution (really 3 with a table of user info) is the way to go. You are essentially creating a many to many link between Users and Messages with tb_read_messages, (though I would name this "user_message" or user_message_status as it is a link between a user and message that records the current status).

The only other though I would through in is to consider groups of users so that you can easily target specific groups of users

Users}-User_Groups-{Group_message_status}-Messages

answered Jan 9, 2011 at 16:47

Jaydee

4,1481 gold badge21 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lachezar Balev · Accepted Answer · 2011-01-09 16:56:33Z

0

Your solution seems fine. What you can further consider is that your
tb_read_messages table may bubble up quite a lot. You may think of some maintenance for it. Perhaps you can do that by archiving the messages already read in a separate table or just dropping them if you do not need keeping such history. Further you may want to get rid of messages queued for inactive users (you may do that with a timestamp attached to the message).

This are just general ideas which may be improved in the context of your actual product.

answered Jan 9, 2011 at 16:56

Lachezar Balev

12.1k9 gold badges57 silver badges77 bronze badges

Collectives™ on Stack Overflow

Help with MySQL database structure - user notification system

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related