New DB Schema Map and Proposed MU updates

OpenEMR Development
1

tmccormi wrote on Tuesday, October 12, 2010:

This URL has some exciting things:
   http://www.openmedsoftware.org/wiki/Mapping_OpenEMR_Data_for_CCD/CCR_and_CQM#Data_Descriptions

1) There is a complete Data Definition Map for OpenEMR Version 4 as it stands.  This map was produced by Fie with Verbus Counts team at EMR Technical Solutions, LLC.   Big, Big round of applause

2) There is an SQL file that forces OpenEMR DB from My_ISAM to INNODB.  This was the fastest way to produce the above documentation and HAS NOT BEEN TESTED in any way.  IT needs to be reviewed and very, very carefully tested one piece at a time down the road.  It is, however, a very good place to start on the much needed data overhaul.

3) There is a file that contains the long waited proposed new tables and table updates for the Meaningful CQM reporting requirement.  Just the first 4; Medications, Allergy, Adverse Reaction, Problem / Condition / Issue, Diagnostic Test Results.  The tables are INNODB as a result of the above, but would likely be MY_ISAM in the beginning.

PLEASE look all of this over and provide you feedback!

Thanks

Tony, MU Certification Project Lead

2

bradymiller wrote on Tuesday, October 12, 2010:

hi,
What are the benefits of converting to INNODB (besides the fancy pdf)?
-brady

3

visolveemr wrote on Tuesday, October 12, 2010:

Hi,

As of our experience with mysql benchmarking, innodb yields better performance than myisam.  More than that, innodb is a transaction safe engine.  Also, most of the Industry standard products that connects with the MySQL DB expects, InnoDB engines.

Thanks
ViCareplus Team
www.vicareplus.com
services@vicareplus.com

4

tmccormi wrote on Tuesday, October 12, 2010:

The other key benefit is referential integrity.   That is the ability to define foreign key id relationships at the database level and protect data records from being removed if other data references it.
-Tony

5

acmoore wrote on Tuesday, October 12, 2010:

Good point, Tony. In my experience with mysql, the referential integrity itself makes it worth switching to innodb. I’d love to see the switch.
-Andy

6

sunsetsystems wrote on Friday, October 22, 2010:

Here are some comments regarding the proposed new tables and table updates.

First, it’s nice to finally see some focus on specific changes to the existing schema!

However we need more explanation and comments to go along with the changes.  For many of them it was not at all obvious to me what the intent is, which makes it really hard to comment or to make constructive suggestions.  Please add some narrative!  In particular I don’t know the purpose of many of the changes to the “lists”, “procedure_type”, “procedure_result” tables.

I was not able to unzip the file referenced from the wiki page.  It seems to be corrupted.  However I did have it from another email.

There are many new lists added.  All of these start with a blank initial entry.  This is not necessary.  Also all of them seem to have numeric values for option_id - I would rather see textual values that are somewhat descriptive of the item.  option_id was created as a text field for this purpose.  See some of the other lists for examples.

I do not understand the primary key changes to the ar_activity and claims tables.  For ar_activity “sequence_no” is intended to be relative only to a given pid and encounter, i.e. it should start with 1 for each new encounter.  It is not intended to be a unique key.  Similarly for “version” in the claims table.

Regarding patient_reason_codes, medical_reason_codes, system_reason_codes: What is the sense in creating three new tables each with only a single column?  Couldn’t list_options be used instead?

What is the purpose of this?  It seems pointless:
ALTER TABLE list_options ADD INDEX (option_id);

Re the “pregnancy” table: shouldn’t pregnancy be a type of issue?

What are the “communication” and “substance” tables for?

Why the “physical_exam” and “physical_exam_finding” tables: Shouldn’t a physical exam be implemented as an encounter form?

That’s all for now….

Rod
www.sunsetsystems.com

7

johnbwilliams wrote on Tuesday, October 26, 2010:

Answers by Fei Lung (developer), additional comments by John Williams (author of OpenEMR MU data model).

Note:  It appears that the full data model is being implemented, covering all a full summary care record and all 44 MU-1 CQMs, rather than the just the four sections of the data model necessary for minimal patient summary care record.

1. We need more explanation and comments to go along with the changes.
For many of them it was not at all obvious to me what the intent is,
which makes it really hard to comment or to make constructive
suggestions. Please add some narrative! In particular I don’t know the
purpose of many of the changes to the “lists”, “procedure_type”,
“procedure_result” tables.

Ok.

2. I was not able to unzip the file referenced from the wiki page. It
seems to be corrupted. However I did have it from another email.

OK

3.  There are many new lists added. All of these start with a blank
initial entry. This is not necessary. Also all of them seem to have
numeric values for option_id - I would rather see textual values that
are somewhat descriptive of the item. option_id was created as a text
field for this purpose. See some of the other lists for examples.

Ok.

4. I do not understand the primary key changes to the ar_activity and
claims tables. For ar_activity “sequence_no” is intended to be
relative only to a given pid and encounter, i.e. it should start with
1 for each new encounter. It is not intended to be a unique key.
Similarly for “version” in the claims table.

Yes

5.  Regarding patient_reason_codes, medical_reason_codes,
system_reason_codes: What is the sense in creating three new tables
each with only a single column? Couldn’t list_options be used instead?

These tables are meant to provide a place for HL7 reason codes.  I do
not know the format of the reason codes (are the predefined?  Are they
provided by the provider?)

Hopefully Mark Harrow can gove us an answer as he is planning to implement the codes sets necessary for thi OpenEMR MU Data mdoel.  - JW

6.  What is the purpose of this? It seems pointless: ALTER TABLE
list_options ADD INDEX (option_id);

Yes, this shouldn’t be in here.

7. Re the “pregnancy” table: shouldn’t pregnancy be a type of issue?

Pregnancy has many requirements and pointers that are not used in
other issues.  If it’s a new type of issue, there will be many new
fields only used by pregnancy

8.  What are the “communication” and “substance” tables for?

They are part of the HL7 doc provided.

“Communication”, e.g., communication to patient, patient communication to physician, etc., and part of the CQM Quality Data Set data model, and are elements of several sections of the OpenEMR MU DM model. - JW

9.  Why the “physical_exam” and “physical_exam_finding” tables:
Shouldn’t a physical exam be implemented as an encounter form?

The way I read it was that there could be multiple findings per exam.

The Quality Data Set data model kept the “Physical Exam” data element seperate from the ecounter data element, so we did that in the OpenEMR MU data model as well. - JW

Thanks,
Fei

8

sunsetsystems wrote on Tuesday, October 26, 2010:

Regarding pregnancy, there is already a precedent for creating issue types that have their own fields in a separate table.  Look at interface/patient_file/summary/add_edit_issue.php and you’ll see that it optionally includes (via require_once) some modules for these.  There may be a better way, but this is a starting point.

Would it mess things up to make Physical Exam an encounter form?  Offhand it seems logical.

Thanks!

Rod

9

johnnytang24 wrote on Monday, November 22, 2010:

There must be an index on option_id if a foreign key constraint is going to be linked to it. 

As far as MyISAM vs InnoDB:

1) There are no transactions with MyISAM tables
2) There is no referential integrity with MyISAM tables
3) MyISAM uses table-level locking, which on a system such as OpenEMR (few full-table scans, multi-user),  should be slower than row-level locking.  Given a single user environment, the speed wouldn’t be an issue
4) The only advantage to MyISAM in this context, the FULLTEXT index,is not used in OpenEMR

MyISAM is not ACID compliant and I am opposed to its use in OpenEMR.

Thanks,
Fei

10

tmccormi wrote on Monday, November 22, 2010:

Fei,

Those are all very good reasons to move to INNODB, but you can’t force that kind of change on the user base.  It would not be a big deal to make it a default for new installations, though it needs to be tested very carefully.  

For upgrades:  a very good upgrade process, and script would need to be written that can handle error conditions easily and the upgrade to INNODB would need to be optional.

In any case the new code needs to be able to work in MyISAM mode out of the box.  We should shoot for INNODB as the default, but not before the M/U Release candidate.

-Tony

11

tmccormi wrote on Tuesday, November 23, 2010:

Question related to meaningful use, specifically related to Rx data.    We need to add a few fields to the prescriptions table that are required, details will follow but it’s not much.  However we noticed these fields which I’m sure are part of the customer ‘Athletic Team’ customizations, and we are curious how they related to Rx data.   I’m not suggesting we remove them or anything just wanted some more info on how they are used.

reinjury_id
injury_part
injury_type
injury_grade

-Tony and the MU team

12

sunsetsystems wrote on Tuesday, November 23, 2010:

Hi Tony,

I’m not sure what you mean but those are not related to prescriptions.  They are attributes of sports injury issues (the “lists” table).

Rod
www.sunsetsystems.com

13

tmccormi wrote on Tuesday, November 23, 2010:

Ahh, my mistake I was looking at both of those tables and I thought I had switched back to the prescriptions table… sigh, must be working too late.  Thanks for the response…
-Tony