Collecting Statistics

[.Darkman]

Hello everybody,

 

I'm writing a banner rotation script. I wish to have a simple statistics feature in it.

It will not be doing much. It will just count the number of times each banner has been displayed. Also, i'd like to present the data datewise.

 

That is, if i choose, "Today", i'd like to see how many times each of the banners has been shown on that day. If i choose "Last Month", i'd like the corresponding data to be showed.

 

Since, i've not worked with stats, i'd need the advice on someone in how to structure the database.

 

What i planned is : For every time the banner is displayed, a new record would be created in a table along with the timestamp and the banner's ID. Then, i'll use this data to display data the way i wanted. But, is this the best way to do it ?

 

Or, are there better ways ?

 

Please help me

 

 

Thanks,

[aschk]

I'm assuming you'll have two tables.

One for storing the banner information (id,name)

And another for updating the display times (banner_id, timestamp)

Thus giving yourself a one->many relationship.

 

Seems pretty sensible to me.

You could even create some denormalised data and have a total_count column in your banner table to give easy access to a total for each banner.

[.Darkman]

Yes, i'll be having two tables.

So, this is the best way. Right ?

 

And, if i have about 5000 pageviews perday, that would mean 5000 new DB entries per day. Wouldn't that harm the database ?

 

 

[aschk]

Nah, insert another line. Easy peasy. Barely any time at all. Restructuring indexes might take the time though. That is if you're indexing anything. I doubt it as you're using a foreign key anyway.

The database will grow in size however, so you might look at clipping information after a certain period ( say 1 month?), and storing it elsewhere.

[GingerRobot]

Well, if you dont need detailed information about the time the banner was used, then you might not need a new row for each view. For example, if you're only interested in statistics by day, then you could store a count of the number of times that banner was viewed on each day in the database. Your 3 columns would be: banner_id,date,views

 

Of course, that would require a check to see if a row exists for the particular banner id on the particular day, and if it does, update the existing row. If it doesn't, create a new row with a view of 1.

 

 

[.Darkman]

Well, if you dont need detailed information about the time the banner was used, then you might not need a new row for each view. For example, if you're only interested in statistics by day, then you could store a count of the number of times that banner was viewed on each day in the database. Your 3 columns would be: banner_id,date,views

 

Of course, that would require a check to see if a row exists for the particular banner id on the particular day, and if it does, update the existing row. If it doesn't, create a new row with a view of 1.

Ah ! Yes, that seems more logical. I just need number of view of the banner on that day.

 

The database will grow in size however, so you might look at clipping information after a certain period ( say 1 month?), and storing it elsewhere.

Oh ! How do i do that ? I just copy the information to another place ? Or should i move it ?

 

 

Thanks,