database design for unread meassages in mysql?

Question

What would be the best way to check unread messages from mysql table...
I have two tables one for users and the other for messages... In the users table I am saving the last-login-date column & in messages table the date-added column, but what would be the best way to check whether the user has accessed the messages or not....
I am thinking of adding another update query to the select query for messages and add another column(read) to the messages table and set read to 'yes' or 'no'... any other suggestions?Thanks

Answer 1

Yes, a read column would be the best, logging in (time) does not mean the message was read. So an indicator to show read messages is the best way to go.

Edit:

You might want to expand the "status" idea to include thing such as read time, notifications, forwarded and replied.

Answer 2

I agree that you should add a column to indicate the message was read in the MESSAGES table. An option to consider would be the datatype - if you made it datetime, you'd know that it was read and when.

Answer 3

I agree with astander add the column 'read'. I also recommend that a tinyint for the datatype of the 'read' column (more broadly compatable than boolean, but still very small).