Database Documentation #552

pri2si17-1997 · 2017-05-24T20:32:05Z

This issue is regarding proper documentation of database and its tables. We can have simultaneous update in table structure if needed and add it here.

pri2si17-1997 · 2017-05-25T16:31:40Z

Hello @aethelwulffe @teryhill, please give the details of following tables (it will contain almost all):

addresses

What is it meant for?
Why all fields are null leaving id as it is primary key. According to me only line2 can be left as null. Rest are the important components of address and we must not store them as null.
What is plus_four and foreign_id?

amc_misc_data

What is it meant for?
There is no primary key in the table.
amc_id, map_category and map_id are referencing to other tables. So they must be foreign keys. Also date_created and date_completed are null. What is its purpose?

amendments

amendment_status is null. We can create one more option as NOT PROCESSED as default value instead of null. Or something more relevant.
pid it must be foreign key as in comments it is mentioned that Patient ID from patient_data. What is your opinion? In present it is mentioned as key.
Again its purpose. It may be default question for all of the tables as I don't know the purpose of each table. May be some irritating question.

amendments_history

No primary key. amendment_id is again a key.
Meaning of created by it is of integer type.
amendment_status is null allowed.
This table must have some reference to amendments table. So we must create a foreign key. Also if amendment_id is foreign key then also we must have a primary key in the table.

Will ask for more tables soon. Else this post will become lengthy.

aethelwulffe · 2017-05-25T17:35:41Z

addresses:

Holds address listings linked to the insurance_company and pharmacy tables. Should be expanded in use, or replaced as Nilesh gets to the smarty stuff which is what drives these tables and their admin UI's
They are either null or blank if the data is not entered. Many addresses do not have a "line 2" component. Plus four is a zip code '33781' plus four '9999' that constitutes the U.S. postal code system. The five digit zip code is good enough for most things, but for medical billing the full 9-digit zip code must be used. This is a mostly U.S. thing, but no-one shares much info on how the insurance company listings or billing systems should work in other countries, so we really only have a U.S. -centric system.
foreign_id is likely the id from the insurance_data or pharmacy tables. There is a crazy way of creating an id for things in the system from a function called GEN_ID or something like that. This function needs to die. We need real sequential indexes. The gen_id function has messed up lots of data migrations, upgrades, database cleanups and other operations.

AMC:
AMC, CQM, and Standard reporting is all outdated and of no current use. Related tables are report_results, report_itemized and many others. It is part of the clinical_rules engine. This particular table will not do anything useful for a user.
Amendments: These are amendments to clinical forms. PID is the patient id that it is linked to. Look for an amendment menu item and you will probably get it to feed some data into your records that will show the relationships. Created by will be a user table ID. The amendment history is to track who/when a record is amended. The full use of this is not something I have to deal with often, as it is a low priority feature that many clinics don't bother with. All the same, you can find a global to toggle this feature, and I believe it is on by default. Look on the patient dashboard (well below the basic demographics) to see this feature. When adding an amendment, you get a pop-up.
Locations:
---------- Find in Files ----------

Searching for the string 'amendments'...
C:\AlphaFiles\bidenis\REPOS\LibreEHR\contrib\util\language_translations\currentConstants.txt(1997,8): Enable amendments feature
C:\AlphaFiles\bidenis\REPOS\LibreEHR\contrib\util\language_translations\currentConstants.txt(5681,8): Select amendments to print
C:\AlphaFiles\bidenis\REPOS\LibreEHR\contrib\util\language_translations\currentLanguage_utf8.sql(2107,16): (2010, 'Enable amendments feature'),
C:\AlphaFiles\bidenis\REPOS\LibreEHR\contrib\util\language_translations\currentLanguage_utf8.sql(5815,16): (5718, 'Select amendments to print'),
C:\AlphaFiles\bidenis\REPOS\LibreEHR\contrib\util\language_translations\current_spreadsheet.tsv(2083,13): 2010 Enable amendments feature Toestaan van aanpassingen mogelijkheid ??????? ??????????? ???????????????? Abilita modifiche presentate ???????????? ????????? ????? dummy
C:\AlphaFiles\bidenis\REPOS\LibreEHR\contrib\util\language_translations\current_spreadsheet.tsv(5839,13): 5718 Select amendments to print Selecteer veranderingen om te printen ?????? ??????????? ?????? ???????????????? Seleziona le modifiche di stampa Vybrat zmeny k tisku ?????? ?????????? ????? dummy
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\add_edit_amendments.php(42,31): $query = "INSERT INTO amendments SET
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\add_edit_amendments.php(64,26): $query = "UPDATE amendments SET
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\add_edit_amendments.php(84,20): // Insert into amendments_history
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\add_edit_amendments.php(85,27): $query = "INSERT INTO amendments_history SET
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\add_edit_amendments.php(99,31): header("Location:add_edit_amendments.php?id=$amendment_id");
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\add_edit_amendments.php(105,29): $query = "SELECT * FROM amendments WHERE amendment_id = ? ";
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\add_edit_amendments.php(112,29): $query = "SELECT * FROM amendments_history ah INNER JOIN users u ON ah.created_by = u.id WHERE amendment_id = ? ";
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\add_edit_amendments.php(168,18): $("#add_edit_amendments").submit();
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\add_edit_amendments.php(176,24):

C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\add_edit_amendments.php(189,27):
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\demographics.php(1146,21):
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\demographics.php(1147,11):
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\demographics.php(1151,21): $widgetLabel = "amendments";
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\demographics.php(1160,34): $sql = "SELECT * FROM amendments WHERE pid = ? ORDER BY amendment_date DESC";
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\left_frame.php(30,50): $featureData['amendment']['addLink'] = "add_edit_amendments.php";
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\left_frame.php(31,47): $featureData['amendment']['listLink'] = "list_amendments.php";
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\list_amendments.php(66,7): var amendments = "";
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\list_amendments.php(67,12): $("#list_amendments input:checkbox:checked").each(function() {
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\list_amendments.php(68,5): amendments += $(this).val() + ",";
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\list_amendments.php(71,8): if ( amendments == '' ) {
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\list_amendments.php(72,34): alert("");
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\list_amendments.php(77,20): var url = "print_amendments.php?ids=" + amendments;
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\list_amendments.php(92,20):
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\list_amendments.php(96,26): $query = "SELECT * FROM amendments WHERE pid = ? ORDER BY amendment_date DESC";
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\list_amendments.php(121,39): $amendmentLink = "<a href=add_edit_amendments.php?id=" . attr($row['amendment_id']) . ">" . oeFormatShortDate($row['amendment_date']) . "";
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\main_frameset.php(32,50): $featureData['amendment']['addLink'] = "add_edit_amendments.php";
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\main_frameset.php(33,47): $featureData['amendment']['listLink'] = "list_amendments.php";
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\print_amendments.php(35,2): $amendments = $_REQUEST["ids"];
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\print_amendments.php(36,2): $amendments = rtrim($amendments,",");
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\print_amendments.php(37,2): $amendmentsList = explode(",",$amendments);
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\print_amendments.php(43,88): $query = "SELECT lo.title AS 'amendmentFrom', lo1.title AS 'amendmentStatus',a.* FROM amendments a
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\print_amendments.php(81,45): $query = "SELECT u.fname,u.lname,ah.* FROM amendments_history ah INNER JOIN users u ON ah.created_by = u.id WHERE ah.amendment_id = ?";
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\print_amendments.php(116,30): for ( $i = 0 ; $i < count($amendmentsList) ; $i++ ) {
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\print_amendments.php(117,36): $lastAmendment = ( $i == count($amendmentsList) - 1 ) ? true : false;
C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\print_amendments.php(118,20): printAmendment($amendmentsList[$i],$lastAmendment);
C:\AlphaFiles\bidenis\REPOS\LibreEHR\library\globals.inc.php(655,6): 'amendments' => array (
C:\AlphaFiles\bidenis\REPOS\LibreEHR\library\globals.inc.php(659,20): xl('Enable amendments feature')
C:\AlphaFiles\bidenis\REPOS\LibreEHR\library\log.inc(606,22): "amendments" => "patient-record",
C:\AlphaFiles\bidenis\REPOS\LibreEHR\library\log.inc(607,22): "amendments_history" => "patient-record",
C:\AlphaFiles\bidenis\REPOS\LibreEHR\patients\get_amendments.php(34,80): $query = "SELECT a.*,lo.title AS AmendmentBy,lo1.title AS AmendmentStatus FROM amendments a
C:\AlphaFiles\bidenis\REPOS\LibreEHR\patients\home.php(64,21): $("#amendmentslist").load("./get_amendments.php", { 'embeddedScreen' : true }, function() {
C:\AlphaFiles\bidenis\REPOS\LibreEHR\patients\home.php(181,34):

C:\AlphaFiles\bidenis\REPOS\LibreEHR\sql\database.sql(42,31): -- Table structure for table amendments
C:\AlphaFiles\bidenis\REPOS\LibreEHR\sql\database.sql(45,23): DROP TABLE IF EXISTS amendments;
C:\AlphaFiles\bidenis\REPOS\LibreEHR\sql\database.sql(46,29): CREATE TABLE IF NOT EXISTS amendments (
C:\AlphaFiles\bidenis\REPOS\LibreEHR\sql\database.sql(57,15): PRIMARY KEY amendments_id(amendment_id),
C:\AlphaFiles\bidenis\REPOS\LibreEHR\sql\database.sql(63,31): -- Table structure for table amendments_history
C:\AlphaFiles\bidenis\REPOS\LibreEHR\sql\database.sql(66,23): DROP TABLE IF EXISTS amendments_history;
C:\AlphaFiles\bidenis\REPOS\LibreEHR\sql\database.sql(67,29): CREATE TABLE IF NOT EXISTS amendments_history (
55 occurrence(s) have been found.

pri2si17-1997 · 2017-05-26T19:59:09Z

Hello @aethelwulffe if it is possible for you then please give a brief of each field in table, it will certainly be a great help. Although some fields are clear through their comments but if we get actual info then it will help in refactoring the database. Also which tables are linked with them.

ar_activity

Purpose.
why pid, encounter and sequence no are composite primary key?
Which tables does it refers?
payer_type should be int(1)
post_user should be foreign key as it references user.id.
Are codes integer type or string? If integer type then field type will change accordingly.

ar_session

Purpose.

array

Purpose.
No primary key.

audit_details

Purpose
audit_master_id must reference to audit_master table. No such linkage is there.
entry_identification type is mentioned as varchar. If it is integer type (making assumption from default value 1), then it should be changed.

audit_master

Purpose
what is pid?
user_id must be referencing users.
what is ip_address?

automatic_notification

Purpose? It seems to send automatic notification to patient or user. But I'm not clear about it.

background_services

name is made as primary key. According to me primary key should be some unique value. We can make it integer auto increment. Two services may have same name. What's your view?

batchcom

Purpose? What actually batch communication do?
Is patient_id referring to patients?
msg_type, msg_subject & msg_text are allowed to be null. But what I think is in communication msg should not be null. So if I'm correct then msg_type and msg_text should not be null.

pri2si17-1997 · 2017-05-26T20:01:35Z

I would like to ask one more thing, are we going to use Eloquent ORM? I am working on stuff to communicate using dummy data. (Not in this project a sample application). Some posts from forum seems to be deleted where Kevin mentioned about ORM.

aethelwulffe · 2017-05-26T21:06:05Z

Having never used ORM, I cannot say what all that involves. Perhaps you can give me an idea of footprint etc...
I do know that with a database model, and a tool such as the user-configurable layout engine that allows users to alter patient_data table and and many others as part of customizing demographics, adding fields to user created forms and the like, and the fact that adding other optional forms etc...may have the same behavior, the database model needs to be very aware of those changes.

I am really sick, and at the end of a long day, so I am going to have to be brief on the table descriptions for now.
ar_session, ar_activity, billing, and claims tables are all related, that behavior is complex. I think that may help you the most is seeing some inserts from real-use instances to help show what is going on.
The table called array has no use to my knowledge. I would grep for an insert anyway.

I don't have immediate knowledge of the audit tables. They are related to a seldom used feature of the same name. They were likely added in an effort to satisfy some regulation

Automatic notification, batchcom, and several other related comm functions are features I have never seen successfully implemented. Tony could probably say more, and I will when I have a bit more time...
...gack...I am too sick. Someone have a go. I am typing useless junk.

pri2si17-1997 · 2017-05-27T06:09:49Z

Ok @aethelwulffe No issues. You take proper rest. We can discuss it later. 🙂

Regarding ORM i'll update in the evening or night.

pri2si17-1997 · 2017-05-29T18:15:02Z

Hello @aethelwulffe can you please explain the core tables and their relationships? Also please explain the column fields if possible, they help a lot in getting the structure.

aethelwulffe · 2017-05-29T18:26:33Z

Hi Pri, I am working on just that. I am adapting the scripts that generate fake patients into a set of docs. I figure that is a guaranteed set of relational mapping.

…

On 2017-05-29 14:15, Priyanshu Sinha wrote: Hello @aethelwulffe <https://github.com/aethelwulffe> can you please explain the core tables and their relationships? Also please explain the column fields if possible, they help a lot in getting the structure. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#552 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAhzFxwE1ejBafv2XRmi33XHFVcQVIpWks5r-wsmgaJpZM4NlobB>.

pri2si17-1997 · 2017-05-29T20:48:54Z

Hello @aethelwulffe , I'm posting new structure here itself. You please check it and let me know if incorrect or any improvements required.

addresses
id int(11) primary key auto-increment, line1 varchar(255) not null, line2 varchar(255) null, city varchar(255) not null, state varchar(255) not null, zip varchar(10) not null, plus_four varchar(4) not null, country varchar(255) not null, foreign_id foreign key (references insurance_comapnies.id)
amendments
id int(11) primary key auto-increment, amendment_data date not null, amendment_by varchar(50) not null, amendment_status int(1) not null, amendment_desc text null, pid foreign key references patient_data(pid), created_by foreign key references Users(id), modified_by foreign key references Users(id), created_time timestamp not null, modified_time timestamp not null
amendments_history
id int(11) primary key auto-increment, amendment_note text not null, amendment_status int(1) not null, created_by foreign key references Users(id), created_time timestamp not null

Note

amendments_history can be somewhat more clear. I need to discuss with you about it again.
choosing status as : 0 -> Not Processed, 1->Accepted ,2-> Rejected. This require just int field.
modified_by initially is set to be null, but it should not. Ideally when first record is saved, created by and modified by will remain same, and if the same amendment is updated, then just update modified_by field. Same is the case with modified_time.

Also please brief me about ar_activity & ar_session.

aethelwulffe · 2017-05-31T04:35:43Z

All the above looks fine. Please drop the array table. Also, config and config_seq as these tables have not been in use for more than 15 years, and I cannot find one instance of use of them. They might have been part of SMARTY.
audit_details and audit_master are used in a Continuity of Care record/document import.

aethelwulffe · 2017-05-31T05:30:30Z

documents_legal_detail, documents_legal_categories and documents_legal_master are not used in the code base.
Any tables named ICD9 should be dropped.

ar_session tracks each time a payment is entered into the system.
Here is a sample row (without quotes that would be needed as an insert):

(12539,2160,223,0,ePay - 058952209,2017-05-24,2017-05-24,804.18,2017-05-24 08:23:42,2017-05-24 08:53:37,0.00,insurance, insurance_payment,2017-05-24,0,electronic);

ar_activity tracks payment activity against each line item (that is billable) in the billing table.
Here are four sample insert rows showing a payment, and an adjustment and other entries for just one service code that was billed. It was paid by the transaction in the above ar_session entry:

(574 ,150759 ,3 ,T1015 ,,2 ,2017-05-24 08:49:23 ,223 ,12539 ,,58.00 ,0.00 ,2017-05-24 08:53:37 ,,,ISP ,,HCPCS)
(574 ,150759 ,4 ,T1015 ,,2 ,2017-05-24 08:49:23 ,223 ,12539 ,Ins adjust Ins2 ,0.00 ,30.00 ,2017-05-24 08:53:37 ,,,IA ,NULL,HCPCS)
(574 ,150759 ,5 ,T1015 ,,2 ,2017-05-24 08:49:23 ,223 ,12539 ,Deductable $2.00 ,0.00 ,0.00 ,2017-05-24 08:53:37 ,,,Deduct ,NULL,HCPCS)
(574 ,150759 ,6 ,T1015 ,,2 ,2017-05-24 08:49:23 ,223 ,12539 ,,0.00 ,0.00 ,2017-05-24 08:53:37 ,y ,co-pay,,NULL,HCPCS)

claims table records each claim item date of service and the claim file names that are produced for export by the system. I have no idea (or can't recall at the moment) why it makes a double entry with a bunch of NULL's for each one...

(574 ,54274 ,1 ,2160 ,2 ,1 ,0 ,2013-04-17 19:01:53 ,NULL,NULL,NULL,0)
(574 ,54274 ,2 ,2160 ,2 ,-1 ,2 ,2013-04-17 19:01:53 ,2013-04-17 19:01:53 ,2013-04-17-1901-batch.txt ,cms ,1822)

Naturally, all the above are related to the billing table, the entries of which are related to the form_encounter table, the entries of which are related to the forms table, the entries of which are related to the patient_data table.

pri2si17-1997 · 2017-05-31T21:46:53Z

Hello @aethelwulffe need some info for patient_data table. I am posting column names (few) and please tell me what are they and if they are related to other, and from where the data comes. I will also mention my views on that :

title (It may be like Mr. or something else.)
financial
pharmacy_id (always 0. As in code only it is set as 0.)
referrer
referrerID (might be related to user table. Not sure about it)
ethnoracial
race
homeless (Data is null. It must be from social statistics)
pubpid
pid (What is the need of this. It seems to be patient id. But can't be it primary key of table.
According to me it should be primary key of table. What you say?)
11.genericname1
genericval1
genericname2
genericval2
squad
fitnes (Always set to be 0)
17.usertext1 - usertext8
18.userlist1 - userlist7
19.pricelevel
regdate (It must be the date when patient created.)
contrastart
completed_ad
ad_reviewed
soap_import_status
care_team

This are unfigured. Rest I have broken down into 7 tables. I will post it tomorrow. You please clear the above.

pri2si17-1997 · 2017-06-01T05:35:43Z

Hello @aethelwulffe Can this address table can be used to store address of patient and employer?

aethelwulffe · 2017-06-01T05:49:25Z

I see no reason why it should not. The interface(s) have to determine how that touchlist works though. That involves the damn layout engine, which likes to make changing how anything is done very difficult.

…

On 2017-06-01 01:35, Priyanshu Sinha wrote: Hello @aethelwulffe <https://github.com/aethelwulffe> Can this |address| table can be used to store address of patient and employer? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#552 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAhzFzskzkZcdZJwIUVs146tg-GUU4mxks5r_k2wgaJpZM4NlobB>.

pri2si17-1997 · 2017-06-01T16:14:07Z

Hello @aethelwulffe . I am now posting structure for patient_data. This does not contain fields posted above. Also I have used Address table to store patient's & employer's address. It is sub divided into :
1 . patient_face_sheet
2. patient_contacts
3. patient_privacy
4. patient_employer
5. patient_social_statistics
6. patient_data

Now structure of each table :

patient_face_sheet
id int(11) auto-increment primary key, f_name varchar(100) not null, m_name varchar(100) null, l_name varchar(100) not null, DOB date not null, marietal_status varchar(10) not null, s.s.n varchar(256) not null unique, license_id varchar(100) not null, email varchar(100) not null, sex varchar(6) not null, billing_note text null

Note : I kept SSN for varchar(256) because I think they must not be stored in plain text. They must be encrypted. Unique because it must be unique for one person.

patient_contacts
id int(11) auto-increment primary key, providerID int(11) not null, ref_providerID int(11) not null, phone_home vacrhar(10) not null, phone_biz varchar(10) not null, phone_contact varchar(10) not null, contact_relationship varchar(100) not null, mothersname varchar(100) not null, gaudiansname varcahr(100) not null, county varchar(10) not null, country_code varchar(100) not null

Note : Initially providerID and ref_providerID are defined to be NULL. Also last column is country_code but we are storing name of country.

patient_privacy
id int(11) auto increment primary key, allow_patient_portal not null int(1) default 0, allow_health_info_ex not null int(1) default 0, allow_imm_info_share not null int(1) default 0, allow_imm_reg_use not null int(1) default 0, hippa_mail not null int(1) default 0, hippa_voice not null int(1) default 0, hippa_message not null varcahr(25) , hippa_allowsms not null int(1) default 0, hippa_allowemail not null int(1) default 0, vfc int(1) default 0, email_direct varchar(50) not null, deceased_reason varchar(50) not null, deceased_date date not null

Note : 0 -> No/Disable & 1 -> Yes/Enable

patient_employer
id int(11) auto-increment primary key, name varchar(60) not null, addressId int(11) foreign key references address.id
patient_social_statistics
id int(11) auto-increment primary key, ethnicity varcahr(50) not null, religion varcahr(50) not null, interpreter varchar(255) not null, migrantseasonal varchar(9) not null, family_size int(2) not null, monthly_income decimal(10, 2) not null, homeless int(1) not null, financial_review datetime not null, language varchar(100) not null

Note : homeless assuming to be boolean like yes/no.

patient_data
id int(11) auto-increment primary key, title varchar(10) not null, occupation varchar(100) not null, industry varcahr(100) not null, addressID int(11) foreign key references address.id, patientFaceSheetID int(11) foreign key references patient_face_sheet.id, patientContactsID int(11) foreign key references patient_contacts.id, patientPrivacyID int(11) foreign key references patient_privacy.id, patientEmployerID int(11) foreign key references patient_employer.id, patientSocialID int(11) foreign key references patient_social_statistics.id

Note : Relationships which I considered is :

patient_address (one-to-many as each patient can have one address but one address can belong to many patients)
patinet_facesheet (one-to-one)
patinet_privacy (one-to-one)
patinet_contacts (one-to-many as each patient can have one contact but one contact can belong to many patient. It can be many-to-many)
patinet_social_statistics (one-to-one)

Please look at it and correct me wherever I'm wrong. Also provide the details of fields mentioned in above post.

aethelwulffe · 2017-06-01T17:29:42Z

None of it is necessarily wrong. Important things to note:
I hope that "patinet_" in the above lists is just a typo...and you mean "patient_" :)

Drop SSN. It is not, and should not be used in a medial setting...for anything...ever.

The abbreviation "pid" or patient ID is very common in the medical industry, and it descriptive enough as a UUID field for a patient record. Just as importantly, it is the record (vs row index) touchlist field that almost everything else in the EHR looks for. I know it is (initially) the same as the index id field, but with data migrations and integrations from other systems, keeping it is probably a good idea. "pubpid" is extraneous though. If you are using just the PID as a UUID field in all the 1:1 or one-to-many associations, you don't really need record row indexes for the other tables.
An existing example of this is the insurance_data table, or issues, forms or any number of other tables that are one-to-many relationships to the existing patient_data table.

I assume you are taking the layout engine that drives the current UI into account. It uses other tables than patient_data, but each table has a tab grouping and ordering layout configuration (with additional data types) it uses to make up the displays. It can all be adapted to new table configurations of course, with or without removing the user's ability to alter the base table schema. The preference would be to keep the base patient data type tables clean, then use a join against "custom_demographics" or something like that to provide a place for user-configured fields to be added, while preserving layout utilities (unless someone is replacing the whole Layout Engine and Layout Based Forms system).

Some other suggestions:
patient_contacts like phone_biz and other listings: Treat these as a one to many contact type listings much like in the FHIR API. As a matter of fact, the closer you make the patient demographics or anything else like the FHIR api, I think the better off we will be. It represents a pretty darn good schema.
Related to this is the patient privacy table you have above. If the contact list is a vertical listing, the privacy table should simply control what is allowed to be used out of the list, and where.
The patient privacy section should be looked at closely just to make sure nothing extraneous is in there, and we know what those flags actually control within the application. Anything with the letters "hippa_" in the name should be looked at with deep suspicion. Those where added by someone that was not even competent enough to know that the abbreviation is "HIPAA" not HIPPA, and HIPAA is a US-only thing. While the function of validating what lines of communication the patient has authorized and in what way those lines of communication should be used is very important, they are international issues, and need field names, not misspelled references to a piece of healthcare privacy legislation that predated the concept of email. It should simply represent control objects for how we can use the contacts.
Example:
Patient has their Mother as an emergency contact. The privacy control flags should be able to say "You can call this person if I die on the exam table, but do not communicate any of my medical records to my them." This is useful when you don't want your mom to know you are being treated for V.D. The patient might also want to permit them to be able to request anything at all. They may also choose to simply exclude this contact from dealing with bills or the like.

ProviderID: This should be a one-to-many association. A patient may have many providers in a clinic. An associative note or list (customizable) for that relationship would be useful. If the list printed out on the screen

Primary Provider: Sue X -MD
Cardiologist: Jerry Y -IM
Physical Therapist: Mary Z -LPT
Behavioral Counselor: Mohd A -LMHC
the data would be more useful than just a single arbitrary provider listing.

Getting the fields down to the bare minimum, then making all others either custom or optional standard additions to the schema on demand would be my preferred way of designing this.

The patient privacy section should be looked at closely just to make sure nothing extraneous is in there, and we know what those flags actually control within the application. Anything with the letters "hippa_" in the name should be looked at with deep suspicion. Those where added by someone that was not even competent enough to know that the abbreviation is "HIPAA" not HIPPA, and HIPAA is a US-only thing. While the function of validating what lines of communication the patient has authorized and in what way those lines of communication should be used is very important, they are international issues, and need field names, not misspelled references to a piece of healthcare privacy legislation that predated the concept of email.

pri2si17-1997 · 2017-06-02T05:32:04Z

Hello @aethelwulffe

"pubpid" is extraneous though. If you are using just the PID as a UUID field in all the 1:1 or one-to-many associations, you don't really need record row indexes for the other tables.

This means I should remove it. Also I will drop SSN and include pid.

I assume you are taking the layout engine that drives the current UI into account. It uses other tables than patient_data, but each table has a tab grouping and ordering layout configuration (with additional data types) it uses to make up the displays. It can all be adapted to new table configurations of course, with or without removing the user's ability to alter the base table schema. The preference would be to keep the base patient data type tables clean, then use a join against "custom_demographics" or something like that to provide a place for user-configured fields to be added, while preserving layout utilities (unless someone is replacing the whole Layout Engine and Layout Based Forms system).

Probably this is for all the above columns which I mentioned. right? So I will create another table and get that linked with patient data.

The patient privacy section should be looked at closely just to make sure nothing extraneous is in there, and we know what those flags actually control within the application. Anything with the letters "hippa_" in the name should be looked at with deep suspicion. Those where added by someone that was not even competent enough to know that the abbreviation is "HIPAA" not HIPPA, and HIPAA is a US-only thing. While the function of validating what lines of communication the patient has authorized and in what way those lines of communication should be used is very important, they are international issues, and need field names, not misspelled references to a piece of healthcare privacy legislation that predated the concept of email. It should simply represent control objects for how we can use the contacts.

I am not able to get it. Can you please elaborate it more?

ProviderID: This should be a one-to-many association. A patient may have many providers in a clinic. An associative note or list (customizable) for that relationship would be useful. If the list printed out on the screen

Primary Provider: Sue X -MD
Cardiologist: Jerry Y -IM
Physical Therapist: Mary Z -LPT
Behavioral Counselor: Mohd A -LMHC
the data would be more useful than just a single arbitrary provider listing.

Ok. Right now I have implemented as one-to-many only. Like one patient can have one provider, but one provider can have many patient. But it seems to be many-to-many, that's what I mentioned in Note. :D Like one patient can have many provider and one provider can have many patient. Or there is something else?

aethelwulffe · 2017-06-02T12:44:39Z

Providers should be many-to-many...right...sort of. It is actually one (patient) to many (Providers) but each provider is a list that is used by all patients. We look up a provider's patients by joining the patient table where ProviderID IN(). It may be better to leave this relationship as an expanded custom field where someone adds more lists of providers to their flat table if they want. This will let them add a label to the field and all that...I dunno.
I may not be sure about the provider list thing at the moment, but the contact stuff I can probably explain better.
You have a patient.
That patient has a list of contact types.
The following would belong to patient /#1, and by association agent /#1:

(owner,foreign_id,type,label,value,priority,permissions)VALUES
(patient_data,1,'tel', 'Cell Phone','123-456-7890',1,1),
(patient_data,1,'email','Secure Email','jane.doe@myhiddenserver.com',2,1),
(patient_data,1,'agent', 'Spouse','1',1,1),
(agents,1,'tel','Spouse Cell','123-456-7891',2,5),
..each row can have a contact priority by type (or overall), as well as one or more "permission" attribute fields to control what type of communications are permissible.
The patient can also have a list of other people associated with them. In house here we refer to them as "agents". This list of people could go something like:
(owner,foreign_id,type,label,fname,lname,priority,permissions,is_patient)VALUES
(patient_data,1,'spouse','Spouse','John', 'Doe',2,1,2),
The above indicates that not only is John a linked contact for patient 1's record, but John is also a patient with pid = 2. The system will know that, but only for contact updating purposes, not to ever report that fact as a data disclosure

I am sure the FHIR standard has a more complete or comparable system for this. I know we do for some of our evaluation forms that link all sorts of data to case management studies. The important bit is to control access and prioritize contacts.

pri2si17-1997 · 2017-06-02T14:53:33Z

Ok @aethelwulffe Now I got it. Sure I will make this and will let you know. Just confirm me few things...

Is there any issue with privacy table? (HIPPA and all.. I will prioritise contacts, take this as consideration) What I think is that let the contacts be created as it is, and then we should have one table like patient_priority and there we can have reference to patient, contact and privacy. And from above design I would like to remove links for contacts and privacy.
Am I moving in right direction or anything need to be changed?

aethelwulffe · 2017-06-02T15:28:31Z

Privacy table is about how to treat contact information. If you put that information in the actual contact listings (people, phone numbers etc...) then you don't need a privacy table itself, as you now have granular control over the behavior of each item.
Yes, you have been moving in the right direction. This is just a big move, it affects a lot of things, and we are going to have to collaborate hard to see things to the end.
I think you may be seeing how bad the schema is right now, and how deeply invested the UI and business logic is in this bad schema. While this represents huge change, and will make a lot of folks very nervous, they also all know that tables like patient_data and users have been needing this a very long time. This is going to be a very big job just moving things around and getting it to all work again.

My advice is still to go through the tables and get rid of totally unneeded tables and fields before actually changing base table structures. That would be the easier and early deliverable. The users table(s) are actually much simpler to revise than the patient_data by a large factor, due to the fact they are called less often, and affect fewer things. They still require some of the same ideas to refactor them properly. patient_data is found 833 times in the code base.

I just realized that not only is the new "patient portal" (I think everything under webroot/patient) chock full of hard-coded field names that seem like would cause everything to break if anything were changed, but that whole thing would need to be refactored too....

pri2si17-1997 · 2017-06-02T20:03:10Z

@aethelwulffe

Now I got that. You mean that patient's contact may be a patient itself or not. Also a patient can prioritise his/her contact's list in order to send data in case of emergency. If he/she sets the priority then he/she must share privacy communication means like email, phone, etc.

I will have to look at this and will let you know.

My advice is still to go through the tables and get rid of totally unneeded tables and fields before actually changing base table structures. That would be the easier and early deliverable.

Ok. Will soon present the list. 🙂

pri2si17-1997 · 2017-06-02T21:08:30Z

@aethelwulffe

These are the immediate list of tables to be deleted. I will update the list while I proceed.

array
categories_seq
documents_legal_detail
documents_legal_categories
documents_legal_master
icd9_dx_code
icd9_dx_long_code
icd9_sg_code
version
sequences
employer_data

pri2si17-1997 · 2017-06-03T22:31:05Z

Hello @aethelwulffe ...

It seems that most of the tables are related either with patient or users. So I think I must go for them first. And regarding unused tables, it requires yours and @teryhill collaboration, as I grepped most of the tables (will not say all) and they are used in code. So I can't figure out whether they are in use or not. So please assist me in this task.

pri2si17-1997 · 2017-06-05T19:58:53Z

Hello @aethelwulffe @teryhill , please look at the following structure :

Keeping patient _contacts table same.
create patient_contact_link table like this :

id int(11) auto-increment primary key, pid foreign key references patient_data.id, contact_id foreign key references patient_contacts.id

Note : This will create many to many relationship.

create privacy_contacts table like this :

id int(11) auto-increment primary key, contact_id foreign key references patient_contacts.id, patient_id foreign key references patient_data.id, allow_patient_portal not null int(1) default 0, allow_health_info_ex not null int(1) default 0, allow_imm_info_share not null int(1) default 0, allow_imm_reg_use not null int(1) default 0, vfc int(1) default 0, email_direct varchar(50) not null, deceased_reason varchar(50) not null, deceased_date date not null

create contacts_communication table :

id int(11) auto_increment primary key, contatct_id foreign key references patient_contacts.id, patient_id foreign key references patient_data.id, hippa_mail not null int(1) default 0, hippa_voice not null int(1) default 0, hippa_message not null varcahr(25) , hippa_allowsms not null int(1) default 0

Example :

**patient_contacts** table :

id | email | ....................

| pri_contact@contact.com |.........
| art_contact@contact.com | ..........
| terry_contact@contact.com | ..........

-patient_data table :
**Note : ** It should be assumed that patient_data contains reference to face sheet, employer, and all related tables. Taking name just for example .

id | name | ...............

| Priyanshu Sinha | ..............
| Art Eaton | ................
| Terry Hill | ...........

-patient_contact_link table :

id | pid | contact_id
1 | 1 | 2
2 | 1 | 3
3 | 2 | 1
4 | 2 | 3
5 | 2 | 2

-privacy_contacts table :

id | contact_id | patient_id | allow_health_info | allow_imm_info | allow_patient_portal | .......
1 | 2 | 1 |Y | Y | N | N | ....
2 | 3 | 1 | N | N | Y | Y | ....

Note : This way we can see that, pid = 1 has two contacts 2 & 3. But different information are shared with contact 2 and contact 3.

-contacts_communication table :

id | contact_id | patient_id | hippa_email | hippa_voice | .........
1 | 2 | 1 | Y | N | ...
1 | 3 | 1 | N | N | Y | ....

same explanation as above.

You please look at it and tell me if this is ok? If anything I'm missing then please let me know, I'll correct it.

**Side Note : **

mothersname & gaurdiansname can be dropped, as I find no use of storing contact's mother's name and gaurdians name.
hippa_* column should be renamed appropriately.
hippa_email & hippa_allowemail seems to be same. One of them can probably be dropped.
contact_relationship column can be renamed as label as suggested by you.
address table should have unique index. I am thinking of zip code as this can be used whenever a new patient enters its address. Although this is not full-proof solution, but can be thought of. It will reduce
some of our data.
email_direct seems to patient's email. If this is the case then it can also be dropped.`
contact_email should be one field stored in patient_contacts table. It will be unique, so we can easily check whether this contact is already in our database or not. Or we have to make one of the phone numbers to be unique. I would prefer email. what you say?

Also can you please brief me about the users table. There are many fields that are Null, when I created a new user. A complete info of each column can be very helpful.

pri2si17-1997 · 2017-07-23T16:42:45Z

Ohh... sorry @aethelwulffe . I thought that globals tab are static and will not change. 😞

aethelwulffe · 2017-07-23T16:44:11Z

Well, the good part is that we just merged a search tool for the Globals interface...makes things simpler on the UI side.

pri2si17-1997 · 2017-07-23T17:55:16Z

Hello @aethelwulffe , I got your point. Increasing tables will only help in easy maintenance. My mistake.

aethelwulffe · 2017-07-23T18:09:53Z

Not a mistake...easy maintenance is nice. Needing multiple joins in queries makes it less nice though, and there are other issues of course.

…

On 2017-07-23 13:55, Priyanshu Sinha wrote: Hello @aethelwulffe <https://github.com/aethelwulffe> , I got your point. Increasing tables will only help in easy maintenance. My mistake. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#552 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAhzF6gRupQJYEL-TAKaqIu507hrLhWuks5sQ4kFgaJpZM4NlobB>.

pri2si17-1997 · 2017-07-23T18:22:22Z

Yeah.. And shall we create another thread for discussion? You start that with billing information.. 😄

aethelwulffe · 2017-07-23T19:04:37Z

Heh. Yeah, I will start with finding all the billing stuff in this thread...and pulling it over there!

pri2si17-1997 · 2017-07-24T16:31:31Z

Hello @aethelwulffe ,

What is gl_index in globals. It's default value is 0 and in table also its value is 0. But from the code in edit_globals.php I think it is index field index i.e, at what index that field is present in UI. (Correct me if I'm wrong.) But I don't know its actual working. Please tell me.

pri2si17-1997 · 2017-07-24T18:00:40Z

one more edit in globals. Why not have json field type?

As i see that globals table is no where related to other tables, so we do not have any foreign key constraints. Also, not every value is varchar, some are boolean also, so using this will give us freedom of this. And in future if we are supposed to add new fields, we can add it.

What you say?

aethelwulffe · 2017-07-24T18:36:52Z

I'd say I am lost. Perhaps re-state the questions, or someone else can chime in.

pri2si17-1997 · 2017-07-24T18:42:52Z

@aethelwulffe , some thing like this :

[
        {
                "tab_name" : 'Appearance',
                "Values" : {
                                "default_first_tab" : 'Dynamic Finder',
                                "navigation_area_width" : 150,
                                .............
                                .............
                        }
        },
        {
                "tab_name" : 'Locale',
                "Values" : {
                                "translate_layouts" : true,
                                "default_language" : 'English(standard)',
                                ...............
                                ...............
                        }
        },
        ............
        ............
]

This will get stored in a field. something like 'settings'.

teryhill · 2017-07-24T18:46:07Z

@pri2si17-1997 globals is like a config table. It holds the settings from globals. The user _settings file interacts with the globals table. You need to consider the user_settings table when you make changes to the globals table. To add the json items you will need to change /interface/super/edit_globals.php

pri2si17-1997 · 2017-07-24T18:49:17Z

Hello @teryhill , I am asking that is this type of structure ok?

Some more information :

id int(11) primary key autoincrement,
settings json

And the above structure will get saved in settings column.

@aethelwulffe Am I clear now?

teryhill · 2017-07-24T19:41:30Z

@pri2si17-1997 What would be the advantage to it? Will it require extensive program changes to use it? Gloabls are used through out the system.

pri2si17-1997 · 2017-07-24T19:48:53Z

@teryhill

Advantages mainly include flexibility and separation of values in the sense that we will know which values are enabled/stored for which tab (that's why I included tab_name in example.). We can add any no of information to that field, and at the time of updation, we need to look for that particular key in data, and just update that value (using key-value pair).

Regarding extensive program change, I may not be able to provide exact answer right now, as it needs a lot of query to be revised, so may be YES.

teryhill · 2017-07-24T19:52:12Z

I would say add the field, put the coding to use it far down the priority list.

pri2si17-1997 · 2017-07-24T19:54:26Z

add the field

Means, add a json field? Or leave it as in the present structure?

teryhill · 2017-07-24T19:57:57Z

Unless @aethelwulffe see's a reason for not adding the json field. I vote add it.

pri2si17-1997 · 2017-07-24T19:58:41Z

ok. Thanks @teryhill ... 🙂
Waiting for @aethelwulffe response.

aethelwulffe · 2017-07-24T20:03:52Z

I said I am lost. If Terry said do it, then do it...and he did say do it...so go ahead and do it! 🤣

pri2si17-1997 · 2017-07-24T20:05:11Z

ok. Thanks @aethelwulffe @teryhill ... 😃

teryhill · 2017-07-24T20:09:37Z

@pri2si17-1997 I hope you know this project will have work even after GSoC. You have a lot of Items that are going to be hidden until this is implemented and tested. Then more Idea's will be generated. Actually all 3 projects are in this same situation.

pri2si17-1997 · 2017-07-24T20:13:34Z

Yes @teryhill . I know it and yes I will contribute even after GSoC as I took the responsibility for Laravel, so I must complete it. 🙂

teryhill · 2017-07-24T20:17:27Z

A man with integrity , I like that

pri2si17-1997 · 2017-07-26T15:02:52Z

Hello @aethelwulffe @teryhill

Some Questions :

Can you please explain information_source, refusal_reason and ordering_provider in immunizations table? I can't find any grep result.
Is immunization_id same as row_id?
There is no use of administered_by as in comment it is mentioned that Alternative to administered_by_id. So this can be dropped.

teryhill · 2017-07-26T15:19:46Z

The refusal reason , ordering provider and administered by are things that are needed in the future. Don't drop them. @aethelwulffe this was stuff that was being worked on at the fork for MU.

pri2si17-1997 · 2017-07-26T15:25:28Z

Ok @teryhill and what about point 2 & 3 ?

teryhill · 2017-07-26T15:38:25Z

keep point 3. point 2 no they are not the same here is a code snipit. Not sure what the immunization id is.

                 id = ?,
                  administered_date = if(?,?,NULL),  
                  immunization_id = ?,
                  cvx_code = ?,

pri2si17-1997 · 2017-07-26T15:54:26Z

Ok... :|

aethelwulffe · 2017-07-26T15:55:14Z

immunization_id may be, or is intended to be, the ID from the code list (not the CVX code though, the administration CPT4 or HCPCS), or possibly a warehouse batch number. These would not be the same as the index. CPT CODE CPT Description CVX Code 90698 Diphtheria, tetanus toxoids, and acellular pertussis vaccine, haemophilus influenza Type B, and poliovirus vaccine, inactivated (DTaP - Hib - IPV), for intramuscular use 120

…

On 2017-07-26 11:38, Terry Hill wrote: keep point 3. point 2 no they are not the same here is a code snipit. Not sure what the immunization id is. |id = ?, administered_date = if(?,?,NULL), immunization_id = ?, cvx_code = ?, | — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#552 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAhzF6F95icFiD2Eex98zY6Ng5IkN2PCks5sR11xgaJpZM4NlobB>.

aethelwulffe added Feature: DATABASE Discussion GSOC 2017 Re-Factoring labels May 25, 2017

aethelwulffe assigned aethelwulffe and pri2si17-1997 May 25, 2017

pri2si17-1997 mentioned this issue Jul 26, 2017

Database Documentation (II) #647

Open

Database Documentation #552

Database Documentation #552

Comments

pri2si17-1997 commented May 24, 2017

pri2si17-1997 commented May 25, 2017

aethelwulffe commented May 25, 2017 • edited

pri2si17-1997 commented May 26, 2017

pri2si17-1997 commented May 26, 2017

aethelwulffe commented May 26, 2017

pri2si17-1997 commented May 27, 2017

pri2si17-1997 commented May 29, 2017

aethelwulffe commented May 29, 2017 via email

pri2si17-1997 commented May 29, 2017 • edited

aethelwulffe commented May 31, 2017

aethelwulffe commented May 31, 2017 • edited

pri2si17-1997 commented May 31, 2017

pri2si17-1997 commented Jun 1, 2017

aethelwulffe commented Jun 1, 2017 via email

pri2si17-1997 commented Jun 1, 2017

aethelwulffe commented Jun 1, 2017

pri2si17-1997 commented Jun 2, 2017

aethelwulffe commented Jun 2, 2017

pri2si17-1997 commented Jun 2, 2017

aethelwulffe commented Jun 2, 2017

pri2si17-1997 commented Jun 2, 2017

pri2si17-1997 commented Jun 2, 2017 • edited

pri2si17-1997 commented Jun 3, 2017 • edited

pri2si17-1997 commented Jun 5, 2017 • edited

pri2si17-1997 commented Jul 23, 2017

aethelwulffe commented Jul 23, 2017

pri2si17-1997 commented Jul 23, 2017

aethelwulffe commented Jul 23, 2017 via email

pri2si17-1997 commented Jul 23, 2017

aethelwulffe commented Jul 23, 2017

pri2si17-1997 commented Jul 24, 2017 • edited

pri2si17-1997 commented Jul 24, 2017

aethelwulffe commented Jul 24, 2017

pri2si17-1997 commented Jul 24, 2017 • edited

teryhill commented Jul 24, 2017

pri2si17-1997 commented Jul 24, 2017 • edited

teryhill commented Jul 24, 2017

pri2si17-1997 commented Jul 24, 2017

teryhill commented Jul 24, 2017

pri2si17-1997 commented Jul 24, 2017

teryhill commented Jul 24, 2017

pri2si17-1997 commented Jul 24, 2017 • edited

aethelwulffe commented Jul 24, 2017

pri2si17-1997 commented Jul 24, 2017

teryhill commented Jul 24, 2017

pri2si17-1997 commented Jul 24, 2017

teryhill commented Jul 24, 2017

pri2si17-1997 commented Jul 26, 2017

teryhill commented Jul 26, 2017

pri2si17-1997 commented Jul 26, 2017

teryhill commented Jul 26, 2017

pri2si17-1997 commented Jul 26, 2017

aethelwulffe commented Jul 26, 2017 via email

aethelwulffe commented May 25, 2017 •

edited

pri2si17-1997 commented May 29, 2017 •

edited

aethelwulffe commented May 31, 2017 •

edited

pri2si17-1997 commented Jun 2, 2017 •

edited

pri2si17-1997 commented Jun 3, 2017 •

edited

pri2si17-1997 commented Jun 5, 2017 •

edited

pri2si17-1997 commented Jul 24, 2017 •

edited

pri2si17-1997 commented Jul 24, 2017 •

edited

pri2si17-1997 commented Jul 24, 2017 •

edited

pri2si17-1997 commented Jul 24, 2017 •

edited