sunsetsystems wrote on Saturday, July 21, 2012:
Seems really obvious to use objects to abstract all access to tables, regardless of schema. So many advantages, including hiding complex queries and allowing choice of views (or not) to be made for reasons other than complexity.
Re multiple names and legacy code, clearly one of the names would be used as the default.
cipher-naught wrote on Saturday, July 21, 2012:
Rod - I agree that obviously objects will abstract the access to the tables, but those objects need to be programmed. If we can make it easier whenever someone needs to create objects that access the data, then it will be quicker, easier, and less prone to errors/bugs.
I guess all I am saying is that complexity should always be a concern, even if developers should be protected by it via objects or some other solution.
Just my two cents.
-cipher-naught
mcaloon wrote on Friday, July 27, 2012:
David,
After a quick review of the data model I had a couple of comments
Does the pharmacy object have any missing attributes that might support
the eRX feature. I am not too familiar with that feature… just curious
Agree with Kevin and Rod on the name stuff. Lets not conflate a data
cleansing issue, the Medicare example, with appropriate attribute object
relationship
Lets think about the UI component model in terms of the data model to
reduce the number of bridge tables. A draft of the proposed changes to the
UI would help in understanding when we could use a bridge table and when
we might be able to just replicate an attribute across a base table. For
example, I would like to understand the contact_to_contact bridge tables a
bit more thoroughly. How about another post with more details on this
I think contact_telephone could be contact_device and include other
attributes such as OS to distinguish between IOS and Android devices. This
could also be accomplished using the TYPE attribute if there were an
appropriate ENUM declaring the supported device types. For example,
currently many tablets are not telephones but can, or could, do just about
anything else in terms of the data interface.
Agree that this data model is very important and we will want to get it
right. Wondering whether to get this set up on the public openEMR Pivotal Tracker scrum as James and I might have some bandwidth.
Mac
deschel wrote on Sunday, July 29, 2012:
Re: One to many tables between person and first/middle/last.
The proposal for first/middle/last names in same table looks to me like a proposal for a vertical table.
First of all, I have explored and debated vertical tables versus horizontal tables, and I’m not crazy about doing vertical tables. Vertical tables basically force you to recreate what the database system already does for you. So, just use the database system and create a new table.
Vertical tables are useful when there are a large number of possible TYPES/attributes. They should have been used in the history_data. Why didn’t you pressure the designers of that table to do a vertical table? (I think I may have already brought this up.)
First names are fairly distinct from last names. First names are not really interchangeable with last names, and are not usually used for both. So, its not really justified to represent them in the same table field.
Also, first names are independent of last names. Therefore, it is not really appropriate to put them as different fields in the same table.
Now, the “medicare name” example is fairly uncommon, and does not quite justify the one to many table. I want to discuss different examples.
For first name, people can have different aliases, nicknames, and legal names. Also, there is the possibility that someone changes their first name. Nicknames is the most common example of what happens, and this happens quite often. Someone’s legal name might be Robert, but they go by Bob. When I address a patient, their chart usually has their legal name. However, the patient is never called this. It would be nice to be able to see their nickname. We could put nickname in a different field. However, then when doing a search for patient first names, it would be nice be able to find the patient by searching for bob or robert. Take the example of someone changing their first name-not as common, but I still applies to a couple of my patients. You might still get correspondence or records that have the patients old name. Therefore, it would to have a record of all of their names.
Now, middle names are a little trickier. Middle names I guess would be more similar to first names than last names. And, some people prefer to be called by their middle name. So, I might consider the idea of representing first name in the same field as middle name, just with a different type attribute.
In regards to people with a long name with multiple middle names and multiple last names all in one name, especially applicable in spanish cultures. This might be too tricky to implement in any of the systems proposed. I don’t think a vertical table addresses this. I might need to have to draw a line here and have someone else do that improvement after we implement our demographic improvement.
As far as people with different last names, again this is fairly common, primarily when someone marries and changes their last name. There might still be records that refer to the maiden name. It would be useful to keep this data. Also, sometimes the same person is referred to their hyphenated name and their new last name at the same time. Therefore, some one who changes their name might be referred to three different ways - example Jane Roberts-Smith vs Jane Roberts vs Jane Smith.
David Eschelbacher, MD
deschel wrote on Sunday, July 29, 2012:
RE: PHARMACY OBJECT
We included pharmacy table as an example of a type of data item that we want to track. The design of this item is definitely incomplete.
The intention is for it to be utilized by the e-Rx module-but at a later date. At this moment, we have not had time to research the database as far as to how e-Rx works.
Also, the focus of the project outlined in this thread is more patient demographic oriented. There is already a lot of work for the demographic project as it is, and we don’t have time to do a lot of work on the Pharmacy table. We will create the table but not fully develop it. However, it will be developed at a later time.
The same goes with the business table. I am planning to utilize these tables more when we overhaul the address book. Address book is a few projects down the road. We will create the basic database elements to support it in this project, but it is beyond the scope of this project to fully develop them and create the interface for them.
Just think of some of the tables created by this project as “laying the groundwork” for later projects.
David Eschelbacher, MD
deschel wrote on Sunday, July 29, 2012:
RE: BRIDGE TABLES
You called them “bridge” tables. However, the proper terminology is “join” tables.
“Join” tables are the standard database convention for supporting many to many relationships.
The data dictates the justification for this.
David Eschelbacher MD
deschel wrote on Sunday, July 29, 2012:
RE: CONTACT DEVICE
This is an interesting thought.
Android “devices” are still telephones. It might be too confusing to people to call it contact_device. If they are looking for which tables store phone numbers, it won’t be readily obvious if we call the table contact_device.
I don’t really see the reason or need for storing the operating system of the telephone that we are calling. However, to be complete, we can add a field to “contact_telephone” called OS.
As far as tablets being stored in the table “contact_telephone” or “contact_device”. How do we directly contact the tablet? I don’t think that there is a reason to store the tablet, because there is no way to directly contact it. Now, we can sent email which will go to the tablet. This will be stored in the table named contact_email_web.
David Eschelbacher MD
deschel wrote on Sunday, July 29, 2012:
Re:
“Wondering whether to get this set up on the public openEMR Pivotal Tracker scrum as James and I might have some bandwidth.”
You totally lost me here. Please elaborate.
David
mcaloon wrote on Sunday, July 29, 2012:
David,
Thanks for this excellent feedback. I am very familiar with database modelling and can see the benefits of where you’re going with this thread. I am also very interested in the UI and its need for simplicity. This thread has identified a number of “projects” as you’ve indicated. One of my challenges since joining the team back in March has been understanding the direction the openEMR implementation is moving. This is why I created the Pivotal Tracker openEMR project ( https://www.pivotaltracker.com/projects/602367 ). It is a scrum ( http://www.scrum.org/what-is-scrum ) management tool for tracking software development projects. I recently added release 4.1.1 as a sprint. Scrum could be used to coordinate our team when large scale work items (like the workflow redesign, or work discussed in this thread) need to get done. When I saw your ideas in this thread, I immediately thought we could get some of the team members to get moving on this as a community scrum. I am going to take some of your ideas in this thread and put a “release” together that itemizes the individual tasks (that I can think of) that could be taken on by willing / able community members. Always in search of and welcome sponsorships 
Mac
sunsetsystems wrote on Monday, July 30, 2012:
Regarding names of persons - we don’t seem to be very close to a consensus. So again, I think it’s wise to look to other projects, especially those that store information about patients, to see how they handle it and how well that’s working for them.
deschel wrote on Monday, July 30, 2012:
There are probably other projects, but they may be difficult to find.
Most projects, even OpenMRS, just use flat tables for names.
I think that my idea of separating the names is fairly unique. But, logical.
If someone can scour the web to find some good examples of other projects doing something similar with names, please post them. I would love to look at these.
-david
sunsetsystems wrote on Monday, July 30, 2012:
Here’s a thought if we end up breaking new ground in storing names. Use a single table with the following columns:
person_id
type (last, first, middle, prefix, suffix, …)
usage (primary, former, nickname, alias, …)
value
This supports names of arbitrary complexity without making a mess of the database. But honestly, I think it’s all overkill.
tmccormi wrote on Monday, July 30, 2012:
I agree with Rod on this, Don’t over normalize the DB, I had exactly the same thought about the name table structure this weekend…
-Tony
deschel wrote on Tuesday, July 31, 2012:
If you want us to stay with the current layout engine, then this will be too difficult to display.
If we get rid of the engine, then this would be easy to code in HTML.
In the current layout engine, it would be much easier to deal with a regular one to many table then a vertical table. Just listing the vertical table would look like a mess.
Listing a the first name table, and listing the last name table separately is so much easier to code into the layout engine. My proposal will support one to many tables, just not vertical tables.
In theory the suggestion sounds nice, in practicality it is not.
Talk about over normalizing the database. Vertical tables take normalization to another level. In my opinion vertical tables represent ultra over normalization.
If you want to do vertical tables, will you pay for creating a new layout engine to support it?
The cost & time of implementing my suggested tables and layout engine modifications would be much less than the cost & time required for your proposal. Unless, you want to give me more details that would show me otherwise.
-david
mcaloon wrote on Tuesday, July 31, 2012:
Hey,
Thinking out of the box here…. I thought as we’re contemplating a major overhaul of the openEMR embedded “layout engine” here…. I thought “this forms generator thingy” part of openEMR …… I’ve seen a number of forms generators over the years and googled open source forms designer and found jFormer a jquery based php GPL framework that might be an interesting tool that could replace the layout stuff… Any thoughts?
Mac