I'm developing an online community site that will feature a rich log and allow each user to see "notifications" something like facebook has. I am at a loss as to how to structure the database of log entries, and how to design the queries.
I think each news item (call it "event") should consist of a subject, an object, and a "verb", but the subject and object could be any items in the database: people, groups, blog entries, photos, and so on. I would enable this by giving every object in the database a unique ID and a record in an "objects" table, with a one-to-one link to the relevant table. So the tables would look like:
OBJECTS
objectID (int)
table (varchar)
EVENT
eventID (int)
subjectID (int)
objectID (int, can be null)
verb (varchar)
datetime
Example output:
joe / added as a friend / bob
sally / wrote on the wall of / you
mary / commented on / photo #123
bill / updated his profile
Does this sound right so far?
I want to go farther, though. I want to program it with a bit of sense. For example, if two of my friends added bob as a friend, i want to display:
joe and mary / added as a friend / bob
The same would go for the objects:
joe / commented on / photo #34, photo #56, and photo #78
Furthermore, all this data has to be stored in such a form that site administrators can use it to generate meaningful reports, for example to tell photo contributors which of their photos are most popular. Has anybody done a feature like this, and how did you do it?
