Jan-04 Feb-04
First place rankings 0 0
Top 5 5 0
Top 10 7 7
Top 20 20 12
Top 30 22 20
Moved up 0 4
Moved down 0 14
Same 22 3
Total 22 21
Gain/Loss 0 -10
Visability Percentage 5.18 4.67
Visability Score 337 286
That is just for one client, there is about 30 all together. Each month, new stats will be added.
This data will be pulled from the database and displayed in text and graph format.
How is the best way to design this DB? will i need more than one table etc etc?
Personally i would just store the possitions on the search engines and then create the stats from that, i.e.
search_engine
site
date
position
From that you can then work out everything i believe (althought i not sure how you calculated the Visability stuff)
By doing this you can also then allow clients to see the name of th search engines they are top of and after a few months you will be able to map trends and such of each search engines possition.
How would that work with 30 clients? Wouldn't your metthod need a table for each client? Also, each month, another column needs to be added in your method.
You also dont know month to month the position of the site in the search engines, therefore you cant work out if its moved up or down and your also at a loss to the exact position of the site.
You just need the 4 fields i suggested and you can work out everything you want, plus more.
just add a client_id then this way you can link to your clients.
Personally i only want to know about timestamps (in this case date is probably more appropriate).
If you use MONTH or YEAR function on that timestamp, you get the respective month and year. So you could throw in a GROUP BY clause to get what you need