software-database-designDesigns database schemas, migrations, and data models for PostgreSQL, MySQL, MongoDB, and Redis. Use when planning tables, relationships, indexes, or ORM-backed schema changes.
Independent GitHub snapshot · Automated classification · Original documentation governs
Schema design, data modeling, migration safety, and ORM patterns. This skill covers structural decisions — what tables exist, how they relate, how schemas evolve. For tuning queries on an existing schema, use data-sql-optimization.
| Task | Default Picks | Notes |
|---|---|---|
| Relational database | PostgreSQL (a supported stable major) | Transactions, complex queries, JSON support; verify the supported major at https://www.postgresql.org/support/versioning/ |
| Relational (MySQL family) | A supported MySQL LTS series | Use an LTS series for production, not an Innovation release; Innovation releases use calendar versioning, so the version number alone does not tell you the track. Check which series are LTS and their support end dates at https://dev.mysql.com/doc/mysql-lts-innovation-release-model/en/ before naming a version |
| Embedded / edge relational | SQLite | Release train moves fast (monthly point releases); pin a version, don't chase latest blindly |
| Flexible schema | MongoDB (a supported major) | Rapid iteration, embedded relationships; verify the major at https://www.mongodb.com/docs/manual/release-notes/ |
| Caching / sessions | Redis | Ephemeral data, counters, pub/sub |
| Graph traversals | Neo4j | Relationship-heavy queries (social, fraud) |
| Time-series | TimescaleDB, InfluxDB | Metrics, IoT, event streams |
| Managed app backend | ../software-baas-platforms/SKILL.md | Use when auth, realtime, storage, and functions are part of the platform choice |
| Schema migrations | expand-contract pattern | Zero-downtime changes |
| ORM | Prisma, Drizzle, SQLAlchemy, EF Core | Match stack; review generated SQL |
| Vector similarity | pgvector (co-located with PostgreSQL) | Start with exact search; add HNSW when measured latency requires ANN, then test recall under filters. See storage selection. |
| Full-text search as the product | Elasticsearch / OpenSearch | For relevance and facets beyond the database's text search; see software-search. |
| Problem | Go here |
|---|---|
| Query optimization on existing schemas | data-sql-optimization |
| Backend service implementation | software-backend |
| Managed app backend platform (Supabase, Convex, Firebase, Appwrite, PocketBase) | software-baas-platforms |
| SwiftData/Core Data schemas mirrored to CloudKit | software-ios-native |
| On-device iOS semantic/vector retrieval | software-ios-ai-engine |
| Data lake or warehouse architecture | data-lake-platform |
| Streaming or real-time pipelines | data-streaming |
| System-level data architecture decisions | software-architecture-design |
1) Identify entities and their relationships
2) Choose data model:
- Relational (PostgreSQL, MySQL) for structured data with complex joins
- Document (MongoDB) for flexible schemas with embedded relationships
- Key-value (Redis) for caching, sessions, counters
- Graph (Neo4j) when variable-depth traversals or path queries dominate
3) Normalize to 3NF by default; denormalize with justification
4) Define primary keys, foreign keys, and constraints
5) Plan indexing strategy based on access patterns
6) Design migration path (can it be applied with zero downtime?)
For the full relational vs. graph vs. vector decision matrix, see references/storage-paradigm-selection.md.
| Situation | Approach | Rationale |
|---|---|---|
| Transactional data (orders, users) | Normalize to 3NF | Data integrity, reduce anomalies |
| Read-heavy dashboards | Denormalize or materialized views | Query performance |
| Audit logs | Append-only, denormalized | Immutability, query speed |
| User preferences/settings | JSON column or document | Flexible schema, rarely joined |
| Hierarchical data (categories, org charts) | Adjacency list or materialized path | Query pattern determines choice |
| Many-to-many with attributes | Junction table with columns | Clean modeling |
When to stop normalizing. Keep independently managed entities separate. If measured queries repeatedly rejoin fields with no independent lifecycle, consider a combined table or a cached field with an explicit invalidation owner. Treat denormalization as an operational decision because copies can drift.
The "one big table + JSON" failure mode. JSON is useful for variable, rarely joined attributes. Repeated filters on an inner field may need an expression or GIN index, but JSON keys still lack ordinary foreign-key integrity; if an attribute becomes central to joins or constraints, promote it to a typed column. Validate the real predicates with EXPLAIN before changing the model.
ALTER TABLE subcommands do not all take the same lock (see migration strategies)SET lock_timeout (a few seconds) and SET statement_timeout, plus a retry loop — lock requests queue in arrival order, so even a fast ACCESS EXCLUSIVE request queues behind, and then blocks, every later reader/writer if it waits behind a long-running transaction. Check pg_stat_activity for long-running transactions on the target table before running DDL.ACCESS EXCLUSIVE for the duration — treat as high-risk)Rule: rules/sql/safety.md loads this invariant when Claude edits a matching file.
Write the compatibility matrix before the change: old code on old schema, old code on expanded schema, new code on expanded schema, and rollback code after partial backfill. The expand phase must preserve old reads and writes; backfills must be restartable and observable; the contract phase begins only after production evidence shows no caller depends on the old shape. Treat destructive DDL as final cleanup, not the migration itself.
Phase 1: EXPAND
- Add new column/table (nullable, no constraints yet)
- Deploy app code that writes to both old and new
- Backfill existing data into new structure
Phase 2: MIGRATE
- Deploy app code that reads from new structure
- Verify data consistency between old and new
- Add constraints and indexes on new structure
Phase 3: CONTRACT
- Deploy app code that only uses new structure
- Remove old column/table in a follow-up migration
- Each phase is a separate deployment — never combine
Choose index types from measured access patterns: B-tree by default, GIN/GiST/BRIN only for the query shapes they serve. In composite indexes put equality columns first, then sort, then range (Equality → Sort → Range); do not order by cardinality. Full access-pattern table and indexing rules (partial, covering, BRIN, fillfactor/HOT): references/indexing-strategy.md.
Use eager loading to prevent N+1, keep domain logic out of persistence models, review generated SQL, and drop to raw SQL for bulk work and reports. Pattern and anti-pattern tables: references/orm-framework-guide.md.
Partition only on measured signals (maintenance time, scheduled retention drops, consistent filtering on the partition key), not because a table "feels big". Transaction-mode poolers break session-scoped state (search_path, advisory locks, session SET), so run migrations over a direct connection. Decision gates and pooler traps: references/partitioning-and-pooling.md.
NOT NULL, uniqueness, or foreign-key constraints before a data cleanup and backfill plan exists.| Avoid | Do Instead |
|---|---|
| EAV (Entity-Attribute-Value) tables | JSON columns or document store |
| Storing money as floats | Use DECIMAL / NUMERIC or integer cents |
| Soft deletes everywhere | Use only when audit trail required; otherwise hard delete |
| UUID v4 as clustered primary key | UUID v7 (RFC 9562; time-ordered, uuidv7() built in since PostgreSQL 18) or BIGINT GENERATED ALWAYS AS IDENTITY |
| Storing files in the database | Store in object storage; keep metadata/URL in DB |
| No foreign keys "for performance" | FK constraints prevent data corruption; index the FK column |
| One migration per PR with schema + data | Separate schema migration from data backfill |
| Graph: indexing every property on every label | Index only properties used in WHERE/MATCH lookup positions |
Graph: generic relationship types (CONNECTED_TO, RELATED) | Use specific typed edges (FOLLOWS, OWNS, REPORTS_TO) |
Primary-key choice. Default to BIGINT GENERATED ALWAYS AS IDENTITY when one database generates IDs. Use UUIDv7 when clients or multiple services must generate IDs before insertion; its time ordering improves index locality compared with UUIDv4, but exposes approximate creation time. InnoDB's clustered-primary-key trade-off differs from PostgreSQL; see MySQL schema notes.
Worked scenarios S1-S5 (multi-tenant shared vs schema-per-tenant, Postgres expand-contract with online backfill and version-specific NOT NULL steps, high-cardinality index design, soft-delete vs archival, JSONB versioning): references/schema-design-scenarios.md.
Full pattern (collection topology, vector index, memory schema, operational checklist): see references/mongodb-atlas-ai-context.md.
| Skill | Relationship |
|---|---|
| data-sql-optimization | Query tuning on existing schemas |
| software-backend | Data access patterns in backend services |
| software-baas-platforms | Managed app-backend platform choice and migration boundaries |
| software-csharp-backend | EF Core data access and migration patterns |
| software-architecture-design | System-level data architecture decisions |
| data-lake-platform | Analytical storage and lakehouse design |
| software-ios-native | SwiftData/Core Data + CloudKit persistence in native iOS apps |
| software-ios-ai-engine | On-device iOS semantic search and local vector retrieval |
Primary sources live in data/sources.json.
Before delivering output:
When prior decisions or pitfalls are relevant, consult learnings.consolidated.md if present; use learnings.md only for needed history or as the available fallback. Otherwise skip both.
After applying it, if you encountered a pattern worth remembering, a mistake worth preventing, or a domain fact that surprised you, append one dated bullet to learnings.md via agents-skills-feedback-loop/scripts/append_learning.py. Do not modify SKILL.md itself.