Bringing the Database to Life

Welcome back to part three of our hospital database series! In the previous installments, we embarked on a journey to translate real-world hospital processes into a structured conceptual model and then transformed it into a relational model. Now, it's time to delve deeper and explore the building blocks that bring this model to life: data types and constraints.

Think of data types as the building materials for your database. They define the kind of information each piece of data can hold – numbers for employee IDs, text for names, or dates for appointments. Using the right data types ensures efficient storage, accurate manipulation, and clear understanding of the data.

Constraints, on the other hand, act as the rules that govern this data. They prevent inconsistencies, maintain data integrity, and guarantee the quality of information within your database. Just like building regulations ensure the stability of a structure, constraints ensure the reliability of your hospital database.

Let's see how this relationships and entities translates into SQL Server syntax.

Personnel Table

create table Personnel (
employeeId int primary key identity not null,
firstName varchar(50) not null,
lastName varchar(50) not null,
position varchar(50) not null,
procedureDesignation varchar(100) not null,
certificationDate date not null,
certificationExpiry date not null,
check (certificationExpiry > certificationDate),
departmentId int
);

alter table Personnel
add foreign key (departmentId) references Department(departmentId);

• Ensures Unique Identification: The employeeId (INT, Identity) guarantees distinct personnel records through auto-generated unique IDs.

• Maintains Data Integrity: The Primary Key constraint on employeeId prevents duplicates, while the Foreign Key on departmentId ensures personnel are linked to valid departments.

• Accurate Dates: The DATE data type for certificationDate and certificationExpiry ensures proper handling of certification information.

• Data Consistency (Optional): A Check Constraint can be implemented to validate that certificationExpiry is always later than certificationDate.

• Flexibility with Names and Roles:VARCHAR fields like firstName, lastName, and position accommodate variations in names and job titles.

Department Table

create table Department (
departmentId int primary key not null,
departmentName varchar(50) unique,
headOfDepartment int
);

alter table Department
add foreign key (headOfDepartment) references Personnel(employeeId);

• Unique Identification: The departmentId (INT, Primary Key, NOT NULL) guarantees each department has a distinct and mandatory identifier.

• Department Names: The departmentName (VARCHAR(50)) provides ample space to accommodate most department names.

• Flexibility for Department Heads: The headOfDepartment (VARCHAR(200)) allows storing full names and titles for department heads.

• Data Integrity: The Primary Key constraint on departmentId ensures no duplicate departments exist.

• Foreign Key Consideration (Optional): A potential future foreign key constraint could link headOfDepartment to a specific record in the Personnel table, further enhancing data integrity.

Service Table

create table Service (
serviceId int primary key identity not null,
serviceName varchar(100) not null,
serviceDescription varchar(255),
price int not null,
departmentId int,
foreign key (departmentId) references Department(departmentId)
);

• Unique Service Identification: The Service_Id (INT, Primary Key, Identity) automatically generates unique identifiers for each service.

• Data Integrity with Foreign Key: The Department_Id (INT, Foreign Key) links each service to a valid department, ensuring consistency and preventing orphaned records.

• Accurate Service Information:Service_name (VARCHAR(100)) accommodates most service names, while the optional Description (VARCHAR(255)) allows for detailed descriptions if needed.

• Cost Management: The Price (INT) ensures accurate storage of service costs.

• Enforced Data Presence:NOT NULL constraints on Service_name, Price, and Department_Id guarantee essential service information is always present.

Patients Table

create table Patients (
patientId int primary key identity not null,
nationalId int,
phoneNo int,
email varchar(100),
residence varchar(100),
nextOfKinContact int,
dateOfBirth date,
gender varchar (10),
medicalRecordsNo int,
category varchar(50)
);

• Unique Patient Identification: The Patient_Id (INT, Primary Key, Identity) guarantees each patient has a distinct and auto-generated identifier.

• Comprehensive Patient Information: Fields like National_Id (INT), Phone_No (INT), Email (VARCHAR(100)), and Residence (VARCHAR(100)) capture essential patient details.

• Additional Considerations: Next_Of_Kin_Contact (INT) stores emergency contact information, while DOB (DATE) and Gender (VARCHAR(10)) provide demographic details.

• Medical Record Linkage: The Medical_Records_No (INT) links patients to their medical records.

• Patient Categorization: The Category (VARCHAR(50)) allows classifying patients (e.g., inpatient, outpatient).

• Data Type Variety: The table utilizes a variety of data types (INT, VARCHAR, DATE) to efficiently store different types of patient information.

Pharmacy Table

create table Pharmacy (
pharmacyId int primary key identity not null,
operatingHours varchar(50)
);

• Unique Pharmacy Identification: The Pharmacy_Id (INT, Primary Key) guarantees a distinct identifier for each pharmacy within the hospital system.

• Operational Transparency: The Operating_Hours (VARCHAR(50)) field allows storing the pharmacy's hours of operation, keeping patients and staff informed.

• Flexibility for Varied Schedules: The VARCHAR data type provides flexibility to accommodate pharmacies with different operating schedules.

Medications Table

create table Medications (
medicationId int primary key identity not null, 
medicationName varchar(100) not null, 
quantity int not null,
description varchar(255), 
expiryDate date not null, 
pharmacyId int, 
foreign key (pharmacyId) references Pharmacy(pharmacyId)
);

• Unique Medication Identification: The Medication_Id (INT, Primary Key) guarantees a distinct identifier for each medication in the database.

• Inventory Management: The Quantity (INT) field allows tracking medication inventory levels.

• Foreign Key Relationship: The Pharmacy_Id (INT, Foreign Key) establishes a link to the Pharmacy table, ensuring data integrity and medication association with a valid pharmacy.

• Accurate Information: Medication_Name (VARCHAR) and Description (VARCHAR) provide flexibility for medication names and descriptions.

• Expiry Tracking: The Expiry_Date (DATE) ensures accurate tracking of medication expiration dates.

• Data Completeness: NOT NULL constraints on crucial fields like Medication_Name, Quantity, and Expiry_Date guarantee essential medication information is always present.

Prescriptions Table

create table Prescriptions (
prescriptionId int primary key identity not null,
patientId int not null,
physicianId int not null,
medicationId int not null,
prescriptionDate date not null,
dosage varchar(50),
quantity int not null,
instructions varchar(255),
foreign key (patientId) references Patients(patientId), 
foreign key (physicianId) references Personnel(employeeId), 
foreign key (medicationId) references Medications(medicationId)
);

• Unique Prescription Tracking: The Prescription_Id (INT, Primary Key) guarantees a distinct identifier for each medication prescribed.

• Data Integrity through Foreign Keys: Foreign Keys link the table to Patients, Physicians, and Medications tables, ensuring data consistency and preventing orphaned records (prescriptions with missing patient, physician, or medication information).

• Enforced Completeness: NOT NULL constraints on crucial fields like Patient_Id, Physician_Id, Medication_Id, Prescription_Date, and Quantity guarantee essential prescription information is always present.

• Dosage and Instructions: The Dosage (VARCHAR(50)) field accommodates detailed dosage instructions, while Instructions (VARCHAR) provides space for additional doctor's notes or patient information.

Physician Table

create table Physician (
physicianId int primary key identity not null, 
empName varchar(100) not null, 
specialization varchar(50) 
);

• Unique Physician Identification: The Physician_Id (INT, Primary Key, Identity) guarantees a distinct identifier for each physician, with auto-generated values for new entries.

• Physician Information: The Emp_Name (VARCHAR(100)) field provides flexibility to accommodate variations in physician names.

• Areas of Expertise: The optional Specialization (VARCHAR(50)) allows recording a physician's specialty (e.g., Cardiology, Pediatrics).

• Data Completeness: The NOT NULL constraint on Emp_Name ensures every physician record has a name.

• Future Relationships: While lacking explicit foreign keys currently, the Physician table can act as a reference point for future relationships with other tables like Prescriptions or Departments.

Lab Tests Table

create table Lab_Tests (
testId int primary key identity not null, 
testName varchar(100) not null,
description varchar(255),
price int not null,
datePerformed date
);

• Unique Test Identification: The Lab_Test_Id (INT, Primary Key, Identity) automatically generates unique identifiers for each lab test, ensuring distinct records.

• Informative Test Details: The Test_Name (VARCHAR) and Description (VARCHAR) fields provide flexibility to store clear names and descriptions of various lab tests.

• Simplified Cost Tracking: The Price (INT, NOT NULL) field is now an integer data type, allowing storage of whole number costs for lab tests. The NOT NULL constraint ensures this field always has a value present.

Tests Ordered Table

create table Test_Ordered (
testOrderedId int primary key identity not null, 
physicianId int not null, 
patientId int not null,
testId int not null, 
dateOrdered date not null, 
foreign key (physicianId) references Physician(physicianId), 
foreign key (patientId) references Patients(patientId), 
foreign key (testId) references Lab_Tests(testId) 
);

• Tracking Lab Activity: This table bridges the gap between Physicians, Patients, and Lab Tests. It records which tests are ordered for specific patients by specific physicians.

• Unique Order Identification: The Test_Ordered_Id (INT, Primary Key, Identity) guarantees a unique identifier for each test order, ensuring clear tracking.

• Data Integrity through Foreign Keys: Foreign key constraints link Physician_Id, Patient_Id, and Test_Id to their respective tables. This ensures data consistency and prevents orphaned records (test orders with missing physician, patient, or lab test information).

• Order Timeline: The Date_Ordered field captures the date the test was ordered, providing a valuable time reference for tracking and reporting laboratory activity.

Nurses Table

create table Nurses (
  nurseId int primary key identity not null,
  nurseName varchar(100) not null, 
  departmentName varchar(50),
  departmentId int,
  foreign key (departmentId) references Department(departmentId) 
);

• Unique Nurse Identification: The nurseId (INT, Primary Key, Identity) automatically generates unique identifiers for each nurse, ensuring distinct records.

• Complete Nurse Information: The nurseName (VARCHAR(100)) provides ample space to accommodate variations in nurses' names.

• Department Association: The departmentName field offers a descriptive reference to the department, while departmentId (INT, Foreign Key) establishes a link to the Departments table, enforcing referential integrity.

• Data Consistency: The Foreign Key constraint on departmentId guarantees each nurse is associated with a valid department in the system, preventing inconsistencies and orphaned records.

Inpatients Table

create table Inpatients (
  inpatientId int primary key identity not null, 
  roomId int not null, 
  nurseId int not null,
  patientId int not null, 
  foreign key (roomId) references Rooms(roomId), 
  foreign key (nurseId) references Nurses(nurseId), 
  foreign key (patientId) references Patients(patientId) 
);

• Unique Inpatient Identification: The inpatientId (INT, Primary Key, Identity) automatically generates unique identifiers for each inpatient record, streamlining record management.

• Location Tracking: The roomId field (INT, Foreign Key) establishes a link to the Rooms table, pinpointing each inpatient's location within the hospital.

• Care Coordination: The nurseId field (INT, Foreign Key) references the Nurses table, facilitating the association between inpatients and the nurses assigned to their care.

• Patient Linkage: The patientId field (INT, Foreign Key) creates a crucial connection to the Patients table, ensuring each inpatient record links back to a valid patient, preventing inconsistencies.

• Data Integrity: The enforced Foreign Key constraints guarantee referential integrity, preventing orphaned records and maintaining data consistency across these interconnected tables.

Appointments Table

create table Appointments (
  appointmentId int primary key identity not null, 
  patientId int not null,
  physicianId int not null, 
  appointmentDate date not null,
  appointmentTime time not null,
  appointmentType varchar(50),
  appointmentDetails varchar(255), 
  foreign key (patientId) references Patients(patientId), 
  foreign key (physicianId) references Physician(physicianId) 
);

Appointments Table:

• Unique Appointment Identification: The Appointment_Id (INT, Primary Key, Identity) guarantees a distinct identifier for each appointment, streamlining scheduling and record retrieval.

• Data Integrity through Foreign Keys: Foreign key constraints on Patient_Id and Physician_Id link appointments to valid patients and physicians, preventing inconsistencies and orphaned records.

• Detailed Appointment Information:

  • Appointment_Date and Appointment_Time capture the date and time for accurate scheduling.

  • Appointment_Type specifies the type of appointment (e.g., check-up, follow-up).

  • Appointment_Details allows for additional information like location or special instructions.

• Enforced Completeness: NOT NULL constraints on crucial fields guarantee essential appointment details like date, time, patient, and physician are always present.

Rooms Table

create table Rooms (
  roomId int primary key identity not null,
  capacity int not null 
);

• Unique Room Identification: The Room_Id (INT, Primary Key) guarantees a distinct identifier for each room within the hospital, simplifying location management.

• Capacity Management: The Capacity field (INT, NOT NULL) stores the number of inpatients a room can accommodate, ensuring efficient room allocation and patient placement.

HMIS Table

create table HMIS (
  hmisId int primary key identity not null,
  admissionDate date not null, 
  dischargeDate date, 
  diagnosis text,
  treatmentDetails text,
  medicalHistory text,
  inpatientId int not null,
  foreign key (inpatientId) references Inpatients(inpatientId)
);

• Unique Record Tracking: The HMIS_Id (INT, Primary Key) guarantees a distinct identifier for each HMIS record, enabling efficient tracking and retrieval of patient information.

• Linking Inpatient Data: The Inpatient_Id (INT, Foreign Key) establishes a link to the Inpatients table, ensuring each HMIS record is associated with a specific inpatient stay.

• Timeline of Care: Admission_Date and Discharge_Date capture the inpatients' admission and discharge times, providing valuable time-based details about their stay.

• Comprehensive Medical Information:

  • Diagnosis stores the identified medical condition(s).

  • Treatment_Details documents the treatment plan and procedures.

  • Medical_History captures relevant past medical information for informed care decisions.

Bill Table

create table Bill (
  billID int primary key identity not null, 
  totalAmount int not null, 
  billingDate date not null, 
  paymentStatus varchar(50),
  inpatientId int not null unique, 
  foreign key (inpatientId) references Inpatients(inpatientId) 
);

• Unique Bill Identification: The Bill_Id (INT, Primary Key) guarantees a distinct identifier for each bill, streamlining financial record management and preventing duplicate charges.

• Linking Patient Stays: The Inpatient_Id (INT, Foreign Key) establishes a link to the Inpatients table, ensuring each bill accurately reflects charges associated with a specific inpatient stay.

• Financial Tracking:

  • Total_Amount captures the total cost of the inpatient's stay.

  • Billing_Date records the date the bill was issued.

  • Payment_Status tracks the payment status (e.g., paid, pending, overdue).

Phew, that was a whirlwind tour of hospital database tables! We covered a lot of ground, from doctors and departments to bills and prescriptions. By now, you're practically a hospital data whiz!

But seriously, understanding these tables is crucial for a well-functioning hospital. They store the information that keeps things running smoothly, ensures patients receive the best care, and helps manage finances effectively.

This wraps up our series on Database Creation using this Hospital Database as our application. I hope you've been able to learn a thing or two in this journey.

Quick refresher on the series:

• Link to Blog Post 1: Database Creation From Scratch: Part 1 : From a Requirements file to a Conceptual Model (https://datacurious.hashnode.dev/database-creation-from-scratch)

• Link to Blog Post 2: Relationship Mapping: Part 2: Conceptual Model to a Relational Schema (https://datacurious.hashnode.dev/database-creation-relationship-mapping)