Page 1 of 1

Help with Database Design

Posted: Wed Feb 04, 2004 8:21 am
by JayBird
I am about to build an application to store search engine statistics for out clients sites but i am unsure on the best way to design my DB.

This is an example of the data that will be stored

Code: Select all

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?

Thanks

Mark

Posted: Wed Feb 04, 2004 8:34 am
by kettle_drum
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.

Posted: Wed Feb 04, 2004 8:47 am
by timvw
I'd start with the following fields

id
timestamp
First place rankings
Top 5
Top 10
Top 20
Top 30
Moved up
Moved down
Same
Total
Gain/Loss
Visability Percentage
Visability Score

All you have to do now is throwing out those fields that can be computed based on other field(s).

Posted: Wed Feb 04, 2004 8:51 am
by JayBird
timvw

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.

Mark

Posted: Wed Feb 04, 2004 9:07 am
by kettle_drum
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.

Posted: Wed Feb 04, 2004 4:50 pm
by timvw
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 ;)