Home › Databases › Normalization

Designing a Hospital Management System

In this case study, we will design a database for a Hospital Management System. The objective is to model a system that can manage information about patients, doctors, treatments, room assignments, guardians, and medical tests. We’ll follow a structured approach, step-by-step, to create an ER diagram and map it to a relational schema.

Requirements Analysis

The first and most critical step in database modeling is understanding the requirements of the system. This involves interacting with stakeholders, analyzing existing processes, and defining the scope of the system.

For our case study, let's outline the key functionalities that the HMS should support:

Patient Management: Register new patients, update patient information, and record patient visits.
Doctor Management: Maintain doctor profiles, specialties, schedules, and availability.
Appointment Scheduling: Schedule appointments between patients and doctors.
Medical Records: Store and retrieve patient medical histories, treatments, and prescriptions.
Billing: Generate bills for services rendered, manage payments and insurance details.
Room Management: Assign rooms to patients, manage room availability and types.

Step-by-Step ER Diagram Creation

We will build the ER diagram for the Hospital Management System through the following four steps.

Step 1: Identify Entities

Based on the requirements, we can identify the following entities:

Patient
Doctor
Appointment
Medical_Record
Prescription
Bill
Room
Department
Medicine
Insurance

Step 2: Identify Attributes and Define Primary Keys

Next, let’s identify attributes for each entity and define primary keys:

1. Patient

Patient_ID (Primary Key)
Name (Composite)
First_Name
Last_Name
Date_of_Birth
Gender
Address
Phone_Number (Multi-valued)
Email

2. Doctor

Doctor_ID (Primary Key)
Name (Composite)
First_Name
Last_Name
Specialty
Phone_Number (Multi-valued)
Email

3. Appointment

Appointment_ID (Primary Key)
Appointment_Date
Appointment_Time
Patient_ID (Foreign Key)
Doctor_ID (Foreign Key)
Reason_For_Visit

4. Medical_Record

Record_ID (Primary Key)
Patient_ID (Foreign Key)
Diagnosis
Treatment_Details
Record_Date
Doctor_ID (Foreign Key)

5. Prescription

Prescription_ID (Primary Key)
Record_ID (Foreign Key)
Medicine_ID (Foreign Key)
Dosage
Frequency
Duration

6. Bill

Bill_ID (Primary Key)
Patient_ID (Foreign Key)
Bill_Date
Amount
Payment_Method

7. Room

Room_Number (Primary Key)
Room_Type
Availability_Status
Patient_ID (Foreign Key)

8. Department

Department_ID (Primary Key)
Department_Name
Location

9. Medicine

Medicine_ID (Primary Key)
Medicine_Name
Manufacturer
Price

10. Insurance

Insurance_ID (Primary Key)
Provider_Name
Policy_Number
Coverage_Details

Step 3: Establish Relationships Between Entities

Now, we will establish relationships between the entities based on the requirements:

Common Types of Relationships

One-to-One (1:1): Each instance of Entity A relates to one instance of Entity B.
One-to-Many (1:N): One instance of Entity A relates to multiple instances of Entity B.
Many-to-Many (M:N): Multiple instances of Entity A relate to multiple instances of Entity B.

Relationships in HMS

1. Patient and Appointment

A patient can have multiple appointments.
Each appointment is for one patient.
Type: One-to-Many (Patient to Appointment)

2. Doctor and Appointment

A doctor can have multiple appointments.
Each appointment is with one doctor.
Type: One-to-Many (Doctor to Appointment)

3. Patient and Medical_Record

A patient can have multiple medical records.
Each medical record belongs to one patient.
Type: One-to-Many (Patient to Medical_Record)

4. Doctor and Medical_Record

A doctor can write multiple medical records.
Each medical record is written by one doctor.
Type: One-to-Many (Doctor to Medical_Record)

5. Medical_Record and Prescription

A medical record can have multiple prescriptions.
Each prescription is associated with one medical record.
Type: One-to-Many (Medical_Record to Prescription)

6. Prescription and Medicine

A prescription can include multiple medicines.
A medicine can be prescribed in multiple prescriptions.
Type: Many-to-Many (Resolved via an associative entity)

7. Patient and Bill

A patient can have multiple bills.
Each bill is for one patient.
Type: One-to-Many (Patient to Bill)

8. Patient and Room

A room can be assigned to one patient at a time.
A patient can be assigned to one room at a time.
Over time, rooms are assigned to different patients.
Type: One-to-One

9. Patient and Department

A department can be assigned to multiple patients at a time.
A patient can be assigned to one department at a time.
Type: Many-to-One (Patient to Department)

10. Department and Doctor

A department can have multiple doctors at a time.
A doctor can have only one department at a time.
Type: One-to-Many (Department to Doctor)

11. Patient and Insurance

A patient can have zero or multiple insurances.
Each insurance with a unique policy number is for one patient.
Type: One-to-Many (Patient to Insurance)

Here is the final ER diagram.

Mapping the ER Diagram to a Relational Schema

To create a relational schema from an ER diagram, we follow a structured approach that includes identifying entities, converting relationships, and handling attributes (especially primary and foreign keys). This ensures a well-designed relational database that preserves the relationships and dependencies specified in the ER model.

Step 1: Convert Entities to Tables

Each entity in the ER diagram becomes a table in the relational model. Here’s how to approach this:

Identify each entity: In this case, the entities are Doctor, Patient, etc.
Convert each entity to a table: Each entity box in the ER diagram will directly translate to a table with the same name.
Assign primary keys: Each table should have a primary key based on the unique attribute of each entity (e.g., Doctor_ID for Doctor, Patient_ID for Patient).

Step 2: Convert Attributes for Each Entity

For each table, we add columns based on the attributes shown in the ER diagram. Here’s how to handle attributes:

Direct Attributes: Each attribute in the ER diagram becomes a column in the corresponding table.
Primary Key Identification: Ensure that each table’s primary key is clearly marked to uniquely identify each record.
Handle Composite Attribute: Remove the composite attribute name and add its subfields first_name and last_name in the table.
Handle Multi-valued Attribute: For each multi-values attributes like Phone_Number create a separate table.

Step 3: Map Relationships to Tables

Map relationships between entities by adding foreign keys or creating junction tables based on the type of relationship.

a. One-to-One (1:1)

Add the primary key of one entity as a foreign key in the related entity’s table.

Example:

Relationship: Patient ↔ Room
Tables:
- Room Table with Assigned_Patient_ID (FK)

b. One-to-Many (1:N)

Add the primary key of the "one" side as a foreign key in the "many" side table.

Example:

Relationship: Doctor ↔ Appointment
Tables:
- Doctor Table
- Appointment Table with Doctor_ID (FK)

c. Many-to-Many (M:N)

Create a junction table with the primary keys of both entities as foreign keys, along with any attributes specific to the relationship.

Example:

Relationship: Prescription ↔ Medicine
Junction Table: Prescription_Medicine with columns:
- Prescription_ID (FK)
- Medicine_ID (FK)

Here is the final relational model for the Hospital Management System.

Relational Model for Hospital Management System

Step 4: Create Relational Schema

Now, we'll translate the ER diagram into a relational database schema.

1. Patient

CREATE TABLE Patient (
    Patient_ID INT PRIMARY KEY,
    First_Name VARCHAR(256),
    Last_Name VARCHAR(256),
    Gender CHAR(10),
    Address VARCHAR(512),
    Email VARCHAR(128),
    Date_of_Birth DATE,
    Department_ID INT
    FOREIGN KEY (Department_ID) REFERENCES Department(Department_ID)
);

2. Patient_Phone_Number

CREATE TABLE Patient_Phone_Number (
    Patient_ID INT,
    Phone_Number VARCHAR(13),
    PRIMARY KEY (Patient_ID, Phone_Number),
    FOREIGN KEY (Patient_ID) REFERENCES Patient(Patient_ID)
);

3. Doctor

CREATE TABLE Doctor (
    Doctor_ID INT PRIMARY KEY,
    First_Name VARCHAR(256),
    Last_Name VARCHAR(256),
    Specialty VARCHAR(16),
    Email VARCHAR(128),
    Department_ID INT
    FOREIGN KEY (Department_ID) REFERENCES Department(Department_ID)
);

4. Doctor_Phone_Number

CREATE TABLE Doctor_Phone_Number (
    Doctor_ID INT,
    Phone_Number VARCHAR(13),
    PRIMARY KEY (Doctor_ID, Phone_Number),
    FOREIGN KEY (Doctor_ID) REFERENCES Doctor(Doctor_ID)
);

5. Appointment

CREATE TABLE Appointment (
    Appointment_ID INT PRIMARY KEY,
    Appointment_Date DATE,
    Appointment_Time TIME,
    Patient_ID INT,
    Doctor_ID INT,
    Reason_For_Visit VARCHAR(1028),
    FOREIGN KEY (Patient_ID) REFERENCES Patient(Patient_ID),
    FOREIGN KEY (Doctor_ID) REFERENCES Doctor(Doctor_ID)
);

6. Medical_Record

CREATE TABLE Medical_Record (
    Record_ID INT PRIMARY KEY,
    Patient_ID INT,
    Diagnosis VARCHAR(512),
    Treatment_Details TEXT,
    Record_Date DATE,
    Doctor_ID INT,
    FOREIGN KEY (Patient_ID) REFERENCES Patient(Patient_ID),
    FOREIGN KEY (Doctor_ID) REFERENCES Doctor(Doctor_ID)
);

7. Prescription

CREATE TABLE Prescription (
    Prescription_ID INT PRIMARY KEY,
    Dosage INT,
    Frequency INT,
    Duration INT,
    Record_ID INT,
    FOREIGN KEY (Record_ID) REFERENCES Medical_Record(Record_ID)
);

8. Prescription_Medicine

CREATE TABLE Prescription_Detail (
    Prescription_ID INT,
    Medicine_ID INT,
    PRIMARY KEY (Prescription_ID, Medicine_ID),
    FOREIGN KEY (Prescription_ID) REFERENCES Prescription(Prescription_ID),
    FOREIGN KEY (Medicine_ID) REFERENCES Medicine(Medicine_ID)
);

9. Medicine

CREATE TABLE Medicine (
    Medicine_ID INT PRIMARY KEY,
    Medicine_Name VARCHAR(256),
    Manufacturer VARCHAR(256),
    Price DECIMAL(10, 2)
);

10. Bill

CREATE TABLE Bill (
    Bill_ID INT PRIMARY KEY,
    Patient_ID INT,
    Bill_Date DATE,
    Amount DECIMAL(10, 2),
    Payment_Method VARCHAR(18),
    Insurance_Coverage Boolean,
    FOREIGN KEY (Patient_ID) REFERENCES Patient(Patient_ID)
);

11. Room

CREATE TABLE Room (
    Room_Number INT PRIMARY KEY,
    Room_Type VARCHAR(50),
    Availability_Status Boolean,
    Assigned_Patient_ID INT,
    FOREIGN KEY (Assigned_Patient_ID) REFERENCES Patient(Patient_ID)
);

12. Department

CREATE TABLE Department (
    Department_ID INT PRIMARY KEY,
    Department_Name VARCHAR(256),
    Location VARCHAR(256)
);

13. Insurance

CREATE TABLE Insurance (
    Insurance_ID INT PRIMARY KEY,
    Provider_Name VARCHAR(256),
    Policy_Number INT,
    Coverage_Details TEXT
);

🤖 Don't fully get this? Learn it with Claude

Stuck on Designing a Hospital Management System? Open Claude, copy a block below, and it'll teach you this exact concept — visually and interactively.

🎨 Explain it visually

Build the mental picture, not memorization.

I just read a lesson on **Designing a Hospital Management System** (Databases) and want to truly understand it. Explain Designing a Hospital Management System from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

🤔 Walk me through it (interactive)

Socratic — adapts to where you're stuck.

Teach me **Designing a Hospital Management System** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

🧪 Quiz me & fix my gaps

Active recall exposes what you missed.

Quiz me on **Designing a Hospital Management System** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

🧠 Make it stick

Intuition + hook + flashcards for long-term memory.

Help me remember **Designing a Hospital Management System** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

📝 My notes

← Exercise 2 Designing a Hotel Management Syste →