Designing event notifications

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Designing event notifications

Post by superdezign »

I want to implement an "Event System." This is just what I like to call it. :P
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. :P

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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Designing event notifications

Post by josh »

I would serialize an object and store it in a blob column. Make it an object that is only used for this purpose ( should just have various public properties that are not likely to ever need to change).

There's also EAV. If you do database re factoring and the schema needs to change later it would not be such a big question either, a lot of people don't realize data can be changed a lot easier then it is to change the code itself.

For something like commenting though, that leaves behind a "surrogate" paper trail, why build an audit log though? Sounds like over-engineering. The comment rows themselves could suffice as a log, and you could implement soft delete / horizontal partitioning to deal with deleted comments you still need a history of.

Unless I'm misunderstanding this is as pointless as logging the fact that some logging occurred, the comments themselves already serve the purpose

Also event notifications means something TOTALLY different in programming. People generally call what you're doing an audit log or just a log.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: Designing event notifications

Post by superdezign »

My "event" system is basically a log, but I normally think of a log for a website as an error log for an administrative user. I normally implement one for logging login attempts, registration attempts, and authentication errors. The goal of the events system is something similar to Facebook's news feed. My dilemma is separating form from function and not actually saving pre-generated output into the database.

I looked into EAV, and I swear that I have done this before using the exact same names of "entity" and "attribute," but instead of "value," I called it "data." Except, instead of representing every data type as a string, different data types had their own table. I was making a system that allowed the administrator to "build" a database-driven system without every modifying the actual database. Why does it seem like every good idea is already taken? o.O Anyway, that sounds feasible. It is very flexible, but I didn't even consider it as an option before. Seems a bit like overkill. :P

And your suggestion about actually using the data as a log is pretty smooth. Instead of having the central events table point to different event objects, why not point to the actual object that the event represents? The event would be made up of a date of occurrence, and a pointer to the object that it represents.

Now, I'd like to know how to do this:
How should I point to one table from another for selection?

Should I save the name in the table and use that to select from another table (if that's possible)?

Code: Select all

CREATE TABLE `events` (`id` int, `table` varchar, `item_id` int);
CREATE TABLE `items` (`id` int, ...);
 
SELECT * FROM `events`
LEFT JOIN `events`.`table`
ON `events`.`table`.`id` = `events`.`id`
I have no idea how that would be done. :P

Or should I have a separate table (which seems even less possible)?

Code: Select all

CREATE TABLE `events` (`id` int, `table_id` int, `item_id` int);
CREATE TABLE `tables` (`id` int, `name` varchar);
CREATE TABLE `items` (`id` int, ...);
 
SELECT * FROM `events`
LEFT JOIN `tables`
ON `tables`.`id` = `events`.`table_id`
LEFT JOIN `tables`.`name`
ON `tables`.`name`.`id` = `events`.`item_id`
I also have no idea how this would be done either, but it's more flexible.

Or should I just handle it in PHP with separate queries for every event type (if I represent events as individual object instead of an event list)?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Designing event notifications

Post by josh »

superdezign wrote: And your suggestion about actually using the data as a log is pretty smooth. Instead of having the central events table point to different event objects, why not point to the actual object that the event represents? The event would be made up of a date of occurrence, and a pointer to the object that it represents.
Why bother?
Just do:

(SELECT 'comment', `date`, `user_id` FROM `comments` where 1 )
UNION
( SELECT 'photo', `date`, `user_id` FROM `photos` WHERE 1 )

etc..

Your idea to build a database system with EAV has been tried before. It leads to very slow balls of mud, EAV should be used carefully only where "joints" are needed in the schema for flexibility. Magento uses EAV for user definable attributes for instance, but VirtueMart did the same thing with 4 programmers instead of 50, and in 4 months instead of 4 years, simply by using DDL to modify tables that are prefixed with a string to show they were system generated.
Post Reply