This page illustrates a data model for Flower Medical Center (FMC) Hepatic Oncology Center (HOC) in tables, a group of similar rows, and relationships among the tables. The main tables are Patient: Information, Alternate Contact, Nurse (Scheduler), Primary Care Physician (PCP), Secretary, and Referring Physician. Each table has a primary key which in this case is a column that identifies a unique row in each table (Kroenke, 2014). To complete this Data Model, I used the MIS Essentials textbook (Kroenke, 2014) along with interviews from professionals that work with this data.
Patient
Patient: Information
In this table the primary key is the patient's Social Security Number (SS#). This table conveys the patient's basic but vital information that all of the other tables are connected to in the relational database model at the end of this page. This table's fields, columns, are the patient's SS#, name, address, home phone number, work or cell phone number, email address, age, date of birth (DOB), sex, occupation, referring physician ID#, primary care physician (PCP) ID#, alternate contact ID#, diagnosis, problem list, whether they are an inpatient or an outpatient, the name of their insurance, their insurance plan ID#, the patient's pharmacy, the nurse employee ID# who scheduled the patient's appointment, the secretary employee ID# who answered the phone, the reason for the patient's visit, whether the patient's appointment is for a second opinion, and if the patient's medical records are in the Electronic Medical Records System (EMRS).
Patient: Conditions
This table's primary key is also the patient's SS#, and it illustrates what if any conditions the patient has ever experienced. If there is no check mark in the column the patient has never experienced that condition, and if there is a check mark the patient has experienced it.
Patient: Social History
This table's primary key is also the patient's SS#, and it illustrates the patient's history with smoking, alcohol, drugs, and mental illnesses. A check mark in a column means that the patient has a history with these substances or mental illnesses.
Patient: Past Surgery History
This table shows what type of surgery the patient has had, and the date that that surgery was performed. The primary key is the patient's SS#.
Patient: Medical History
This table demonstrates any type of health problems or illnesses that the patient has had and when the medical condition was first diagnosed. The primary key is the patient's SS#.
Patient: Current Medications
This table shows what type of medications each patient is currently taking and at what dose. The primary key is the patient's SS#.
Patient: Family Medical History
This table shows the patient's family medical history by illustrating which biological family member has had a certain type of medical condition. The primary key is the patient's SS#.
Patient: Allergies
This table demonstrates the type of allergies each patient has to medications and the reaction that occurs if they come in contact with that medication. The primary key is the patient's SS#.
Alternate Contact
This table indicates who the patient's alternate contact is, the alternate contact's relationship to the patient, and the phone number the alternate contact can be reached at if needed. The primary key of this table is the alternate contact's ID#. This primary key is a foreign key in the Patient: Information table. A foreign key is a column used to represent a relationship to another table, and the values of the foreign keys match the values of primary keys in another table. (Kroenke, 2014)
Nurse (Scheduler)
This table represents the nurse who called the patient to schedule their appointment at FMC HOC. The primary key is the nurse's employee ID#, and it is a foreign key in the Patient: Information table. The other fields in this table include the nurse's name, the FMC HOC physician they are assigned to, how the nurse the patient's medical records, what date the patient was referred to FMC HOC, the referral type, and the date and time the patient's appointment will be at FMC HOC.
Primary Care Physician (PCP)
This table shows who the patient's PCP is, the PCP's office address, phone number, fax number, and email address. The primary key is the PCP ID#, and this key is a foreign key in the Patient: Information table.
Referring Physician
This table illustrates the physician that referred each patient to FMC HOC. The primary key is the referring physician ID#, and it is a foreign key in the Patient: Information table. Other fields that are included in this table are the referring physician's contact name which is usually the doctor's nurse or secretary, the office phone number and fax, the doctor's or office's email address, the doctor's office address, the referring physician's specialty, if the referring physician is requesting a specific FMC HOC physician and if so which one, whether they have a Referring Physician Patient Appointment Request & Pre-Registration Form at their office, and what secretary they talked to about the patient referral.
Secretary
This table indicates which secretary answered the phone when either the patient or the referring physician called the FMC HOC office. The primary key is the secretary's employee ID#, and it is a foreign key in the patient information table and the referring physician table.
Data Relationships
This illustration demonstrates the relationship between each table and their cardinality of each of the relationships. There are three types of cardinality that a relationship can have. It can be a one-to-one relationship which means that each primary key is associated with only one other primary key in another table. The second type of relationship is a one to many relationship which means that one individual from one table can be associated with multiple individuals in another table. The third type of relationship is a many to many relationship which means that many individuals in one table can be associated with many individuals in another table. In this model the patient's SS# is a one to one relationship with the patient: conditions, Patient: Allergies, Patient: Medications, Patient: Past Surgery History, Patient: Medical History, Patient: Social History, and Patient: Family Medical History because each patient can only have one SS# associated with them. The referring physician is a one to many relationship with the Referring Physician ID# foreign key in the Patient: Information table because each referring physician can refer more than one patient to FMC HOC. The PCP table is associated with the PCP ID# foreign key in the Patient: Information table. It is a one to many relationship because one PCP can be the primary doctor for multiple patients that are seen at FMC HOC. The nurse (scheduler) table is also a one to many relationship with the nurse employee ID# foreign key in the Patient: Information table because the nurse can schedule and be associated with many patients. The secretary table is a one to many relationship with the secretary employee ID# in the Patient: Information table because each secretary can be associated with many patients. The Alternate Contact table is also a one to many relationship with the alternate contact ID# in the Patient: Information table because each alternate contact can be the alternate contact for multiple patients seen at FMC HOC.
Works Cited
Kroenke, D. M. (2014). MIS Essentials (pp.28-33). Upper Saddle River, NJ: Pearson Education, Inc.
No comments:
Post a Comment