mdsupport wrote on Wednesday, October 21, 2015:
Why not use the existing insurance framework? Isn’t an insurance just a subset of ‘Guarantors’?
It may make sense to change demographic tab from ‘Insurance’ to ‘Billing’ or ‘Financial Responsibility’. That will provide a single view of all financial parties involved in paying for patient care.
yehster wrote on Wednesday, October 21, 2015:
No, the guarantor is who pays if the insurance company rejects the claim.
Again, all of these parties generically are “contacts” and it would be nice to have a more basic entity that could be generically used, but there is a lot of legacy code that would need to be refactored to accomplish that.
tmccormi wrote on Wednesday, October 21, 2015:
Having had to modify the insurance framework to support more than the current defaults and break the Primary->Secondardary->Tertiary flow that is baked into the process, I can tell you that it’s very hard, but, we are finding all the possible issues that we have “caused” 
However, I agree that that is one possible solution from a stricly idealistic point of view.
I have attached a couple of screen shots of what this looks like
mdsupport wrote on Wednesday, October 21, 2015:
I completely agree with your assessment of ‘Primary->Secondardary->Tertiary’ logic. My only worry would be adding another element for biller to consider => developer to put on reports and screens.
At the end of the day, this is billing related feature. Many of commercial billing systems use an abstraction layer to implment flexible billing plans - e.g. Kareo uses ‘cases’. Within a case you can define payor hierarchy to be followed by billers. Since you can specify the billing plan for each encounter, situation like worker’s comp visits, litigation related payments, family payments etc. can be set up in addition to ‘default’ insurance hierarchy + patient responsibility.
aethelwulffe wrote on Thursday, October 22, 2015:
Hmm…we seem to be missing a lot of posts…like the one where I stated that vertical tables are a pain, and we have to go there anyway…and like where I thanked Terry for taking on doing anything…by any method…to tackle the guarantor issue that has been grinding around.
bradymiller wrote on Thursday, October 22, 2015:
Hi,
Kevin brings up a good point on the maximum row size. There was time not so long ago when OpenEMR would break if users added too many layout entries into the demographics screen, since it was adding VARCHAR(255) columns. This was fixed by changing the newly added columns to TEXT. This is because VARCHAR(255) takes up 765 or so bytes and TEXT only takes anout 10 bytes or so:
https://sourceforge.net/p/openemr/discussion/202506/thread/dc3229a4/
Below was the commit that included this fix:
According to these links, there should be a 65,535 maximum byte limit for the patient_data table:
https://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html
http://lists.mysql.com/mysql/175711
The patient_data table currently has 60 or so VARCHAR(255) columns already taking up 45,360 bytes. The other 29 or so columns probably bring in about 900 bytes(just a quick estimation glancing at the columns). So, there should be, in theory, plenty of room as long as stop adding VARCHAR (instead add TEXT) to patient_data. We could also free up space by converting appropriate VARCHAR columns to TEXT and removing stuff that is not needed (are all those usertext? and userlist? columns needed on a new install??? that’s 15 columns alone taking up 11,000 bytes or so…).
Regarding how to add the Guarantor stuff:
It seems like just adding a new Guarantor tab (could use a global to turn it off if a user desired) to the demographics layout and the elements to patient_data would suffice here (all as TEXT fields, of course). The problem with creating pseudo-patients that are guarantors is that many downstream things may be affected considering the entire codebase has assumed that a patient_data is a patient(unlike the users table, which can represent labs/people/companies/atoms); and if have a tough time doing this, then will not be feasible to point to other patient’s since most guarantors will likely not be real patient’s in the practice. And the problems with both the vertical table and a “new” place for storing “people” is that we are adding a lot of “baggage” for a developer to add a seemingly straightforward and important feature here. I agree a vertical table would be nice; another way to go about this would be to support this when users add new elements to the layouts(via the layouts GUI); then the infrastructure and downstream changes would be in place to add the future new demographic (and other layout) fields to the layouts.
-brady
teryhill wrote on Thursday, October 22, 2015:
What would be the impact of changing the existing VARCHAR(255) columns to text?
Could this be done in the next upgrade?
Terry
sunsetsystems wrote on Thursday, October 22, 2015:
There could be a significant performance impact, positive or negative. See:
I would suggest instead reducing most of the lengths to more reasonable values. For example there’s no need for marital status to be 255 characters.
aethelwulffe wrote on Thursday, October 22, 2015:
Down here, (and Utah) marital status may be trickier than you think. 
I have noticed other issues with using text fields as well, but that link expanded my knowledge significantly.
I am bonzer for optimizing the tables…short of allowing EMUM to create restrictions. Of course, there are also unused fields, unused functions, and unused tables (look to the calendar for plenty of that) all around. A start might be optimizing a copy of a real data set…then giving some leeway. Certainly when the field for entry is restricted already, the table limit need not be any larger than needed for a reasonable number of escape characters. The results of optimization still don’t come close to the benefits of optimized procedural table design.
teryhill wrote on Thursday, October 22, 2015:
I am trying to lock this down.
So this is what I have so far;
1 We would like to get the Guarantor information in to OpenEMR. It is generally felt that it is a needed addition.
2 We would need to at some point, preferably early on, optimize the fields in the patient data table.
3 We would like to at some point move to more efficient use of the patient data table. Preferably a vertical implementation.
4 In the patient data table we will need to have a way of distinguishing a patient from a guarantor. This will be used to keep the guarantors out of patient specific reports and aid the user in finding just the guarantors in the look-up. Both the Patient and Guarantor records would exist in the patient data table.
5 A global control is needed for this feature.
6 Initially Statements and collections would need to be changed to support the Guarantor record . The statement would probably need to indicate which patient generated the charges.
7 A family billing option would need to be added to reports that require it.
Here is where I need some clarification.
Would we use the full guarantor tab as in the SQL file I submitted or just do a PID to PID relation by adding the Guarantor PID field to the patient record and add a minimal number of fields to the guarantor tab to indicate that the records are linked (First, middle, last name, PID and DOB of Guarantor probably making these fields read only and not storing these in the patient data table as extra fields) .
Thanks
Terry
mdsupport wrote on Thursday, October 22, 2015:
I would strongly discourage mixing patients and guarantor in same table. “Patient” is so central to this application that this would guarantee a maintenance nightmare. If you have to have them in a table, create a new one that represents a person and break apart patient_data (simplest approach - just stop using existing fields).
If community does adopt this change, please make it completely optional.
teryhill wrote on Thursday, October 22, 2015:
5 A global control is needed for this feature.
What are you seeing as the issue with mixing them? In some instances the Guarantors are patients, does this cause a maintenance issue? How do you handle the lack of a guarantor to patient relationship now?
Just trying to understand the issues.
Thanks
Terry
mdsupport wrote on Friday, October 23, 2015:
Must confess that we do not have that big an issue which is why we would never turn the function on. If we needed, we would have explored use of issues to immediately provide that visibility without affecting standard functionality. But that is highly dependent on people involved in practices. If this were to be integrated in billing, we would have considered using insurance company as an option. Typically our patients do not have 3 insurances so it would have worked out.
Back to the challenges we see in touching patient_data :
If existing code was developed with understanding that patient_data will contain different classes of patients, your approach would work. Since existing code does have that notion, now you would need to implement it in every SQL query that references patient_data. You would now have to prevent (as examples) :
Implementing restrictions alone would involve review of 10s if not 100s of SQL statements - some of which are constructed dynamically.
If entire project had used object models, you could have implemented a filter centrally without worrying about side effects. But the project is not using such structures.
For what it is worth. 2 cents…
sunsetsystems wrote on Friday, October 23, 2015:
Terry,
I recommend you first address the problem of runaway growth of the patient_data table by creating a “vertical” table for fields that are new or not hard-referenced in OpenEMR code. Everyone seems to agree this is a good thing to do, and it would take away some of the objections to just adding guarantor attributes to the demographics layout.
Check out the commit here:
https://github.com/sunsetsystems/openemr/commits/lbt
This is some work I did to convert transactions in general to being layout-based, but I’m not promoting it yet because I’m not sure anyone cares. But it has some aspects that would be useful and instructive for you.
Look at the changes to edit_layout.php. This adds a function addOrDeleteColumn() that makes addition and deleting of fields safer and easier to code. It also disallows accidental deletion of fields that contain data.
Look at the changes to sql/4_2_0-to-4_2_1_upgrade.sql, especially where data is converted from the transactions table to the lbt_data table.
Note the enhancement to library/sql_upgrade_fx.php to support (2) above.
Other stuff in there may also be of interest.
teryhill wrote on Friday, October 23, 2015:
You are correct those things have to be coded around or limited.
Terry
teryhill wrote on Friday, October 23, 2015:
that is the thoughts behind the PID to PID relationship which would work for a vertical table it would mean the addition of at least 1 field to the Patient data table .
Thanks for the link I will look at it and by the way i could use the “not letting fields with data in them be deleted”
As I have said What I am doing is just “a way” not “the way” that is what I am trying to get to.
Still Thinking it thru
Terry
teryhill wrote on Friday, October 23, 2015:
Lets see if this is closer to what the community wants;
1 We would like to get the Guarantor information in to OpenEMR. It is generally felt that it is a needed addition.
2 We would need to at some point, preferably early on, optimize the fields in the patient data table .
3 We should move to a more efficient use of the patient data table. Starting with a vertical implementation of this feature.
4 A global control is needed for this feature.
5 Initially Statements and collections would need to be changed to support the Guarantor record . The statement would probably need to indicate which patient generated the charges.
6 A family billing option would need to be added to reports that require it.
Would we use the full guarantor tab as in the SQL file I submitted in the vertical table with a PID to PID relation by adding the Guarantor PID field to the patient record .
Thanks
Terry
teryhill wrote on Friday, October 23, 2015:
Attached is a preliminary copy of the vertical table. The patient type could be Guarantor, subscriber, etc. The field types are basically mimicking the existing field lengths in address, phones and patient data. I am sure this will change (it always does once the coding starts).
additions?
subtractions?
Terry
sunsetsystems wrote on Friday, October 23, 2015:
That’s not a vertical table. lbf_data is, for example.
bradymiller wrote on Friday, October 23, 2015:
Hi,
I agree with MD Support that should not create “pseudo” patients for the guarantors; and thus no need to worry about pid to pid mapping. Won’t just having the Guarantor tab in the demographics gui for the patient suffice with the necessary elements (name, address, etc). What benefit are you gaining by placing them in a “pseudo” patient?
Regarding the vertical table, again, not sure if necessary to force this on the unlucky developer whom has decided to add something to the patient_data table at the wrong time.
As an example of a vertical table that is already in the layout engine, can checkout the ‘Facility Specific User Information’ layout in the Administration->Layouts GUI. These data elements are all stored in the following vertical table:
(note it is “vertical” because all the facility items, no matter what they are, get stored in the field_id and field_value columns; the field_id is the label/id of the element and the field_value is the data of the element)
Here is the commit that brought in that feature:
-brady