Single versus Multiple databases

I am working on a hospital management system using DB2 with pureXML.

Is better to use a single database for all the data (patient, doctor details, appointments, history, medicines, etc) or to use multiple databases in order to be scalable?

Designing a platform: one database or multiple databases? is relevant background reading for your question.

You’re possibly approaching this from the wrong angle. Is there really going to be a single, all mighty, all encompassing, one true system called “THE Hospital Management System”? Or will there be:

• Appointments Management System
• Patient Records Systems
• Medicines/pharmacology database (highly likely to be something bought in, rather than re-invented)
• … ?

I’m sure there are dozens of other applications to add the list. Each of these is a separate application and probably a separate database, all of which need to talk to each other via a defined interface. One big application with one big database is a fast road to chaos.

I would start with a single db, unless table sizes are really big right from the start. It seems your next step towards scaling would be vertical partitioning where you may want to move set of associated tables on different servers. You may increase the load handling by using slave servers. but if writes start to overwhelm master then you will need to take the bitter pill and look for horizontal sharding.

I would recommend reading these articles- 1)http://www.25hoursaday.com/weblog/2009/01/16/BuildingScalableDatabasesProsAndConsOfVariousDatabaseShardingSchemes.aspx
2)http://www.codefutures.com/database-sharding/
3)http://www.mysqlperformanceblog.com/2009/11/16/shard-early-shard-often/

The most irritanting and common answer on IT: it depends.

If most of data is used (shared) by all applications, a single database would simplify things.

BUT if the applications (or modules) share little relation which other (except for a few shared tables, like patients), makes sense separate things on different databases – or a different schema, as not all database servers support queries acessing different databases at the same time. Example of such query:

SELECT f1.col1, f2col2 
FROM 
  DATABASE1.dbo.tABLE2 f1 
  inner join DATABASE2.dbo.table2 f2
    on f1.fkcol = f2.origcol
where 
  f1.col1 = 'ALPHA'

This query is based on MS SQL Server. It support multidabases with ease.

On FirebirdSQL, you’d have to do the join between databases on your own using 2 connections – but transactions between database connections aren’t a problem in it.

If your database server support schemas, it can help since you won’t need to create different databases. In Oracle, you’d have no choice at all – since all table must be in the same database, you’d need to separate your data structures in schemas.

Don’t know DB2 at all, but if your system architecture does not share much of the tables between modules (or applications, or services, or whatever you call them) and it support multidatabases with ease – I’d go for it since will ease your live. But if you need integrity constraints (and DB2 doesn’t support it on multiple databases) on data between multiple modules (which would be on different databases if you choose that path), a single database would be appealing – unless you warrant that the consistency of data across the DBs on the application.