Designing event notifications
Posted: Sat Dec 12, 2009 7:13 pm
I want to implement an "Event System." This is just what I like to call it. 
Basically, it's a log of a user's activity on the website.
The way I'm thinking of implementing it is to give every subsystem of the main system the ability to generate an event using the event subsystem. The events would be physically represented as a message, which could optionally have links. So, for example, if a user makes a new post in a content management subsystem, the creation of the new content would generate an event that says "<user> has posted a new <item-type>, <content-title>." The underlined elements would be hyperlinks. Now, this is where I'm getting lost.
I could design the event subsystem to save the raw message, but then I lose the benefit of separating form from function. Plus, if the user's name changed, the content's title or link changed, etc., then I would be left with an outdated log message. A user link with one name pointing to a user with a different name is basically senseless.
What I want to do is make the main events table be a table of pointers, and each pointer points to it's event in another table. In order to do this, every subsystem would be able to get their own event table. But this is where I'm having problems. What if in a content management subsystem, I also have commenting? I'd want a message in the format "<user> has posted a comment on a <item-type>, <content-title>." The first message requires a user_id and a content_id. But the second one requires a user_id, a content_id, and a comment_id.
Not only that, but to have pointers to other tables would mean that I either use PHP to determine which events table to check, and then run a separate query for every event item, or find some way to get MySQL to dynamically call another table (hopefully without the use of a bunch of IF statements... I'd prefer not to have to hardcode the tables into the query).
tl;dr
How should I design an event logging subsystem where an event can be generated by any other subsystem, and still make the event messages dynamic?
Examples (underlined words = hyperlinks):
When you edit your profile, the profile system should create a public event stating: "<user> has updated their profile."
Yet, to you, the public event will state: "You have updated your profile."
When you comment on someone else's profile, the profile system should create a private event for the recipient stating: "<user> has left a comment on your profile."
Yet, to you, the private event will state: "You have left a comment on <recipient>'s profile."
I'm toying with the idea of a central events table with pointers (foreign keys) to the actual events in other tables, and with the idea of serializing the data and storing it in a "data" field. I'd like to avoid the latter solution, though, as it doesn't allow any table joins.
Basically, it's a log of a user's activity on the website.
The way I'm thinking of implementing it is to give every subsystem of the main system the ability to generate an event using the event subsystem. The events would be physically represented as a message, which could optionally have links. So, for example, if a user makes a new post in a content management subsystem, the creation of the new content would generate an event that says "<user> has posted a new <item-type>, <content-title>." The underlined elements would be hyperlinks. Now, this is where I'm getting lost.
I could design the event subsystem to save the raw message, but then I lose the benefit of separating form from function. Plus, if the user's name changed, the content's title or link changed, etc., then I would be left with an outdated log message. A user link with one name pointing to a user with a different name is basically senseless.
What I want to do is make the main events table be a table of pointers, and each pointer points to it's event in another table. In order to do this, every subsystem would be able to get their own event table. But this is where I'm having problems. What if in a content management subsystem, I also have commenting? I'd want a message in the format "<user> has posted a comment on a <item-type>, <content-title>." The first message requires a user_id and a content_id. But the second one requires a user_id, a content_id, and a comment_id.
Not only that, but to have pointers to other tables would mean that I either use PHP to determine which events table to check, and then run a separate query for every event item, or find some way to get MySQL to dynamically call another table (hopefully without the use of a bunch of IF statements... I'd prefer not to have to hardcode the tables into the query).
tl;dr
How should I design an event logging subsystem where an event can be generated by any other subsystem, and still make the event messages dynamic?
Examples (underlined words = hyperlinks):
When you edit your profile, the profile system should create a public event stating: "<user> has updated their profile."
Yet, to you, the public event will state: "You have updated your profile."
When you comment on someone else's profile, the profile system should create a private event for the recipient stating: "<user> has left a comment on your profile."
Yet, to you, the private event will state: "You have left a comment on <recipient>'s profile."
I'm toying with the idea of a central events table with pointers (foreign keys) to the actual events in other tables, and with the idea of serializing the data and storing it in a "data" field. I'd like to avoid the latter solution, though, as it doesn't allow any table joins.