Designing, Securing, and Optimizing SQL Server: A Realistic E-Commerce Example

Akshita Zarbade
22h
351
0
0

Article

1. Schema Design

Key Principles

Normalize to reduce redundancy
Use foreign keys and constraints
Track timestamps for auditing

Schema Diagram

Customers ───< Orders ───< OrderItems >─── Products

Example Table: Customers

Column	Type	Notes
CustomerID	INT (PK)	Identity
FirstName	NVARCHAR(50)	Not Null
LastName	NVARCHAR(50)	Not Null
Email	NVARCHAR(100)	Unique, Not Null
CreatedAt	DATETIME2	Default SYSUTCDATETIME()

✅ Checklist

Primary & foreign keys defined
Appropriate data types
Default timestamps added

2. Indexing & Query Tuning

Scenario: Calculate total spending per customer.

Query

SELECT c.FirstName, c.LastName, SUM(oi.Quantity * oi.Price) AS TotalSpent
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN OrderItems oi ON o.OrderID = oi.OrderID
WHERE o.OrderDate >= '2025-01-01'GROUP BY c.FirstName, c.LastName
ORDER BY TotalSpent DESC;

Optimization: Add covering indexes to speed up joins.

Table	Index	Purpose
Orders	(CustomerID, OrderDate)	Filter & join optimization
OrderItems	(OrderID, ProductID, Quantity, Price)	Aggregate faster

3. ETL: Efficient Data Loading

Scenario: Update daily product prices from CSV.

Process Diagram

CSV File → Staging Table → Merge → Products Table

Example SQL

BULK INSERT ProductStaging FROM 'C:\Data\ProductPrices.csv' 
WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');

MERGE Products AS target
USING ProductStaging AS source
ON target.ProductID = source.ProductID
WHEN MATCHED THEN UPDATE SET Price = source.Price
WHEN NOT MATCHED BY TARGET THEN INSERT (Name, Price, Stock) VALUES (source.Name, source.Price, source.Stock);

✅ Checklist

Validate incoming data
Only update changed records
Log ETL results

4. Backup & Recovery

Backup Strategy Diagram

Full Backup → Differential Backup → Transaction Log Backups

Example SQL

BACKUP DATABASE ECommerceDB TO DISK = 'C:\Backups\ECommerceDB_FULL.bak' WITH INIT, COMPRESSION;
BACKUP LOG ECommerceDB TO DISK = 'C:\Backups\ECommerceDB_Log.trn';

✅ Checklist

Full backups scheduled
Transaction logs backed up for minimal data loss
Test restores regularly

5. Security: Controlling Access

Role-Based Access Table

Role	Access Type	Notes
AppUser	Read/Write	Application use only
Analyst	Read-Only	Reporting and analytics only
Admin	Full	DBAs only

SQL Example

CREATE ROLE ReadOnlyRole;
ALTER ROLE ReadOnlyRole ADD MEMBER [ReportingUser];
DENY INSERT, UPDATE, DELETE TO ReadOnlyRole;

6. Monitoring & Maintenance

Maintenance Flow Diagram

Index Rebuild → Statistics Update → Query Performance Monitoring → Alerts

Example SQL for Long-Running Queries

SELECT TOP 10 
    qs.total_elapsed_time/1000 AS TotalMs,
    qs.execution_count,
    SUBSTRING(qt.text, qs.statement_start_offset/2, 
        (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY TotalMs DESC;

✅ Checklist

Monitor slow queries & deadlocks
Rebuild fragmented indexes
Update statistics regularly
Set alerts for critical issues