1 / 10

In our previous discussion about patient treatments, we noted some problems:

In our previous discussion about patient treatments, we noted some problems:. Patients can have more than 1 Physician. Other personnel can be involved in a treatment, including:. Other physicians. Nurses. Therapists. A patient may be admitted for multiple illnesses.

neveah
Download Presentation

In our previous discussion about patient treatments, we noted some problems:

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. In our previous discussion about patient treatments, we noted some problems: • Patients can have more than 1 Physician • Other personnel can be involved in a treatment, including: • Other physicians • Nurses • Therapists • A patient may be admitted for multiple illnesses • Prescriptions can be tricky • We didn’t really address the costs involved There are also additional issues we didn’t properly address, including: • Some patients are admitted to the hospital • Some patients are seen on an out-patient basis • Treatments may involve additional procedures • Treatments may require the involvement of additional personnel The following is an attempt at addressing these issues It is by no means a final solution !!!

  2. First, let’s slightly free-up our constraint that a patient can have only one Physician: • Let’s assume that they can have any number of physicians, but that only one physician at a time can be involved in a treatment. • This is actually a pretty simple fix: Patient • Only Two Tables need to be changed: Physician Supervises Patient • PatID Treatment x Name x Address Illness Drugs (No Physician Field) Treatment • ~ PatID • ~ IllCode • ~ DrugCode • TDate ~ PhysID

  3. Another Possible Redefinition of Treatment: This one assumes multiple personnel per treatment * Foreign Key Treatment Illness Patient Treatments can involve a number of procedures and drugs Procedures TreatDrugs TreatProcedures What we know (so far): Drugs • Sonia Sotomayor suffered a fracture of the R. Tibia on 10/04/2009 • She received three different procedures (above) • She was given five different drugs (left) • So far, she owes: 600.00 • Procedures: 300 + 200 + 100 = • Drugs: (30 * 1.5) + (10 * 10) + (50 * 2) + 150 + (5 * 12.75) = 45 + 100 +100 + 150 + 63.75 = 458.75 1,058.75

  4. Possible Additional Treatment Relationships * Foreign Key Treatment *** Assumes that treatment may occur in only one location Patient TreatDrugs Illness TreatProcedures TreatLoc *** TreatPrescrip Drugs Buildings We now also know: • Sonia Sotomayor was treated in Pricey Operating Theater #3 in the Conrad Murray Building • She was charged 2 * $300 = $600 for use of the room • She now owes: $1,058.75 + $600 = $1,658.75 Rooms • She was given a prescription for 20 Demerol and 30 Naproxen (which may or may not be filled at the hospital)

  5. What if Sonia Needs a Room? PatientRoom PatientMeals PatientRmTypes Meals Meal Comp. We now also know: • Sonia Sotomayor spent 2 nights in a double room ($1,400) • Her meals cost her $83.50 • She now owes $3,142.25

  6. What if Sonia Needs a Room? PatientRoom InRoomDrugs Drugs We now also know: • Sonia Sotomayor was given 4 different drugs in the hospital (above): 7 Aspirins at 1.50 each, 7 Demerols at 10.00 each, 8 Naproxen at 5.50 each, and 2 Placebos at $12.75 each • The total cost of the drugs was: $150.00 • She now owes $3,392.25 Patients in rooms are also in need of/Charged for: • Telephone lines • Sponge Baths • IV hook-ups • Wheelchair Transportation You get the idea ….

  7. How are we linking the hospital staff to the Treatment? * Foreign Key Treatment We now also know: TreatProcedures • Dr. Dolittle reset Sonia Sotomayor ‘s broken bone and applied her cast • Dr. Doless gave her the local anesthetic (We have already addressed the fees) Physician Treatments Procedures Physician

  8. How are we linking the hospital staff to the Treatment? * Foreign Key Treatment We now also know: TreatProcedures • Nurses Drew, Ratchet, and Barton assisted in Sonia’s procedures • Sonia was charged a total of 2 * 100 + 2 * 100 + 2 * 50 or $500 • Sonia now owes $3,892.25 Nurse Treatments Nurses

  9. What about treatments given After the treatment? It would require another entry into the treatment table and the Treatment Procedures table: Treatment TreatProcedures Procedures PT Treatments Physical Therapists We now also know: • Sonia Sotomayor ‘s had a Physical Therapy Leg Stretching treatment pm 10/10/2009 • It lasted 2 hours and was administered by Sadie Masochist • It cost her another $300

  10. Our Basic ERD Room Type PatientRoom PatientMeals Meals MealComp Patient InRmDrugs Treatment Illness TreatDrugs Drugs Bldgs TreatLoc Rms TreatProcedures TreatPrescription Procedures NurseTreat PhysTreat PTTreat Nurses Physicians Physicians Note: Table with Foreign Key Table Referred to

More Related