Database Documentation

[pri2si17-1997]

Hello @aethelwulffe @teryhill @tmccormi

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]

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

• addresses

1. What is it meant for?

2. 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.

3. What is plus_four and foreign_id?

• amc_misc_data

1. What is it meant for?

2. There is no primary key in the table.

3. 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

1. amendment_status is null. We can create one more option as NOT PROCESSED as default value instead of null. Or something more relevant.

2. 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.

3. 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

1. No primary key. amendment_id is again a key.

2. Meaning of created by it is of integer type.

3. amendment_status is null allowed.

4. 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]

addresses:

1. 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
2. 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.
3. 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): <?php echo xlt('Back');?> C:\AlphaFiles\bidenis\REPOS\LibreEHR\interface\patient_file\summary\demographics.php(1146,21): <?php if ($GLOBALS['amendments']) { ?> 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("<?php echo xls('Select amendments to print'); ?>"); 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 = "" . 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]

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

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

• ar_session

1. Purpose.

• array

  1. Purpose.
  2. No primary key.

• audit_details

  1. Purpose
  2. audit_master_id must reference to audit_master table. No such linkage is there.
  3. 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

  1. Purpose
  2. what is pid?
  3. user_id must be referencing users.
  4. what is ip_address?

• automatic_notification

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

• background_services

  1. 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

  1. Purpose? What actually batch communication do?
  2. Is patient_id referring to patients?
  3. 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]

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]

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]

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

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

[pri2si17-1997]

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]

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 https://github.com/LibreHealthIO/LibreEHR/issues/552#issuecomment-304710104, or mute the thread https://github.com/notifications/unsubscribe-auth/AAhzFxwE1ejBafv2XRmi33XHFVcQVIpWks5r-wsmgaJpZM4NlobB.

[pri2si17-1997]

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

1. 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)

2. 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

3. 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

1. amendments_history can be somewhat more clear. I need to discuss with you about it again.
2. choosing status as : 0 -> Not Processed, 1->Accepted ,2-> Rejected. This require just int field.
3. 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]

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]

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]

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 :

1. title (It may be like Mr. or something else.)
2. financial
3. pharmacy_id (always 0. As in code only it is set as 0.)
4. referrer
5. referrerID (might be related to user table. Not sure about it)
6. ethnoracial
7. race
8. homeless (Data is null. It must be from social statistics)
9. pubpid
10. 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
11. genericval1
12. genericname2
13. genericval2
14. squad
15. fitnes (Always set to be 0) 17.usertext1 - usertext8 18.userlist1 - userlist7 19.pricelevel
16. regdate (It must be the date when patient created.)
17. contrastart
18. completed_ad
19. ad_reviewed
20. soap_import_status
21. 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]

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

[aethelwulffe]

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 https://github.com/LibreHealthIO/LibreEHR/issues/552#issuecomment-305393697, or mute the thread https://github.com/notifications/unsubscribe-auth/AAhzFzskzkZcdZJwIUVs146tg-GUU4mxks5r_k2wgaJpZM4NlobB.

[pri2si17-1997]

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 :

1. 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.

2. 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.

3. 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

4. patient_employer id int(11) auto-increment primary key, name varchar(60) not null, addressId int(11) foreign key references address.id

5. 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.

6. 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 :

1. patient_address (one-to-many as each patient can have one address but one address can belong to many patients)
2. patinet_facesheet (one-to-one)
3. patinet_privacy (one-to-one)
4. 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)
5. 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]

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 phonebiz 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]

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]

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]

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

1) 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.

2) Am I moving in right direction or anything need to be changed?

[aethelwulffe]

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]

@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. :slightly_smiling_face:

[pri2si17-1997]

@aethelwulffe

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

1. array
2. categories_seq
3. documents_legal_detail
4. documents_legal_categories
5. documents_legal_master
6. icd9_dx_code
7. icd9_dx_long_code
8. icd9_sg_code
9. version
10. sequences
11. employer_data

[pri2si17-1997]

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]

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

1. Keeping patient _contacts table same.

2. 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.

3. 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

4. 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 | ....................

1. | pri_contact@contact.com |.........
2. | art_contact@contact.com | ..........
3. | 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 | ...............

1. | Priyanshu Sinha | ..............
2. | Art Eaton | ................
3. | 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.

[aethelwulffe]

Contact relationship is a contact table field for certain. It may be possible to do the privacy bit in the contact table as well, but it is certainly more extensible the way you have written it. The hippa_email was probably intended as a special secure email address...which will never happen in the real world. Dropping that is reasonable. As with all things, when we drop or move, we have to note what that affects. I see no inherent issue in how you have arranged these. You should reserve the right to change your opinion as you go along though. There seems to always be another tweak possible in this business.

[pri2si17-1997]

@aethelwulffe Ok. Now I should implement this. For the time being we can leave the contact to be patient. I will surely consider, once I am over with users table. This thing complete the patient_data table. Only few columns left, like usertext etc. I don't know what they are and why they are? But this type of columns is used in many tables. So I will make a separate table for it, and will see how it can be linked with other tables.

[tmccormi]

The usertext and userlist fields are intended to be available for end users to enable their own custom data collection via the Demographics Layout engine. I think that is a another opportunity to clean up, moving the 'user defined custom fields' to it's own reference table.

[tmccormi]

... and making that completely dynamic (as in "Add a new field" not a fixed set of pre-named fields

[pri2si17-1997]

@tmccormi by dynamic you mean that user will enter new field, type etc and new field should be created in table. Right?

[tmccormi]

That is one way, another is to use a META data approach, where you have a table that is meta .. ID (auto increment) FOREIGN ID (like patient id) CUSTOM FIELD NAME CUSTOM FIELD VALUE

-or- ID (auto increment) FOREIGN ID (like patient id) METADATA - where meta data could be a JSON Key/Value pair or anything else

That might be going too far...

[pri2si17-1997]

Ok. Metadata approach is much better. I will look into that. Thanks :slightly_smiling_face:

[tmccormi]

The UI part of that can be something @nileshprasad137 can take a look at down the road.

[nileshprasad137]

Yes, Sure.

[aethelwulffe]

All fields named "Generic" or User are trash fields, or if database.sql still happens to use them (and you may find this in layout_options or something) that there is what appears to be a useful dataset in the "lists" somewhere and it does not have a clear name (someone has used a "user_defined_list_1" or something to add an important feature) then it needs proper naming. I hope all such things are already cleaned up...but... In any case, as I already stated and Tony re-iterated, any custom fields in tables should go to their own table. Anything created by the layout engine (or its replacement in the future) should never touch the canon fields or tables. Pre-made generic options are silly and messy. Also silly and messy is any time someone is referring to an array built up by specifically calling a list of mutable fields (like for csv export or the like). Robust, extensible customization that also provides nearly as good of performance as the canon (meaning standard-native) schema bits will be difficult, but is very valuable. With such resources available, modules will be able to add values to the table(s) reserved for custom fields without any worries. The Layout engine, as Terry just mentioned, allows you to delete important stuff. This too must stop. The "ID" of the layout admin page should allow you to reference and select any available table and any field in that table by a drop-down or pop-up search list that displays the field information...especially the comments and datatype. This will allow the user to select an appropriate field, and allow a lot more useful behavior. Currently, if you are doing a layout change for Demographics, you see the groups (facesheet etc...) but you can only reference the patient_data table. I think this is the major failing. If we get beyond being restricted to a single table, then we can do most anything. This will also mean that all data save/updates need to be fully aware that they will be updating one or more tables based on the layout info, and not simply assuming that it is all in patient_data or history.

[pri2si17-1997]

Hello @aethelwulffe @teryhill please clear me about the users table. Below is the detail I'm providing.

Following are fields which I see null in table. I'm not sure from where these data are coming from?

1. upin
2. billname
3. email
4. email_direct
5. url
6. assitant
7. organization
8. valedirectory
9. street
10. streetb
11. city
12. state
13. zip
14. street2
15. streetb2
16. city2
17. state2
18. zip2
19. phone
20. fax
21. phonew1
22. phonew2
23. phonecell
24. notes
25. abook_type
26. pwd_expiration_date *
27. default_warehouse
28. irnpool
29. cpoe

My Thoughts 9-18 : are resedential info. They should be dropped and we can have a reference to address table. 19-23 : we can have one phone number either cell or basic. Or we can store that in phone numbers and have a reference to that. 26: pwd_expiration_date is set to be the same day of registering new user. 3-4 : Either one of them can be needed. We are storing facility name as well as its id. I think storing id will only serve the purpose. If in case, user is applicable for multiple facilities, then we can have a table in which we can have id's of users and facility table. Rest I don't know. So please tell me.

Regarding user_security As Kevin suggested, we should not alter user's password structure until and unless it is needed. But I think if we are making a new structure, we can have some changes, like :

1. username, password, pwd_history1, pwd_history2 can be dropped from users table, as they are totally authentication related thing.
2. pwd_expiration_date and authorizationshould be moved to user_secure table.
3. salt column can be dropped if we switch to PHP new hashing algorithms like password_hash and password_verify.

I dont know how changing its structure will affect migration. It was my thought. Please have a look at it and tell me wherever I'm wrong.

[teryhill]

Look at the addr book, you will see more fields that are used. The Users UI and the addr book UI use the same table (Users)

[pri2si17-1997]

Yes. I missed it. I have seen this in past. Right now it slipped out of my mind. Sorry for that. And thanks... :slightly_smiling_face: I got that.

What about other things?

[aethelwulffe]

users table:

• upin Drop. Not used any longer
• billname An alias (I think) to be used in billing communications
• email A contact listing to be moved
• email_direct A field to be used by phiMail (I believe) that uses a poorly adopted system called DIRECT. This can still go to a contacts table.
• url A website for an addressbook contact.
• assitant Assistant (sp), a little or unused field
• organization Company name as an address book contact
• valedirectory A field that should be converted to something to store credentials, like M.D., so that you don't get user and provider last names like "Gupta M.D."
• street should go to addresses table
• streetb should go to addresses table flagged as both "add_line_1" and type "business_add_1"
• city same
• state same
• zip same
• street2 same
• streetb2 same
• city2 same
• state2 same
• zip2 same
• phone same
• fax same
• phonew1 same
• phonew2 same
• phonecell same
• notes: should be kept, but careful looking for crap in the code base looking for "WHERE users.notes LIKE 'Provider'" should be snuffed, because lazy stuff like that exists.
• abook_type Address book type. You can look at the address book stuff to figure that out. Might be a case where metadata obviates the need.
• pwd_expiration_date * Obvious, but you should look through the current and past issues list for problems relative to this. Until someone sets the "passwords expire" globals setting, this does not currently get set. Bad idea.
• default_warehouse Drop...I think. This should be keyed to the facility in use.
• irnpool Something to do with nursing, or perhaps some of that planned parenthood junk code we need to continue to erase pending getting a real specialty module to handle the stuff.
• cpoe Something for the Care Plan that uses the ZEND framework stuff. Personally, I think we should drop that stuff completely to allow a refactor. Should not be added unless someone were actually using that module. In any case, it should only apply to "real" system users.

[teryhill]

I think the default_warehouse has to do with the inventory section, not 100% sure on that though.

[pri2si17-1997]

@aethelwulffe

Here is the link of structure till refactoring of patient_data. : https://github.com/pri2si17-1997/LibreEHR/blob/GSoC_LibreEHR/LaravelIntegration/Documentation/Database_Structure.pdf

Here is the branch in which i'm developing : https://github.com/pri2si17-1997/LibreEHR/tree/GSoC_LibreEHR/LaravelIntegration

Please have a look at it. Directory to look for : /LaravelIntegration/LibreLaravel/database/migrations/*, /LaravelIntegration/LibreLaravel/database/factories/*, /LaravelIntegration/database/seeds/*, LaravelIntegration/LibreLaravel/app/Console/Commands/CreateDatabase.php

It can be easily migrated on the local system.

Please let me know in any suggestions or clarification.

Next I will commit for users table.

Also let me know if it is correct to proceed to achieve goal as you mentioned in #583. Because i'm thinking of first refactor the tables. Doing this I will get complete knowledge of each table and re-writing all sql queries will be then easy. What you say?

[tmccormi]

yes it is related to the Inventory, assumes USER is at a facility and the Inventory for that user/facility would come from a default warehouse.

Tony McCormick, CTO www.mi-squared.com Support: 866-735-0897, Direct: 713-574-6709 My Calendar: http://bit.ly/XznvDo

tóg go bog ē - Best advice from a pub owner in Ireland, to take it easy.

On Wed, Jun 7, 2017 at 12:20 PM, Terry Hill notifications@github.com wrote:

I think the default_warehouse has to do with the inventory section, not 100% sure on that though.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/LibreHealthIO/LibreEHR/issues/552#issuecomment-306897987, or mute the thread https://github.com/notifications/unsubscribe-auth/AARci_juWK6MzucIXVY3HCFbKYysdTm8ks5sBvfugaJpZM4NlobB .

-- Please be aware that e-mail communication can be intercepted in transmission or misdirected. Please consider communicating any sensitive information by telephone. The information contained in this message may be privileged and confidential. If you are NOT the intended recipient, please notify the sender immediately with a copy to hipaa-security@mrsb-ltd.com and destroy this message.

[tmccormi]

We definitely need to separate users that need to login and have specific setting from the general Address Book of things the the users might what in a shared contact directory...

[aethelwulffe]

Yes, it really does look good. You are very much on track, and #583 is just a reality check statement we can reference and discuss in context (talk about related stuff). That issue posting was a result of the conversation today on our weekly call-in conference, and serves somewhat as the minutes of that little talk. We figured it was just a good thing to have out there for reference...so that ALL of us (not just the GSOC people) stay mindful of the current deliverable list. Frankly, your own part is 90% preparation for what happens next vs some immediate high-value new feature, and it looks like you are treating it as such. As I go through your stuff, I intend to comment everywhere I think either a confirmation or consternation would be helpful. This learning, either by documentation or by breaking it down and re-designing, is the real value here. Your approach is great.

[aethelwulffe]

And contact type would be "business contact" or "facility contact" or whatever, so you can tie them as an org-wide (or more granular) contact.

On 2017-06-07 19:17, Tony McCormick wrote:

We definately need to separate users that need to login and have specific setting from the general Address Book of things the the users might what in a shared contact directory...

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/LibreHealthIO/LibreEHR/issues/552#issuecomment-306952709, or mute the thread https://github.com/notifications/unsubscribe-auth/AAhzF3qMIGDU4R4uJaTIRAPGSx5j0zdqks5sBy-ggaJpZM4NlobB.

[pri2si17-1997]

@aethelwulffe Thanks... :) I will make that contact table generic.

Here is users table break-down. Please do have a look at it and let me know if any correction.

Notes :

• All the address related field's info will be saved in address table.
• User's security credentials has been moved entirely to user_secure table.

1. user_residential_link id int(11) auto-increment primary key, user_id foreign key references Users.id, address_id foreign key references address.id, type int(1) not null.

Note : type : 0 -> primary address, 1 - > alternate address

2. user_secure id int(11) auto_increment primary key, username varchar(100) not null unique, password varchar(256) not null, active boolean not null, authorized boolean not null, pwd_expiration_date datetime not null, user_id foreign key references users.id

3. user_password_history id int(11) auto-increment primary key, password varchar(256) not null, last_update datetime not null, user_id foreign key references users.id

Note :

• I dropped salt* field, because I am taking password_hash and password_verify function into account for future. I will include it if you say that it is really needed. I think while everything is in process this to be removed. But you are the guy to decide, so it's totally up to you.

• Intention of creating a new table user_password_history is that it is dedicated to store only password history and it has nothing to do in authentication i.e, while logging in we don't need to look at these fields. They will come in use whenever any change is made. Regarding how it will work, then, first time entered password is saved in password field and a time stamp is maintained, second time also a password is saved and time stamp. Now each user is having a count of 2 passwords (must be coded in program), then a new change will update the older time stamp password. This way we can increase the limit to 2, 3 , 4 .. and also this is properly relational. What you say?

4. user_communication id int(11) auto increment primary key, number varchar(10), type int(1), user_id foreign key references users.id

Note :

• type : 0->home_phone, 1 -> work_phone, 2 -> mobile, 3 -> fax, 4-> alternate_work_phone

• According to me this table will remove the chances of storing 5 times phone numbers (including fax.) We can have one number entered and mark its type according to the entry. Suppose a user enters all 5 phone numbers, then there will be 5 row linked to one user, but if he/she enters only 1 or 2 phone numbers then we can easily avoid null values in our database. More over we can extend this to store contact numbers of patient and contacts. It will remove some columns from the table. What you say?

5. users id int(11) auto-increment primary key, fname varchar(100) not null, mname varchar(100) null, lname varchar(100) not null, federalTaxId varchar(100) unique, federalDrugId varchar(100) unique, see_auth int(1) not null, npi varchar(15) not null, suffix varchar(10), taxonomy varchar(100) , calendar varchar(100), info text null, access_control_id foreign key references access_control.id, user_role foreign key references role.id

6. user_addr_book id int(11) auto-increment primary key, title varchar(10), email varchar(100) unique, url varchar(100), assistant varchar(100), organization varchar(100), valedictory varchar(100), speciality varchar(100), notes text null, abook_type varchar(10), user_id foreign key references users.id

7. user_facility_link id int(11) auto-increment primary key, user_id foreign key references users.id, facility_id foreign_key references facility.id, is_default boolean not null

8. access_control id int(11) auto increment primary key, access_type varchar(15)

9. role id int(11) auto-increment primary key, role_name varchar(20)

Note:

• 8 & 9 are static table. I think instead of storing name we can store their reference. May be needed in future, but can be merged in users table. I will do according to your suggestion.

• 7 will create many to many relationship and marking any facility for user as default will be set accordingly. We can check in code to make sure that only one facility is marked as default.

• some fields like source, default_warehouse, irnpool, are not getting info from either user or addr_book. Is there anything to get that fields info from UI?

• You told me that password_expire_duration is set from global settings. Can't we do it in the code itself where we are storing the info at the time of user's registration. Lets say 180 days, and set that in table from the current timestamp?

These are my suggestions. You please look at it and let me know if i'm missing anything or is there any wrong info? I will surely improve it.

[aethelwulffe]

Sorry to reply in short, but I am in between meetings, and my energy level is very low. Let me stick to the initial impressions in no order.

1. the 180 day thing was once suggested by me. I think you may be able to find relevant discussion in old issues with that, but for a simple fix vs. a very elaborate one, I think the impression was that it would be OK provider there was in-context documentation of what behavior to expect when someone turns the expiration flag on. The flag would have to be set for 180 out from the time the flag was changed, or the policy change needs a set of options for handling that. This is an issue where changing one global flag could lock everyone out of the system.
2. The drifting user fields are something it is best you just GREP for. They may be for three different features in the system, and I doubt any of them are particularly valuable, except that if they are not there, they break stuff.
3. Please spell check the base words in your variables. It is Residential. :)
4. The BIG ONE: I am not "The Man", and certainly not regarding the security bits. The closest we have is Kevin Yeh. The salt store and all that was designed after a lot of trial and error, and the methods used were a replacement for all that old blowfish stuff...and security is not an area where I roll my own functions. Please post the meat of that particular discussion on the forum (with your proposals), and attempt to get a wide audience of feedback. This is a big-time big deal sort of thing, and way above my paygrade.

[pri2si17-1997]

Ok @aethelwulffe I am posting this issue and I will seek suggestions from Kevin. Sorry for spelling mistake. It was a typo. I will keep this in mind. Whenever you get time, please look at the structure.

[pri2si17-1997]

@aethelwulffe

Have you looked at above structure? If there is an issue with salt field then I can include that.

Now I'm posting few more structures:

1. background_services id int(11) auto-increment primary key, name varchar(100), title varchar(100), active boolean default 0, running boolean default 0, next_run timestamp default NOW(), execute_interval int(11) not null, function varchar(256), sort_order int(11)

Note :

• require_once column is dropped as it stores the file url : /library/direct_message_check.inc. I think that this should be coded, so i dropped it. What you say?

• What is sort_order? It is mentioned lower number will run first. Probably it means that when we have more than 1 service then which will have lower number, will be executed first. Some sort of priority.

2. batchcom id int(11) auto-increment primary key, patientId foreign key references patient_data.id, sent_by foreignkey references users.id, msg_type varchar(255) not null, msg_subject varchar(255) not null, msg_text text not null, msg_date_sent datetime not null

3. automatic_notification id int(11) auto-increment primary key, sms_gateway_type varchar(256) not null, next_app_schedule datetime not null, provider_name varchar(100) not null, message text, email_sender varchar(100) not null, email_subject varchar(100) null, type enum('SMS', 'Email') default SMS, notification_sent_date datetime not null

Note :

• next_app_time column is dropped, and instead that I made a new column next_app_schedule of date time field. I think the purpose is to store when next notification will be placed. So it is best to store in one field.

• Please explain provider_name and email_sender.

• I think email_sender should be any user. So it should be foreign key to users table. But I am not sure.

4. categories_to_documents id int(11) auto-increment primary key, categoryID foreign key references categories.id, documentID foreign key references documents.id

Note :

• First please explain me categories table.

• There are two tables config and config_seq which are exactly same as categories and categories_seq. So I think they must be dropped.

• In users_facility table, it is mentioned that joins users or patient_data to facility table. So I made users_facility_link for it. I haven't linked that with patient, but I will.

• Also can you please explain the facility table's fields. It will provide me more help.

At last please look at above structures and users table structure provided earlier. Then I will complete its implementation. Now I think two big tables namely patient_data and users are refactored, and its time for related tables. I will start posting structures of tables which I complete.

[pri2si17-1997]

Hello @aethelwulffe Now I am proceeding with patient's related table. Starting from appointments. When we create appointment , data is stored on libreehr_postcalendar_events. First I need to know the details of following tables :

1. libreehr_postcalendar_categories
2. libreehr_postcalendar_events
3. libreehr_postcalendar_limits
4. libreehr_postcalendar_topics

Table 3 & 4 doesn't seems to be so useful and can be dropped. I grep - ed for them and found that they are used in library/log.inc. Also there are many fields in table 1 and 2, which are common, but before saying anything I need to have information of each field.

You please look at table 3 & 4 and tell me if they can be dropped and provide me with detailed information of each field, means what each fields are meant for. Also it seems that table 1 is linked with table 2.

[aethelwulffe]

These are related to the Post Nuke Calendar system (SMARTY based) that Ujjwal is replacing.

...to answer about 3 and 4 despite this, I believe these are used to set parameters for available times and the like. All the above is irrelevant in any case.

On 2017-06-12 14:58, Priyanshu Sinha wrote:

Hello @aethelwulffe https://github.com/aethelwulffe Now I am proceeding with patient's related table. Starting from appointments. When we create appointment , data is stored on |libreehr_postcalendar_events|. First I need to know the details of following tables :

1. |libreehr_postcalendar_categories|
2. |libreehr_postcalendar_events|
3. |libreehr_postcalendar_limits|
4. |libreehr_postcalendar_topics|

Table 3 & 4 doesn't seems to be so useful and can be dropped. I grep - ed for them and found that they are used in |library/log.inc|. Also there are many fields in table 1 and 2, which are common, but before saying anything I need to have information of each field.

You please look at table 3 & 4 and tell me if they can be dropped and provide me with detailed information of each field, means what each fields are meant for. Also it seems that table 1 is linked with table 2.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/LibreHealthIO/LibreEHR/issues/552#issuecomment-307886924, or mute the thread https://github.com/notifications/unsubscribe-auth/AAhzF_T5JcUO1gRY8ldNO5HUNJ3PaXLxks5sDYo8gaJpZM4NlobB.