ID, PID, Patient_ID, PubPID confusion

pfwilliams wrote on Friday, August 24, 2012:

I’ve had some time to work a little more on planning the conversion of our 15,000 patients into OpenEMR.
Populating the Patient_Data table is to be the first phase, but I keep getting to a point where I’m left scratching my head.
There is an auto-increment index named “id”, and also a unique “pid” index which must be assigned a value by the user. It appears that when a new patient is keyed in manually through the new patient dialogue that the value from “id” is copied to “pid”. So, we just have a redundant index for the patient_data table?  Which index is used internally in queries? Which index do I treat as the key to the table? Other tables like payments also have the both the “id” and “pid” columns.  Is one index used over the other consistantly for queries throughout OpenEMR?  Some tables have a “patient_id” column, is that the same as “pid” and just an example of being creative with naming conventions?   I’m fairly certain that the “pubpid” column is just for including the chart number from our current system, and is used only as a search or display field, and is not used for linkage between tables.  It appears “foreign_id” is actually “pid” in the documents table?  I feel like I’m looking at a plate of spahgetti.  Am I safe to just assume that in any table that contains both an “id” column and a “pid” column, that I should ensure that the fields contain identical values?

Pardon the rambling, disjointed message, but that’s where my thought process ended up after trying (again) to sort out how all the tables in OpenEMR are linked.

sunsetsystems wrote on Friday, August 24, 2012:

The “id” column is mostly, perhaps completely, unused.  It would be interesting to take it out and see what breaks.

For new patients, pid is computed from the currently-greatest pid value + 1.  pid and id are not necessarily the same.

Rod
www.sunsetsystems.com

cipher-naught wrote on Friday, August 24, 2012:

I have been doing some looking around as well as part of the demographics improvements.  Here is what I believe:
* Patient_Data.id - Auto-Incremented ID for the table.
* Patient_Data.pid - the actual patient_id that is used in OpenEMR.  Since MySQL only supports one Auto-Incremented field per table this is not auto-incremented and must be updated manually (as Rob pointed out).
* Patient_Data.pubpid - this is a public id that can be entered.  Such as an id for another system.

I think this is correct, but I am not 100% positive.  If you are converting data don’t forget about the insurance tables as well, which brings me to this…

Question: The Address and Phone_Numbers tables seem to use a foreign_id to reference where they come from.  But I ran into a problem with Insurance_Companies and Pharmacies.  Meaning that there is an insurance company with an ID = 1 and a Pharmacy with an ID=1.  Is there something I am missing or is that just a bug?

Thanks

-ciper-naught

sunsetsystems wrote on Friday, August 24, 2012:

phone_numbers has a “type” column that seems to indicate which foreign table, but I don’t see anything like that for addresses.  Might be a bug, definitely worth looking into.

Rod
www.sunsetsystems.com