I work on a message system. in this message system admin can send messages to single user, user group or all users and users just can see messages. know i want to find unread messages in user panel. i designed two database table like this :
1- msg_messages :| id | title | messages | date | status |
2- msg_control :| id | message_id | from | toUser | toGroup | status |
Know i think about this two methods of saving users who saw the messages.
Method 1 : create a string with readers id Like this : 1,5,9,12,... and check users id with this string by Strpos php function or in_array function.
Method 2 : create a new table like below table and save readers id in that :| id | message_id | readers_id | date |
which one of this two method is better ?
after think more about this problem i decided to use a new method by combining method 1 and method 2. actually i added a new column to user table and named it read_msg . system will save read message_id in this filed in a string like this 1,5,9,12,98,125,... for each user (message_id in this filed refer to msg_control table ) and when we want to find unread messages just need to compare msg_control ids with this filed.
we use strpos to compare read_msg and message_id because its faster than is_array (reference).
I once implemented a small messaging system and structured it as follows (however it might not be perfect and there are multiple possibilities):
Tab Conversation
ID | Conv Title | ... master data
Participants
UserId | ConversationID | lastRead
MessageHeaders
ConversationID | messageID | ... use your "inbox"-schema here. You can remove the toUser/toGroup etc. fields as this is covered using the participants table.
Using the timestamps of a message and the "last-read"-timestamp of a user for a conversation you can determine which messages are new/unread and which are not. Using constructs like in your first suggestion can become very inefficient. Storing data atomar is considered good practice in my opinion. This means no multiple values in a single field.