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?

  • Scalable to what? To serve a single hospital to serve a group of hospitals? You may also ask such questions on healthcareit.stackexchange.com/questions which is currently in public beta.
    – bernd_k
    Nov 27, 2011 at 9:35
  • Is pureXML a key feature you intend to use or is it just something that DB2 provides?
    – bernd_k
    Nov 27, 2011 at 10:03
  • yes... we have to include pureXML
    – nkvp
    Dec 9, 2011 at 15:19
  • One can't answer that question with only the information of the opening post. I personally am in favor of data integrity, at cost of performance, so that would lead to one big database. But, that is my preference, and it doesn't mean that would be the best solution for your problem. The type of question you are asking, is not a question to ask along the way. It can take several people a lot of time, to come to any conclusion. Including detailing into all scenario's and architectures.
    – tvCa
    Jan 18, 2015 at 15:45

3 Answers 3


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.

  • Which one do you think will be more efficient?? I thought it would be better to create a single database and then change it later once the database grows...
    – nkvp
    Dec 9, 2011 at 15:17
  • Efficiency (i.e. performance) wouldn't be my primary concern here, I'd be focusing on loose coupling between systems. Dec 9, 2011 at 15:35
  • What if it were just a project... which one would be better??
    – nkvp
    Dec 13, 2011 at 1:58

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/

  • this comes as part of a project, and table sizes will be small at the start
    – nkvp
    Nov 27, 2011 at 8:04
  • All tables are small in the beginning. A more interesting question would be : how much data will you think you have after 5 years ? And after 10 years ?
    – tvCa
    Jan 18, 2015 at 15:46

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 
  DATABASE1.dbo.tABLE2 f1 
  inner join DATABASE2.dbo.table2 f2
    on f1.fkcol = f2.origcol
  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.

  • 1
    "It depends" often is the only correct answer .. That means, if you ask a general question. It's a bit like : what car is best for me ? But, without stating anything about what I like.
    – tvCa
    Jan 18, 2015 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.