The complete BlogApp schema brings together all T-SQL concepts from this chapter: proper data types, named constraints, foreign key relationships, and check constraints. This schema is the foundation that EF Core migrations (Chapter 37–38) build upon, and the SQL Server knowledge from Chapters 63–70 will optimise it. Understanding the raw SQL schema — not just the EF Core entity models — allows diagnosing performance issues, optimising queries, and making schema changes that EF Core cannot express.
Complete BlogApp Schema
SET XACT_ABORT ON;
USE BlogApp;
GO
-- ── Users (simplified — full ASP.NET Core Identity schema managed by EF) ──
CREATE TABLE dbo.Users (
Id NVARCHAR(450) NOT NULL,
Email NVARCHAR(256) NOT NULL,
NormalizedEmail NVARCHAR(256) NOT NULL,
DisplayName NVARCHAR(100) NOT NULL,
AvatarUrl NVARCHAR(500) NULL,
Bio NVARCHAR(1000) NULL,
IsActive BIT NOT NULL DEFAULT 1,
PublishedPostCount INT NOT NULL DEFAULT 0,
CreatedAt DATETIME2(7) NOT NULL DEFAULT SYSUTCDATETIME(),
CONSTRAINT PK_Users PRIMARY KEY CLUSTERED (Id),
CONSTRAINT UQ_Users_NormalizedEmail UNIQUE (NormalizedEmail)
);
GO
-- ── Categories ─────────────────────────────────────────────────────────────
CREATE TABLE dbo.Categories (
Id INT NOT NULL IDENTITY(1,1),
Name NVARCHAR(100) NOT NULL,
Slug VARCHAR(100) NOT NULL,
Description NVARCHAR(500) NULL,
SortOrder INT NOT NULL DEFAULT 0,
CONSTRAINT PK_Categories PRIMARY KEY CLUSTERED (Id),
CONSTRAINT UQ_Categories_Slug UNIQUE (Slug)
);
GO
-- ── Posts ──────────────────────────────────────────────────────────────────
CREATE TABLE dbo.Posts (
Id INT NOT NULL IDENTITY(1,1),
AuthorId NVARCHAR(450) NOT NULL,
CategoryId INT NULL,
Title NVARCHAR(200) NOT NULL,
Slug VARCHAR(200) NOT NULL,
Body NVARCHAR(MAX) NOT NULL,
Excerpt NVARCHAR(500) NULL,
CoverImageUrl NVARCHAR(500) NULL,
Status VARCHAR(20) NOT NULL DEFAULT 'draft',
ViewCount INT NOT NULL DEFAULT 0,
CommentCount INT NOT NULL DEFAULT 0,
IsPublished BIT NOT NULL DEFAULT 0,
IsFeatured BIT NOT NULL DEFAULT 0,
PublishedAt DATETIME2(7) NULL,
CreatedAt DATETIME2(7) NOT NULL DEFAULT SYSUTCDATETIME(),
UpdatedAt DATETIME2(7) NOT NULL DEFAULT SYSUTCDATETIME(),
CONSTRAINT PK_Posts PRIMARY KEY CLUSTERED (Id),
CONSTRAINT UQ_Posts_Slug UNIQUE (Slug),
CONSTRAINT FK_Posts_Authors FOREIGN KEY (AuthorId)
REFERENCES dbo.Users (Id) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT FK_Posts_Category FOREIGN KEY (CategoryId)
REFERENCES dbo.Categories (Id) ON DELETE SET NULL ON UPDATE NO ACTION,
CONSTRAINT CHK_Posts_Status CHECK (Status IN ('draft','review','published','archived')),
CONSTRAINT CHK_Posts_Counts CHECK (ViewCount >= 0 AND CommentCount >= 0)
);
GO
-- ── Tags and PostTags ─────────────────────────────────────────────────────
CREATE TABLE dbo.Tags (
Id INT NOT NULL IDENTITY(1,1),
Name NVARCHAR(50) NOT NULL,
Slug VARCHAR(50) NOT NULL,
CONSTRAINT PK_Tags PRIMARY KEY CLUSTERED (Id),
CONSTRAINT UQ_Tags_Slug UNIQUE (Slug)
);
GO
CREATE TABLE dbo.PostTags (
PostId INT NOT NULL,
TagId INT NOT NULL,
CONSTRAINT PK_PostTags PRIMARY KEY CLUSTERED (PostId, TagId),
CONSTRAINT FK_PostTags_Posts FOREIGN KEY (PostId)
REFERENCES dbo.Posts (Id) ON DELETE CASCADE,
CONSTRAINT FK_PostTags_Tags FOREIGN KEY (TagId)
REFERENCES dbo.Tags (Id) ON DELETE CASCADE
);
GO
-- ── Comments ──────────────────────────────────────────────────────────────
CREATE TABLE dbo.Comments (
Id INT NOT NULL IDENTITY(1,1),
PostId INT NOT NULL,
AuthorId NVARCHAR(450) NOT NULL,
Body NVARCHAR(2000) NOT NULL,
IsApproved BIT NOT NULL DEFAULT 0,
CreatedAt DATETIME2(7) NOT NULL DEFAULT SYSUTCDATETIME(),
CONSTRAINT PK_Comments PRIMARY KEY CLUSTERED (Id),
CONSTRAINT FK_Comments_Posts FOREIGN KEY (PostId)
REFERENCES dbo.Posts (Id) ON DELETE CASCADE,
CONSTRAINT FK_Comments_Users FOREIGN KEY (AuthorId)
REFERENCES dbo.Users (Id) ON DELETE NO ACTION
);
GO
-- ── RefreshTokens ─────────────────────────────────────────────────────────
CREATE TABLE dbo.RefreshTokens (
Id INT NOT NULL IDENTITY(1,1),
UserId NVARCHAR(450) NOT NULL,
TokenHash VARBINARY(64) NOT NULL, -- store SHA-256 hash, never raw token
DeviceInfo NVARCHAR(200) NULL,
CreatedAt DATETIME2(7) NOT NULL DEFAULT SYSUTCDATETIME(),
ExpiresAt DATETIME2(7) NOT NULL,
RevokedAt DATETIME2(7) NULL, -- NULL = not revoked
CONSTRAINT PK_RefreshTokens PRIMARY KEY CLUSTERED (Id),
CONSTRAINT FK_RefreshTokens_User FOREIGN KEY (UserId)
REFERENCES dbo.Users (Id) ON DELETE CASCADE
);
GO
-- ── Seed data for development ─────────────────────────────────────────────
INSERT INTO dbo.Categories (Name, Slug, Description, SortOrder) VALUES
(N'.NET Development', 'dotnet', N'ASP.NET Core, C#, and the .NET ecosystem', 1),
(N'Angular', 'angular', N'Angular framework and TypeScript', 2),
(N'SQL Server', 'sqlserver', N'T-SQL, database design, and performance', 3),
(N'Architecture', 'architecture', N'Software design patterns and best practices', 4),
(N'DevOps', 'devops', N'CI/CD, Docker, and deployment', 5);
GO
INSERT INTO dbo.Tags (Name, Slug) VALUES
(N'C#', 'csharp'), (N'ASP.NET Core', 'aspnet-core'),
(N'Entity Framework', 'entity-framework'), (N'Angular', 'angular'),
(N'TypeScript', 'typescript'), (N'SQL Server', 'sql-server'),
(N'Performance', 'performance'), (N'Security', 'security');
GO
PRINT 'BlogApp schema created and seed data inserted.';Note: The
RefreshTokens table stores a VARBINARY(64) hash of the refresh token, not the raw token string. If the database is compromised, stolen token hashes cannot be directly used for authentication — the attacker would need to reverse the SHA-256 hash (computationally infeasible). This mirrors how passwords are stored — always hash sensitive credentials before storing. The application computes SHA256(rawToken) before querying this table.Tip: The
ON DELETE SET NULL on FK_Posts_Category means that when a category is deleted, the CategoryId on all its posts is set to NULL rather than raising an error or cascade-deleting the posts. This is a reasonable “orphan” policy for a blog — posts without a category remain published and visible; the admin can reassign them. Choose the ON DELETE behaviour based on the business rule: CASCADE for owned child records, NO ACTION for important relationships requiring explicit handling, SET NULL for optional references.Warning: This schema represents a starting point — EF Core migrations will manage it after initial creation. When EF Core generates migrations, it produces its own version of this schema with its own naming conventions (
PK_Posts, IX_Posts_AuthorId etc.). If you have manually created the schema with this script and then run EF Core migrations, conflicts will occur. Choose one approach: either manage the schema entirely with EF Core migrations (recommended), or manage it entirely with manual SQL scripts, not both.Common Mistakes
Mistake 1 — Storing raw refresh tokens in the database (security risk if database is compromised)
❌ Wrong — Token NVARCHAR(500) storing the raw token; database breach exposes all active sessions.
✅ Correct — store TokenHash VARBINARY(64); compute SHA-256 of the raw token before storing and querying.
Mistake 2 — Running EF Core migrations against a manually-created schema (conflicts)
❌ Wrong — creating the schema manually with SQL scripts then running EF Core migrations; migration tries to create tables that already exist.
✅ Correct — choose one approach: EF Core migrations (recommended for application development) or manual SQL scripts, not both.