How to create a table structure?

Question

MySQL

MySQL database
I want to make notifications for users.
The simplest kind of table is:
id | message text | serialized array with user ids We
check if there are users in our array and if there are - we show them a message and remove them from this array so that the message is no longer shown.
Some messages can be shown to two users, some to 2 thousand.
Well, I sort through all the lines and look for IDs in arrays, I think very soon it will become slow.
So I ask for help who can do it) How to design something like this?

Answer 1

Make a table of read messages with the structure user_id & notification_id, PK for both fields.
Notification display:

select текст from уведомления left join прочитанные_уведомления on id_уведомления=id and user_id=? where user_id is null

And mark notifications as read by creating such records.
Or you can invert the logic - enter the id of unread notifications into the table and delete them when they are read.

Answer 2

messages:
+----+------+
| id | text |
+----+------+

user_messages:
+---------+------------+
| user_id | message_id |
+---------+------------+

Answer 3

Also, if you make user ids in the form of a serialized array with user ids, then in the event of a race condition , incorrect data will appear in the serialized array.
I also think that the option proposed by Melkij is the most suitable in your case.

Answer 4

you can not bother and make
user_id, message
Why do you need to bother with message id? The user either read all the notifications and clicked the “read” button (then we clear the message), or did not read anything. Someone selectively reads notifications and squeezes buttons?
But if you really want to get it right + messages addon and other troubles, then yes, you will have to make another sign.

Answer 5

Use a table of connections between notifications and users - a many-to-many relationship is obvious. Remove the link after showing.