Structuring database for multi-object "activity" and "following" functionalities
- by romaninsh
I am working on a web application which operate with different types of objects such as user, profiles, pages etc. All objects have unique object_id.
When objects interact it may produce "activity", such as user posting on the page or profile. Activity may be related to multiple objects through their object_id.
Users may also follow "objects" and they need to be able to see stream of relevant activity.
Could you provide me with some data structure suggestions which would be efficient and scalable? My goal is to show activity limited to the objects which user is following I am not limited by relational databases.
Update
As I'm getting advices on ORM and how index things, I'd like to again, stress my question. According to my current design model the database structure looks like this:
As you can see - it's quite easy to implement database like that. Activity and Follower tables do contain much larger amount of records than the upper level but it's tolerable.
But when it comes for me to create a "timeline" table, it becomes a nightmare. For every user I need to reference all the object activities which he follows. In terms of records it easily gets out of control.
Please suggest me how to change this structure to avoid timeline creation and also be abel to quickly retrieve activity for any given user. Thanks.
