I have a design question related to the use of the above fields. I understand the auto_increment id field and I understand the need for an External ID (pubpid) sometimes, I use that frequently. What I don’t get is what the pid field is for.
I also don’t seem to be able to consistent lookup patient records using the ID search, sometimes it works but there seem to be hidden rule related to those three fields.
I’m getting ready to load a bunch of demographic data from an external source for a customer and I was hoping to just do a straight up SQL import, but the pid field worries me.
"pid" - The unique identifier of the patient and used in all the tables to identify records that belong to a single unique individual. This is the unique identifier to use for cross table queries. Duplicates are not allowed.
"id" - the unique row identifier in a single table for the MySQL engine to do its job sorting and identifying rows in a specific table. Generally each each table starts with record "id" = 1 and autoincrements up to the number of rows. Duplicates are not allowed.
"pubpid" - Used to incorporate historical alpha-numeric chart numbers while converting from paper charts or an older system. From a developer stand point "pubpid" is not very useful and frequently a pain. In a new install where there has never been a previous system the "pid" is usually equal to the "pubpid". So it is hard to see the difference until you do something to cause them to be different. Duplicates are allowed.
"find" - The find function uses the "pupid". The "pubpid" is what gets displayed in the results of a "find" query.
I run an older accounting system and have to coordinate the older accounting id with the "pubpid". I like to force the "pubpid" to equal the "pid". However my staff frequently thwarts my efforts and I have several hundred duplicate "pubpid" that have different "pid" numbers. Half the information is in one "pid" and have the information is in th esecond "pid". I have to manually go back and reassign the bad "pid" to the good "pid" and then mark the bad "pid" not to be used.
The "id" is the unique row identifier and each table, all the tables, have an "id". but each table has a separate set. IE stop thinking of "id" as having any importance. Unless you are a developer and creating a new table this has no relevance. It is sometimes important to the develop to use a one-to-one cross table query, but this is an unusual situation.
The single most important value to a non-developer is the "pid". It is critical to tying together all of the tables, one-to-many cross table queries, and the construction of a foreign key in another table.
In my system:
The pid that identifies "Samuel T, Bowen, MD" is "6732". Every bit of data about "Samuel T. Bowen, MD" in the system contains a pid of "6732".
Each table has its own list of "id". They vary from one table to the next.
Remember in OpenEMR "id" does not equal "ID". The system is (mostly) case sensitive.
The "ID" (with capitals) is only a label in the find function and the search is really on the "pubpid".
I think that if you routinely leave the "pubpid" blank the "find" function will not work very well. Your users will only see a blank and not know what the chart number is. I have not tested this extensively.
PUBPID = PID = patient_data.id when a new patient is created, so I’m not hurting there. Unless I start using PUBPID for some external reason (like subscriber/family grouping).
So, I still think that the FIND function is looking at the wrong field for anyone that has no need to refer to legacy EMR or Charts (ie: all my customers, so far). Perhaps it should search both? SSN searches should be out moded by now, so we could chg to PID & ExtID finds instead. And label the columns to match the demographic field names for consistency.
I have a problem. When I try to insert a ptient data on a new database I get an error…#1062 - Duplicate entry ‘0’ for key 1. I find the id is on Autoincrement and there can only one field that can be Autoincrement. Is this error related to PID not being on autoincement. Can someon help me please. Thanks