Trying to conceptualize table design for Stats 1

[3dColor]

I have a stats page my users can go to so they can view how much traffic their listing has received.

Creating this for the total view stat is straight forward and I have that working. But I want to also add two more features to this:

1.	See the total views for just today
2.	See a history for the listing over the duration of the post (ie. 30 days).

I am at a lost how I would create a table or several tables to accomplish this. I am looking for some guidance to get my thinking about the best way to do this.

 

[r937]

what is a stat? what columns are in the table?

do you know about UNION?

r937.com | rudy.ca

 

[3dColor]

r937, I sorry I should have been more clear in my post.

Stats for the listing are how many times the page has been viewed (hits).

This is the code that i have now that works that just gives me the total number of times the listing has been viewed.

<cfquery name="UpdateHits" datasource="#DSN#">
  		UPDATE Traffic
		SET views = views + 1
	 	Where 	trafficId = '#URL.id#'
</cfquery>

But I would like to expand the Traffic table so I can extract history data.

Would I have to add to the Traffic table to include:

traficId | todayViews | now-1 | now-2 | now-3 | now-4 | now-5, etc.

Then do I each night run a cfm page at 1AM to move the "todayViews " to now-1 and so on so I know the history of each day?

 

[r937]

why not simply use an INSERT?

for each hit/view, insert the url, timestamp, IP, and any other data that's relevant

then you can pull stats, summaries, analyses, or details as desired

r937.com | rudy.ca

 

[3dColor]

Very interesting way to think of that r937.

But won't that table become huge in a short time, do I need to worry about performance issues because of the table's size?

 

[r937]

define "huge"

let's say each row takes 100 bytes (order of magnitude)

how long before you fill up 100 megabytes of data?

that's right, a million hits

the next consideration is, how quickly will you reach a million hits?

that's how often you need to plug in a new 100Meg disk drive



r937.com | rudy.ca

 

[3dColor]

So let's say I get really lucky with my site and I get 50K visitors a day and everyone looked at just one listing per visit that would be 18 million rows for that table for one year.

So 18 million rows would be 1.8Gig of DB storage I would need.

My hosting provider allows for 200Meg of DB storage. Of course if i am getting 50K a day i should be able to afford my own servers. But for right now I guess I can delete the rows associated with a listing that expires.

 

[r937]

yes, delete expired stuff, but only after archiving it



r937.com | rudy.ca

 

[3dColor]

Thanks r937.

On the subject of archiving, is there anyway to do automatic backups on a scheduled basis without having to manually export that data from the hosting server to my local machine?

I use MySQL-Front and I can't seem to figure out how to do this on a schedule.

 

[TonyGroves]

You would have to set up a cron/scheduled job which would execute a command to dump data to files on your machine. This would be typically done using the mysqldump program, assuming you have mysqldump on your local machine.