Doctors Information Technology (DocIT) is an IT services company supporting medical practices with a variety of computer technologies to make medical offices more efficient and less costly to run. Medical offices are rapidly becoming automated with electronic medical records, automated insurance claims processing and prescription submissions, patient billing, and other typical aspects of medical practices. In this assignment you will address only insurance claims processing; however, what you develop must be able to be generalized and expanded to these other areas of a medical practice. Your assignment is to draw an ERD to represent each phase of the development of an insurance claims processing database and to answer questions that clients might raise about the capabilities of the application the database supports in each phase.
a. The first phase deals with a few core elements. Draw an ERD to represent this initial phase, described by the following:
• A patient is assigned a patient ID and you need to keep track of a patient's gender, date of birth, name, current address, and list of allergies.
• A staff member (doctor, nurse, physician's assistant, etc.) has a staff ID, job title, gender, name, address, and list of degrees or qualifications.
• A patient may be included in the database even if no staff member has ever seen the patient (e.g., family member of another patient or a transfer from another medical practice). Similarly, some staff members never have a patient contact that requires a claim to be processed (e.g., a receptionist greeting a patient does not generate a claim).
• A patient sees a staff member via an appointment. An appointment has an appointment ID, a date and time of when the appointment is scheduled or when it occurred as well as a date and time when the appointment was made, and a list of reasons for the appointment.
b. As was noted in part a of this exercise the first phase, information about multiple members of the same family may need to be stored in the database because they are all patients. Actually, there is a broader need. A medical practice may need to recognize various people related to a particular patient (e.g., spouse, child, caregiver, power of attorney, an administrator at a nursing home, etc.) who can see patient information and make emergency medical decisions on behalf of the patient. Augment your answer to part a of this exercise, to represent the relationships between people in the database and the nature of any relationships.
c. In the next phase, you will extend the database design to begin to handle insurance claims. Draw a revised ERD to your answer to part b of this exercise, to represent the expanded second phase database:
• Each appointment may generate several insurance claims (some patients are self-pay, with no insurance coverage). Each claim is for a specific action taken in the medical practice, such as seeing a staff member, performing a test, administering a specific treatment, etc. Each claim has an ID, a claim code (taken from a list of standard codes that all insurance companies recognize), date the action was done, date the claim was filed, amount claimed, amount paid on the claim, optionally a reason code for not paying full amount, and the date the claim was (partially) paid.
• Each patient may be insured under policies with many insurance companies. Each patient policy has a policy number; possibly a group code; a designation of whether the policy is primary, secondary, tertiary, or whatever in the sequence of processing claims for a given patient; and the type of coverage (e.g., medicines, office visit, outpatient procedure).
• A medical practice deals with many insurance companies because of the policies for their patients. Each company has an ID, name, mailing address, IP address, and company contact person.
• Each claim is filed under exactly one policy with one insurance company. If for some reason a particular action with a patient necessitates more than one insurance company to be involved, then a separate claim is filed with each insurance company (e.g., a patient might reach some reimbursement limit under her primary policy, so a second claim must be filed for the same action with the company associated with the secondary policy).
d. How useful and sufficient a database is depends, in part, on questions it can be used to answer using reports or online queries. For each of the example inquiries that follow, justify for your answer to part c of this exercise whether your database could provide answers to that inquiry (if you already know SQL, you could provide justification by showing the appropriate SQL query; otherwise, explain the entities, attributes, and relationships from your ERD in part c that would be necessary to produce the desired result):
• How many claims are currently fully unreimbursed?
• Which insurance company has the most fully or partially unreimbursed claims?
• What is the total claims amount per staff member?
• Is there a potential conflict of interest in which a staff member is related to a patient for which that staff member has generated a claim?
e. As was stated in previous parts of this exercise, some claims may be only partially paid or even denied by the insurance company. When this occurs, the medical practice may take follow-up steps to resolve the disputed claim, and this can cycle through various negotiation stages. Draw a revised ERD to replace the ERD you drew for part c to represent the following:
• Each disputed claim may be processed through several stages. In each stage, the medical practice needs to know the date processed, the dispute code causing the processing step, the staff person handling the dispute in this stage, the date when this stage ends, and a description of the dispute status at the end of the stage.
• There is no limit to the number of stages a dispute may go through.
• One possible result of a disputed claim processing stage is the submission of a new claim, but usually it is the same original claim that is processed in subsequent stages.