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

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.