|
I followed the approach of introducing UUID value attribute.
With this the UUID is generated automatically when other values are inserted. For that I use a trigger to generate automatically.
(But when I enter the command "show triggers" , I got an empty set. However I used trigger to populate the UUID key field automatically. Is my approach is fine, or do I need to follow another way? )
We can add UUID easily, but we have to take into consideration about best way of storing UUID in optimum way.
1. Optimal select/ insert performance
It can be stored as,
-
VARCHAR
-
BINARY(16)
-
CHAR(36)
-
BIGINT
According to findings as http://iops.io/blog/storing-billions-uuid-fields-mysql-innodb/ BINARY(16) is the optimal option for the most efficient storage space and query speed .
There are some different ways to do;
One thing is we can use set of ordered values for this unique keys when it is generated through the main database, such as, 00000000-0000-0000-0000-000000000001, 00000000-0000-0000-0000-000000000002
and at the same time creating unique values from UUID(); at offline status. In those two situations the data is generating different way. but we have to give 00000000-0000-0000-0000-000000000001, 00000000-0000-0000-0000-000000000002 such values while inserting values for other attributes. Even this may be vulnerable for attacks.
So I thought of going to the another way of automatically generating UUID keys. Considering on space I used binary(16) data type.
I studied , https://mifosforge.jira.com/wiki/display/MIFOSX/Mifos+X+Database , and followed the installed database version of Mifox and ER diagrams in listed below .
Core MifosX Database Tables
MifosX Application Users, Roles and Permissions Tables
MifosX Code, Enum, Notes and CurrencyTables
MifosX Reporting MetaData Tables
So I selected m_staff, m_group, m_client, m_loan tables. because I thought they are in the big need of having this uuid keys, for these offline app usage.
m_staff All the MFI's staff. Some (or all) of these will have logins for Mifos X (m_app_user). So offline id may be created.
m_group people in a group may use connect to the main database, or sometimes in offline. So The group should have a unique id
m_client clients may log in offline.
m_loan m-loan has relations with clients, group, staff. So this need to be updated with every changes doing to other related tables.
But there are some more tables who are having relations with these tables such as;
m_office
m_office_transaction
m_savings_account This has relations with client, group,m_savings_product.
m_savings_account_transfer
m_savings_account_transaction
m_savings_account_charge
m_savings_account_charge_paid_by
m_payment_detail
m_fund
m_product_loan
m_loan_transaction
m_loan_term_variations
m_loan_repayment_schedule
m_loan_paid_in_advance
m_loan_overdue_installment_charge
m_loan_officer_assignment_history
m_loan_installment_charge
m_loan_disbursement_detail
m_loan_collateral
m_loan_charge_paid_by
m_loan_charge
m_loan_arrears_aging
m_guarantor
m_group_roles
m_client_identifier
m_client_attendance
m_meeting
m_appuser_previous_password
acc_product_mapping
acc_accounting_rule
acc_gl_account
acc_gl_closure
acc_gl_journal_entry
m_note
m_code_value
sms_messages_outbound
m_portfolio_command_source
Should all these have the foreign_key constraint indicated with this?
I have already committed my changes , mifox. It is to mifosx\mifosng-db\migrations\core_db. So you all can see what I have done.
If there are any thing wrong please let me know, then I can adjust my changes.
|
MIFOSX-165
