String templating technique

Question

I am working on a fitness app that is going to deliver notifications to the user. I intend using MySQL events to populate the necessary tables, and then using an Ajax timer or Websockets or whatnot to deliver them to the User FaceBook style.

The problem is that I am going to have a whole class of notifications eg. "Spongebob you have not taken a class in 2 weeks, stop slacking", and "You are xyz% away from reaching your goal weight, don't stop now!" etc etc.

I was thinking of a structure like this:

**Notification**
--Id
--Message

**UserNotification**
--Id
--UserId
--NotificationId
--ProcessedMessage
--Seen?

My question is, so I will have placeholders in the Notification message eg. "{{name}} you have reached your goal weight! Congratulations", but these placeholders will be different depending on the message.

I was thinking of "compiling" (via string replace) the messages with the MySQL event when I have all the relevant data on hand e.g. I will create an event for getting users who have not done any exercise in 3 weeks and so on, and then saving the value in UserNotification. This lets me update the message with time, whilst keeping history intact, but I lose the ability to fix spelling errors etc. in one place only and having it automatically propagating.

If I don't compile the message in the MySQL event, then I will have to do a custom columns solution in UserNotification so I can generate the message later.

Is there a better way to doing this?

Answer 1

Custom columns {{shudder}}.

There's not One True Way® that comes to mind, but what about one or more rows in the new UserNotificationMetadata table, which has a MetaKey VARCHAR(x) not null (with a small but practical "x") and primary key of (NotificationId,MetaKey) and a MetaValue TINYTEXT not null (or a comparably suitable data type)?

This is one of the relatively few cases where a sort of EAV-style structure is arguably not a case of urDoinItWrong™ since the data, here, isn't part of your core schema and there's (again, arguably) not a need to be able to impose constraints on the data... a number or date stored here is just a string of characters that's going into a message.

For each (yes, that is also a little bit procedural-sounding) row matching the UserNotification, take CONCAT('{{',MetaKey,'}}') and use REPLACE() to substitute it with MetaValue in the message.

I would also suggest that 'seen' should be a timestamp, rather than a boolean.