Statistics – Simple Logging Design

by: Caleb Jonasson - August 4th, 2011 2 Comments

Introduction

This article is the first in a series focussed on Statistics, Logging and Graphing web application events and information

When creating a web application it is important to keep track of everything a user does. Some people may think that this is a little over the top but the more information we can gather within a web application the better. It also allows simple and easy moderation practices for those that are moderators within the application.

Pre-Planning

This is by far the most important stage in development. During this stage you are going to need to create proper data structures that in theory will not have to be change (although this is extremely rare.) This means that you are going to create the general database that will handle most of the current up to date information and another section that will be used for logging.

Within the main database we are going to have simple things like a user table, a user settings table, a user profile table and a user information table. On tables that hold general information it is important to add a time stamp column that holds the value of last modified. The user should have a column that tells us when the users profile was created. These values aren’t going to really help us when gathering information for a single user simply because the sample of data that we can compare it to is so small. Fortunately these simple values are excellent when we need to pull up values quickly in our system to display user information.

Single User Sample

The logging database is going to help us create a large sample of a single user. This is where we are going to constantly insert information and never delete or modify the records. In one of these tables we can store required values that will tell us when a user has had one of their comments deleted by a moderator. Within this table we are going to log the moderator’s id, the user’s id, the time stamp that this action was performed on. and a numeric value that reflects on the reason that the comment was removed. With this data we can then count the comments that are still alive and well and count the comments that were deleted. With this information we can divide the comments that are still alive and well by the comments that were deleted thus giving us a ratio that we can work with.

Note: It is a good idea to keep track of numbers like this as well and when the ratio was calculated. This will allow us to track user behaviour.

Multiple User Sample

Given that we could have a ratio of how well the user follows rules when it comes to commenting on things and taking part in discussion we filter out the users that are not contributing to the community/application. We can also sort users by this ratio and from there bury into their statistics, activities and logs.

Because we have logged all of the individual comments from this user we can closely examine the user interactions by graphing comments, removed comment, comments removed for x

Tags: application design, logging, samples, statistics

About: Caleb Jonasson

I am a web application developer currently spending my days coding at work, completing contracts and running around with my Nikon. This is my primary place for updates and everything code, technology and database related.

http://girasquid.com Luke

Another way to do this would be to fire up something schema-less (I like MongoDB), and then start tracking users as objects – everything logged would just get added onto the user. Structure would look something like this:

http://pastie.org/private/j1cxxxoln8cjbemdkwfrqg

You’d want to key your users off of something that let you find them in the database, and you might need to clear your logs every so often – but you end up with something that you can query for things like “every action that occurred on July 10th”, or “What did Luke do over his entire lifespan as a user?”. And because it’s schema-less, you don’t have to worry about having a whole crapton of nullable varchars for all the columns you added for edge cases – just tie it on and go.
http://codewithdesign.com Caleb Jonasson

It seems like an excellent way to store information on users when working with storing behaviour but my only question is performance. I feel as though a fixed table and a sql script that doesn’t need to perform checks on null values will be much faster.(although then there is the case of crons. At least reduce server load.)
It’s an interesting concept and I would like to use it to handle notification and other data sources that are going to have flexible data. When it comes to simple logging I do like to keep the size of these things strict to keep loads to a minimal.

Code With Design