---

name: relational-db description: Use when designing, modifying, or troubleshooting any relational database (MySQL, MariaDB, SQL Server/MSSQL, and other ANSI-SQL engines) — schema and normalization, keys and constraints, indexing, query tuning, execution plans, transactions and isolation, locking and deadlocks, pagination, or schema migrations. Engine-neutral guidance with per-engine dialect notes. Load when working with a relational database and the engine is unspecified or portability across engines matters. license: MIT metadata: version: "1.0.0" engines: [mysql, mariadb, mssql, ansi-sql]

Relational Database (Engine-Agnostic)

Overview

Engine-neutral best practices for relational databases. Principles and patterns are written once; only the points where SQL dialects diverge (MSSQL vs MySQL/MariaDB) are flagged inline or collected in the dialect cheatsheet. Optimize for correctness and the smallest change backed by measured evidence.

When to use

• Designing or reviewing a schema (tables, keys, normalization, data types).
• Adding or fixing indexes; diagnosing slow queries or full table scans.
• Reading an execution plan to find the bottleneck.
• Choosing or troubleshooting transaction isolation, locking, or deadlocks.
• Paginating large result sets; eliminating N+1 access.
• Planning a production-safe schema migration.

Not for: engine-internal deep dives (storage-engine internals like InnoDB buffer pool, vendor-only features, T-SQL-only constructs, replication/sharding topology). Use the engine's own docs or a dedicated single-engine skill.

Workflow

1. Establish context first. Which engine + major version? Read/write mix, row volume, latency target, OLTP vs analytics. Advice changes with these.
2. Read only the relevant reference linked in each section below.
3. Propose the smallest change that solves the problem, with trade-offs.
4. Validate with evidence — capture an execution plan and row/timing metrics before and after (see references/execution-plans.md), not rules of thumb.
5. For production, include a rollback plan and post-deploy verification.

Schema Design

• Model relationships explicitly; start normalized (3NF/BCNF) and denormalize only with a measured reason. Every table needs a stable primary key.
• Pick the narrowest correct data type; constrain invalid states at the schema level (NOT NULL, UNIQUE, CHECK, FK) rather than only in application code.

References: normalization · keys-and-constraints · data-types

Indexing

• Index columns used in WHERE, JOIN, and ORDER BY — by selectivity, not by guess. Composite index order: equality columns first, then range/sort (leftmost prefix rule). A range predicate stops index use for later columns.
• Covering indexes avoid table lookups. Audit and drop unused/duplicate indexes; every index taxes writes.

References: indexing · composite-indexes

Query Optimization

• Keep predicates SARGable — no functions/implicit casts on indexed columns in WHERE. Select only needed columns. Prefer EXISTS over IN (subquery), UNION ALL over UNION when dedup is unneeded, and batch large writes.
• Use keyset (cursor) pagination instead of large OFFSET. Eliminate N+1 by batching or joining.

References: query-optimization · execution-plans · pagination

Transactions & Locking

• Keep transactions short; do I/O and slow work outside them. Access rows in a consistent order to avoid deadlocks; expect deadlocks and retry with backoff.
• Know your engine's default isolation level (it differs) and the anomalies each level allows. Use locking reads (FOR UPDATE) deliberately, not by habit.

References: transactions-and-isolation · locking-and-deadlocks

Migrations & DDL

• Make schema changes additive-first and backward-compatible; expand → backfill → contract. Large/locking DDL needs an online strategy and a rollback path.

Reference: migrations-and-ddl

Engine Dialect Differences

Auto-increment, identifier quoting, pagination, upsert, string functions, current time, EXPLAIN, and isolation defaults all diverge across engines. Confirm syntax for the target engine before writing portable SQL.

Reference: engine-dialect-cheatsheet

Guardrails

• Prefer measured evidence (execution plans, metrics) over blanket rules.
• State the engine and major version when behavior depends on it.
• Get explicit human approval before destructive operations (DROP/DELETE/TRUNCATE or any non-reversible migration). Always confirm a backup exists first.