Health One Medical DataBase

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.

THANK YOU DEEPLY FOR YOUR TIME!

ANY QUESTIONS?

Made with Adobe Slate

Make your words and images move.

Get Slate

Report Abuse

If you feel that this video content violates the Adobe Terms of Use, you may report this content by filling out this quick form.

To report a Copyright Violation, please follow Section 17 in the Terms of Use.