Database Design

Database Design Skill

Comprehensive knowledge for designing robust, scalable, and maintainable database schemas for SQL Server.

Database Design Principles

1. Understand the Domain

Before designing:

• Identify all entities (nouns in requirements)
• Identify relationships between entities
• Understand business rules and constraints
• Consider access patterns and query requirements
• Plan for future growth and scalability

2. Normalization

Apply normalization to eliminate redundancy and ensure data integrity.

First Normal Form (1NF)

• ❌ Violates 1NF: Products(Id, Name, Tags VARCHAR) where Tags = "laptop, computer, electronics"
• ✓ Conforms to 1NF: Separate ProductTags table with one tag per row

Second Normal Form (2NF)

• ❌ Violates 2NF: OrderItems(OrderId, ProductId, CustomerName) - CustomerName depends on OrderId, not the composite key
• ✓ Conforms to 2NF: Move CustomerName to Orders table

Third Normal Form (3NF)

• ❌ Violates 3NF: Employees(Id, DepartmentId, DepartmentName) - DepartmentName depends on DepartmentId (transitive dependency)
• ✓ Conforms to 3NF: Create Departments table with DepartmentName

Boyce-Codd Normal Form (BCNF)

• Stricter version of 3NF
• Every determinant must be a candidate key
• Usually achieved when 3NF is properly implemented

When to Denormalize

Sometimes denormalization is acceptable for:

• Performance: Avoid complex joins for frequently accessed data
• Read-heavy workloads: Optimize for reads at expense of writes
• Reporting databases: Data warehouses often denormalize
• Caching computed values: Store aggregates to avoid recalculation

Examples:

• Store CustomerName in Orders for faster order display
• Maintain OrderCount on Customers table
• Store denormalized product details in OrderItems for historical accuracy

Common Design Patterns

1. One-to-Many Relationship

Example: Customers → Orders

CREATE TABLE Customers (
    CustomerId INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName NVARCHAR(100) NOT NULL,
    Email NVARCHAR(255) NOT NULL UNIQUE,
    CreatedAt DATETIME2 DEFAULT GETUTCDATE()
);

CREATE TABLE Orders (
    OrderId INT IDENTITY(1,1) PRIMARY KEY,
    CustomerId INT NOT NULL,
    OrderDate DATETIME2 DEFAULT GETUTCDATE(),
    TotalAmount DECIMAL(10,2) NOT NULL,

    CONSTRAINT FK_Orders_Customers
        FOREIGN KEY (CustomerId)
        REFERENCES Customers(CustomerId)
);

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
    ON Orders(CustomerId);

2. Many-to-Many Relationship

Example: Students ↔ Courses

CREATE TABLE Students (
    StudentId INT IDENTITY(1,1) PRIMARY KEY,
    StudentName NVARCHAR(100) NOT NULL
);

CREATE TABLE Courses (
    CourseId INT IDENTITY(1,1) PRIMARY KEY,
    CourseName NVARCHAR(100) NOT NULL
);

-- Junction/Bridge table
CREATE TABLE Enrollments (
    StudentId INT NOT NULL,
    CourseId INT NOT NULL,
    EnrollmentDate DATETIME2 DEFAULT GETUTCDATE(),
    Grade CHAR(2),

    PRIMARY KEY (StudentId, CourseId),

    CONSTRAINT FK_Enrollments_Students
        FOREIGN KEY (StudentId)
        REFERENCES Students(StudentId),

    CONSTRAINT FK_Enrollments_Courses
        FOREIGN KEY (CourseId)
        REFERENCES Courses(CourseId)
);

3. One-to-One Relationship

Example: Users → UserProfiles

CREATE TABLE Users (
    UserId INT IDENTITY(1,1) PRIMARY KEY,
    Username NVARCHAR(50) NOT NULL UNIQUE,
    Email NVARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE UserProfiles (
    UserId INT PRIMARY KEY,  -- PK and FK combined
    Bio NVARCHAR(MAX),
    ProfilePictureUrl NVARCHAR(500),
    DateOfBirth DATE,

    CONSTRAINT FK_UserProfiles_Users
        FOREIGN KEY (UserId)
        REFERENCES Users(UserId)
        ON DELETE CASCADE
);

4. Self-Referencing Relationship

Example: Employees (manager hierarchy)

CREATE TABLE Employees (
    EmployeeId INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeName NVARCHAR(100) NOT NULL,
    ManagerId INT NULL,  -- References another Employee

    CONSTRAINT FK_Employees_Manager
        FOREIGN KEY (ManagerId)
        REFERENCES Employees(EmployeeId)
);

Example: Categories (tree structure)

CREATE TABLE Categories (
    CategoryId INT IDENTITY(1,1) PRIMARY KEY,
    CategoryName NVARCHAR(100) NOT NULL,
    ParentCategoryId INT NULL,

    CONSTRAINT FK_Categories_Parent
        FOREIGN KEY (ParentCategoryId)
        REFERENCES Categories(CategoryId)
);

5. Polymorphic Associations (Use with Caution)

Problem: Multiple entity types sharing a relationship

Approach 1: Separate Foreign Keys (Preferred)

CREATE TABLE Comments (
    CommentId INT IDENTITY(1,1) PRIMARY KEY,
    CommentText NVARCHAR(MAX),
    PostId INT NULL,
    PhotoId INT NULL,

    CONSTRAINT FK_Comments_Posts
        FOREIGN KEY (PostId) REFERENCES Posts(PostId),
    CONSTRAINT FK_Comments_Photos
        FOREIGN KEY (PhotoId) REFERENCES Photos(PhotoId),

    CONSTRAINT CK_Comments_OneParent
        CHECK (
            (PostId IS NOT NULL AND PhotoId IS NULL) OR
            (PostId IS NULL AND PhotoId IS NOT NULL)
        )
);

Approach 2: Supertype/Subtype (Preferred for similar entities)

CREATE TABLE Media (
    MediaId INT IDENTITY(1,1) PRIMARY KEY,
    MediaType NVARCHAR(20) NOT NULL,  -- 'Post' or 'Photo'
    CreatedAt DATETIME2 DEFAULT GETUTCDATE()
);

CREATE TABLE Posts (
    MediaId INT PRIMARY KEY,
    Title NVARCHAR(200),
    Content NVARCHAR(MAX),

    CONSTRAINT FK_Posts_Media
        FOREIGN KEY (MediaId) REFERENCES Media(MediaId)
);

CREATE TABLE Photos (
    MediaId INT PRIMARY KEY,
    Url NVARCHAR(500),
    Caption NVARCHAR(500),

    CONSTRAINT FK_Photos_Media
        FOREIGN KEY (MediaId) REFERENCES Media(MediaId)
);

CREATE TABLE Comments (
    CommentId INT IDENTITY(1,1) PRIMARY KEY,
    MediaId INT NOT NULL,
    CommentText NVARCHAR(MAX),

    CONSTRAINT FK_Comments_Media
        FOREIGN KEY (MediaId) REFERENCES Media(MediaId)
);

6. Audit Pattern

Track who created/modified records and when:

CREATE TABLE Products (
    ProductId INT IDENTITY(1,1) PRIMARY KEY,
    ProductName NVARCHAR(100) NOT NULL,
    Price DECIMAL(10,2) NOT NULL,

    -- Audit columns
    CreatedAt DATETIME2 DEFAULT GETUTCDATE() NOT NULL,
    CreatedBy INT NOT NULL,
    UpdatedAt DATETIME2,
    UpdatedBy INT,
    IsDeleted BIT DEFAULT 0 NOT NULL,
    DeletedAt DATETIME2,
    DeletedBy INT,

    CONSTRAINT FK_Products_CreatedBy
        FOREIGN KEY (CreatedBy) REFERENCES Users(UserId),
    CONSTRAINT FK_Products_UpdatedBy
        FOREIGN KEY (UpdatedBy) REFERENCES Users(UserId),
    CONSTRAINT FK_Products_DeletedBy
        FOREIGN KEY (DeletedBy) REFERENCES Users(UserId)
);

7. Temporal Tables (SQL Server 2016+)

Automatic history tracking:

CREATE TABLE Products (
    ProductId INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    Price DECIMAL(10,2),

    -- System-versioning columns (required)
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductsHistory));

-- Query historical data
SELECT * FROM Products
FOR SYSTEM_TIME AS OF '2024-01-01';

SELECT * FROM Products
FOR SYSTEM_TIME BETWEEN '2024-01-01' AND '2024-12-31';

8. Type Tables (Lookup Tables)

For enumeration values:

CREATE TABLE OrderStatuses (
    StatusId INT PRIMARY KEY,  -- Fixed values (1=Pending, 2=Processing, etc.)
    StatusName NVARCHAR(50) NOT NULL UNIQUE,
    DisplayOrder INT NOT NULL
);

INSERT INTO OrderStatuses VALUES
    (1, 'Pending', 1),
    (2, 'Processing', 2),
    (3, 'Shipped', 3),
    (4, 'Delivered', 4),
    (5, 'Cancelled', 5);

CREATE TABLE Orders (
    OrderId INT IDENTITY(1,1) PRIMARY KEY,
    StatusId INT NOT NULL DEFAULT 1,

    CONSTRAINT FK_Orders_Status
        FOREIGN KEY (StatusId)
        REFERENCES OrderStatuses(StatusId)
);

Data Type Selection Guide

Strings

Examples:

Email NVARCHAR(255)
CountryCode CHAR(2)
ProductDescription NVARCHAR(MAX)
SKU VARCHAR(50)

Numbers

Examples:

Age TINYINT
Quantity SMALLINT
UserId INT
TransactionId BIGINT
Price DECIMAL(10,2)
TaxRate DECIMAL(5,4)  -- e.g., 0.0825 for 8.25%

Dates and Times

Examples:

DateOfBirth DATE
OrderDate DATETIME2
CreatedAt DATETIME2 DEFAULT GETUTCDATE()
AppointmentTime TIME
EventStartTime DATETIMEOFFSET

Other Types

-- Boolean
IsActive BIT DEFAULT 1

-- GUID
UniqueId UNIQUEIDENTIFIER DEFAULT NEWID()

-- Binary (files, images)
ProfilePicture VARBINARY(MAX)

-- JSON
Metadata NVARCHAR(MAX) CHECK (ISJSON(Metadata) = 1)

-- XML
Configuration XML

Indexing Strategy

Clustered Index (one per table)

Usually on:

• Primary key (default)
• Most common filter/sort column
• Narrow, unique, static column

-- Default: clustered on PK
CREATE TABLE Users (
    UserId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
);

-- Custom: clustered on different column
CREATE TABLE Logs (
    LogId BIGINT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    LogDate DATETIME2 NOT NULL,
    Message NVARCHAR(MAX)
);

CREATE CLUSTERED INDEX IX_Logs_LogDate ON Logs(LogDate);

Non-Clustered Indexes

Create for:

• Foreign keys (JOIN columns)
• WHERE clause columns
• ORDER BY columns
• Frequently searched columns

-- Basic index
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
    ON Orders(CustomerId);

-- Composite index (order matters!)
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_OrderDate
    ON Orders(CustomerId, OrderDate DESC);

-- Covering index (includes extra columns)
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_Covering
    ON Orders(CustomerId)
    INCLUDE (OrderDate, TotalAmount);

-- Filtered index (partial index)
CREATE NONCLUSTERED INDEX IX_Orders_Active
    ON Orders(OrderDate)
    WHERE IsActive = 1 AND IsDeleted = 0;

-- Unique index
CREATE UNIQUE NONCLUSTERED INDEX IX_Users_Email
    ON Users(Email);

Constraints

Primary Key

-- Identity primary key
UserId INT IDENTITY(1,1) PRIMARY KEY

-- GUID primary key
UserId UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY

-- Composite primary key
PRIMARY KEY (StudentId, CourseId)

-- Named constraint
CONSTRAINT PK_Users PRIMARY KEY (UserId)

Foreign Key

CONSTRAINT FK_Orders_Customers
    FOREIGN KEY (CustomerId)
    REFERENCES Customers(CustomerId)
    ON DELETE CASCADE          -- Options: NO ACTION, CASCADE, SET NULL, SET DEFAULT
    ON UPDATE CASCADE

Unique

CONSTRAINT UQ_Users_Email UNIQUE (Email)

-- Multi-column unique
CONSTRAINT UQ_Products_SKU_Supplier UNIQUE (SKU, SupplierId)

Check

-- Range check
CONSTRAINT CK_Products_Price CHECK (Price >= 0)

-- Pattern check
CONSTRAINT CK_Users_Email CHECK (Email LIKE '%@%.%')

-- Multi-column check
CONSTRAINT CK_Events_Dates CHECK (EndDate >= StartDate)

-- Complex business rule
CONSTRAINT CK_Orders_Discount
    CHECK (DiscountPercent >= 0 AND DiscountPercent <= 100)

Default

CreatedAt DATETIME2 DEFAULT GETUTCDATE()
IsActive BIT DEFAULT 1
Status NVARCHAR(20) DEFAULT 'Pending'
Quantity INT DEFAULT 0

Schema Organization

Use Schemas for Logical Grouping

-- Create schemas
CREATE SCHEMA Sales;
CREATE SCHEMA HR;
CREATE SCHEMA Reporting;

-- Create tables in schemas
CREATE TABLE Sales.Orders (...);
CREATE TABLE Sales.Customers (...);
CREATE TABLE HR.Employees (...);
CREATE TABLE Reporting.SalesSummary (...);

-- Query with schema
SELECT * FROM Sales.Orders;

Benefits:

• Logical organization
• Security (grant permissions per schema)
• Namespace management

Common Anti-Patterns to Avoid

1. Entity-Attribute-Value (EAV)

-- ❌ Hard to query, poor performance
CREATE TABLE EntityAttributes (
    EntityId INT,
    AttributeName NVARCHAR(50),
    AttributeValue NVARCHAR(MAX)
);

-- ✓ Proper design with columns
CREATE TABLE Products (
    ProductId INT PRIMARY KEY,
    Name NVARCHAR(100),
    Price DECIMAL(10,2),
    Color NVARCHAR(50)
);

2. Comma-Separated Values

-- ❌ Violates 1NF, hard to query
CREATE TABLE Products (
    Tags NVARCHAR(500)  -- 'laptop,computer,electronics'
);

-- ✓ Separate table
CREATE TABLE ProductTags (
    ProductId INT,
    Tag NVARCHAR(50),
    PRIMARY KEY (ProductId, Tag)
);

3. Using Reserved Keywords

-- ❌ Avoid
CREATE TABLE User (...);  -- 'User' is reserved
CREATE TABLE [Order] (...);  -- Needs brackets

-- ✓ Better
CREATE TABLE Users (...);
CREATE TABLE Orders (...);

4. No Foreign Keys

-- ❌ No referential integrity
CREATE TABLE Orders (
    CustomerId INT  -- No FK constraint
);

-- ✓ With foreign key
CREATE TABLE Orders (
    CustomerId INT NOT NULL,
    CONSTRAINT FK_Orders_Customers
        FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)
);

Documentation

Always document your schema:

-- Table comments
EXEC sp_addextendedproperty
    @name = N'MS_Description',
    @value = N'Stores customer information',
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE',  @level1name = 'Customers';

-- Column comments
EXEC sp_addextendedproperty
    @name = N'MS_Description',
    @value = N'Unique customer identifier',
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE',  @level1name = 'Customers',
    @level2type = N'COLUMN', @level2name = 'CustomerId';

When to Use This Skill

Use this skill when:

• Designing a new database schema
• Normalizing existing tables
• Creating entity-relationship diagrams
• Choosing appropriate data types
• Defining relationships and constraints
• Planning indexes
• Making architectural database decisions

Simply mention database design, schema modeling, or data architecture, and this knowledge will be applied to help create robust, scalable database structures.