New Century Health Clinic Case Study

10873-9.CS-1NCH AID: 1112 | 15/02/2014

New Century Health Clinic

1. ERD

One has to first deeply study the process of the business which they carry out in day to day life. One can consider the following process based on some analysis carried out some medical insurance company.

Firstly patients have to fix an appointment for the particular service offered by provider and it incurs some charges against it. Patient claims for its insurance payment. This is the basic process which is followed in every insurance co. one important thing that remains unique throughout the process is CPT code which incurs fees and is compulsory to generate.

The entities involved in the company can be defined as follows:

PATIENT, EMPLOYER, APPOINTMENT, CPT CODE AND FEE, APPOINTMENT_SERVICE, CHARGE, INSURANCE CO., PROVIDER, PAYMENT, HOUSEHOLD, CLAIM

The relation and cardinality involved between the entities is shown in the diagram.

[pic]

2. Normalized tables

The following are the normalized tables for the entities defined above in the diagram.

|Entities |Fields (including primary key) |

|PATIENT |Patient_id, Patient_name, D.O.B, relation_code |

|APPOINTMENT |Appt_id, patient_id, appt_date, appt_time, provider_id |

|APPOINTMENT_SERVICE |Appt_service_id, Appt_id, cpt_code, status, fees, explanation |

|PROVIDER |Provider_id, provider_name |

|EMPLOYER |Employer_id, employer_name, insurance_co_id |

|HOUSEHOLD |Household_id, household_name, household_addr, household_city, household_state,...