Starting a supported Public Health Care Database

[wrkadri]

Hello guys,

Sorry for the long post, but I needed to explain the full idea of the database so you can know what I am talking about.

I am starting a new database about a public health care center (PHC) which is supported* by the NGO where I work

*The patient pays a reduced amount of money per consultation while we pay the remaining and provide medicines, laboratory tests and radiology for free

I first want to start with profiling the PHC, identify the staff with their roles (doctor, nurse, pharmacist), create family files (for the full family) and identify patients per family.
The procedure goes as follows:

• The patient comes to the PHC, he gets registered by a nurse

• If the patient came for pharmacy, he goes directly to the pharmacy.

• If the patient came for vaccination, he could go into two scenarios:
  
  • The nurse will do the triage (Measuring temperature, blood pressure, etc), then:
    
    • He goes directly to vaccination.
    • He goes to a consultation (checked by a doctor) and then goes to vaccination.
    
    
  
  

• If the patient came for consultation:
  
  • The nurse will do the triage (Measuring temperature, blood pressure, etc), then:
  • he goes to see the doctor.
  
  

• After the consultation is done, we have different scenarios:
  
  • The doctor can directly diagnose the disease
  • The doctor is suspicious with a disease so he asks for a lab test
    
    • When the result is received, doctor diagnose the disease
    
    
  • The doctor is suspicious with a disease so he asks for radiology
    
    • When the result is received, doctor diagnose the disease
    
    
  
  

• After diagnosing the case, he gives a prescription that will be received at the pharmacy.

• As for the pharmacy, there will be medicines from different suppliers.

• Medicines prescribed will be deducted from the medicines stock.

• If the patient is pregnant:
  
  • We create her a pregnancy file
  • Regular antenatal and postnatal check up will be done until delivery or abortion
  
  

I started to work on identifying the tables and relationships and created the first draft, what do you think?



Thank you for your support.

[Micron]

After a quick review, I'd say a lot of though went into this good work.
What I'm curious about is that I don't see any date fields that pinpoint the date of any visit.
Not knowing what a ANCPNC table is for, I don't see how to get the visit results if there was a vaccination but no consult and no prescription as your business case suggests is possible.
Also, assuming it is possible to issue a prescription without a diagnostic test, I also don't see how to get the prescription data without involving the diagnostic table (for which there may be no diagnostics). Maybe have the VisitID in the prescription and Diagnosis table rather than rely on the Diagnosis link? I would probably leave the consult links to the test tables.

[orange]

My first reaction was similar to Micron--a lot of work has gone into the diagram.
My concern--there is only 1 one to many relationship on the diagram???

It would seem normal in a Health Care facility that a Pharmacist could issue multiple Transactions; and
a Patient could have multiple Visits......

Good luck with your project.

[wrkadri]

Thank you guys for your prompt reply, I kinda posted the picture without it being finished 100% (Sorry for that)

ANCPNC table will hold regular antenatal and postnatal check ups done until delivery or abortion

Can you please check these relationships and tell me what do you think !!

Thank you so much for your support.

[orange]

Looks better to me. Have you checked/vetted your model?

Don't rely on the M_Price in the Medicine table to resolve Historical records/reprints etc.
Record the Price on the medicine and quantity in your Transaction.
see this link for more info
and this one too
Take some sample queries, reports, (any expected outputs).
Using pencil and paper (add test data to tables)
Can you get the output/result from the data you have?

[Micron]

Orange's advice is good. Here's what I don't get:

Tables with only ID's. That is, there's visit ID and table row id but nothing else? F'rinstance:
RadTest_ID & Visit_ID but no related information (technician, results, maybe type/body location, whatever).
Provider: no details like address, phone, sales rep...
There are about 9 of these.

No dates anywhere except for visit. Is it not possible that I'd have my consult today but RadTest tomorrow because they're all booked up for the consult day?
More than 1 diagnosis is permitted per visit, so as long as a patient can only have 1 vaccination or consult per visit, OK. Can 2 docs consult during 1 visit, and would that be 2 consults?

[wrkadri]

Looks better to me. Have you checked/vetted your model?

Don't rely on the M_Price in the Medicine table to resolve Historical records/reprints etc.
Record the Price on the medicine and quantity in your Transaction.
see this link for more info
and this one too
Take some sample queries, reports, (any expected outputs).
Using pencil and paper (add test data to tables)
Can you get the output/result from the data you have?

Thank you a lot for your valuable input, I didn't finalize the system yet. This is only the structure that I am still working on.
I didn't test any data entry because the system is not ready yet.
I took your advice and moved the price to the transaction table to keep the historical records.

Thanks again

[wrkadri]

Orange's advice is good. Here's what I don't get:

Tables with only ID's. That is, there's visit ID and table row id but nothing else? F'rinstance:
RadTest_ID & Visit_ID but no related information (technician, results, maybe type/body location, whatever).
Provider: no details like address, phone, sales rep...
There are about 9 of these.

No dates anywhere except for visit. Is it not possible that I'd have my consult today but RadTest tomorrow because they're all booked up for the consult day?
More than 1 diagnosis is permitted per visit, so as long as a patient can only have 1 vaccination or consult per visit, OK. Can 2 docs consult during 1 visit, and would that be 2 consults?

Thank you Micron, as I said in my reply to Orange, this is only the first draft of the structure. That's why you can see the ID fields only. I am still working on that.
On the other hand, as you said for the docs doing the consultations, 1 doc will be responsible for each visits. If 2 docs then it will be two consultations.
Do you suggest any change to the structure?

Thank you again.

[Micron]

If I understand what you posted regarding visits, here's my take.
If 2 docs on 1 visit = 2 consults, your indexed - no dupes VisitID will (I think) prevent you from recording each separate DoctorID for what is essentially the same visit. You'd need to be able to put the same VisitID in twice (or more) for when two (or more) doctors consult, so you might want to change the VisitID to allow duplicates, but still index it.

[wrkadri]

If I understand what you posted regarding visits, here's my take.
If 2 docs on 1 visit = 2 consults, your indexed - no dupes VisitID will (I think) prevent you from recording each separate DoctorID for what is essentially the same visit. You'd need to be able to put the same VisitID in twice (or more) for when two (or more) doctors consult, so you might want to change the VisitID to allow duplicates, but still index it.

Thank you, Visist_id was changed to allow duplicates while still indexed.
I shall be sharing a new updated version of the relationship in the coming couple of days.

Thank you again.

[wrkadri]

This is the new updated relationships.
What do you think?

[Micron]

It's getting too complicated to follow, not having any experience with the subject matter. Suggest you take a day or two off of it, and come back with fresh eyes. That will help you re-analyze things and perhaps find potential glitches. One might be that you'd notice disparities such as Nationality and Nationty, unless of course that was intended. Using your knowledge of the business, ask questions of yourself and explore possibilities, such as what will happen if you wish to record different dosages given of the same medicine type. Your design will not allow that as far as I can tell. The id of that medicine can only be entered once, so two different dosages of that type will not be allowed. This sort of analysis might drive you to either allow dupes, or move a field to another table (i.e. can/should dosage be in Medicine Transaction?).

When you've done that, I'd recommend playing with a db copy and creating as many records as you can to test your design. Excel is a good tool for creating dummy data in that it allows you to quickly generate data via dragging cells (auto complete) or using Find and Replace. You can often Paste/Append these records into your db tables. While testing, implement as many possible scenarios you can think of, and if at all possible, get the input of as many people as you can that have experience with the matter, especially those you expect will be using this. It is highly likely that someone will work through data input and retrieval in ways you'd never anticipate (such as typos that mix number/text data types) which may generate errors or other problems.
Good Luck!