Database Design
Good database design determines the long-term maintainability, performance, and correctness of any data-driven application. Schema decisions made early are expensive to reverse later. Every table, column, index, and constraint should exist for a reason backed by access patterns and business rules.
Data Modeling Principles
Start from Access Patterns
Design tables around how the application reads and writes data, not around how entities look in a domain model. Two questions drive every schema decision:
- What queries will run most frequently? - these determine table structure, indexes, and denormalization choices
- What consistency guarantees does the data need? - these determine normalization level, constraints, and transaction boundaries
Entity Relationships
| Relationship |
Implementation |
When to Use |
| One-to-one |
Foreign key with UNIQUE constraint on the child table |
Splitting rarely-accessed columns into a separate table, or enforcing exactly-one semantics |
| One-to-many |
Foreign key on the child table referencing the parent |
Orders to order items, users to addresses |
| Many-to-many |
Join table with composite primary key |
Tags to articles, students to courses |
| Many-to-many with attributes |
Join table with its own columns beyond the two foreign keys |
Enrollment with grade, membership with role |
| Self-referential |
Foreign key referencing the same table |
Org charts, category trees, threaded comments |
Naming Conventions
Consistent naming prevents confusion across teams and tools:
- Tables: plural nouns in snake_case (
order_items, user_addresses)
- Columns: singular snake_case describing the value (
created_at, total_amount)
- Foreign keys:
<referenced_table_singular>_id (user_id, order_id)
- Indexes:
idx_<table>_<columns> (idx_orders_user_id_created_at)
- Constraints:
chk_<table>_<rule>, uq_<table>_<columns>, fk_<table>_<referenced>
Normalization vs Denormalization
Normal Forms
| Form |
Rule |
Violation Example |
| 1NF |
Every column holds atomic values; no repeating groups |
Storing comma-separated tags in a single column |
| 2NF |
Every non-key column depends on the entire primary key |
In a composite-key table, a column depending on only part of the key |
| 3NF |
No non-key column depends on another non-key column |
Storing both city and zip_code when zip determines city |
| BCNF |
Every determinant is a candidate key |
A scheduling table where room determines building but room is not a key |
When to Denormalize
Normalization prevents anomalies but adds JOINs. Denormalize selectively when:
- Read-heavy workloads dominate and JOIN cost is measurable in profiling
- Reporting tables need pre-aggregated data that would otherwise require expensive queries
- Caching a computed value avoids recalculating on every read (e.g.,
order_total stored on the order row)
- Document-oriented access retrieves an entire aggregate in one read
Rules for safe denormalization:
- Always keep the normalized source of truth - denormalized data is a derived cache
- Define how and when the denormalized copy is updated (trigger, application event, batch job)
- Monitor for drift between the source and the copy
- Document why the denormalization exists and what access pattern it serves
Choosing a Database Type
| Type |
Strengths |
Fits When |
| Relational (PostgreSQL, MySQL) |
ACID transactions, complex queries, mature tooling, JOINs |
Structured data with relationships, transactional workloads, most CRUD applications |
| Document (MongoDB, DynamoDB) |
Flexible schema, nested data, horizontal scaling |
Aggregates accessed as a unit, rapidly evolving schemas, per-tenant isolation |
| Key-value (Redis, Memcached) |
Sub-millisecond reads, simple data model |
Session storage, caching, counters, rate limiting |
| Column-family (Cassandra, ScyllaDB) |
High write throughput, wide rows, linear scaling |
Time-series, IoT telemetry, append-heavy workloads |
| Graph (Neo4j, Neptune) |
Traversal queries, relationship-centric data |
Social networks, recommendation engines, fraud detection |
| Time-series (TimescaleDB, InfluxDB) |
Optimized for time-stamped data, automatic partitioning |
Metrics, monitoring, financial tick data |
Polyglot persistence - using different databases for different parts of the same system - is valid when access patterns genuinely differ. It is not valid as a way to avoid learning one database well.
Indexing Fundamentals
Indexes accelerate reads at the cost of slower writes and additional storage. Every index must justify its existence through query patterns.
Index Types
| Type |
Structure |
Best For |
| B-tree |
Balanced tree, sorted data |
Equality and range queries, ORDER BY, most general-purpose indexing |
| Hash |
Hash table |
Exact equality lookups only; no range support |
| GiST |
Generalized search tree |
Spatial data, geometric queries, range types, nearest-neighbor |
| GIN |
Generalized inverted index |
Full-text search, JSONB containment, array membership |
| BRIN |
Block range index |
Large tables with naturally ordered data (timestamps, sequential IDs) |
Composite Index Design
The order of columns in a composite index matters. The leftmost prefix rule means a composite index on (a, b, c) supports queries filtering on (a), (a, b), or (a, b, c), but not (b, c) alone.
Column ordering guidelines:
- Equality conditions first - columns compared with
=
- Range conditions last - columns compared with
>, <, BETWEEN
- Most selective column first among equals
Covering and Partial Indexes
- Covering index: includes all columns the query needs, so the database reads only the index. Use
INCLUDE (PostgreSQL) or just add columns to the index key.
- Partial index: indexes only rows matching a condition, reducing size and write overhead. Ideal for querying a small subset of a large table (e.g.,
WHERE status = 'pending').
See Indexing Strategies Reference for detailed index types, EXPLAIN analysis, and anti-patterns.
Schema Evolution
Schema changes are inevitable. The question is whether they break running applications.
Backward-Compatible Changes (Safe)
- Adding a new nullable column
- Adding a new table
- Adding a new index (may lock briefly on some engines)
- Widening a column type (e.g.,
VARCHAR(50) to VARCHAR(100))
Breaking Changes (Require Migration Strategy)
- Renaming or removing a column
- Changing a column type in incompatible ways
- Adding a NOT NULL constraint to an existing column with null data
- Splitting or merging tables
The Expand-Contract Pattern
For breaking changes in production with zero downtime:
- Expand - add the new structure alongside the old one
- Migrate - backfill data from old to new, dual-write during transition
- Switch - update application code to use the new structure
- Contract - remove the old structure once nothing references it
See Migration Patterns Reference for zero-downtime strategies, rollback techniques, and multi-tool examples.
Partitioning and Sharding
Table Partitioning (Single Database)
| Strategy |
How It Works |
Use Case |
| Range |
Rows split by value ranges (e.g., by month) |
Time-series data, log tables, archival |
| List |
Rows split by discrete values (e.g., by region) |
Multi-tenant data, geographic segmentation |
| Hash |
Rows distributed by hash of a column |
Even distribution when no natural range exists |
Sharding (Multiple Databases)
Sharding distributes data across separate database instances. Use it only after single-instance optimizations (indexing, caching, read replicas) are exhausted.
Shard key selection criteria:
- High cardinality - many distinct values to distribute evenly
- Present in most queries - avoids scatter-gather across all shards
- Stable - values that do not change after creation
- Avoid hotspots - do not shard by a value that concentrates writes (e.g., current date)
Quick Reference: Common Design Mistakes
| Mistake |
Consequence |
Fix |
| No foreign key constraints |
Orphaned rows, inconsistent data |
Always define foreign keys unless there is a documented reason not to |
| Over-indexing |
Slow writes, wasted storage |
Index only columns used in WHERE, JOIN, ORDER BY of actual queries |
| Storing computed values without a refresh strategy |
Stale data, silent bugs |
Define update triggers, events, or batch jobs alongside any denormalization |
| Using ENUM types for values that change |
Schema migration for every new value |
Use a lookup table with a foreign key instead |
| Storing money as floating-point |
Rounding errors |
Use DECIMAL/NUMERIC or store as integer cents |
| Missing created_at / updated_at timestamps |
No auditability, difficult debugging |
Add timestamp columns to every table by default |
Generic type + type_id polymorphism everywhere |
No referential integrity, complex queries |
Evaluate STI, CTI, or separate tables first |
Reference Files
| Reference |
Contents |
| Modeling Patterns |
Polymorphic associations (STI/CTI/TPT), soft deletes, audit trails, temporal data, self-referential trees, JSON columns |
| Indexing Strategies |
B-tree/hash/GiST/GIN details, composite index design, covering and partial indexes, EXPLAIN analysis, anti-patterns |
| Migration Patterns |
Version-based vs state-based migrations, expand-contract, data migrations, rollback strategies, multi-tool examples |
Integration with Other Skills
| Situation |
Recommended Skill |
| Optimizing query performance and caching |
Install knowledge-virtuoso from krzysztofsurdy/code-virtuoso for performance optimization patterns |
| Designing domain models and aggregates |
Install knowledge-virtuoso from krzysztofsurdy/code-virtuoso for clean architecture and DDD guidance |
| Building APIs that expose database-backed resources |
Install knowledge-virtuoso from krzysztofsurdy/code-virtuoso for API design principles |
| Testing database interactions |
Install knowledge-virtuoso from krzysztofsurdy/code-virtuoso for testing strategies |
1---2name: database-design3description: Database design patterns and data modeling for relational and NoSQL databases. Use when the user asks to design a database schema, normalize or denormalize tables, create indexing strategies, plan schema migrations, model temporal data, implement audit trails, set up table partitioning, or optimize data access patterns. Covers entity relationships, naming conventions, constraint design, migration safety, and performance-oriented schema decisions.4---56# Database Design78Good database design determines the long-term maintainability, performance, and correctness of any data-driven application. Schema decisions made early are expensive to reverse later. Every table, column, index, and constraint should exist for a reason backed by access patterns and business rules.910## Data Modeling Principles1112### Start from Access Patterns1314Design tables around how the application reads and writes data, not around how entities look in a domain model. Two questions drive every schema decision:15161. **What queries will run most frequently?** - these determine table structure, indexes, and denormalization choices172. **What consistency guarantees does the data need?** - these determine normalization level, constraints, and transaction boundaries1819### Entity Relationships2021| Relationship | Implementation | When to Use |22|---|---|---|23| **One-to-one** | Foreign key with UNIQUE constraint on the child table | Splitting rarely-accessed columns into a separate table, or enforcing exactly-one semantics |24| **One-to-many** | Foreign key on the child table referencing the parent | Orders to order items, users to addresses |25| **Many-to-many** | Join table with composite primary key | Tags to articles, students to courses |26| **Many-to-many with attributes** | Join table with its own columns beyond the two foreign keys | Enrollment with grade, membership with role |27| **Self-referential** | Foreign key referencing the same table | Org charts, category trees, threaded comments |2829### Naming Conventions3031Consistent naming prevents confusion across teams and tools:3233- **Tables**: plural nouns in snake_case (`order_items`, `user_addresses`)34- **Columns**: singular snake_case describing the value (`created_at`, `total_amount`)35- **Foreign keys**: `<referenced_table_singular>_id` (`user_id`, `order_id`)36- **Indexes**: `idx_<table>_<columns>` (`idx_orders_user_id_created_at`)37- **Constraints**: `chk_<table>_<rule>`, `uq_<table>_<columns>`, `fk_<table>_<referenced>`3839---4041## Normalization vs Denormalization4243### Normal Forms4445| Form | Rule | Violation Example |46|---|---|---|47| **1NF** | Every column holds atomic values; no repeating groups | Storing comma-separated tags in a single column |48| **2NF** | Every non-key column depends on the entire primary key | In a composite-key table, a column depending on only part of the key |49| **3NF** | No non-key column depends on another non-key column | Storing both `city` and `zip_code` when zip determines city |50| **BCNF** | Every determinant is a candidate key | A scheduling table where room determines building but room is not a key |5152### When to Denormalize5354Normalization prevents anomalies but adds JOINs. Denormalize selectively when:5556- **Read-heavy workloads** dominate and JOIN cost is measurable in profiling57- **Reporting tables** need pre-aggregated data that would otherwise require expensive queries58- **Caching a computed value** avoids recalculating on every read (e.g., `order_total` stored on the order row)59- **Document-oriented access** retrieves an entire aggregate in one read6061**Rules for safe denormalization:**62631. Always keep the normalized source of truth - denormalized data is a derived cache642. Define how and when the denormalized copy is updated (trigger, application event, batch job)653. Monitor for drift between the source and the copy664. Document why the denormalization exists and what access pattern it serves6768---6970## Choosing a Database Type7172| Type | Strengths | Fits When |73|---|---|---|74| **Relational (PostgreSQL, MySQL)** | ACID transactions, complex queries, mature tooling, JOINs | Structured data with relationships, transactional workloads, most CRUD applications |75| **Document (MongoDB, DynamoDB)** | Flexible schema, nested data, horizontal scaling | Aggregates accessed as a unit, rapidly evolving schemas, per-tenant isolation |76| **Key-value (Redis, Memcached)** | Sub-millisecond reads, simple data model | Session storage, caching, counters, rate limiting |77| **Column-family (Cassandra, ScyllaDB)** | High write throughput, wide rows, linear scaling | Time-series, IoT telemetry, append-heavy workloads |78| **Graph (Neo4j, Neptune)** | Traversal queries, relationship-centric data | Social networks, recommendation engines, fraud detection |79| **Time-series (TimescaleDB, InfluxDB)** | Optimized for time-stamped data, automatic partitioning | Metrics, monitoring, financial tick data |8081Polyglot persistence - using different databases for different parts of the same system - is valid when access patterns genuinely differ. It is not valid as a way to avoid learning one database well.8283---8485## Indexing Fundamentals8687Indexes accelerate reads at the cost of slower writes and additional storage. Every index must justify its existence through query patterns.8889### Index Types9091| Type | Structure | Best For |92|---|---|---|93| **B-tree** | Balanced tree, sorted data | Equality and range queries, ORDER BY, most general-purpose indexing |94| **Hash** | Hash table | Exact equality lookups only; no range support |95| **GiST** | Generalized search tree | Spatial data, geometric queries, range types, nearest-neighbor |96| **GIN** | Generalized inverted index | Full-text search, JSONB containment, array membership |97| **BRIN** | Block range index | Large tables with naturally ordered data (timestamps, sequential IDs) |9899### Composite Index Design100101The order of columns in a composite index matters. The **leftmost prefix rule** means a composite index on `(a, b, c)` supports queries filtering on `(a)`, `(a, b)`, or `(a, b, c)`, but not `(b, c)` alone.102103**Column ordering guidelines:**1041051. Equality conditions first - columns compared with `=`1062. Range conditions last - columns compared with `>`, `<`, `BETWEEN`1073. Most selective column first among equals108109### Covering and Partial Indexes110111- **Covering index**: includes all columns the query needs, so the database reads only the index. Use `INCLUDE` (PostgreSQL) or just add columns to the index key.112- **Partial index**: indexes only rows matching a condition, reducing size and write overhead. Ideal for querying a small subset of a large table (e.g., `WHERE status = 'pending'`).113114See [Indexing Strategies Reference](references/indexing-strategies.md) for detailed index types, EXPLAIN analysis, and anti-patterns.115116---117118## Schema Evolution119120Schema changes are inevitable. The question is whether they break running applications.121122### Backward-Compatible Changes (Safe)123124- Adding a new nullable column125- Adding a new table126- Adding a new index (may lock briefly on some engines)127- Widening a column type (e.g., `VARCHAR(50)` to `VARCHAR(100)`)128129### Breaking Changes (Require Migration Strategy)130131- Renaming or removing a column132- Changing a column type in incompatible ways133- Adding a NOT NULL constraint to an existing column with null data134- Splitting or merging tables135136### The Expand-Contract Pattern137138For breaking changes in production with zero downtime:1391401. **Expand** - add the new structure alongside the old one1412. **Migrate** - backfill data from old to new, dual-write during transition1423. **Switch** - update application code to use the new structure1434. **Contract** - remove the old structure once nothing references it144145See [Migration Patterns Reference](references/migration-patterns.md) for zero-downtime strategies, rollback techniques, and multi-tool examples.146147---148149## Partitioning and Sharding150151### Table Partitioning (Single Database)152153| Strategy | How It Works | Use Case |154|---|---|---|155| **Range** | Rows split by value ranges (e.g., by month) | Time-series data, log tables, archival |156| **List** | Rows split by discrete values (e.g., by region) | Multi-tenant data, geographic segmentation |157| **Hash** | Rows distributed by hash of a column | Even distribution when no natural range exists |158159### Sharding (Multiple Databases)160161Sharding distributes data across separate database instances. Use it only after single-instance optimizations (indexing, caching, read replicas) are exhausted.162163**Shard key selection criteria:**164165- High cardinality - many distinct values to distribute evenly166- Present in most queries - avoids scatter-gather across all shards167- Stable - values that do not change after creation168- Avoid hotspots - do not shard by a value that concentrates writes (e.g., current date)169170---171172## Quick Reference: Common Design Mistakes173174| Mistake | Consequence | Fix |175|---|---|---|176| No foreign key constraints | Orphaned rows, inconsistent data | Always define foreign keys unless there is a documented reason not to |177| Over-indexing | Slow writes, wasted storage | Index only columns used in WHERE, JOIN, ORDER BY of actual queries |178| Storing computed values without a refresh strategy | Stale data, silent bugs | Define update triggers, events, or batch jobs alongside any denormalization |179| Using ENUM types for values that change | Schema migration for every new value | Use a lookup table with a foreign key instead |180| Storing money as floating-point | Rounding errors | Use DECIMAL/NUMERIC or store as integer cents |181| Missing created_at / updated_at timestamps | No auditability, difficult debugging | Add timestamp columns to every table by default |182| Generic `type` + `type_id` polymorphism everywhere | No referential integrity, complex queries | Evaluate STI, CTI, or separate tables first |183184---185186## Reference Files187188| Reference | Contents |189|---|---|190| [Modeling Patterns](references/modeling-patterns.md) | Polymorphic associations (STI/CTI/TPT), soft deletes, audit trails, temporal data, self-referential trees, JSON columns |191| [Indexing Strategies](references/indexing-strategies.md) | B-tree/hash/GiST/GIN details, composite index design, covering and partial indexes, EXPLAIN analysis, anti-patterns |192| [Migration Patterns](references/migration-patterns.md) | Version-based vs state-based migrations, expand-contract, data migrations, rollback strategies, multi-tool examples |193194---195196## Integration with Other Skills197198| Situation | Recommended Skill |199|---|---|200| Optimizing query performance and caching | Install `knowledge-virtuoso` from `krzysztofsurdy/code-virtuoso` for performance optimization patterns |201| Designing domain models and aggregates | Install `knowledge-virtuoso` from `krzysztofsurdy/code-virtuoso` for clean architecture and DDD guidance |202| Building APIs that expose database-backed resources | Install `knowledge-virtuoso` from `krzysztofsurdy/code-virtuoso` for API design principles |203| Testing database interactions | Install `knowledge-virtuoso` from `krzysztofsurdy/code-virtuoso` for testing strategies |
krzysztofsurdy