0

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 ?

A new way to find unread messages ...

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).

Improve this question
1
  • 1
    Either is fine it will be more performance based as your tables get larger but instead of using strpos or in_array in php you may want to use MySQLs NOT FIND_IN_SET(1, readers) if you are using a a set to find unread messages. Commented Sep 4, 2014 at 18:59

1 Answer 1

Reset to default
1

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.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.