Take the 2-minute tour ×
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 stackoverflow.com 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 healthcareit.stackexchange.com/questions 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
add comment

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
add comment

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/

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
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

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.