CODEWITHSUNDEEP
mysqlsqldatabasedesign-patterns
busytraining
busytraining

Reputation: 773

Traffic exchange script database design suggestions

I want to design a traffic exchange script that counts incoming traffic and tries to return a n:m (1in:2out for example) ratio of traffic. In the database, I want to have the sites info (SITE), then track traffic by site and ip (DAILY-HITS), then I want to have the trade counted per hour or day - not sure yet (SITE-TOTAL-HITS). I would love any suggestions I can get for designing a well designed database for handling traffic trades.

Right now I'm worried about tracking the incoming hits, later worry about returning. So basically my real question is, how can I design a database that can be efficiently used for returning the perfect amount of traffic for say the last 24 hours. The problem I'm having is, when it comes to programming, I want to have the best designed database for getting information on the last 24 hours and making sure I stay within the n:m ration. Heres what I've designed so far for the database:

SITE (just basic info):

id,

url,

title,

description.

DAILY-HITS:

id,

site_id,

ip,

date (include time - will be broken down per hour),

amount (count how many hits from this ip for this hour).

SITE-TOTAL-HITS (probably be updated every hour via script - useful later for counting last 24 hours):

id,

site_id,

year,

month,

day,

hour.

Any tips, suggestions or point me in the right direction would be greatly appreciated. Thanks in advance.

Upvotes: 1

Views: 490

Answers (2)

user2986638
user2986638

Reputation: 1

You can use HitLeap to do that.

What is HitLeap? HitLeap is a Traffic Exchange, also known as an autohits service We help you increase your website hits, rankings (alexa, google) and more. Our affiliate program gives cash and traffic commissions of up to 50%. How does it work? After signing up, you will submit all the websites you want to send traffic to. Then you will earn free traffic by viewing other people's websites. Alternatively, you could buy a traffic package from us.

Go tho this link to sign up

Upvotes: 0

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

Here is my take on it for what it's worth:

Site:

Same as what you have

Traffic:

id,
site_id,
ip,
request_DateTimeStamp <- this is a date and time for the url request

no count here, just logging here is why: 

assume you do have a count column
- url is requested
- lock up a record in the database with the same date and hour and ip
- Found, update the account
- Not Found, Create a new record and set the count to 1

this is a long process for a table that will presumably be updated a lot, within an hour span multiple request from the same ip will try to update the same record and they will have to wait on each other.

take out that count column and your process is simply to log the incoming traffic by always creating new records.

as far as data analysis is concerned, you can do it in couple of ways:

you can try building cubes: http://datacharmer.blogspot.com/2010/01/multi-dimensional-cubes-in-mysql.html

or you could create information aggregation tables like site_total_hits and just update them using nightly jobs or is often as you wanna run them depending on how accurate they have to be.

these are my 2 cents :D

Upvotes: 1

Related Questions