"Alerts viewed" database design

[nloding]

I have an existing site that I want to display a maintenance warning message about across the top.  It just a simple div with some text culled from the database.  I want the users to have the ability to click the "X" on the side and close it out.  Here's the setup for the messages:

 

maint_msgs:
| msgid | msg | author | date_added | date_start | date_end |

 

The message is displayed if it falls between the 'date_start' and 'date_end' periods.  If the user clicks the "X" ... how do I record that?

 

maint_msgs_user_link:
| userid | msgid |

 

I realize this isn't real SQL or PHP, it's just short-handed:

 

$msgid = SELECT msgid FROM maint_msgs WHERE date_start <= NOW() and date_end >= NOW()

$test = SELECT * FROM maint_msgs_user_link WHERE msgid = '$msgid'

if (num_rows($test) >= 1) {
// do not display
} else {
// display
}

 

That seems really tedious to me.  But I can't think of another way to do it!  Is that the way to go?

[celsoendo]

If you want to store a "log" per user, yes, you must have another table.

 

I suggest that maint_msgs_user_link table has these columns:

 

msg_id, user_id, date

 

You can make msg_id and user_id a composite primary key.

 

When you store action logs is always a good practice to store the date and time that the event occurred. This is why I added the column date on your table.

 

Then you can retrieve the messages with this query:

 

SELECT 
    m.msg_id, 
    m.msg, 
    m.author, 
    m.date_added 
FROM 
    maint_msgs m 
WHERE 
    NOT EXISTS (SELECT 1 FROM maint_msgs_user_link WHERE msg_id = m.msg_id AND user_id = $userId) AND 
    CURDATE() BETWEEN m.date_start AND m.date_end;