How to Store alert messages in MySQL

Question

I plan on creating a Facebook style alerts system and I am wonder what is the best way to store the alerts. What kind of table should I use?

This is what I was thinking:

   CREATE TABLE `alerts`(
   id INT NOT NULL PRIMARY KEY
   status enum('active','inactive','deleted') DEFAULT active,
   user_id INT NOT NULL,
   message VARCHAR 255 NOT NULL,
   );

Answer 1

Absent any more detail, I think the most we can say is: sure, that'll work.

There is no fundamental flaw with your table schema. Be sure to include an index on (user_id) or more likely on (user_id, status) (since you're likely to query only active alerts most of the time), and a foreign key from user_id to your user table.

And you might consider making message a TEXT field, unless you specifically want to limit it to 255 characters.