Designed and implemented an Azure SQL backend for a service request management system, creating a normalized relational model with lookup tables, constraints, assignment tracking, comments, and a reporting-ready SQL view.
Challenge
The system needed a reliable backend structure that avoided free-text sprawl, enforced valid request categories and statuses, and could support future Power Apps and Power BI integration.
What I Owned
Azure SQL schema design, normalized table structure, primary and foreign keys, priority constraints, seed/reference data, timestamp trigger logic, and reporting view creation.
Stack
Azure SQL Database, T-SQL, relational modeling, SQL views, triggers
Impact
Created a governed backend foundation for request intake, status tracking, assignment history, comments, and analytics-ready reporting.
01
Lookup Tables
Categories and statuses are stored as controlled reference data instead of repeated free text.
02
Request Table
The main service request table stores requester, priority, due date, category, and status data.
03
Relationships
Foreign keys enforce valid category, status, comment, and assignment relationships.
04
Database Logic
Constraints and trigger logic maintain priority rules and updated timestamps at the database layer.
05
Reporting View
A SQL view exposes readable request data for future Power BI reporting and Power Apps display.
View full SQL build script
CREATE TABLE dbo.sr_category (
category_id INT IDENTITY(1,1) PRIMARY KEY,
category_name NVARCHAR(200) NOT NULL UNIQUE,
is_active BIT NOT NULL DEFAULT 1
);
CREATE TABLE dbo.sr_status (
status_id INT IDENTITY(1,1) PRIMARY KEY,
status_name NVARCHAR(100) NOT NULL UNIQUE,
is_closed BIT NOT NULL DEFAULT 0
);
CREATE TABLE dbo.sr_request (
request_id BIGINT IDENTITY(1,1) PRIMARY KEY,
title NVARCHAR(200) NOT NULL,
description NVARCHAR(MAX) NULL,
requester_name NVARCHAR(200) NOT NULL,
requester_email NVARCHAR(320) NULL,
category_id INT NOT NULL,
status_id INT NOT NULL,
priority NVARCHAR(20) NOT NULL DEFAULT 'Medium'
CHECK (priority IN ('Low','Medium','High','Urgent')),
created_at DATETIMEOFFSET NOT NULL DEFAULT SYSDATETIMEOFFSET(),
updated_at DATETIMEOFFSET NOT NULL DEFAULT SYSDATETIMEOFFSET(),
due_at DATETIMEOFFSET NULL,
CONSTRAINT FK_sr_request_category FOREIGN KEY (category_id)
REFERENCES dbo.sr_category(category_id),
CONSTRAINT FK_sr_request_status FOREIGN KEY (status_id)
REFERENCES dbo.sr_status(status_id)
);
CREATE TABLE dbo.sr_comment (
comment_id BIGINT IDENTITY(1,1) PRIMARY KEY,
request_id BIGINT NOT NULL,
comment_text NVARCHAR(MAX) NOT NULL,
commented_by NVARCHAR(200) NOT NULL,
commented_at DATETIMEOFFSET NOT NULL DEFAULT SYSDATETIMEOFFSET(),
is_internal BIT NOT NULL DEFAULT 0,
CONSTRAINT FK_sr_comment_request FOREIGN KEY (request_id)
REFERENCES dbo.sr_request(request_id) ON DELETE CASCADE
);
CREATE TABLE dbo.sr_assignment (
assignment_id BIGINT IDENTITY(1,1) PRIMARY KEY,
request_id BIGINT NOT NULL,
assigned_to NVARCHAR(200) NOT NULL,
assigned_at DATETIMEOFFSET NOT NULL DEFAULT SYSDATETIMEOFFSET(),
unassigned_at DATETIMEOFFSET NULL,
CONSTRAINT FK_sr_assignment_request FOREIGN KEY (request_id)
REFERENCES dbo.sr_request(request_id) ON DELETE CASCADE
);
INSERT INTO dbo.sr_status (status_name, is_closed)
VALUES
('New', 0),
('Acknowledged', 0),
('In Progress', 0),
('Waiting on Requester', 0),
('Resolved', 1),
('Cancelled', 1);
INSERT INTO dbo.sr_category (category_name)
VALUES
('Program Enrollment'),
('Service Access / Eligibility'),
('Data / Reporting Request'),
('Technical Support'),
('Account / Profile Update'),
('Training / Workshop Request'),
('Other');
CREATE TRIGGER dbo.trg_sr_request_updated_at
ON dbo.sr_request
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE r
SET updated_at = SYSDATETIMEOFFSET()
FROM dbo.sr_request r
INNER JOIN inserted i ON r.request_id = i.request_id;
END;
CREATE VIEW dbo.vw_service_requests AS
SELECT
r.request_id,
r.title,
r.description,
r.requester_name,
r.requester_email,
c.category_name,
s.status_name,
s.is_closed,
r.priority,
r.created_at,
r.updated_at,
r.due_at
FROM dbo.sr_request r
JOIN dbo.sr_category c ON r.category_id = c.category_id
JOIN dbo.sr_status s ON r.status_id = s.status_id;
