Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from Nov 27 '11 at 6:54

This question came from our site for professional and enthusiast programmers.

Scalable to what? To serve a single hospital to serve a group of hospitals? You may also ask such questions on which is currently in public beta. – bernd_k Nov 27 '11 at 9:35
Is pureXML a key feature you intend to use or is it just something that DB2 provides? – bernd_k Nov 27 '11 at 10:03
yes... we have to include pureXML – nkvp Dec 9 '11 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 at 15:45

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer
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 '11 at 15:17
Efficiency (i.e. performance) wouldn't be my primary concern here, I'd be focusing on loose coupling between systems. – Mark Storey-Smith Dec 9 '11 at 15:35
What if it were just a project... which one would be better?? – nkvp Dec 13 '11 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) 2) 3)

share|improve this answer
this comes as part of a project, and table sizes will be small at the start – nkvp Nov 27 '11 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 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.

share|improve this answer
"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 at 15:47

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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