Health Care Options, Inc.
Health Care Options, Inc. (HCO) is a home-health care facilitylocated in the heart of the Blue Ridge Mountains. It supports localresidents within a 50-mile radius by providing home health careservices that range from medication administration,catheterization, and wound dressing to bathing, dressing, andfeeding. These services are administered by skilled nurses and homehealth aides who are employed by HCO. The management staff includesa receptionist, two co-owners/managers, and an accountant.
Patients receive services based upon a doctor’s writtenreferral. Referrals consist of the type of care needed (i.e.skilled nursing or home health aide), the equipment needed toprovide the services (i.e. albuterol and breathing apparatus forbreathing treatments, syringes, catheters, etc.), and a descriptionof the care needed (i.e. change wound bandages twice per week,provide baths every other day, etc.).  Payment forservices can occur in one of four ways: Medicare Part B, Medicaid,private insurance, and private-pay.
In cases where Medicare or Medicaid is used, payment is receivedbased upon a cost-reimbursement basis. This basis is calculated bydetermining total direct costs for all home visits, plus anallowable amount for overhead. Direct costs include both labor andinventory. Labor consists of a standard rate for a nurse or aide,depending on the services needed. A nurse currently has a standardcost of $150 per hour, while an aide has a standard cost of $47 perhour. Inventory is another direct cost. Inventory consists of itemssuch as various types of syringes, gauze, catheters, etc. Allinventory is currently provided by a single medical supplier, BlueRidge Medical Supplies, which provides a list of all of its medicalsupplies and prices on a quarterly schedule. Prices are subject tochange from quarter to quarter, and the company uses aweighted-average inventory method to keep track of the cost ofinventory that is purchased and used.
Using the direct cost data that is accumulated throughout theyear, HCO submits an annual cost report to its Medicare/MedicaidIntermediary on December 31. This cost report includes a total ofdirect costs as well as the indirect costs of the business.Indirect costs include salaries of staff personnel, rent,utilities, and other miscellaneous office supplies. Total directcosts and indirect costs are totaled in the annual cost report, anda per-patient cost reimbursement rate is determined by dividing thetotal costs (both direct and indirect) by the total number ofpatient visits. It is this cost-reimbursement rate that is billedto the Medicare/Medicaid home office when a home health care visitis incurred. Note that the direct costs that are accumulated andsubmitted within the cost report are only those costs associatedwith Medicare/Medicaid patients. Private pay and insurance patientsare excluded from these calculations. Total indirect costs areincluded in the cost report submission as they cannot be directlytraced to any individual patient or patient type.
For patients with insurance, a pre-determined rate per visit isnegotiated with each insurance company. The rate-per-visit perinsurance company is currently stored in an Excel spreadsheet. Thisrate per visit is based solely upon the skill level of the careprovider. For instance, an RN has a skill level of 10, whereas anLPN has a skill level of 7, and an aide has a skill level of 3. Anentry in the spreadsheet indicates that for All-Insurance (a localinsurance company), any visit provided by a staff member with askill level above 5 is reimbursed at a rate of $238 per visit, upto a maximum of 30 visits in a three-month period. Most otherinsurance companies also provide a rate for a range of skillslevels up to a maximum number of visits within a given duration(usually in months). Once this maximum has been exceeded, apatient’s doctor must write a new referral.  Thisreferral serves as the basis for a new contract between the patientand HCO.
Private pay patients are unique in that a different rate isnegotiated with each patient. That is, a contract is signed witheach patient that requires the patient to pay a certain amount foreach home health care visit up to a maximum number of visits. Thismaximum number is also negotiated and included in the patient’scontract.
Contracts are required for every patient who receives services.Contracts include the type of services performed (i.e. wounddressings, bathing, etc.), the number of visits per week, thesupplies used in the provision of these services, and the durationof services needed. Naturally, the contract includes the type ofpayment (Medicare, Medicaid, insurance, or private pay) to be used.Contracts are assigned start and end dates based on the terms ofthe referral, or in cases where the method of payment is insurance,the duration of the contract is the lesser of the term specified inthe referral or the constraints of the insurance company. In mostcases, doctors write referrals that are within the allowableduration that most insurance companies stipulate.
Scheduling is a major issue in home health care. It is importantto match the needs of the patient with the skills of the nurse oraide. Additionally, the geographic location of the patient mustmatch the geographic location of the nurse or aide providing theservices. Currently, zipcode is used to match the locations ofpatients to their home health care providers. One additionalcomponent of the scheduling feature is the availability of the careproviders. Some nurses and aides are available seven days per week,while others work a part-time schedule-- perhaps on Mondays,Wednesdays, and Fridays only. An important feature of scheduling isto match not only the skills and geographic locations of patientswith providers, but also to match the availability of careproviders with the frequency of visits needed by the patients. Incases where no match is found for a patient and provider based onthese factors (geography and availability), notices are sent to themanagers, who must individually assign nurses/aides who are outsideof their geographic areas or days of availability. These exceptionsare rare, but when they occur, management pays an additionalstipend to its nurses/aides who must work outside of theirlocations and/or days available. No replacements are ever made whenskill sets of providers do not match the needs of patients. Inthese circumstances, a patient’s doctor is immediately notified toallow the patient to receive in-hospital or rehabilitative care,and the existing contract is nullified. This situation only occurswhen a nurse/aide who provides a specialized care subsequentlyleaves the company, and no other health care professionals areavailable with the same skill set in the company.
Phase I Deliverables:
1. ERD
For this phase, assume you are a member of a team of databaseand GUI developers/analysts. As a member of the database team, youare required to create an ER Diagram that will facilitate thedevelopment of Health Care Options’s company-wide database. Itshould be created using ER Assistant and include entities,attributes, and relationships.
The scope of the database includes all entities referencedabove. The goal of this phase of development is to support ascheduling system that will replace the current spreadsheetprocess, but your database should be designed to support all of thecompany’s current and future needs.  Note that your teamwill not actually create the scheduling system. You are responsibleonly for designing the database that will be used by the system.Subsequent phases will include table design and broaden the scopeto support billing and cost report compilation. Thus, plan anddesign your initial database carefully, as it will affect yourfuture deliverables.
2. CREATE TABLE Statements
Based on your ERD design, in step 1, write and execute theCREATE TABLE statements for each of the tables. Be sure to createprimary, foreign key, and NOT NULL constraints as appropriate.
To receive full credit for this assignment, all of your CREATETABLE statements must be executed in MS SQL Server.
In addition to creating the tables, you must add at least 3 rowsof data to each one. If there are any errors in your ERD, you willlikely find them yourself at this point in the process and make anyneeded adjustments to your design. Remember, order matters when youare creating tables and loading data.
Finally, execute the following commands for each table and printout the results to show that you have successfully performed theCREATE TABLE step of this assignment and have populated the tablescorrectly.
