Database Creation From Scratch

Have you ever been tasked with building a database but felt overwhelmed by the initial requirements file? Translating those requirements into a functional database can seem daunting. But fear not! In this blog series, we'll break down the process into manageable steps, using a practical example to guide you. We'll explore how to analyze a requirements file and translate its information into a conceptual model, which forms the foundation for your database design.

To illustrate this process, we'll follow along as we build a conceptual model for a hospital database. While the specific details of the hospital will be our example, the steps involved can be applied to any database project. This way, you'll gain valuable insights you can use for your own database endeavors!

The task is to design a database following the information as given;

A hospital's key personnel are the physicians, nurses and technicians, who operate in departments, OPD, Radiology, Laboratory, Pharmacy, Theatre, Oncology, ER.

A physician has an employment number, position and their procedure designation, a record of their certification date and expiry is also kept, this is also done for the pharmacy and lab technicians as required by the relevant governing boards e.g. PPB. The departments they operate in are headed by senior physicians. Pricing for services in the hospital are determined by the relevant committee and these are coded per procedure to avoid duplicity or different price for the same service.

When a patient makes a visit to the hospital, their National ID number is recorded, alongside their phone number for tracking across different service points in the hospital and it is the combination of these numbers for which their financial data is linked. Other demographics and contact information is also kept, that is their email address, phone number, location of stay, next of kin contact, date of birth and gender. (come up with creative ways to enable data validation/accuracy of these records) A MRN-Medical records number is system-generated for every patient upon their visit and registration and this serves as their primary identifier. Patients are of different categories, the hospital has its inhouse scheme, that is for staff and their dependents, credit patients ie insurance, their insurance numbers must be collected by the medical records personnel handling the patients' registration, and cash payers. A record of the Physician who handles the patient must be kept for reference and contesting an invoice rejection by the payer-insurance company. This record is an appointment with the start and end time and date.

When a doctor prescribes a medication the patient is sent to the Pharmacy, these can be branded or generic drugs, a record of their expiry dates are kept and the Pharmacy-in-charge is notified (100 days- hypothetically) prior to the drug expiry. All medication must have a description to reduce errors resulting from sound-alikes and look-alikes.

All patients sent to the lab have their data recorded by the lab technician, these include, the prescribed test, test description, lab-scientist performing the test date and time of the test, test fee, and result.

In-patients are admitted by the in-patient nurse, who locates a room within the system; this tracking is enabled in the HMIS-Health Management Information System. Upon their discharge a finalized bill is prepared detailing the length of stay, ward charges, bed charges, doctors charges, nursing charges, lab test charges cost of operation for theatre if any,

Key Deliverables:

1. Conceptual data model - identifying the high level relationships between the key entities

2. Logical Data model - highlighting all attributes and the constraints, use the 3NF approach.

3. Physical data model - specifying all the table structures

Here's a breakdown of my approach, with insight into each relationship considerations.

Entities and Attributes

• Personnel (Employee_Id, Emp_Name, Position, Procedure_Designation, Certification_Date, Certification_Expiry): This entity represents hospital staff members. In the logical design phase, we might consider creating a separate "Employees" table with attributes like "Employee_Id" (primary key) and then referencing it using foreign keys within other entities like "Physicians" or "Nurses" to avoid redundancy.

• Departments (Department_Id, Department_Name, Head_Of_Department): This entity represents different hospital departments.

• Services (Service_Id, Service_Name, Description, Price): This entity represents the various medical services offered by the hospital.

• Patients (Patient_Id, National_Id, Phone_No, Email, Residence, Next_Of_Kin_Contact, DOB, Gender, Medical_Records_No, Category): This entity captures information about patients admitted to the hospital. "Medical_Records_No" could potentially become a foreign key referencing a separate "Medical Records" table in the logical design.

• Physicians (Physician_Id, Emp_Name, Specialization): This entity represents doctors working at the hospital. "Physician_Id" might become a foreign key referencing the central "Employees" table during logical design.

• Medications (Medication_Id, Medication_Name, Quantity, Description, Expiry_Date): This entity represents medications used in patient care.

• Pharmacy (Pharmacy_Id, Operating_Hours): This entity represents the hospital pharmacy.

• Lab Tests (Test_Id, Test_Name, Price, Date_Performed): This entity represents various laboratory tests performed on patients.

• Nurses (Nurse_ID, Nurse_Name, Nurse_Specialization, Nurse_Contact_Info, Nurse_Shift): This entity represents nurses working at the hospital. "Nurse_ID" could become a foreign key referencing the "Employees" table in the logical design stage.

• Inpatients (Inpatient_ID, Admission_Date, Discharge_Date): This entity represents patients currently admitted to the hospital.

• Rooms (Room_ID, Room_Number, Room_Status, Bed_Count): This entity represents hospital rooms.

• HMIS (Health Management Information System) (HMIS_ID, Admission_Date, Discharge_Date, Diagnosis, Treatment_Details, Medical_History): This entity represents the central information system for patient records.

• Bill (Bill_ID, Total_Amount, Billing_Date, Payment_Status): This entity represents bills generated for patient services.

ER Diagram

Personnel-Departments Relationship:

Identifying Relationships:

Let's take a look at the first requirement: "Physicians, Nurses, and Technicians operate within Departments". This tells us that there's a connection between employees and teams. In database terms, this translates to a relationship between two entities.

Analyzing the Relationship:

The requirement clearly states that employees belong to specific teams. This suggests a mandatory participation on both sides. In other words, each staff member (employee) must be assigned to a team, and each team must have employees working within it.

Cardinality:

The requirement also indicates a one-to-many cardinality. This means one team can have many employees working there, but each staff member can only belong to one team at a time.

Why is this important?

Defining this relationship ensures that the database can effectively track which employees belong to which teams(Departments). This information is crucial for various purposes, such as scheduling, assigning tasks, and managing resources within the hospital.

Services-Department Relationship:

Identifying the Relationships:

Take the concept of Departments (think Teams or Divisions within an organization) in a hospital setting. These departments group related healthcare specialists and offer specific Services to patients. For instance, the Cardiology department might provide services like heart consultations, echocardiograms, and stress tests.

Analysis:

The requirement states that "Departments offer Services, and these services are priced by a central committee". This indicates a relationship between Departments and the Services they provide. However, not all services might be exclusive to a specific department. For example, general checkups might be offered by multiple departments.

Participation:

Considering this, participation on the Departments side might be mandatory (each department must offer services), but not all services need to be associated with a specific department (optional participation on the Services side).

Cardinality:

The requirement suggests a one-to-many cardinality. Each department can offer many services, but each service is likely associated with only one department in the primary context of its delivery.

Why is this important?

Defining this relationship ensures the database tracks which services each department offers. This information is crucial for managing service delivery within the hospital (or any organization), assigning responsibilities, and potentially tracking service costs.

Patients-Physicians Relationship:

Consider the interaction between Patients (like Customers or Clients) and Physicians (like Service Providers or Consultants) in a hospital setting. Patients visit physicians for consultations and treatment.

Analysis:

The requirement states that "Patients have appointments with Physicians". This indicates a direct relationship between Patients and Physicians. However, not all patients may require appointments during a visit, and not all physicians may have patients scheduled on a given day.

Participation:

Therefore, participation for both Patients and Physicians might be optional. Not all patients require appointments, and some physicians may have open slots in their schedule.

Cardinality:

The requirement suggests a many-to-many cardinality. Each patient can have appointments with multiple physicians for different health concerns, and each physician can see multiple patients throughout the day.

Why is this important?

Defining this relationship ensures the database tracks patient interactions with physicians. This information is crucial for managing appointments, scheduling, and potentially tracking treatment plans within the hospital (or any organization that provides consultations or services).

Patients-Medications Relationship:

In a hospital setting, Physicians prescribe Medications (like Products or Treatments) to Patients (like Customers or Clients) based on their medical needs.

Analysis:

The requirement states that "Patients may receive prescriptions from Physicians". This indicates a connection between Patients and Medications. However, not all patients will require medications during their treatment, and not all medications in the hospital inventory will be prescribed to patients at a given time.

Participation:

Therefore, participation for both Patients and Medications might be optional. Not all patients will need medications, and some medications might not be actively prescribed.

Cardinality:

The requirement suggests a many-to-many cardinality. Each patient can receive multiple medications during their treatment for different health concerns, and each medication can be prescribed to multiple patients with similar medical conditions.

Why is this important?

Defining this relationship ensures the database tracks medications prescribed to patients. This information is crucial for managing medication inventory, tracking treatment plans, and potentially monitoring potential drug interactions within the hospital (or any organization that provides products or treatments to clients).

Medications-Pharmacy Relationship:

In a hospital setting, the Pharmacy (like Dispensary or Supplier) is responsible for dispensing Medications (like Products or Inventory) to patients.

Analysis:

The requirement states that "Medications are dispensed by the Pharmacy". This suggests a direct relationship between Medications and the Pharmacy. However, not all items in the pharmacy inventory may be medications (e.g., bandages, sanitizers).

Participation:

Therefore, participation on the Medications side might be mandatory (each medication must be linked to a pharmacy for dispensing), while participation on the Pharmacy side might be optional (not all pharmacy inventory needs to be medication-related).

Cardinality:

The requirement suggests a one-to-many cardinality. Each medication is likely associated with one dispensing pharmacy, but each pharmacy can have a variety of medications in their inventory.

Why is this important?

Defining this relationship ensures the database tracks which medications are dispensed by the pharmacy. This information is crucial for managing medication inventory, recording dispensing activities, and potentially tracking medication usage within the hospital (or any organization that dispenses products or inventory to clients).

Lab Tests-Patients Relationship:

In a hospital setting, Physicians may order Lab Tests (like Services or Analyses) for Patients (like Customers or Clients) to diagnose or monitor their health conditions.

Analysis:

The requirement states that "Lab Tests are prescribed by Physicians for Patients". This indicates a connection between Lab Tests and Patients. However, not all patients will require lab tests during their visit, and not all lab tests performed will be associated with a specific patient at a given time.

Participation:

Therefore, participation for both Patients and Lab Tests might be optional. Not all patients will undergo lab tests, and some lab tests might be performed for research purposes or on unidentified samples.

Cardinality:

The requirement suggests a many-to-many cardinality. Each patient can undergo multiple lab tests during their diagnosis and treatment, and each lab test can be performed on multiple patients with similar medical conditions.

Why is this important?

Defining this relationship ensures the database tracks lab tests performed on patients. This information is crucial for diagnosis, monitoring treatment progress, and potentially analyzing trends within the hospital (or any organization that provides services or analyses to clients).

Inpatients-Nurses Relationship:

In a hospital setting, Nurses (like Caregivers or Providers) are responsible for admitting and caring for Inpatients (like Residents or Clients) during their stay.

Analysis:

The requirement states that "Inpatients are admitted by Nurses". This indicates a direct relationship between Inpatients and Nurses. In most cases, inpatients require constant care, and nurses are assigned to attend to multiple patients within their designated area.

Participation:

Therefore, participation on both sides might be mandatory. Each inpatient needs to be assigned a nurse for care, and each nurse is responsible for attending to patients.

Cardinality:

The requirement suggests a one-to-many cardinality. Each nurse can care for multiple inpatients at a time, but each inpatient will be assigned to one nurse for their immediate needs.

Why is this important?

Defining this relationship ensures the database tracks which nurses are caring for inpatients. This information is crucial for managing patient care, assigning responsibilities, and ensuring proper staffing within the hospital (or any organization that provides care to residents or clients).

Inpatients-Rooms Relationship:

In a hospital setting, Inpatients (like Residents or Clients) are assigned rooms (like Units or Spaces) to stay in during their visit.

Analysis:

The requirement states that "Inpatients stay in allocated Rooms". This indicates a connection between Inpatients and Rooms. Both inpatients and rooms are essential for managing occupancy within the hospital.

Participation:

Participation for both Inpatients and Rooms might be mandatory. Each inpatient needs to be assigned a room during their stay, and each room is meant to be occupied by patients.

Cardinality:

The requirement suggests a one-to-many cardinality with a time element. Each room can house multiple inpatients throughout its existence, but at any given time, each inpatient will be assigned to one specific room.

Why is this important?

Defining this relationship ensures the database tracks which inpatients are occupying which rooms. This information is crucial for managing room occupancy, assigning beds, and ensuring efficient resource allocation within the hospital (or any organization that assigns residents or clients to units or spaces).

Inpatient-HMIS Relationship:

In a hospital setting, each Inpatient (like Resident or Client) has a corresponding record in the Health Management Information System (HMIS) (like Central Information System or Record Management). This system tracks the patient's information and activities during their stay.

Analysis:

The requirement states that "each inpatient has a corresponding record in the HMIS". This indicates a direct and essential relationship between the two. The HMIS serves as a central repository for all patient information and activity data.

Participation:

Therefore, participation for both Inpatients and the HMIS might be mandatory. Every inpatient must have a record in the system for proper management, and the HMIS needs to be able to track information for all inpatients.

Cardinality:

The requirement suggests a one-to-one cardinality. Each inpatient will have one complete record within the HMIS, and each record will be associated with a specific inpatient.

Why is this important?

Defining this relationship ensures that all patient information and activities during their stay are recorded in the HMIS. This central repository is crucial for managing patient care, tracking treatment progress, and ensuring data accuracy within the hospital (or any organization that

Inpatient-Bill Relationship:

In a hospital setting, each Inpatient (like Resident or Client) has a corresponding Bill (like Invoice or Statement) prepared upon discharge. This bill details the various charges incurred during their stay.

Analysis:

The requirement states that "each inpatient has a bill generated upon discharge". This indicates a direct relationship between inpatients and bills. An accurate bill is essential for both the hospital and the patient.

Participation:

Therefore, participation for both Inpatients and Bills might be mandatory. Every inpatient should have a corresponding bill for their stay, and each bill needs to be associated with a specific inpatient.

Cardinality:

The requirement suggests a one-to-one cardinality. Each inpatient will have one bill generated upon discharge, and each bill will be linked to a specific inpatient's stay.

Why is this important?

Defining this relationship ensures that bills are accurately tied to individual inpatients. This link between inpatients and their bills allows for efficient billing processes, transparency in charges, and proper financial management within the hospital (or any organization that provides services to residents or clients and needs to generate invoices or statements).

Wrapping Up and What Next ...

We've explored the process of analyzing relationships between entities in a hospital database model, using the requirements document as a guide. By breaking down each requirement and considering factors like participation, cardinality, and rationale, we gained valuable insights into how these entities interact and how the database should be structured to effectively manage this information.

We've also introduced general analogies alongside the hospital-specific examples to make the core concepts applicable to a broader range of database design scenarios.

In the next part of this blog post, we'll delve into the attributes of each entity, which represent the specific data points we want to capture and store within the database. By understanding both the entities and their relationships, we'll have a solid foundation for building a comprehensive and functional hospital database model.

See you there!!!