Demographic Improvements, New Contact System

system · July 19, 2012, 4:01pm

deschel wrote on Thursday, July 19, 2012:

Overview

Storing and managing contact information is an important part of a medical practice. Most medical practices need to track contact information for patients, patient family members, specialists, durable medical equipment suppliers, pharmaceutical representatives, hospitals, home health agencies, etc.

Therefore, a robust contact system should thus be integral part of most EMRs.

The contact system currently in OpenEMR (and, for a matter of fact, most commercial EMRs) makes contact tracking fairly difficult and limiting. In a world where a person or business might have several phone numbers, email addresses, and physical locations, the need for a fully functioning and all encompassing contact system is important.

In the current database structure of OpenEMR: Phone numbers are stored in multiple tables, and even multiple times within the same table (no normalization). The same goes with addresses. Additionally, the address book stores information in the same table that OpenEMR user information/passwords are stored in (crazy, isn’t it?). Additionally, most contact info is stored in flat tables. This is one area where database normalization is strongly needed.

We would like redesign the way that contact information is stored in OpenEMR, creating a system with much more power for storing and managing contact information.

This system will contain normalized databases, allowing much easier searching of phone numbers and addresses. It will also allow flexibility as to what numbers will be stored, and allowing storage of old phone numbers and addresses. It will also be powerful and modularized enough to be used for every type of address and phone number stored (not just patient information). And, it will allow a centralized place for storage of all contact information stored in the database.

The first area this contact database will be used in will be the patient demographic system. Later projects will implement it in other areas that track contact information – address book, insurance companies, pharmacies, facilities, etc.

Some may not be crazy about this design – people don’t like change, and it will involve an entirely new paradigm of database design – taking the database away from flat storage, to take advantage of relational features/design. The power and usefulness of doing this should far outweigh these concerns.

In designing this layout we looked at another open source EMR project for inspiration and ideas — OpenMRS (https://wiki.openmrs.org/display/docs/Data+Model). We examined how it stores demographic information about “people” in the database. Our proposed database layout is partially modeled after theirs.

We will be providing database diagrams and sample queries to see how to interface with the new system.
Since there are really two aspects—the data layer and the user interface, this thread will focus on the data issues. If need be we can open another thread with how to display multiple phone numbers.

The attached diagram has the following table groups:
• Person
• Patient
• Business
• Patient Related Businesses
• Contact/Address Book
• User
• Data Logging and Related Tables

Question: Why have X in a separate table? Why not include it in Y?
Answer: The reason is for both current and future use, and due to complicated nature of the data. A business may have several names with different uses and needs. The new database layout will allow OpenEMR to grow with that information.

The purpose of this post is to get feedback from the community about these changes.

SEE FOLLOWING POST FOR DIAGRAM OF DATABASE TABLES.

David Eschelbacher MD
Chris Paulus, Cipher-Naught LLC

system · July 19, 2012, 4:05pm

deschel wrote on Thursday, July 19, 2012:

Diagram of Proposed Database Tables
(This is a very large diagram—you may need to use your browser zoom button to zoom in to read the table & column names.)

Link to Image:

David Eschelbacher MD
Chris Paulus, Cipher-Naught LLC

system · July 19, 2012, 4:16pm

deschel wrote on Thursday, July 19, 2012:

Backward Compatibility and Ease of Use

We will be adding some database views to the database to help with ease of use.

Wiki (http://en.wikipedia.org/wiki/View_(database)) seems to indicate view support in:
* DB2
* MS SQL
* MySQL
* PostgreSQL
* SQL Lite
* Oracle
* CouchDB

For those not familiar with database views:
“Views are queries that can be treated as tables. Saving you from having to do all the joins across multiple tables.”

Database views would help in two ways:
• Allowing current pages reading & selecting data in the table patient_data to operate in much the same way.
• Views will allow easier table joining since common tables joins can be stored as a view.

The view will obtain data from the new tables. Therefore, if there is any code that we miss, or that is not yet updated, it will still be able to access the data in the new tables, via the view called patient_data.

Of course, we will also be creating SQL files to run on the database to update the old tables and old data to the new table format.

David Eschelbacher MD
Chris Paulus, Cipher-Naught LLC

system · July 19, 2012, 4:25pm

yehster wrote on Thursday, July 19, 2012:

I think this in exciting and ambitious plan.

However, your post on views makes me think. I wonder if it might be possible to integrate OpenMRS with OpenEMR more rapidly/easily by constructing some compatible views.

An integration with OpenMRS seems like it ultimately would be more useful than trying to replicate their functionality.

The old buy vs. build paradigm.

system · July 19, 2012, 4:34pm

deschel wrote on Thursday, July 19, 2012:

Demographics Interface

Most of the patient demographic related tables are modified, updated, & displayed in three web pages:
1. Demographic Display
2. Patient List
3. Patient New/Search

All three of these web pages will be updated to support the new tables.

We will adapt the existing interface for displaying and customizing the display of customized data.

David Eschelbacher MD
Chris Paulus, Cipher-Naught LLC

system · July 19, 2012, 4:42pm

deschel wrote on Thursday, July 19, 2012:

We will keep the current demographic structure, with different types of information being stored in different tabs.

Tables with one to many relationships with patient will be listed in their own tab, as a repeating list. The format to display each group of items in the list can still be customizable in “Administration - Layouts - Demographics”. A new button will be added to add an item.

system · July 19, 2012, 4:48pm

deschel wrote on Thursday, July 19, 2012:

I wanted to present the new tabs, but source forge won’t let me - it rejects it as spam.

David Eschelbacher MD
Chris Paulus, Cipher-Naught LLC

system · July 19, 2012, 4:49pm

deschel wrote on Thursday, July 19, 2012:

Who
Address
Telephone
Secondary Contacts

system · July 19, 2012, 4:51pm

deschel wrote on Thursday, July 19, 2012:

Email/Web

system · July 19, 2012, 4:53pm

sunsetsystems wrote on Thursday, July 19, 2012:

Very interesting, and looks quite comprehensive. Why are first and last name in separate tables? What are person_to_source_table and business_to_source_table for?

I guess there will be PHP classes corresponding to the tables? Note that simplification of data access can be accomplished by coding suitable classes - this should be considered an alternative to views, and pros and cons should be weighed accordingly.

I would strongly emphasize the importance of doing and presenting the work in bite-size pieces. That works much better when code review raises objections.

For what it’s worth: the current “users” table would be better named “address_book”, and makes more sense if you think of it that way. When the address book was introduced, it used that table because there would otherwise be a lot of redundancy between users and contacts, they shared many of the same fields, and a major reworking of contact data was not in the cards at that time.

Looking forward to developments!

Rod
www.sunsetsystems.com

system · July 19, 2012, 4:53pm

deschel wrote on Thursday, July 19, 2012:

Pharmacies
Medical Providers
Send Results
Employer

system · July 19, 2012, 4:53pm

deschel wrote on Thursday, July 19, 2012:

The tabs will be changed to:
• Who
• Address
• Telephone
• Secondary Contacts
• Email/Web
• Pharmacies
• Medical Providers
• Send Results
• Employer

system · July 19, 2012, 4:54pm

deschel wrote on Thursday, July 19, 2012:

The tabs will be changed to:
• Who
• Address
• Telephone
• Secondary Contacts
• Email/Web
• Pharmacies
• Medical Providers
• Send Results
• Employer
• Misc

• Misc

system · July 19, 2012, 8:40pm

cipher-naught wrote on Thursday, July 19, 2012:

RE: First Name/Last Name being in separate tables:

This has to do with how the data might be in other sources. Think of an example in which Medicare has “James Smith”'s last name as “Smyth”. With first name and last name being separate you can have:

+-------------+-------------+
| First Name  |    Type     |
+-------------+-------------+
|   James     |   Legal     |
+-------------+-------------+
|   Jimmy     |   Nickname  |
+-------------+-------------+
+-------------+-------------+
| Last Name   |    Type     |
+-------------+-------------+
|   Smith     |   Legal     |
+-------------+-------------+
|   Symth     |   Medicare  |
+-------------+-------------+

By having the values in different tables we are able to store the First Name (legal and nickname) while storing the fact that Medicare has it wrong.

There would need to be some business logic or php classes to map James (legal) to Smyth when fetching values for type = Medicare.

I hope that helps, let me know if I am unclear.

Cipher-Naught

system · July 19, 2012, 9:01pm

yehster wrote on Thursday, July 19, 2012:

RE: First Name/Last Name being in separate tables:

This seems like an awkward way to accomplish this. A generic contact attributes table seems like a better more flexible solution.

A first normal form table with Contact ID, Source(equivalent to your Type), AttributeName, AttributeValue,
Then you can have multiple Attribute examples (FirstName, LastName, PatientID) and flexibility to handle more types in the future without too much difficulty.

system · July 19, 2012, 9:04pm

sunsetsystems wrote on Thursday, July 19, 2012:

Agree with Kevin re attributes.

Rod
www.sunsetsystems.com

system · July 20, 2012, 1:46am

bradymiller wrote on Friday, July 20, 2012:

Hi,

Agreed that it is exciting to see such comprehensive and ambitious plans. The thing I figured I’d focus on first are “Database Views” since I honestly didn’t really know what it is. I found a nice comprehensive discussion on Views here:
http://net.tutsplus.com/tutorials/databases/introduction-to-mysql-views/

It appears that the cons of going the Database View route are very similar to using Triggers, which we discussed several years back here:
http://sourceforge.net/projects/openemr/forums/forum/202506/topic/3532190
The following issues were brought up in the above thread as concerns for using triggers:
1) Will make OpenEMR unusable for users using shared hosting (shared hosting does not allow root access to their sql databases, and generally doesn’t allow users to have privileges for installing procedures)
2) Will remove the option of database portability for OpenEMR in the future.
3) The install/upgrade process will become much more complicated.

It appears Database Views will also bring up these issues. For example:
1) It appears it may not be supported by some shared hosting services:
http://www.webassist.com/forums/showthread.php?t=10122
2) There are differences in how Views are used in different databases (for example, some allow UPDATE, while others don’t)

Thoughts?
-brady
OpenEMR

system · July 20, 2012, 1:54am

bradymiller wrote on Friday, July 20, 2012:

Hi,

The next thing is to ensure you do not underestimate the current use of the patient_data table in the current codebase. Running the following command in linux:

grep -R "patient_data" /var/www/openemr/ | less

(it shows about 600 lines over lots of files in the codebase that use this and note this is actually an underestimate because of the functions in library/patient.inc that are also called frequently and derive/write date to the patient_data table)
(all in all, predicting several thousand places throughout the codebase are affected)

-brady
OpenEMR

system · July 20, 2012, 2:51am

yehster wrote on Friday, July 20, 2012:

My take on the use of views is that it is meant to ease the transition from the old implementation to the new one and views will eventually go away. Regarding your specific objections,

1. Loss of portability between database platforms shouldn’t really be a big deal. A lot of things would need to happen to get OpenEMR running on a different platform anyway for not very much gain. The desire to run on other databases is in my mind a nice idea, but the advantages of this proposed redeisgn (if done well) certainly out being able to use Postgres instead of MySQL.

3. Installation and upgrade really shouldn’t be a big deal. Creating views can be scripted just like creating new tables or indicies is. The check for existing objects would need to be enhanced to look for views in addition to the other object types but it shouldn’t be that hard.

2. As for shared host environments, I don’t have a great answer to this issue, but running OpenEMR on GoDaddy is probably a HIPAA violation to begin with. since the data at rest isn’t encrypted, and a GoDaddy administrator could likely run queries on your PHI without your knowledge.

Also, I believe that one of the goals (based on the previous Framework discussion) is a modular design, so people don’t have to use these features if they don’t want to.

system · July 20, 2012, 5:24am

tmccormi wrote on Friday, July 20, 2012:

I agree with Yehster regarding database portability, while it is a laudable goal, there is no real gain and a lot of risk and we lose the the ability to use some fine features in MySQL.

Regarding views: I use them a lot to be able to write a single program that can be used to produce a reports and exports for different data sets, some very complex SQL logic. No new PHP code is typically needed. It’s kind of cool. Views are easy to add and really powerful for transitions of the database, you can do pretty radical stuff and then keep a “view” that looks like the old format to take care of the legacy code for a while (at least lookups/reports).

I would NEVER recommend running OpenEMR on ISP or cloud service that does not have a published HIPAA compliance document / policy.

No matter how you approach this project it is going to be a big job….

-Tony