Help with Database Design

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Help with Database Design

Post 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
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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).
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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 ;)
Post Reply