need help with visitor statistics table structure

[scarhand]

i am writing a visitor statistics system to go with a script i am working on

 

the script will be using a transparent 1x1 gif (which is actually a php page) in order to track visitor date, ip, host, referer, country, browser, OS, and the page they have visited

 

i am going to be writing scripts for pages where the admin can view visitor statistics by ip, date, host, referer, country, etc. and will also show unique and total pageview counters.

 

i am wondering, should i just use 1 table for all of this and insert a new row each time a visitor comes to the page? or should i have a "hits" column and if the person is visiting the same page from the same IP just update that column?

 

heres my current table structure:

 

create table visits (

  id int unsigned not null auto_increment,

  page text not null,

  domain text not null,

  keyword text not null,

  visit_date int unsigned not null,

  ip text not null,

  host text not null,

  referrer text not null,

  country text not null,

  hits int unsigned not null,

  primary key (id)

);

 

any advice would be great.

[Daniel0]

ip should be an unsigned integer (use MySQL functions INET_ATON() and INET_NTOA(), or PHP functions ip2long and long2ip). visit_date should be DATE or DATETIME. All of those TEXTs would be better off as VARCHARs. referrer should not be set to NOT NULL seeing as it might be the case that there is no referrer, in which case NULL would be the most appropriate value.

 

Also, you'll not be able to track the referrer using that gif.