.Darkman Posted October 31, 2007 Share Posted October 31, 2007 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, Quote Link to comment Share on other sites More sharing options...
aschk Posted October 31, 2007 Share Posted October 31, 2007 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. Quote Link to comment Share on other sites More sharing options...
.Darkman Posted October 31, 2007 Author Share Posted October 31, 2007 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 ? Quote Link to comment Share on other sites More sharing options...
aschk Posted October 31, 2007 Share Posted October 31, 2007 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. Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted October 31, 2007 Share Posted October 31, 2007 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. Quote Link to comment Share on other sites More sharing options...
.Darkman Posted November 1, 2007 Author Share Posted November 1, 2007 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, Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.