Should I use unique tables for every user?

Question

I'm working on an web app that collects traffic information for websites that use my service. Think google analytics but far more visual. I'm using SQL Server 2012 for the backbone of my app and am considering using MongoDB as the data gathering analytic side of the site.

If I have 100 users with an average of 20,000 hits a month on their site, that's 2,000,000 records in a single collection that will be getting queried.

• Should I use MongoDB to store this information (I'm new to it and new things are intimidating)?
• Should I dynamically create new collections/tables for every new user?

Thanks!

Answer 1

With MongoDB the collection (aka sql table) can get quite big without much issue. That is largely what it is designed for. The Mongo is part HuMONGOus (pretty clever eh). This is a great use for mongodb which is great at storing point in time information.

Options :

1. New Collection for each Client

very easy to do I use a GetCollectionSafe Method for this

public class MongoStuff

    private static MongoDatabase GetDatabase()
    {
        var databaseName = "dbName";
        var connectionString = "connStr";
        var client = new MongoClient(connectionString);
        var server = client.GetServer();
        return server.GetDatabase(databaseName);
    }

    public static MongoCollection<T> GetCollection<T>(string collectionName)
    {
        return GetDatabase().GetCollection<T>(collectionName);
    }



    public static MongoCollection<T> GetCollectionSafe<T>(string collectionName)
    {
        //var db = GetDatabase();
        var db = GetDatabase();
        if (!db.CollectionExists(collectionName)) {
            db.CreateCollection(collectionName);
        }
        return db.GetCollection<T>(collectionName);
    }
}

then you can call with :

var collection =  MongoStuff.GetCollectionSafe<Record>("ClientName");

Running this script

static void Main(string[] args)
{
    var times = new List<long>();
    for (int i = 0; i < 1000; i++)
    {
        Stopwatch watch = new Stopwatch();
        watch.Start();
        MongoStuff.GetCollectionSafe<Person>(String.Format("Mark{0:000}", i));
        watch.Stop();
        Console.WriteLine(watch.ElapsedMilliseconds);
        times.Add(watch.ElapsedMilliseconds);
    }
    Console.WriteLine(String.Format("Max : {0} \nMin : {1} \nAvg : {2}", times.Max(f=>f), times.Min(f=> f), times.Average(f=> f)));

    Console.ReadKey();
}

Gave me (on my laptop)

Max : 180 
Min : 1 
Avg : 6.635

Benefits :

• Ease of splitting data if one client needs to go on their own
• Might match your brain map of the problem

Cons :

• Almost impossible to do aggregate data over all collections
• Hard to find collections in Management studios (like robomongo)

2. One Large Collection

Use one collection for it all access it this way

var coll =  MongoStuff.GetCollection<Record>("Records");    

Put an index on the table (the index will make reads orders of magnitude quicker)

coll.EnsureIndex(new IndexKeysBuilder().Ascending("ClientId"));

needs to only be run once (per collection, per index )

Benefits :

• One Simple place to find data
• Aggregate over all clients possible
• More traditional Mongodb setup

Cons :

• All Clients Data is intermingled
• May not mentally map as well

Just as a reference the mongodb limits for sizes are here : [http://docs.mongodb.org/manual/reference/limits/][1]

3. Store only aggregated data

If you are never intending to break down to an individual record just save the aggregates themselves.

Page Loads : 
#    Page            Total Time      Average Time 
15   Default.html    1545            103