nosql example, which engine ?

Question

a) I have 1000000 domain names

b) Every domain has about 100000 sites

c) each site has about 10000 visits daily / (5000 unique visits daily)

d) As the owner of all those websites, I want to see, how many visitors on selected sites I had in a selected periods of time, for example:

How many unique visitor were from 4th December 1987 to 23 April 2010 on mydomain.com/tutorials

How many unique visitor were from 30 August 1996 to 16 July 2009 on yourdomain.com/reference ?

For a traditional SQL database this is a pain.

What is the smartest approach ? What storage engine to use ?

I have only SQL knowledge. Any additional resources greatly appreciated.

Answer 1

I think DB is the best approach for this option You just have to cr4eate a couple of tables and spread the data between them, e.g.:

Table: Domains [id, name]
Table: Sites [id, domain_id, name]
Table: Visits [id, site_id, date]

so you can select let say:

SELECT COUNT(v.id) 
FROM Visits AS v 
RIGHT JOIN Sites AS s
ON v.site_id = s.id
RIGHT JOIN Domains AS d
ON s.domain_id = d.id
WHERE d.name = 'mydomain.com' 
      AND s.name = 'tutorials' 
      AND v.date BETWEEN startDate AND endDate 

startDate and endDate should be passed through programming language(PHP, ASP) or they can be set manually in the SELECT

Hope that helps.

Answer 2

With the numbers and potential queries similar to those that you have listed I would very much dobut that a simple SQL (PSQL/TSQL) database would meet your needs. Instead you'll need some form of OLAP processing like SSAS (SQL Server Analysis Services) or an similar offering from Oracle.