Proposal: create a convention database table

[jniles]

This proposal restructures the way bhima handles conventions, debtors, and debtor groups.

Why change anything?

The debitor_group database table currently stores conventions, and I will argue that this is not appropriate. The reasons are given in the list below:

1. Storing conventions in the debitor_group table requires that each debitor_group has a location_id and contact information (phone, email). For a generic group of patients, this practice does not make sense, forcing the user to put in garbage data.
2. There is a potential for conflicting information: what do we do with a patient's location_id is different from the debitor_group's location_id? What happens if a debitor_group is locked, but a patient is not locked? Who do we contact about problems with the transaction, the patient's contact information or the debitor_group's?
3. The is_convention flag must be set for every debitor_group.
4. Who is the debitor_uuid in the posting journal when the convention pays its bills? Put another way, what debtor is responsible for the convention? One of the patients in the group? Or someone else? How is that represented within bhima?

I think we can do better.

Proposal: Treat Conventions like Patients and Employees

I propose change our database schema to reflect the following diagram:

In this model, a convention is an entity with a debtor representation, a debtor group, and a linked account. Getting all conventions is as easy as querying a SELECT * FROM convention. We also now have a clear debtor to debit when a convention agrees to support a patient. Finally, we remove everything but the financial details from the debitor_group database table.

For this to work properly, we require the following rules:

1. When creating a convention, you must create a new debitor_group. The new convention is assigned to that debitor_group. Bhima may allow you to change the debtor group of a convention, but we probably should ensure that only one convention is in a debitor group at any given time. While there is nothing wrong with having two conventions in a single debtor group (in the application logic), that was probably a mistake, since both conventions would receive a bill for the same patients.
2. Any number of patients can inhabit the convention's debitor_group. Their bills will show up in the convention's invoice. Individual invoices will still exist for each patient, allowing you to see what an individual patient owes, if required.

   Advantages:

3. The proposal clearly assigns a convention debtor to a debtor group, and is available to be used as the debtor in transactions where the convention supports a patient or group of patients.
4. Allows us to store additional information about a convention, since we are no longer writing all the information in debitor_group table.
5. Removes confusion between duplicate and potentially conflicting information in the patient and debitor_group tables.
6. Clearly aligns the debitor_group table with only financial information applicable to all debtors.

   Disadvantages:

7. This model assumes that all our debtors will be either a) conventions, b) patients or c) employees. If we come across a new debtor type, we must create a new entity to model that debtor. Considering that we've had a year or more of testing without needing any other type of debtor, I think this model is pretty solid for hospital financial information.
8. When creating generic transaction in the posting journal (via Journal Vouchers or editing a journal transaction), the accountant will need to tell bhima what type of debtor they are looking for -- a patient, convention, or employee. This might present a user interface challenge.

cc @IMA-WorldHealth/remote-contributors @IMA-WorldHealth/owners

[DedrickEnc]

Thanks because you did your best to be clear. First of all, the * debitor_group table* doesn't store conventions for the first impression, it is storing debtor group. A debtor group can be a convention (enterprise) that why we have a flag is_convention.

1. location_id, phone and email can be optional excepted if the is_convention flag is checked This situation can be handled on the client side.
2. The patient location is different from the debtor group location of course. Each client transaction is not possible without a debtor group which is linked to an account so, there is no need to lock a patient, we have to lock only the debtor group, by the way there is no locked column in the patient table.
3. We need to define is_convention flag to know if a debtor group is a convention. it is just a boolean and just 1 byte for mysql.
4. When a convention (an enterprise) come through is employee to pay the bill at the primary cash (convention can only pay to the primary cash), no debitor_uuid is written to the posting journal table so NULL. No debtor is responsible of the convention. The convention him self is responsible and it has an account, phone, email to allow us tracking it.

In BHIMA, we are writting only the sale uuid in the inv_po_id column so that we are able to know what debtor was concerned by the bill.

In your schema, you are considering the convention at the same level then a patient, in other word, an enterprise at the same level then a patient. We have a convention for Moral person and a conventioned patient for physical person, This design destroy the group character of convention and make a convention a physical person as patient and employee. A convention contains debtors so it is a debtor group by definition and those debtors are different because there are supposed to not pay for their bill. We have an other procedure called subsidy where a whatever debtor group can support whatever patient group but the obligation character is not required. So I am less easy when you are employing the word supporting to reference the main relation between a debtor and a convention even if I have not an other word but supporting is the best word to characterize the subsidy.

[jniles]

@DedrickEnc thanks for the feedback. I guess if conventions are not entities, than my proposal makes no sense. I thought for sure they had a debtor representation, but maybe I'm misremembering.

I'm still not satisfied with defining location ids, emails, and other metadata dependent on the is_convention field. That doesn't seem like good database creation. However, my proposal is too drastic a change to simply address that one issue. I'll close this issue as won't fix.