Introducing the Team:
Our Database Design Team Consisted of:
- Andrew Kismali
- Michael Read
- Mariusz Stepien
Project:
- We have designed a data model for a database application system for a mid-size health insurance company called Health One to keep track of health claims.
- As the company is rooted in the medical industry it is important that the company has access to a wide range of information to do with each customer the company has.
- Eventually the database created will be used to track trends for modelling further business rules on the accumulated data for the business.
Entity Relationship Diagram:
Entities and their Tables:
Insurance Company:
Business Rules: Insurance Company - Patient
- One Insurance Policy can have one Person.
- One Person can have one Insurance policy.
- This is non- transferable from Person to Person. IE: One person and his/her family can be on one policy. Another person and his/her family cannot use that persons policy.
Patient:
Business Rules: Patient - Visit
- One Patient may Visit many times.
- One Visit must have one Patient.
- Visits are non-transferable: even in the case of a cancellation a new visit ID must be set up. Another patient could not go to another patients followup.
Business Rules: Patient - Prescription
- One Patient may have many prescriptions.
- One Prescriptions must have one patient.
- Prescriptions are non-transferable.
Business Rules: Patient - History
- One Patient May have History.
- One History must have one Patient.
- Patient History is non-transferable.
Business Rules: Patient - Doctor
- One Patient must have one Doctor.
- One Doctor may have many Patients.
- A patient may be transferred to another doctor IE: From the primary care doctor to a specialist.
Business Rules: Patient - Person
- A Patient is a Person.
- A Person is a Patient.
- non-transferable.
Person:
Business Rules: Person - Patient / Person - Doctor / Person - Address / Person - Name
- A Person is a Patient - A Patient is a Person (non-transferable).
- A Person is a Doctor - A Doctor is a Person (non-transferable).
- A Person has an Address (transferable to other persons with the same address)
- A Person has a Name(non-transferable).
Name:
Business Rules: Name - Person
- A name belongs to a person.
Address:
Business Rules: Address - Person
- An Address belongs to a Person.
Doctor:
Business Rules: Doctor - History
- A Doctor looks at one Patients History.
- A Patients History can be looked at by a Doctor.
Business Rules: Doctor - Visits
- A doctor can have many visits
- A Visit must be with a Doctor
Business Rules: Doctor - Hospital Affiliation
- A Doctor can be affiliated with many Hospitals.
- A Hospital Affiliation must have one Doctor.
Business Rules: Doctor - Person
- A Doctor must be a Person.
Business Rules: Doctor - Patient
- A patient has one Doctor.
- A doctor may have many Patients
History:
Business Rules: History - Patient:
- One Patient May have History.
- One History must have one Patient.
- Patient History is non-transferable
Business Rules: History - Doctor:
- A Doctor looks at one Patients History.
- A Patients History can be looked at by a Doctor.
Hospital:
Business Rules: Hospital - Hospital Affiliation.
- One Hospital can have many affiliations.
- One or more Affiliations must have one hospital.
Hospital Affiliation
Business Rules: Hospital Affiliation - Hospital
- One or more Hospital Affiliations must have one hospital.
Business Rules: Hospital Affiliation - Doctor
- One Doctor must have one or more affiliations with hospitals.
Prescription:
Business Rules: Prescription - Patient.
- One Patient may have many prescriptions.
- One Prescriptions must have one patient.
- Prescriptions are non-transferable.
Business Rules: Prescription - Medicine.
- One prescriptions may have one or more medicines.
- One or more medicines must have one prescription.
Medicine:
Refillable:
Business Rules: Medicine - Refillable
- Medicine can be Refillable.
Non-Refillable:
Business Rules: Medicine - Non-Refillable
- Medicine can be Non-Refillable
Sub-types of Visit Super-type:
Business Rules: Visit - Patient
- One Patient may Visit many times.
- One Visit must have one Patient.
- Visits are non-transferable: even in the case of a cancellation a new visit ID must be set up. Another patient could not go to another patients followup.
Business Rules: Visit - Doctor
- A doctor can have many visits
- A Visit must be with a Doctor
Clients Needs:
The needs that were met for the client to keep track of the health claims are:
- All patient information, address, phone etc. The doctor of the patient. Information about visits(including history), and prescriptions (refillable or non-refillable).
- The details of doctors associated with the patients like Hospitals they are affiliated with and any specialities of the doctors.
- The hospitals must also have a database for details such as location and contact.
- Medicines need data such as name type and side effects to make sure there are no conflicts between prescriptions IE: Antibiotics interfering with Contraception.
Assumptions:
- The Initial visits made by the customers are to primary care doctors.
- The doctors will be working in hospitals and not private practises.
- The prescriptions are not Professionally Administered Only.
- Customers can have policies that cover more than one person.