tmccormi wrote on Friday, July 20, 2012:
PS: I have just spent the whole week hanging out with the OpenMRS people at OSCON and they have some great stuff and are looking for ways to collaborate with the OpenEMR community (bi-bidirectionally) …
Do you have the SQL for this posted DB schema up on a github account or somewhere that I can download it to play with? I am intrigued.
Tony
www.mi-squared.com / @tonymi2
oemr.org / @OEMR_org
deschel wrote on Friday, July 20, 2012:
Regarding Generic Attributes Table
(1) I disagree that first name & last name in a different table is awkward.
I feel it is a natural grouping of the data. People have multiple first names, and they have multiple last names. But, last names and first names are independent of each other. Additionally, the names can be changed. I am a strong believer in not throwing out old data, especially when it might be useful at a later date. The nature of the information makes a separate table with 1:many a natural extension of this.
This 1:many table structure is easy to see and understand at a glance. I would like to contend that a generic contact attributes table would be much more confusing and awkward than a separate table with a 1:many relationship.
(2) Vertical vs Horizontal Tables.
Horizontal Tables = Structure in the above “Diagram of Proposed Database Tables.”
Vertical Tables = Entity-Attribute-Value System that is proposed by yehster.
In our database design, we actually did discuss whether or not to use a vertical table structure. The advantage of vertical tables is expandability.
However, we felt that the disadvantages of using vertical tables far outweigh this advantage. Additionally, there are a limited number of contact types to add, so the data does not really have a need for it. If we need to add another contact type, we could just add another table.
The problems with vertical tables:
- They are confusing and difficult to read by just looking at the database structure.
- They can’t be properly indexed.
- Their performance is much slower.
- They add too much complexity.
- They don’t leverage the features of the database – you almost have to re-create what the database would do for you if you had a horizontal table.
Now, it is kind of ironic that you bring up using vertical tables for this contact database design, when it would have been much more appropriate to use the vertical structure in an area of OpenEMR that almost begs to have a vertical structure – the history_table.
The history_table is one very long list of attributes. It is more logical to make the history_table into a vertical table than it does to make the contact table or patient names table into a vertical table. (We actually plan to try to tackle improving the poorly designed history_table in my Project #12. FYI, my demographic table change is Project #3.)
David Eschelbacher MD
deschel wrote on Friday, July 20, 2012:
Regarding Collaboration with OpenMRS
This sounds fun, but I’m not sure how useful it would be.
OpenMRS is written in Java, not PHP.
Our table structure was only loosely based on theirs. Their design provided ideas and inspiration, but we took it our own direction.
I must say, though, that their database design is much more advanced / evolved than OpenEMR’s database design. However, in terms of implementation of features, they are very far behind OpenEMR.
David Eschelbacher
deschel wrote on Friday, July 20, 2012:
Regarding SQL for the proposed DB Schema
We designed it using MySQL Workbench. It takes only a few clicks to export an SQL of the DB Schema.
We will work on posting this file to github so you can play with it.
When playing with it please make sure to give us feedback on how to improve it.
David
sunsetsystems wrote on Friday, July 20, 2012:
Dr. E’s point about “vertical” tables diluting the advantages of the DBMS is valid. Here’s an article about that as it relates to Oracle:
Vertical tables are best used where data structure is dynamic, with fields that can be added or removed by the user. However with the patient_data table that is largely the case - Layout Administration can be used to add and remove fields, and to change how they are implemented, and the number of columns in the table can easily become unwieldy.
I’m thinking a hybrid approach may be ideal. A horizontal table for “fixed” fields, those that are required for normal operation of the system, and a separate vertical table for the fields that can be added, deleted or modified by users.
Re first and last names - what about middle names? Will there be a separate table for those also? And name titles, prefixes and suffixes? Also in some cultures, names do not break down in any of these ways - there might be a single name, or more more than three parts to a name. I feel it’s unwise to get too fancy, too rigid or too committed to a particular structure for names.
fndtn357 wrote on Friday, July 20, 2012:
I had a client mention that they would like options in the “gender” category to have options for FtM and MtF and date fields associated with them. Is this an option in the vertical tables?
Vertical tables are best used where data structure is dynamic, with fields that
can be added or removed by the user. However with the patient_data table that
is largely the case - Layout Administration can be used to add and remove fields,
and to change how they are implemented, and the number of columns in the table
can easily become unwieldy.
I’m thinking a hybrid approach may be ideal. A horizontal table for “fixed”
fields, those that are required for normal operation of the system, and a separate
vertical table for the fields that can be added, deleted or modified by users.
sunsetsystems wrote on Friday, July 20, 2012:
I had a client mention that they would like options in the “gender” category to have options for FtM and MtF and date fields associated with them. Is this an option in the vertical tables?
Sure. I don’t think there is a layout field type that associates a date with a list, but by choosing suitable titles and positioning it should be clear enough.
yehster wrote on Friday, July 20, 2012:
I buy the argument that a separate table for FirstName and LastName may be reasonable, but you’re still essentially implementing a vertical arrangement based on SourceType.
yehster wrote on Friday, July 20, 2012:
Consider this notion. If a generic attributes table were used, you could create a FirstName and LastName table view of the generic attributes table very simply (Select * from Attributes where AttributeName=“FirstName”). However, implementing separate tables for FirstName and LastName (and possibly other future attributes) would not allow easy creation of a search across all attributes (you have to hit each table individually).
If you clustered the index on AttributeName you’d have close to the same performance as the separate tables. However, clustering on ContactID probably makes more sense. (You can get/check all the attributes on one contact you might be interested in with one Query rather than a separate for each attribute.)
sunsetsystems wrote on Friday, July 20, 2012:
Regarding database portability: MySQL is owned by Oracle now, and it’s not too hard to imagine that it may eventually become less competitive among the free DBMS options. While I don’t think we need to jump on a portability project right now, neither should we be doing anything to make it more difficult later. Portability is always a consideration in our designs.
Regarding names of persons: I’m not so concerned about performance, but I think we’re in danger of making it more complicated than it’s worth. Let’s look to commonly accepted “best practices” to decide on a “normal” way to handle it.
tmccormi wrote on Friday, July 20, 2012:
I just went to a presentation at OSCON from Oracle where they presented their plans for MySQL and specifically INNODB engine. If anything they are likely to be MORE competitive going forward.
http://www.oscon.com/oscon2012/public/schedule/detail/24321
-Tony
bradymiller wrote on Friday, July 20, 2012:
Hi,
Still educating myself (and exercising due diligence) on Database Views. Can anybody point me towards any mainstream LAMP projects that use Database Views in their core codebase/database (I have not found one yet) ?
-brady
OpenEMR
sunsetsystems wrote on Friday, July 20, 2012:
I just went to a presentation at OSCON from Oracle where they presented their plans for MySQL and specifically INNODB engine. If anything they are likely to be MORE competitive going forward.
http://www.oscon.com/oscon2012/public/schedule/detail/24321
But what if the “good stuff” becomes non-free? They are, after all, competing with their own “big guns” database.
fndtn357 wrote on Friday, July 20, 2012:
stackexchange usually has the most current information and thoughts (for me)
bradymiller wrote on Friday, July 20, 2012:
Hi,
Thanks for the site James; very useful. Just to add to the list of potential Database View issues, also potential performance issues:
http://dba.stackexchange.com/questions/16372/when-to-use-views-in-mysql
Database Views seem cool and useful, but the main issue here is:
Would it be acceptable to include them in a production release LAMP project?
It seemed the best way to figure this out would be to find a mainstream LAMP project that uses them and then see if there were complaints/problems by users. However, I have yet to find a mainstream project that contains them. Was hoping that somebody knew of one or more that do.
thanks,
-brady
OpenEMR
cipher-naught wrote on Friday, July 20, 2012:
The DBA Stackexchange question/topic you link to has more to do with problems using views and the TEMPTABLE algorithm.
The views I was thinking about for the demographic table changes were more of the MERGE variety which are “MERGE is simply a query expansion with appropriate aliases.” Meaning that if you have SQL like this:
select person_first_name.person_first_name, person_last_name.person_last_name from person
inner join person_first_name on person.person_id = person_first_name.person_id
inner join person_last_name on person.person_id = person_last_name.person_id
where person.person_id = 12 and person_first_name.type='legal' and person_last_name.type='legal'
With a view you could do:
select * from view_Person_Legal_Name where Person_id = 12
The view would just act as alias for the other tables making the query easier to use. Most/All the concerns on views are when you have grouping clauses which require temp tables to be created.
-cipher-naught
fndtn357 wrote on Friday, July 20, 2012:
just as a thought, we could integrate a Symfony layer and then we could be more database agnostic. Symfony is working with the Drupal 8 initiative to do just that.
bradymiller wrote on Friday, July 20, 2012:
oops,
Just realized I posted the following in the wrong forum thread:
Hi cipher-naught,
For the Database Views, just want to clarify that you plan to use it for two things:
1) Support legacy codebase (ie. have a patient_data view so doesn’t break codebase that calls the patient_data table to grab or update stuff in the new demographic tables). If so, curious to see what you plan to use as the patient_data view that goes along with your proposed table set.
2) Simplify queries. My thoughts are that this can get out of hand rather quickly (envisioning hundreds of potential views); any thoughts to instead encompassing this within modular php functions/classes?
-brady
cipher-naught wrote on Saturday, July 21, 2012:
Brady -
1) Support legacy codebase - for patient_data table
I think there might have to be some discussion about the best way to implement some of the new “multitude of data” supported with the new design. For example there is only one name available to a patient now, how do we make backward compatibility work for the new design that supports more than one name.
(I could see some type of design supporting some type of priority legal > medicare > other or something like that.)
Simplify queries
- I have run into the opposite problem with views, them not taking hold when they should. For example if I get used to typing:
select * from person
inner join person_first_name on person.person_id = person_first_name.person_id
inner join person_last_name on person.person_id = person_last_name.person_id
where person.person_id = 12 and person_first_name.type='legal' and person_last_name.type='legal'
When I could have used the
view_Person_Legal_Name
table forgetting that the view is easier to use (for lines of text and typos) than the joins above.
As to the thought of using modular php functions/classes - I think it can be a two edged sword. Databases are very, very efficient at data processing. Take an example of having two views on the
view_person_legal_name
and the other
view_person_business_name
(maps a person to a business name). With these two views you could have a query like this:
select view_person_business_name.business_name from view_person_legal_name
inner join view_person_business_name on
view_person_legal_name.person_id = view_person_business_name.person_id
where person_id = 12
Now if you have that code in php you might have this:
$businessObject->getBusinessName($personObject->getBusinessIDByPersonID($personID));
Now you could just say well we should create a single method that fetches both these values which is fine, but then you are copying the SQL around everywhere. But in the end it is just a value judgement.
It is the same parallel to Business Logic in the Database (a good link from Martin Fowler). Should all business logic be at the code level? (Another discussion for another day, perhaps.)
But my point pro-view point is:
1) Makes database level programming/SQL easier
2) Centralizes data model access to reduce common mistakes (joins that look right, but aren’t).
3) I feel it makes programming easier and cleaner since the code becomes easier to read since there are less joins, etc.
Fortunately, you can probably argue every one of these points in the opposite manner and still be right.
I don’t think there is a one single pure answer to this, like so many things it is a balance.
-cipher-naught