General Hospital System
Project Overview
The OakRidge General Hospital project is a comprehensive portfolio initiative designed to showcase the full capabilities of a Senior SQL Database Administrator and Database Designer. This project demonstrates expertise across the full data lifecycle, including database design, security implementation, cloud migration, DevOps automation, performance optimization, and advanced analytics using Power BI. With a modern front-end built in Razor Pages and a backend fully powered by Azure SQL technologies, the project emulates a real-world hospital system running in a secure, scalable cloud environment.
Project Duration: 6 weeks
Project Breakdown
My Role
As the project's sole database designer and developer, I led the initiative end-to-end—from data modeling and security implementation to cloud migration and DevOps automation. I designed the medical database schema, ensured data protection through Dynamic Data Masking and Always Encrypted features, and built a responsive web application using .NET Core and Razor Pages. I also integrated monitoring, logging, and observability solutions, and created dashboards in Power BI to deliver actionable healthcare insights. Every component was crafted to simulate enterprise-level hospital data workflows.
Project Specifications
• Designed and populated an on-premises medical database, later migrated to Azure SQL Managed Instance.
• Built with .NET Core, Razor Pages, and Entity Framework Core, following layered architecture principles.
• Integrated security features: Always Encrypted, Dynamic Data Masking, Row-Level Security, and SQL Auditing.
• Created role-specific Razor Pages (Admin, Doctor, Receptionist, Nurse) to simulate real hospital workflows.
• Enabled CI/CD via Azure DevOps pipelines, with full Git version control and deployment to Azure App Service.
• Incorporated Power BI dashboards to visualize appointments, bed usage, lab turnaround times, and vital signs.
• Connected export pipelines to PostgreSQL, MongoDB, CSV, API, and PowerShell automation.
• Included maintenance plans, query optimization, and observability tooling using Query Store and Log Analytics.
Deliverables
• End-to-end hospital management system with real-world use case coverage.
• Fully responsive Razor Pages interface with role-based views.
• Azure-hosted database using secure and scalable cloud architecture.
• Power BI dashboards with KPIs for appointments, lab results, patient demographics, and vital signs.
• Database performance tuning artifacts: index plans, execution plans, and monitoring scripts.
• Security audit reports, encryption strategies, and access control setup.
• Export tools and automated connectivity to external systems and formats.
• Complete documentation: system architecture, data flows, security policies, and deployment guidelines.
Database Architecture & Design
The database design of the OakRidge General Hospital system reflects a meticulous approach to healthcare data modeling. Built to support high availability, regulatory compliance, and secure integration with other clinical systems, the architecture ensures complete traceability, optimal performance, and long-term scalability. This section illustrates the key aspects of the data architecture, supported by a visual walkthrough of the database design. Explore how each component—from appointments and diagnostics to user security and audit trails—was structured to enable a seamless digital hospital experience.
🔗 Data Integrity
Designed to ensure accuracy and reliability of patient information, with constraints and normalization applied for consistency, traceability, and compliance with healthcare standards.
🔒 Security Layers
Sensitive data like SSNs, diagnoses, and emails are protected using Always Encrypted and Data Masking, supported by access roles and audit trails that enforce HIPAA-grade security.
📈 System Scalability
Built with modular, API-ready structures that support integration with external systems like EHRs and insurance, the schema enables long-term expansion and cloud-optimized performance.
Migration flow diagram
Step 1: Database Migration
- Use Azure DMS for seamless transfer
- Validate schema compatibility
- Configure Azure SQL firewall rules
Step 2: Local Testing
- Update connection strings
- Test with Azure AD authentication
- Verify performance benchmarks
Step 3: WHC Deployment
- Publish as self-contained .NET Core app
- Secure credentials via ENV variables
- Enable HTTPS enforcement
Step 4: Post-Migration
- Implement auto-scaling
- Set up Azure monitoring
- Schedule failover drills
Power BI Analytics
The analytics layer of the OakRidge General Hospital system transforms clinical and operational data into actionable insights through Power BI. By leveraging optimized SQL views, secure connectivity, and real-time indicators, hospital staff and administrators gain access to dashboards that support faster decisions and better patient outcomes. From bed availability to diagnostic turnaround times, this analytics integration demonstrates how data becomes a strategic asset in a modern healthcare environment.
📅 Operational Dashboards
Daily metrics on appointments, cancellations, bed occupancy, and resource utilization help staff plan and respond effectively.
🧬 Clinical Trends
Visualizations track lab results, patient vitals, and prescription activity over time—enabling proactive monitoring and care adjustments.
💰 Financial Insights
Reports reveal patterns in billing, insurance claims, and departmental cost trends, supporting audits and strategic planning.
Data Export & Interoperability Layer
The OakRidge General Hospital database was designed with seamless data interoperability in mind. Whether exchanging information with insurance systems, exporting patient summaries, or feeding external analytics tools, the schema supports secure, structured, and automated data flows. This integration-ready design allows the hospital to function within a hybrid ecosystem—on-prem and in the cloud—without compromising data governance or performance.
🧾 Export-Ready Views
SQL views were crafted to deliver clean, filtered datasets for reporting, dashboards, or secure external sharing via APIs.
🔁 Multi-Format Support
Data can be exported to CSV, JSON, PostgreSQL, and MongoDB, enabling cross-platform use without complex ETL processes.
⚙️ Automated Pipelines
Scheduled exports and sync jobs run via SQL Agent or Azure Data Factory, with full audit tracking of job history and success.
Tools
