Single or multiple databases

Question

Hello everybody

I am working on service which tracks visitors of websites (website owner just puts some java script to his site... just like Google Analytic or Woopra). There can be websites with low but also heavy traffic (more than 10 000) visits per day. Also count of users of this service can grow to thousands. Database does not have very difficult structure (3 or 4 tables). I am using MSSQL 2008.

What would be the best solution for this system?

1. Single database system, one database for all users
2. Multiple database system, one database for each user
3. Multiple database system, one database for some range of users

So 1. is probably bad idea, I can't decide between 2. and 3.

Thanks for your answers

Rudy

Answer 1

Well, as long as there isn't some weird constraint on the number of databases, why not go with option 3?

Answer 2

Go with option 1.

Databases are meant to scale and manage large amounts of data. Trying to use several databases will just get things messy and add more code on your side. Just imagine having to create a database for each new user, then record which database is for which user...

Answer 3

I assume that you will have some sort of service interface that can be called over http and that through this interface statistics are persisted to the db(s).

I'd start with using a single database, which (by definition) will be a database capable serving multiple users. It might well be that this (simple) system performs well enough for your first 200 (300? 800? 1000?) users. And then you'll have a simple system that works well.

As you notice database payload increasing to a certain level (monitor it), you can slightly change you're service by serving sets of users to different db's. No need to change your db design, because it works for multiple users already. Only adjust the service app such that it chooses the right db based on the user.

You can choose sets of user that make business sense; for instance a set of 150 users of a single webhosting company, that pays you (or your customer) to gather statistics.

In this case, the single db makes it easier to group statistic over all users.

Answer 4

Have you thought of 'Cloud computing'. Here you don't have to worry about scalability at all.