All,

I have an application A (Standard Version) that is deployed locally at different facilities. Also there is an application B (Enterprise Version) which should be able to access data from application A database and produce a consolidated report. The data volume in every facility is extremely high close to about 50,000 messages/day.

I am thinking of having a single database which will use a centralized database for all installation for of standard version with a specific facility id. This will help in generating faster enterprise reports. Alternatively each facility could have a local database and the enterprise application could collect the data from each facility to generate reports. But I am not really sure if it is an effective design consideration.

Any thoughts or ideas could really help me in designing the application.

Thanks
Samanth

Before I start I should say that 50k records a day is not a lot (a previous system took 250m records a day) so a global system is quite straight forward. Advantages and disadvantages of your options are:

Lots of separate local databases:

• Pros
  • Users feel in control of their data (though if you're the one looking after lots of separate databases, each subtly different, then you'll probably feel out of control).
  • Faster response times
  • Databases can be altered to add features useful to that locality
• Cons
  • Difficult to produce global reporting
  • Much more code to maintain
  • Each system may be running different versions of your application which makes it more difficult to maintain and support
  • Expensive as you'll need more servers, more software (ie multiple database licences) and more staff to maintain the systems.

Separate databases with additional global database:

• Pros
  • As above but you can now do the global reporting
• Cons
  • All the above cons but it now needs even more code and yet another database.

Global database to service all users:

• Pros
  • Global reporting is easy
  • Easier to maintain as just one set of code with no different versions
  • Cheaper as less hardware (servers), fewer database licenses required and fewer people required to keep it running
  • Everyone is on the same version
  • If you add a new report or screen then it will be a benifit everyone immediately rather than need re-implementing on all local systems.
  • No installing of software to set up a new group
• Cons
  • If it's a truely global system then it will be used 24 hours a day with little downtime
  • You have to be a bit more carefull with backups (perhaps providing a warm standby) as a loss of a single database will now stop everyone
  • You have to be carefull that users can only see the data they are permitted to see
  • Users may feel their data belongs to someone else rather than to them

Others will probably come up with lots more opinions but personaly I'd go for the global system any day. If you can provide a web interface then life gets very easy and users can access their data from anywhere.

Mike

if you are concerned about reporting on large volumes of data you may want one database optimized for data entry and one database optimized for reporting.