Single versus Multiple databases

Question

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?

• bernd_k: 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: Is pureXML a key feature you intend to use or is it just something that DB2 provides?
• nkvp: yes... we have to include pureXML
• tvCa: 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.

Answer 1

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.

• nkvp: 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...
• Mark Storey-Smith: Efficiency (i.e. performance) wouldn't be my primary concern here, I'd be focusing on loose coupling between systems.
• nkvp: What if it were just a project... which one would be better??

Answer 2

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/

• nkvp: this comes as part of a project, and table sizes will be small at the start
• tvCa: 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 ?

Answer 3

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.

• tvCa: "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.