Project Overview

I designed and developed this SQL Server database for capturing, storing, and processing nutrition services data. This database serves as a centralized repository for over 5,000 health facilities, enabling structured data collection, validation, and analysis for health managers at all levels—from local clinics to national decision-makers.

The database was cloud-hosted and integrated into an ASP.NET Core MVC web application to facilitate real-time data access and reporting. It was designed to handle large-scale data processing, ensuring efficient storage, retrieval, and analysis through optimized queries, stored procedures, and indexing strategies.

Database Architecture & Optimization

Database Design & Data Modeling

🔹Developed a relational database model that ensures data integrity and scalability.
🔹Designed an Entity-Relationship Diagram (ERD) to define the relationships among patients, facilities, nutrition services, providers, and reporting structures.
🔹Implemented referential integrity using foreign keys and domain integrity with constraints to ensure data accuracy (e.g., valid service types, standard reporting formats).

Database Structure & Core Modules

🔹Organized the database into 9 key modules, each designed to store and process specific aspects of nutrition services:

User Management & Access Control

🔑A dedicated User Management module was designed to secure system access and prevent unauthorized data manipulation.

🔑Security Features Implemented:
    ✅Password Hashing & Salting – Prevents credential leaks.
    ✅Role-Based Query Access – Restricts sensitive data access.
    ✅Login Attempt Tracking – Blocks repeated failed login attempts.
    ✅Session Expiry Enforcement – Ensures inactive users are automatically logged out.

Query Optimization & Performance Tuning

🔹Utilized advanced SQL techniques such as Common Table Expressions (CTEs) and Window Functions (ROW_NUMBER(), RANK()) to efficiently process large datasets.
🔹Designed optimized queries for data aggregation, summarization, and trend analysis.
🔹 Indexed frequently queried columns (FacilityID, ServiceID, UserID) to boost performance.
🔹 Used query execution plans to identify bottlenecks and optimize slow-running queries.

Automated Data Processing & Backup Management

🔹Developed stored procedures for:

• Data validation – Ensuring facility data completeness and consistency.
• Data movement – Automating monthly summarization and report generation.
• Automated backup & recovery – Scheduled using SQL Server Agent.

🔹Integrated job scheduling to ensure daily backups without manual intervention.

Key Takeaways

Final Thoughts

This project transformed nutrition services data management across thousands of health facilities, ensuring secure access control, automated reporting, and optimized data processing. By leveraging SQL Server best practices, and query optimization. Health decision-makers now have accurate, structured, and scalable data for evidence-based decision-making.

Sample Code (TSQL)

                    

CREATE PROCEDURE [dbo].[UpdateHFMonitoringTableValues]
AS
BEGIN
    -- Delete orphaned records from DateValues, NumberValues, and TextValues tables
    DELETE FROM DateValues 
    WHERE ReportId NOT IN (SELECT id FROM Reports);

    DELETE FROM NumberValues 
    WHERE ReportId NOT IN (SELECT id FROM Reports);

    DELETE FROM TextValues 
    WHERE ReportId NOT IN (SELECT id FROM Reports);

    -- Declare variables
    DECLARE @TotalReports INTEGER;
    DECLARE @DateValues INTEGER;
    DECLARE @NumberValues INTEGER;
    DECLARE @TextValues INTEGER;
    DECLARE @GrandTotal INTEGER;
    DECLARE @GrandTotal2 INTEGER;

    -- Get counts of NumberValues and TextValues
    SET @NumberValues = (SELECT COUNT(*) FROM NumberValues);
    SET @TextValues = (SELECT COUNT(*) FROM TextValues);

    -- Calculate GrandTotal from TempHFMonitoringrecords
    SET @GrandTotal = (
        SELECT NumberValues + TextValues AS Total 
        FROM TempHFMonitoringrecords
    );

    SET @GrandTotal2 = @NumberValues + @TextValues;

    -- Compare GrandTotal values
    IF @GrandTotal <> @GrandTotal2
    BEGIN
        SET @TotalReports = (SELECT COUNT(*) FROM Reports);
        SET @DateValues = (SELECT COUNT(*) FROM DateValues);

        -- Truncate TempHFMonitoringrecords table
        TRUNCATE TABLE TempHFMonitoringrecords;

        -- Insert new values into TempHFMonitoringrecords
        INSERT INTO TempHFMonitoringrecords (TotalReports, NumberValues, DateValues, TextValues) 
        VALUES (@TotalReports, @NumberValues, @DateValues, @TextValues);

        -- Execute related stored procedures
        EXEC dbo.spHFMonitoringNumberValues;
        EXEC dbo.spHFNutritionServicesValues;
        EXEC dbo.spHFMonitoringfindings;
        EXEC dbo.spHFSAMDataQuality;

        -- Optional: Uncomment to view records
        -- SELECT * FROM TempHFMonitoringrecords;
    END
END

                

                    

SELECT 
    ProvCode, 
    ProvName, 
    Year, 
    Month, 
    SUM(TotalNewCases) AS Num, 
    1 AS Denom
FROM (
    -- First UNION query
    SELECT 
        p.ProvCode, 
        p.ProvName, 
        n.Year, 
        n.Month, 
        SUM(ISNULL(otptfu.z3score, 0)) + 
        SUM(ISNULL(otptfu.odema, 0)) + 
        SUM(ISNULL(otptfu.MUAC115, 0)) AS TotalNewCases
    FROM dbo.NMR n
    INNER JOIN dbo.tblOTPTFU otptfu ON n.NMRID = otptfu.NMRID
    INNER JOIN dbo.FacilityInfo fi ON n.FacilityID = fi.FacilityID
    INNER JOIN dbo.Districts d ON fi.DistCode = d.DistCode
    INNER JOIN dbo.Provinces p ON d.ProvCode = p.ProvCode
    WHERE n.Year > 1395
    GROUP BY n.Year, n.Month, p.ProvCode, p.ProvName
    
    UNION ALL
    
    -- Second UNION query
    SELECT 
        p1.ProvCode, 
        p1.ProvName, 
        n1.Year, 
        n1.Month, 
        SUM(ISNULL(otp.z3score, 0)) + 
        SUM(ISNULL(otp.odema, 0)) + 
        SUM(ISNULL(otp.MUAC115, 0)) AS TotalNewCases
    FROM dbo.NMR n1
    INNER JOIN dbo.tblOTP otp ON n1.NMRID = otp.NMRID
    INNER JOIN dbo.FacilityInfo fi1 ON n1.FacilityID = fi1.FacilityID
    INNER JOIN dbo.Districts d1 ON fi1.DistCode = d1.DistCode
    INNER JOIN dbo.Provinces p1 ON d1.ProvCode = p1.ProvCode
    WHERE n1.Year > 1395
    GROUP BY n1.Year, n1.Month, p1.ProvCode, p1.ProvName
) AS mm
GROUP BY ProvCode, ProvName, Year, Month;

                

                    

SELECT 
    CONCAT(p.ProvCode, tpc.FacilityTypeId, tpc.IndicatorId, tpc.[Year], tpc.[Month]) AS Id,
    p.ProvCode AS ProvinceId,
    p.ProvName AS Province,
    tpc.FacilityTypeId,
    ft.TypeAbbrv AS FacilityType,
    tpc.IndicatorId,
    ic.IndicatorName,
    tpc.Implementer,
    tpc.Module,
    tpc.Year AS [Year],
    tpc.Month AS [Month],
    tpc.Quarter AS Quarter,
    SUM(tpc.Num) AS Num,
    SUM(tpc.Denom) AS Denom
FROM dbo.totalpivotcombined_sbp tpc
INNER JOIN dbo.FacilityInfo fi ON tpc.FacilityID = fi.FacilityID
INNER JOIN dbo.Districts d ON fi.DistCode = d.DistCode
INNER JOIN dbo.Provinces p ON d.ProvCode = p.ProvCode
INNER JOIN dbo.FacilityTypes ft ON fi.FacilityType = ft.FacTypeCode
INNER JOIN dbo.Indicatorscombined ic ON tpc.IndicatorId = ic.IndicatorId
GROUP BY 
    p.ProvCode, 
    p.ProvName, 
    tpc.FacilityTypeId, 
    ft.TypeAbbrv, 
    tpc.IndicatorId, 
    ic.IndicatorName, 
    tpc.Implementer, 
    tpc.Module, 
    tpc.Year, 
    tpc.Month, 
    tpc.Quarter;

                

                    

WITH CTE_HF_ReqDetails AS (
    SELECT 
        hfrd.Id,
        hfrd.RequestId,
        hfrd.FacilityId,
        fi.FacilityName,
        hfrd.SupplyId,
        hfrd.FacilityTypeId,
        ft.TypeAbbrv AS FacilityType,
        d.DistName AS District,
        p.ProvName AS Province,
        imp.ImpAcronym AS Implementer,
        CONVERT(INT, hfrd.Children) AS Children,
        CONVERT(FLOAT, ROUND(hfrd.Buffer, 2)) AS Buffer,
        CONVERT(INT, hfrd.CurrentBalance) AS CurrentBalance,
        CONVERT(INT, hfrd.Adjustment) AS Adjustment,
        hfrd.AdjComment,
        CONVERT(FLOAT, hfrd.StockForChildren) AS StockForChildren,
        hfrd.Program,
        tss.Item,
        CONVERT(FLOAT, ISNULL(hfrd.Adjustment, 0)) + 
        CONVERT(FLOAT, ISNULL(hfrd.StockForChildren, 0)) AS TotalNeeded,
        hfrd.Esttype,
        sr.RequesttypeId,
        -- Adding a row number to uniquely identify each record per facility & supply
        ROW_NUMBER() OVER (PARTITION BY hfrd.FacilityId, hfrd.SupplyId ORDER BY hfrd.RequestId DESC) AS RowNum,
        -- Calculating the running total of stock adjustments per facility
        SUM(hfrd.Adjustment) OVER (PARTITION BY hfrd.FacilityId ORDER BY hfrd.RequestId) AS RunningTotalAdjustment,
        -- Calculating the average buffer per province
        AVG(hfrd.Buffer) OVER (PARTITION BY p.ProvCode) AS AvgBufferPerProvince
    FROM dbo.scmHFReqDetails hfrd
    INNER JOIN dbo.FacilityInfo fi ON hfrd.FacilityId = fi.FacilityID
    INNER JOIN dbo.FacilityTypes ft ON hfrd.FacilityTypeId = ft.FacTypeCode
    INNER JOIN dbo.tlkpSstock tss ON hfrd.SupplyId = tss.sstockID
    INNER JOIN dbo.Districts d ON fi.DistCode = d.DistCode
    INNER JOIN dbo.scmRequest srq ON hfrd.RequestId = srq.RequestId
    INNER JOIN dbo.scmRounds sr ON srq.RequestPeriod = sr.RoundId
    INNER JOIN dbo.Provinces p ON srq.ProvinceId = p.ProvCode
    INNER JOIN dbo.Implementers imp ON srq.ImpId = imp.ImpCode
)
SELECT * FROM CTE_HF_ReqDetails;

                

                    

ALTER PROCEDURE [dbo].[UpdateNutritionMNPCampaigns]
    @Username NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON; -- Prevent extra result sets from interfering with SELECT statements.

    -- Insert new records into NutritionMNPCampaigns if they do not already exist
    INSERT INTO [dbo].[NutritionMNPCampaigns] (
        ProvinceID, Year, Month, 
        Children6to59MonthReceivedMNPMale, Children6to59MonthReceivedMNPFemale,
        MDsTrainedOnMIYCNMale, MDsTrainedOnMIYCNFemale, MidwivesTrainedOnMIYCN,
        NursesTrainedOnMIYCNMale, NursesTrainedOnMIYCNFemale, 
        NCsReceived12DaysInitialTraining, NCsReceived6DaysRefresherTraining, 
        FoodDemoSessions, CHWsReceivedMonthlyIncentiveMale, CHWsReceivedMonthlyIncentiveFemale,
        TargetWomenReceivedCashWithNutritionServices, 
        BoysUnder2WeightedForGrowthCommunity, GirlsUnder2WeightedForGrowthCommunity, 
        NoofAdolescentGirlsReceivedIronFolicAcid, 
        ChschwstrainedonCBNPmale, ChschwstrainedonCBNPfemale,
        ChschwstrainedonMNPmale, ChschwstrainedonMNPfemale, 
        ChwschwstrainedoncomWIFSmale, ChwschwstrainedoncomWIFSfemale, 
        ChwschwstrainedonmontherMUACmale, ChwschwstrainedonmontherMUACfemale,
        CampaignVitaminAMale, CampaignVitaminAFemale,
        NoHPsupportedbyCBNPprogram, Username, Updatedate
    )
    SELECT 
        v.ProvinceID, v.Year, v.Month, 
        v.Children6to59MonthReceivedMNPMale, v.Children6to59MonthReceivedMNPFemale, 
        v.MDsTrainedOnMIYCNMale, v.MDsTrainedOnMIYCNFemale, v.MidwivesTrainedOnMIYCN, 
        v.NursesTrainedOnMIYCNMale, v.NursesTrainedOnMIYCNFemale, 
        v.NCsReceived12DaysInitialTraining, v.NCsReceived6DaysRefresherTraining, 
        v.FoodDemoSessions, v.CHWsReceivedMonthlyIncentiveMale, v.CHWsReceivedMonthlyIncentiveFemale, 
        v.TargetWomenReceivedCashWithNutritionServices, 
        v.BoysUnder2WeightedForGrowthCommunity, v.GirlsUnder2WeightedForGrowthCommunity, 
        v.NoofAdolescentGirlsReceivedIronFolicAcid, 
        v.ChschwstrainedonCBNPmale, v.ChschwstrainedonCBNPfemale,
        v.ChschwstrainedonMNPmale, v.ChschwstrainedonMNPfemale, 
        v.ChwschwstrainedoncomWIFSmale, v.ChwschwstrainedoncomWIFSfemale, 
        v.ChwschwstrainedonmontherMUACmale, v.ChwschwstrainedonmontherMUACfemale,
        v.CampaignVitaminAMale, v.CampaignVitaminAFemale,
        v.NoHPsupportedbyCBNPprogram, v.Username, v.Updatedate
    FROM dbo.vTempNutritionMNPCampaigns v
    WHERE 
        CONCAT(v.ProvinceID, v.Year, v.Month) NOT IN 
        (SELECT CONCAT(ProvinceID, Year, Month) FROM NutritionMNPCampaigns)
        AND v.ProvinceID IS NOT NULL 
        AND (v.Year * 100 + v.Month) <= (YEAR(GETDATE()) * 100 + MONTH(GETDATE()));

    -- Update existing records if they were updated within the last 60 days by the specified user
    UPDATE n
    SET 
        n.Children6to59MonthReceivedMNPMale = t.Children6to59MonthReceivedMNPMale,
        n.Children6to59MonthReceivedMNPFemale = t.Children6to59MonthReceivedMNPFemale,
        n.MDsTrainedOnMIYCNMale = t.MDsTrainedOnMIYCNMale,
        n.MDsTrainedOnMIYCNFemale = t.MDsTrainedOnMIYCNFemale,
        n.MidwivesTrainedOnMIYCN = t.MidwivesTrainedOnMIYCN,
        n.NursesTrainedOnMIYCNMale = t.NursesTrainedOnMIYCNMale,
        n.NursesTrainedOnMIYCNFemale = t.NursesTrainedOnMIYCNFemale,
        n.NCsReceived12DaysInitialTraining = t.NCsReceived12DaysInitialTraining,
        n.NCsReceived6DaysRefresherTraining = t.NCsReceived6DaysRefresherTraining,
        n.FoodDemoSessions = t.FoodDemoSessions,
        n.CHWsReceivedMonthlyIncentiveMale = t.CHWsReceivedMonthlyIncentiveMale,
        n.CHWsReceivedMonthlyIncentiveFemale = t.CHWsReceivedMonthlyIncentiveFemale,
        n.TargetWomenReceivedCashWithNutritionServices = t.TargetWomenReceivedCashWithNutritionServices,
        n.BoysUnder2WeightedForGrowthCommunity = t.BoysUnder2WeightedForGrowthCommunity,
        n.GirlsUnder2WeightedForGrowthCommunity = t.GirlsUnder2WeightedForGrowthCommunity,
        n.NoofAdolescentGirlsReceivedIronFolicAcid = t.NoofAdolescentGirlsReceivedIronFolicAcid,
        n.ChschwstrainedonCBNPmale = t.ChschwstrainedonCBNPmale,
        n.ChschwstrainedonCBNPfemale = t.ChschwstrainedonCBNPfemale,
        n.ChschwstrainedonMNPmale = t.ChschwstrainedonMNPmale,
        n.ChschwstrainedonMNPfemale = t.ChschwstrainedonMNPfemale,
        n.ChwschwstrainedoncomWIFSmale = t.ChwschwstrainedoncomWIFSmale,
        n.ChwschwstrainedoncomWIFSfemale = t.ChwschwstrainedoncomWIFSfemale,
        n.ChwschwstrainedonmontherMUACmale = t.ChwschwstrainedonmontherMUACmale,
        n.ChwschwstrainedonmontherMUACfemale = t.ChwschwstrainedonmontherMUACfemale,
        n.CampaignVitaminAMale = t.CampaignVitaminAMale,
        n.CampaignVitaminAFemale = t.CampaignVitaminAFemale,
        n.NoHPsupportedbyCBNPprogram = t.NoHPsupportedbyCBNPprogram,
        n.Username = t.Username,
        n.Updatedate = t.Updatedate
    FROM NutritionMNPCampaigns n
    INNER JOIN vTempNutritionMNPCampaigns t
        ON n.ProvinceID = t.ProvinceID 
        AND n.Year = t.Year 
        AND n.Month = t.Month
    WHERE 
        n.Username = @Username 
        AND DATEDIFF(DAY, n.Updatedate, GETDATE()) < 60;
END;