The only thing closest to what I'm developing is Facebook's activities log for users. So I figured if I could ask about how FB might possibly implement such a feature at the database layer, then it might give me some insight on how to solve my own similar problem.
How would one design a schema in a MySQL database to hold all the activities of a specific user. In FB's case, you have many activities that a user might be able to do, like something, comment on something, add a friend, use an app, etc. All these activities have their own database schema, I'm sure, so I'm assuming that the activities log schema would need to reference these other data somehow.
So what I'm asking is for ideas. The only ideas I can come up with is to create an activities table and multiple "connecting tables", tables that reference the activities table and the activity data (e.g. comments on something, likes something, etc.). However, this schema would require a new table for every new kind of activity and new business logic to handle these new tables. Maybe one can't get around this, but I'm interested in a schema that would require very little updating to the schema and business logic when new kinds of activities are added to the system.
Just to be clear, this question isn't intended to sound like I'm just simply trying to build another Facebook clone. I only use FB as a reference to something similar to what I'm trying to do. To be more abstract about my question: I'm trying to aggregate various kinds of data in my database into a single schema that could be queried, and anytime there is a new kind of data, there should little if any schema changes, otherwise what's the point? FB has two instances of this kind of aggregation, activity logs and notifications (that I can think of).
Any help is appreciated.