So I am creating the spec for the user tracking module for a social website. Just wanted feedback on the best approach to do this.
Currently I have something like this: First the Activity table gets populated with ActivityID and UserID (Activity is same as footprint).
Then the ActivityDetails is filled with the Activity details which has:
ObjectID (the object the activity occured on, such as Photo, Video, Status update, etc)
PageID (the page the user was on)
LocationID (optional, like the place on the page - like on the comment box, user wall, ad box, etc.)
OwnerID (the User ID of the owner of the Object)
ActionID (The action the user took such as "Share" "Post" "Add", etc)
UserID (the user the tracking is being done on)
For the above we are storing the Type of each from a reference table and the unique ID of the actual entity.
Now the open issue is this: First is the above complete or is something else missing? This is a social site being developed, like facebook but for different purposes. Second thing is, from my understanding there are two types of user activities possible:
A) Any activity by the user itself
B) Any activity other people take relating to the user.
For A, it has to flow out of the user session because obviously the user has to be logged in to perform any action on his account if it is to be tracked. For B, the user may or may not be online as other people may take actions like "View profile" "Send friend request" etc when the user is offline.
So for B, the above data looks fine. But for A, if session based then additional data needs to be recoded such as the session details (IP address, MAC address, Geolocation, Session start/end time, etc.) So I am thinking have a flag or reference set in the activity table if it is by user or by others. If by user then link all the activities with the parent session as all the activities will share the same session if they occurred within that session. So 1 session can have multiple Activities.
Then there is user notifications. So when an "Action" is entered, it triggers a notification event to notify the user of the action. Notifications on this site are sent via SMS, Email and on-site notifications depending on the user's preferences.
Is this a valid design keeping in mind scalability and trying to track user activities in detailed? In addition the users will be able to view this tracked data, and for the privacy concerned folks - even delete it from system after X amount of time.