Where can I find documentation on the database structure. I have begun looking at the table using phypmyadmin and converting them to Innnodb tables to insure transactions and ACID compliance. I started in alphabetical order and the first two gave no errors. The third table is ar_activity and it has a key consisting of pid, encounter, and sequence_no which are listed as PRIMARY, however sequence_no is auto_increment and thus must be a primary key when converting to Innodb table. If I convert sequence_no to primary and the combined key to unique, it converts to Innodb easily.
So, is pid = patient_id? What are encounter and sequence_no? Is encounter a foreign key?
Is there a need to document the table structures? I would be interested in doing that analysis and documentation.
Wayde Shipman
Please, please document the structure as you discover it. I would use the SQL structure comment field to make reference notes, etc so the “documentation” is mostly built into the code where possible. Then the ‘Data Dictionary’ link in phpMyAdmin will be useful.
–Tony
Tony,
I will be glad to document the structure as I discover it. Where should I post my comments? It will be helpful as I post them to get feedback, in the event (ok, when) i misinterpret a table.
Wayde
There is some information about database structure in the distribution, Documentation/SystemArchitecture.odt. This is an OpenOffice document.
ar_activity.sequence_no should not by itself be a primary or unique key. Are you certain auto_increment requires that for innodb?
lbf_data.form_id is also a tricky auto_increment field. It is the *first* field of the primary key, but cannot by itself be unique. As best I can tell, MySQL does not have any other good and straightforward way to solve this problem.
PID is an auto-increment field, than only can be changed through phpMyAdmin. The Demographics Page gives Extrenal ID the PID if External-ID is left blank. So External-ID gets PID or your own filled External-ID number. If a client is deleted, the new client input gets the next new incremental ID number.
Good to hear from you Wayde. I enjoyed meeting with you in Houston at the FOSS HealthCare Conference.
There is no current organized documentation will be extremely useful. We would love to have this in the wiki as well. I you will sign up at http://oemr.org/ I will be glad to give you wiki editing privileges to help with this documentation.
To answer your original question, there are three IDs in the patient_data table:
id - this is unique to the table and used as the primary key
pid - this is used everywhere in the database and can be considered the real patient ID
pubpid - this is used lots of places too, just to make life hard
