Attention Physician Developers

Having studied and going through dozens of EMR demos for the past 8 years, I have become disappointed with what’s available.  Although I’ve played with computers for 20+ years and always wanted to learn software development, only recently have I been able to find the time to do so.  Approximately 6 months ago, I invested in several training books and videos on SQL Server, Visual Studio 2008, and general database design.  Of course, the internet has been invaluable.   I’ve started an EMR database in SQL Server 2005 which houses about 40 tables. 

 

EMRs present some unique challenges in terms of design and it’s difficult to communicate the requirements to non-physician developers.  I’m curious to know how the physician developers here approached database design.  For example, how did you structure tables and their respective data types for HPI, ROS, and PE data?  How did you create and store templates in the database table structure? 

 

SQL Server 2005 allows one to store xml data types.  Would you find this useful in terms of allowing one to store the aforementioned items or would traditional tables in a relational manner be preferable?  (I see no situation where I would want to create a database query looking for items found in the HPI, ROS, or PE data, for example).   

 

Other items:

 

Where can I find ERDs showing how you house this data?

 

What is your preferred database and why?

 

Where did you guys get your ICD9, CPT, HCPCs, and drug tables (preferably free of course)?   

 

Any other tips would be appreciated.  Thanks.

Where did you guys get your ICD9, CPT, HCPCs, and drug tables (preferably free of course)?  

 

The codes can be downloaded from the CMS site, updated every quarter in an MS Access data base, may be excel now, but usually MS Access.

 

You can also get the CPT codes out of there if you know what you are doing with the tables for your own use, as any EMR Vendor has to pay copyrights on the CPT codes to include a full set.  One pays for part of these when they purchase an EMR, built in to the overall cost.

 

Relational Data base my choice, but XML elements helpful, but everyone has their own opnion and it depends a lot on how you really want to design it.  Good luck as it will be a lot of work to complete an entire project!  Ask any of the vendors around here:)  I changed to do more consulting as writing and keeping up my project for one person became the EMR that was eating Orange County and I found out that there is daylight outside my office again when I stopped developing.

Hello ktdodson,

Have you looked into Open Source EMR systems that are currently available. Why invent the wheel from scratch when there is something is available in OPEN SOURCE format. What type of GUI would you be using? Would you be focusing on Client Server based or API systems or both?

Would you be considering integrating the EMR with Biliing and PM systems, and medical devices? How about RIS/PACS system integration.

There is also a project called Mirth which focuses on HL7 interface.

As far as database is concerned I would prefer MySQL database. The MySQL® database has become the world's most popular open source database because of its consistent fast performance, high reliability and ease of use. Plus the support is free and you dont have to pay for the license.

Most of the programmers prefer to use .Net or C#. Why particularly Visual Studio?

Hope this helps

Cheers

Tim

 

 

 

 

Very Gutsy KT !

 

 

Very Gutsy KT !

Yea; there's that fine line between bravery and stupidity. 

I considered open source but I really wanted to learn and understand database functionality.  Starting from scratch mandates this.  Most of it seems relatively straightforward but I have came across snags here and there as to how one structures the database for the elements noted above.  I do have some template data in .xml format and have a tool which will convert it to relational tables in sql but for some reason it will not run on my development machine.

My EMR will be clinical only.  I'm married to e-MDs for billing as my billing agency uses them.  At my stage, I think the HL7 interface is a bit too ambitious but certainly will be an ultimate goal.  I'll checkout the Mirth project.

 

Scoot on over to SynapseDirect.com and we'll help you hash out your plans.  Graham, of course, knows alot about all this stuff.

A friend of mine has spent $30,000 / 3 years on hiring a local guy to make the EMR he wants.  He's a happy guy.

My first devil's advocate suggestion for you is this: I doubt you have the time to do this.

Another idea might be: why not look into joining a project like PatientOS ?

I would love to hear how you plan to approach your EMR differently than other products and your project's GOALS.

 

A couple of years ago I supported MySQL (for a different open source project) but it was missing some features that other databases had that turned out to be very problematic.  Then things like tables being case sensitive on Linux but not on Windows threw additional wrenches into the works.  While MySQL is undoubtably the easiest database for a non-technical user to install you have to think about scalability, backups, maintenance, flexibility, admnistration tools etc.  Now since Sun is taking over MySQL and is it becoming more popular perhaps it is catching up.  But I have been extremely happy with PostgreSQL  - very similar to Oracle and in fact I like it more as it is easier to automate many things. SQL Server I always found painful to automate updating the schema as it didnt support same set of scripted commands that Oracle, PostgreSQL, MySQL supported.

One big concern for your database is how do you upgrade users in production when your database schema changes.  I have a nice open source program which determines the difference between the PostgreSQL new version and old version of the schema and generates the DDL addtions - sql for the tables, columns, indexes, constraints etc.

Make sure you keep a careful separation between your code (business logic) and your database schema.  It is given your database will evolve as you determine new relationships within your database - so prepare to have to add/remove/modify tables and make sure you can identify all the code that uses each table/column.  I use Hibernate, an ORM mapper which helps a lot.

Good luck.

Greg

http://www.patientos.org

 

I seem to recall reading somewhere that PostgreSQL was someone's Editor's choice.

Greg: I'd appreciate a link to that open source program mentioned above.

Hi Kirk:

Great to hear that you are back into programming! I think that I first met you in the PhysiciansOnline days, and you were the one to bring me here back in 2004. You eventually switched over to e-MDs... did you use the e-MDs EMR for some years?

You're doing right in getting into an SQL backend if you are starting out... at the end you'll have a lot of power. Cudos for your efforts! BTW, visit my site dedicated to EMR programming where I'll be discussing tips and tricks on programming. It's still heavily focused on DAO programming and a Jet 4 backend, but there are methods, s.a. presciptioin printing, PMS programming, doing a scheduler using the MS Access calendar applet, SOAP note tips and tricks. When the first ebook is finished and posted, I'll be doing another book on MS Access/Word ADO programming with an SQL backend EMR during 2009. There is so much stuff to disseminate, so little time! I do bring in some political stuff too- one can never attack CCHIT, e-prescribing and P4P enough!

It seems to be growing by leaps, and for October the number of visitors should be off the map. I didn't know that there were so many nerds out there:

Cheers,

Al

Yes Al; it was circa 2004 when I told you about EMRU and you spawned my interest in development.  I purchased a number of books on VB but at the time was migrating into private practice and could never find the time to really start.  Now I can usually free 3-4 hours per day.  The POL days were great as there was no censorship/bias.

Recall I tried e-MDs EMR and simply could not use it.  Problems:   TNTC.

I started with SQL as I knew many who said they started in Access and it was difficult to migrate.  Furthermore, I tooked into speed and SQL smokes.   Certainly though, it's probably overkill for a solo physician with 2-3 users at most and a portal for patients.  (In my Internal Medicine/Geriatric practice I suspect the portal will be used infrequently).

I will definately checkout your site.  The questions above still puzzle me:  how does one store SOAP data and template data in the DB?  With the former, I've been told it's not wise to simply have a varchar data type for an HPI column and fill it with text that discusses 3-5 individual problems. Essentially what I'd like to see is an ERD for these 2 items.

BTW, I have found a free drug DB if anyone is interested.

I'm going to plug away but have professionals oversee what I'm doing as the DB is obviously critical to this process.  

 

ktdodson:

I seem to recall reading somewhere that PostgreSQL was someone's Editor's choice.

Greg: I'd appreciate a link to that open source program mentioned above.

Sure here it is:

http://apgdiff.sourceforge.net/

 

 

ktdodson:

The questions above still puzzle me:  how does one store SOAP data and template data in the DB?  With the former, I've been told it's not wise to simply have a varchar data type for an HPI column and fill it with text that discusses 3-5 individual problems. Essentially what I'd like to see is an ERD for these 2 items.

Well varchar is not a good data type for what could be long text.  But the question is are you designing for just yourself - or for 10,000 future physicians? 

If and only if it is the latter then as soon as you add a COLUMN for S, O, A and P then you have (in my opinion) boxed yourself into a corner...  You need a more generic data model that can support SOME physicians needing a single word-like document which is free text and provide macros.  You also need to be able to support SOME physicians that want to click away with a pen on a touchscreen that may be lists/checkboxes for the most part but also text fields.  You also need to provide SOME forms suited to DNS... in other words you need to have a flexible data model and a flexible presentation layer that works for many different users.  And with that you begin to face the software engineering challenges of designing a solid architecutre.

Or you can keep it simple like many EMRs do and build it for like minded physicians.  I cannot do that because I dont think like a physician - and so I need to be able to create a flexible tool as possible that physicians can customized to their preference.

ktdodson:

BTW, I have found a free drug DB if anyone is interested.

RxNorm is the best I have found.  Though I am using Medispan for commercial use.

ktdodson:

I'm going to plug away but have professionals oversee what I'm doing as the DB is obviously critical to this process.  

The battle is often won and lost in the database design.

 

Caultonpos: Multi-modal input (macros, templates, voice, HR), at least to me, would seem vital to any EMR irrespective of the number of users as it offers the ability to rapidly speed documentation. This is perhaps my major motivation for this project as most EMRs tend to slow down physicians; I maintain that to be appealing and encourage widespread adoption they should make physicians more, not less, efficient. As I viewed dozens of demos over the years, I would usually ask the VSR to chart using a typical scenario we face and watch them fumble around going through dozens of screen changes, ultimately saying, "we can't do that" (or if they can it's a major work-around). I'll checkout RxNorm. Thanks.

Some thoughts.....

Once you have a data model, keep in mind that you will need a collaborative record in which any user can contribute to the chart at any time without lock-outs.  Also, once collaborative, you want each computer to reflect the changes made on another computer within seconds.  While that seems obvious, it's worth noting that the nurse may carry around her computer and you carry around yours.  The data she enters in hers should be reflected quickly on your computer.  You can't rely on using the same computer for the same pt.

Also, keep the formatting separate from the data.  That is,  you may wish to use bold, italics, underscore, different fonts, bullets, and indenting.  If you do, you also need to keep the data uncluttered with formatting commands.  You will find many needs for the data and to have it cluttered with formatting commands will be really disheartening.

You also want persistence.  That is, you want the controls, of whatever type, to repopulate in the way that you left them when you clicked on to another pt and then back to this pt.  Persistence is very important in the multi-tasking environment which we expect.  You may be documenting on John Doe, but a call comes in from Mary Smith while you receive a message re: Jerry Clark.  You want to change contexts very quickly from one pt to another and be able to continue where you left off with any of those pts.

Remember the audit trail in all of this.  While you may think you don't no stinken' audit trail, it becomes really important when  you need to do forensics.

When considering clinical content, a good concept, as I'm sure you've already discovered, it to provide the user a method of entering new clinical content easily and efficiently.  If a user wants to add a new listbox, or checkbox, edit control, draw-on image, number control, calendar, etc, you really need to make each simple to create and populate the database.

Also, keep in mind that any document that you create or include into your EMR, you'll want to be able to output that to a fax or PDF.  Otherwise, your data becomes stranded on an island.

I would seriously consider using GUIDs to provide a unique identifier to each record in your database, no matter the table.