Databases
StackOverflow2013
Postsdbo
POImplementing visitors statistics for many users
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POImplementing visitors statistics for many users
    text
    copied!<p>I'm facing a challenge and I need your opinion, let me explain:</p> <p>I have a database of around 300 000 users, which all have a profile page, and I would like to store the amounts of visitors that visit their profile on a weekly ( or daily?) basis for reporting purpose (graph would be available on their admin page).</p> <p>I'm thinking about doing so in a dedicated table (let's call it "stat") organised as follows:</p> <ul> <li>id / integer (id of users -- unique)</li> <li>current_ip / text (serialized array of ip of visitors of the current period)</li> <li>statistics / text (serialized array of statistics per period)</li> </ul> <p>I'm thinking about an AJAX request on the profile page that would filter only non-robot user, check if the ip exist in the ´current_ip´ table (with a LIKE request) and if it doesn't exist I would unserialize the ´current_ip´, push the ip of the new visitor, serialize the ip and UPDATE the table.</p> <p>At the end of each period (so every week or every day) I'm thinking about a cron task counting the number of ip un the 'current_ip', push that number (with the date) in the 'statistic' value (using the same method than previously explained), and then delete the ´curent_ip´ value so it´s empty for the next period.</p> <p>Btw I'm using php5 and PostgreSQL (9.1) with an i5 (4 x 3.2 Ghz) in an ubuntu 12.04LTS dedicated server with SSD and 16g RAM.</p> <p>Is that the best, easiest or fastest way of doing it? Am I all wrong?! Should I use 1 line per period instead of using a serialized array to store historical values?!</p> <p>Any suggestion is welcome =)</p> <p>Cheers</p> <p>Geoffrey</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload