Data aggregation - multiple websites, single superuser site

Question

I have a requirement for a set of asp.net MVC websites as follows:

1. Multiple sites, using the same codebase, but each site will have a separate database (this is a requirement), and users will login and enter data.

2. A single site for super users where they log in and work on data aggregated from each of the individual sites.

The number of sites in point one is liable to expand as we roll it out to more clients.

My question is about the architecture of the above - how to manage the data aggregation, given that it needs to be real time. Do we maintain this at the database level (e.g. a view that is essentially a union across the individual site databases), or at the application level.

A few infrastructure points:

1. We have complete control over the database server and naming of databases.

2. All these websites are deployed onto a server that we manage.

I'd appreciate any input/ideas from folks that may have done this before.

Answer 1

Does the data aggregation have to be completely real-time, or can you get away with almost real-time? If "almost real-time" is acceptable then you can write a service application that harvests the data from the sites databases into your single central database. As long as the process runs continuously and you don't have too many sites to gather data from the delay should be more or less invisible for the user.

Having a view that accumulates the data from all the databases doesn't sound like a good solution. Not only will it probably be very slow, but you will also have to update the view whenever you add a new site.

What is the intention of the super user site, btw? Is it only for reporting or should super users edit the data across all sites as well? That may affect which solution you choose.