klod68/database-schema-design

---

name: database-schema-design description: "Use when designing new database schemas, reviewing existing schemas for normalization issues, choosing data types for SQL Server columns, adding temporal tables or partitioning to large datasets, or when schema decisions impact query performance and data integrity. Covers normalization through BCNF with denormalization decision criteria, SQL Server data type selection (narrowest type principle, VARCHAR vs NVARCHAR, DATETIME2 vs DATETIME), constraint design at the database level (PK, FK, UNIQUE, CHECK, DEFAULT), schema organization for security boundaries, temporal tables with retention policies, table partitioning for lifecycle-managed data, row-level security for multi-tenant systems, and schema anti-patterns (EAV misuse, God tables, polymorphic associations). Domain: Database, Architecture. Level: Intermediate. Tags: schema-design, normalization, data-types, temporal-tables, partitioning, row-level-security, constraints."

Database Schema Design

Problem

The KB covers EF entity modeling (C#-side Fluent API) but never addresses the physical database schema. EF modeling assumes the schema exists and is well-designed — but poor schema decisions (wrong data types, missing constraints, no normalization discipline) create performance and integrity problems that no ORM optimization can fix. The schema is the foundation — everything else builds on it.

Normalization Guide

1NF (First Normal Form): Atomic values, no repeating groups.

-- ❌ Violates 1NF: repeating group in single column
CREATE TABLE dbo.OrderBad (
    OrderId INT PRIMARY KEY,
    ProductIds VARCHAR(MAX)  -- "1,2,3" — not atomic
);

-- ✅ 1NF: separate row per product
CREATE TABLE dbo.OrderItem (
    OrderItemId INT IDENTITY(1,1) PRIMARY KEY,
    OrderId INT NOT NULL REFERENCES dbo.[Order](OrderId),
    ProductId INT NOT NULL REFERENCES dbo.Product(ProductId),
    Quantity INT NOT NULL DEFAULT 1
);

2NF (Second Normal Form): No partial dependencies (all non-key columns depend on the FULL primary key).

-- ❌ Violates 2NF: ProductName depends only on ProductId, not the full key (OrderId, ProductId)
CREATE TABLE dbo.OrderItemBad (
    OrderId INT NOT NULL,
    ProductId INT NOT NULL,
    ProductName NVARCHAR(200),  -- partial dependency on ProductId only
    Quantity INT NOT NULL,
    PRIMARY KEY (OrderId, ProductId)
);

-- ✅ 2NF: ProductName lives in Product table
CREATE TABLE dbo.OrderItem (
    OrderId INT NOT NULL,
    ProductId INT NOT NULL REFERENCES dbo.Product(ProductId),
    Quantity INT NOT NULL,
    PRIMARY KEY (OrderId, ProductId)
);

CREATE TABLE dbo.Product (
    ProductId INT PRIMARY KEY,
    ProductName NVARCHAR(200) NOT NULL,
    Price DECIMAL(18,2) NOT NULL
);

3NF (Third Normal Form): No transitive dependencies (non-key columns depend ONLY on the primary key, not on other non-key columns).

-- ❌ Violates 3NF: CustomerCity depends on CustomerId, not OrderId
CREATE TABLE dbo.OrderBad (
    OrderId INT PRIMARY KEY,
    CustomerId INT NOT NULL,
    CustomerName NVARCHAR(100),  -- depends on CustomerId, not OrderId
    CustomerCity NVARCHAR(100),  -- transitive dependency
    OrderTotal DECIMAL(18,2)
);

-- ✅ 3NF: customer data lives in Customer table only
CREATE TABLE dbo.[Order] (
    OrderId INT IDENTITY(1,1) PRIMARY KEY,
    CustomerId INT NOT NULL REFERENCES dbo.Customer(CustomerId),
    OrderTotal DECIMAL(18,2) NOT NULL
);

CREATE TABLE dbo.Customer (
    CustomerId INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName NVARCHAR(100) NOT NULL,
    CustomerCity NVARCHAR(100) NOT NULL
);

BCNF (Boyce-Codd Normal Form): Every determinant is a candidate key (stricter than 3NF for tables with multiple candidate keys).

-- ❌ Violates BCNF: InstructorOffice determines Instructor, but InstructorOffice is not a candidate key
CREATE TABLE dbo.CourseScheduleBad (
    CourseId INT NOT NULL,
    TimeSlot VARCHAR(20) NOT NULL,
    Instructor NVARCHAR(100),
    InstructorOffice VARCHAR(20),  -- determines Instructor, but is not a candidate key
    PRIMARY KEY (CourseId, TimeSlot)
);

-- ✅ BCNF: separate Instructor table where InstructorId is the key
CREATE TABLE dbo.CourseSchedule (
    CourseId INT NOT NULL,
    TimeSlot VARCHAR(20) NOT NULL,
    InstructorId INT NOT NULL REFERENCES dbo.Instructor(InstructorId),
    PRIMARY KEY (CourseId, TimeSlot)
);

CREATE TABLE dbo.Instructor (
    InstructorId INT IDENTITY(1,1) PRIMARY KEY,
    InstructorName NVARCHAR(100) NOT NULL,
    InstructorOffice VARCHAR(20) NOT NULL
);

Denormalization Decision Matrix

| Condition | Denormalize? | Technique | |---|---|---| | Read:write ratio > 10:1 on specific queries | Consider | Materialized view or computed column | | Reporting queries JOIN 5+ tables | Consider | Summary/reporting table refreshed on schedule | | Sub-millisecond read latency required | Consider | Precomputed columns or indexed views | | Write-heavy transactional workload | No | Maintain normalized form; optimize indexes | | Data integrity is paramount (financial, medical) | No | Normalization protects against update anomalies | | Small dataset (<100K rows) | No | JOINs are cheap at small scale |

Data Type Selection

The narrowest type principle: Use the smallest type that can hold all valid values. Narrower types = more rows per page = fewer I/O operations = faster queries.

| Data Need | ❌ Avoid | ✅ Prefer | Why | |---|---|---|---| | Small integer (0–255) | INT (4 bytes) | TINYINT (1 byte) | 4× space saving | | Integer up to 2B | BIGINT (8 bytes) | INT (4 bytes) | 2× space saving unless >2B rows expected | | Boolean flag | INT, VARCHAR | BIT (1 bit, packed) | Semantic clarity + space | | Date only (no time) | DATETIME (8 bytes) | DATE (3 bytes) | 62% smaller, no accidental time component | | Date with time | DATETIME (8 bytes, 3.33ms precision) | DATETIME2(3) (7 bytes, 1ms) | More precise, smaller, wider range | | UTC timestamp | DATETIME2 | DATETIMEOFFSET or DATETIME2 with convention | DATETIMEOFFSET stores offset; document UTC convention | | Money | FLOAT, REAL | DECIMAL(18,2) or MONEY | Floating point has rounding errors | | ASCII-only strings | NVARCHAR (2 bytes/char) | VARCHAR (1 byte/char) | 50% space saving when Unicode not needed | | Unicode strings | VARCHAR | NVARCHAR | Required for international characters | | Short fixed-length strings | VARCHAR(MAX) | CHAR(n) or VARCHAR(n) | MAX prevents inlining, disables some optimizations | | GUIDs | VARCHAR(36) | UNIQUEIDENTIFIER (16 bytes) | Native type, proper indexing, smaller | | Large text/documents | NVARCHAR(4000) repeated | NVARCHAR(MAX) | MAX stored off-row when >8000 bytes |

Constraint Design

Enforce data integrity at the database level — never rely solely on application code.

CREATE TABLE dbo.[Order] (
    OrderId INT IDENTITY(1,1),
    PublicId UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    Title NVARCHAR(200) NOT NULL,
    Status VARCHAR(20) NOT NULL DEFAULT 'Draft',
    CustomerId INT NOT NULL,
    OrderTotal DECIMAL(18,2) NOT NULL DEFAULT 0,
    CreatedAt DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    ModifiedAt DATETIME2(3) NULL,

    -- Primary key
    CONSTRAINT PK_Order PRIMARY KEY CLUSTERED (OrderId),

    -- Business key uniqueness
    CONSTRAINT UQ_Order_PublicId UNIQUE NONCLUSTERED (PublicId),

    -- Referential integrity
    CONSTRAINT FK_Order_Customer FOREIGN KEY (CustomerId)
        REFERENCES dbo.Customer(CustomerId)
        ON DELETE RESTRICT   -- explicit, not CASCADE
        ON UPDATE RESTRICT,

    -- Domain validation
    CONSTRAINT CK_Order_Status CHECK (Status IN ('Draft', 'Submitted', 'Approved', 'Shipped', 'Cancelled')),
    CONSTRAINT CK_Order_TotalNonNegative CHECK (OrderTotal >= 0),
    CONSTRAINT CK_Order_TitleNotEmpty CHECK (LEN(LTRIM(RTRIM(Title))) > 0)
);

Constraint naming convention: {Type}_{Table}_{Column(s)} — PK_, FK_, UQ_, CK_, DF_

Schema Organization

Use SQL Server schemas for logical grouping and security boundaries:

| Schema | Purpose | Example | |---|---|---| | dbo | Core business entities | dbo.Order, dbo.Customer | | audit | Audit and history tables | audit.OrderHistory, audit.LoginLog | | reporting | Denormalized reporting views/tables | reporting.MonthlySalesSummary | | staging | ETL and import landing tables | staging.CustomerImport | | security | RLS predicates, security functions | security.fn_TenantFilter |

Grant permissions at the schema level: GRANT SELECT ON SCHEMA::reporting TO ReportingRole;

Temporal Tables

Built-in change tracking for audit trails and point-in-time queries.

CREATE TABLE dbo.[Order] (
    OrderId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Title NVARCHAR(200) NOT NULL,
    Status VARCHAR(20) NOT NULL DEFAULT 'Draft',
    CustomerId INT NOT NULL,

    -- Temporal columns (managed by SQL Server)
    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 = audit.OrderHistory));

-- Point-in-time query: what did order 42 look like yesterday?
SELECT * FROM dbo.[Order]
    FOR SYSTEM_TIME AS OF '2024-12-14T12:00:00'
    WHERE OrderId = 42;

-- Full change history for an order
SELECT * FROM dbo.[Order]
    FOR SYSTEM_TIME ALL
    WHERE OrderId = 42
    ORDER BY ValidFrom;

Best practices:

• Index the history table on ValidFrom, ValidTo + the primary key columns
• Set retention policy: ALTER TABLE dbo.[Order] SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 1 YEAR));
• Use UTC for temporal columns (SQL Server default)
• Partition the history table for large datasets with scheduled purge

Table Partitioning

Split large tables across filegroups for manageability and query performance.

-- Step 1: Create partition function (monthly boundaries)
CREATE PARTITION FUNCTION pf_OrderByMonth (DATETIME2(3))
AS RANGE RIGHT FOR VALUES
    ('2024-01-01', '2024-02-01', '2024-03-01', '2024-04-01' /* ... */);

-- Step 2: Create partition scheme (map partitions to filegroups)
CREATE PARTITION SCHEME ps_OrderByMonth
AS PARTITION pf_OrderByMonth ALL TO ([PRIMARY]);  -- all to PRIMARY for simplicity

-- Step 3: Create table on the partition scheme
CREATE TABLE dbo.OrderArchive (
    OrderId INT NOT NULL,
    CreatedAt DATETIME2(3) NOT NULL,
    Title NVARCHAR(200) NOT NULL,
    -- ...
    CONSTRAINT PK_OrderArchive PRIMARY KEY CLUSTERED (OrderId, CreatedAt)
) ON ps_OrderByMonth(CreatedAt);

When to partition:

• Tables exceeding 50GB or 100M+ rows
• Data with clear lifecycle (archive older partitions, drop instead of DELETE)
• Sliding window scenarios (add new partition, drop oldest)

When NOT to partition:

• Small tables — partitioning overhead exceeds benefit
• OLTP workloads that always query across all partitions (no partition elimination)

Row-Level Security

Automatic tenant isolation — queries only see rows they're authorized for.

-- Security predicate function
CREATE FUNCTION security.fn_TenantFilter(@TenantId INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS Result
    WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS INT);

-- Apply RLS policy
CREATE SECURITY POLICY security.TenantPolicy
    ADD FILTER PREDICATE security.fn_TenantFilter(TenantId) ON dbo.[Order],
    ADD BLOCK PREDICATE security.fn_TenantFilter(TenantId) ON dbo.[Order]
WITH (STATE = ON);

-- Application sets the tenant context on each connection
EXEC sp_set_session_context @key = N'TenantId', @value = 42;
-- All subsequent queries automatically filtered to TenantId = 42

Schema Anti-Patterns

| Anti-Pattern | Problem | Fix | |---|---|---| | Entity-Attribute-Value (EAV) | No type safety, no constraints, horrible join performance, unqueryable | Proper columns; use JSON column for truly dynamic attributes | | God table (100+ columns) | Wide rows, wasted space from NULLs, impossible to index effectively | Split into focused tables by cohesion | | Polymorphic association (FK to "any table") | No FK constraint possible, broken referential integrity | Separate FK per target table, or use discriminated union with CHECK | | No foreign keys ("we enforce in the app") | Data corruption when app has bugs, no CASCADE/RESTRICT protection | Always define FK constraints | | VARCHAR(MAX) for every string | Prevents inline storage optimization, wastes buffer pool | Size to actual max: VARCHAR(200), VARCHAR(50) | | FLOAT for money | Rounding errors in financial calculations | DECIMAL(18,2) or MONEY | | No CHECK constraints | Invalid data enters through bulk loads, migration scripts, other apps | Define CHECK for domain validation | | Nullable foreign keys | Ambiguous relationships, outer joins forced everywhere | Make FK NOT NULL; use separate linking table if truly optional | | Surrogate key without natural key unique constraint | Duplicate business records with different IDs | Add UNIQUE constraint on natural key columns | | DATETIME instead of DATETIME2 | Lower precision, smaller range, 8 bytes vs 6–8 bytes | Use DATETIME2(3) for millisecond precision |

When to Use

• Designing a new database schema from scratch
• Reviewing an existing schema for normalization violations or data type waste
• Adding temporal tables for audit requirements
• Evaluating partitioning for large tables with lifecycle management
• Implementing multi-tenant data isolation
• Establishing team schema design standards

When NOT to Use

• EF Core entity modeling, migrations, and data access in C# (see ef-core.md rule — Fluent API, value objects, DbContext, querying)
• Index design and query optimization (see index-design-strategy skill)
• T-SQL coding inside stored procedures (see sql-coding-standards skill)

Gotchas

1. Normalization is not always the answer: Reporting databases and read-heavy workloads often benefit from strategic denormalization. The key word is "strategic" — denormalize with intent and document the trade-off, never by accident or laziness.

2. Temporal table history grows forever: Without a retention policy, the history table can dwarf the main table. Set HISTORY_RETENTION_PERIOD and consider partitioning the history table for efficient purge.

3. Partitioning does not automatically improve queries: Only queries that filter on the partition key benefit (partition elimination). Queries that scan across all partitions may actually be slower due to per-partition overhead.

4. Row-level security performance: The filter predicate function is executed for every row access. Keep it simple (single column comparison) and ensure the filtered column is indexed. Complex predicates in the security function will destroy query performance.

5. CHECK constraints and EF Core: EF Core does not scaffold CHECK constraints from Fluent API. Define them in migration scripts (migrationBuilder.Sql(...)) or maintain them separately as database-level artifacts.

6. VARCHAR vs NVARCHAR is a schema decision: Changing later requires a full table rewrite on large tables. Choose correctly upfront based on whether the column will ever store Unicode data.

Related Skills

• Index Design Strategy (index the schema you designed)
• SQL Coding Standards (write procedures against this schema)
• EF Core Query Optimization (ORM queries that target this schema)
• Bulk Operation Batching (bulk loads that must respect constraints)