How to implement one time (per user) messages in a web app?

Question

When you want to alert a user of something once (one time notes about new features, upcoming events, special offers, etc.), what's the best way to do it?

I'm mainly concerned with the data representation, but if there are more issues to think about please point them out. This is my first time approaching this particular problem.

So my thoughts so far ...

You could have a users, a messages, and a seen/acknowledged messages table. When the user acknowledges the messages, we have a new entry in the seen table with a user id & message id pair.

However, the seen table will grow rapidly with the number of users and messages. At some point, this would become unwieldy (any insight when that would be for a single mysql db on a single server?).

Would it be better to just create 1 seen table per message and maybe end up with 20-30 such additional tables to start? Not really a problem. It just comes with the added nuisance of having to create a new table every time there is a new message (of course, that would be automated in the code - still a little more coding).

This is for a project that has 2-3K current users, but the hopes are to grow that to 10K over the next year, and of course, we're looking beyond that, too ...

Edit: I'm not enthusiastic about the currently top voted method at all. The proposal seems to be to prepopulate a messages table and delete messages as they are seen. This seems to be a lot more work. You not only have to add your entire user list each time you add a new message. You also have to add all the messages for a new user each time you add a new user - separate logic.

On top of that, the record of a message being "seen" is actually the absence of a record. That does not seem right. Plus, if you later decide to track when messages were seen with a simple time stamp. You've have to rewrite a lot of code and other code becomes unusable.

Lastly, could someone tell me why it's so absolutely horrible to add new tables to the database? Doesn't this happen all the time when a new feature is added? Take any CMS: Joomla or Wordpress for example. When you add new plugins, you are creating tables dynamically. So it has to be more nuanced and contextual than "don't do it". What are the pitfalls and what are the circumstances under which you don't do it or it's okay to do?

I can see that you might say: Be careful about creating new tables on a production servers. Make sure it's been well tested, but ultimately, you're just adding an empty table.

This may require and extended answer, so if any knows any articles, please post them.

Edit: Gabriel Sosa gave a nice flushed out example of his messages table, and I'll simply create a seen table similar to what I originally posted although with timestamp column too. Thanks!

Answer 1

You could have the unseen messages listed in a table, and once the message is displayed you delete that row from the table. And you could also delete rows after X weeks, perhaps, whether or not the users see those messages. That would keep the table from growing unbounded. I'm imagining tables like this:

messages
--------
type            PRIMARY KEY
text            TEXT

unseenMessages
--------------
id              PRIMARY KEY
messageType     FOREIGN KEY
user            FOREIGN KEY
expirationDate  DATE

This unseenMessages table would hold all of the messages in your system, once per message per user. When a user loads a page you check if they have any entries in this table. If so you display those messages and then delete them from the table. Think of it like a message "inbox".

Also, I would not do anything that involves dynamic table creation. You should never, ever* create tables on the fly. Ever.

^{* Except temporary tables, of course.}

All of your messages should be stored in one table, or at least a fixed number of predefined tables. It is a cardinal database sin to create tables on the fly. The same goes for adding and removing columns on the fly. You just don't change the database schema dynamically. You don't do that in polite society. If you think you have to, you haven't designed the database correctly.

The programming analogue is the eval() function: it's just one of those things that's almost never a good idea. And in all fairness, eval() is okay in certain situations. Creating tables on the fly never is.

Answer 2

How important are the messages? If they are truely just a view once "hey here's what's new", does it matter if you know if everyone sees them? (But maybe you want to measure effectiveness?) Why not just display for a week or month or whatever timeframe it is your average user logs in on. Any key site updates can be put on a separate change log page for those really interested. If there are special offers, then its just time limited and why wouldn't users want to be reminded of it each time they log in? Or you could have part of the home page (or a link) dedicated to what's new on the site and record in a cookie if the user has clicked it since it has last been updated, and if not, then highlight it for that user. Sorry for the rambley response.

Answer 3

You could have a users, a messages, and a seen/acknowledged messages table. When the user acknowledges the messages, we have a new entry in the seen table with a user id & message id pair.

That seems pretty reasonable.

However, the seen table will grow rapidly with the number of users and messages. At some point, this would become unwieldy (any insight when that would be for a single mysql db on a single server?).

Based on the number of users you're talking, I wouldn't bet on that being a concern unless you do a lot of messages often. If it makes sense to retire messages, you could have a disabled flag on messages and a background job to remove rows from the seen table (warehouse it!) that correspond to disabled messages.

Performance wise, a lot of it is riding on your server's specs and what else it is doing. You can get a lot of performance out of a well-indexed, simple table even with lots (100s of thousands+) of rows and some configuration tweaks.

John Kugelman's solution (1 entry per user per message) makes more sense if you want to control who sees messages on a person/group level.

The techniques aren't mutually exclusive either.

Update: Agree with no dynamic table creation. :)

Answer 4

Your volume is not intimidating for a modern RDBMS. Keep in mind that there are many 100's of MILLIONS of records sitting in Twitter's MySQL database and other SQL Server and Oracle databases.

I can see two ways to actually solve this

1. Set a distinct cookie for the particular message which is good if these messages area a rarity

2. Create a couple tables to hold the message details

   Messages - would hold the message definition including the message text (or HTML in your case?), as well a message status of active/inactive.

   User Messages - a cross reference table that includes a row if a user has viewed the message. When the user sees and acknowledges a message you could insert a row into this table.

   To determine whether a user should see the message or not, you would query this table and the active messages with the user's ID. If a result is returned, then you should bypass the message, otherwise display it..

I think this provides you an opportunity to scale well into the future since the "User Messages" tables would only be integer association keys between the User/Account table and the Message table. You could also log the user's disposition on the User Message table (acknowledged, viewed, bypassed, etc...)

Let me know if this isn't clear, and i can try to explain better or provide a diagram. I'm sure there are some other patterns for doing this as well. Bank of America flashes these things after logging into my online accounts about once every month or so.

Answer 5

this was my aproach:

CREATE TABLE IF NOT EXISTS `system_user_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `section` enum('home','account','all') NOT NULL DEFAULT 'home',
  `message` varchar(250) NOT NULL,
  `message_type` varchar(25) NOT NULL,
  `show` tinyint(4) NOT NULL DEFAULT '1',
  `allow_dismiss` tinyint(4) NOT NULL DEFAULT '1',
  `created_on` datetime NOT NULL,
  `dismissed_on` datetime DEFAULT NULL,
  `show_order` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx-user_id` (`user_id`),
  KEY `idx-section` (`section`),
  KEY `message_type` (`message_type`)
);

I added allow_dismiss because you may dont want allow the user to dismiss that message. In my case when some user's CC is about to expire we dont allow to dismiss and then the system remove the message once the user updates the CC information. By the other hand you may also want to show the message only on certain areas of your site.

I posted the sql because I think is clear in this way... I know there are lot improments to make over this schema but maybe give you an idea.