database-design

Name: database-design
Author: krzysztofsurdy

40installs1forks

How to install database-design?

krzysztofsurdy/code-virtuoso/database-design

$npx -y skills add krzysztofsurdy/code-virtuoso --skill database-design

Installs into the current project.

Summary

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 partit

Files · 1

View on GitHub

SKILL.md

1	# Database Design
2
3	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.
4
5	## Data Modeling Principles
6
7	### Start from Access Patterns
8
9	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:
10
11	1. What queries will run most frequently? - these determine table structure, indexes, and denormalization choices
12	2. What consistency guarantees does the data need? - these determine normalization level, constraints, and transaction boundaries
13
14	### Entity Relationships
15
16	\| Relationship \| Implementation \| When to Use \|
17	\|---\|---\|---\|
18	\| 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 \|
19	\| One-to-many \| Foreign key on the child table referencing the parent \| Orders to order items, users to addresses \|
20	\| Many-to-many \| Join table with composite primary key \| Tags to articles, students to courses \|
21	\| Many-to-many with attributes \| Join table with its own columns beyond the two foreign keys \| Enrollment with grade, membership with role \|
22	\| Self-referential \| Foreign key referencing the same table \| Org charts, category trees, threaded comments \|
23
24	### Naming Conventions
25
26	Consistent naming prevents confusion across teams and tools:
27
28	- Tables: plural nouns in snake_case (`order_items`, `user_addresses`)
29	- Columns: singular snake_case describing the value (`created_at`, `total_amount`)
30	- Foreign keys: `<referenced_table_singular>_id` (`user_id`, `order_id`)
31	- Indexes: `idx_<table>_<columns>` (`idx_orders_user_id_created_at`)
32	- Constraints: `chk_<table>_<rule>`, `uq_<table>_<columns>`, `fk_<table>_<referenced>`
33
34	---
35
36	## Normalization vs Denormalization
37
38	### Normal Forms
39
40	\| Form \| Rule \| Violation Example \|
41	\|---\|---\|---\|
42	\| 1NF \| Every column holds atomic values; no repeating groups \| Storing comma-separated tags in a single column \|
43	\| 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 \|
44	\| 3NF \| No non-key column depends on another non-key column \| Storing both `city` and `zip_code` when zip determines city \|
45	\| BCNF \| Every determinant is a candidate key \| A scheduling table where room determines building but room is not a key \|
46
47	### When to Denormalize
48
49	Normalization prevents anomalies but adds JOINs. Denormalize selectively when:
50
51	- Read-heavy workloads dominate and JOIN cost is measurable in profiling
52	- Reporting tables need pre-aggregated data that would otherwise require expensive queries
53	- Caching a computed value avoids recalculating on every read (e.g., `order_total` stored on the order row)
54	- Document-oriented access retrieves an entire aggregate in one read
55
56	Rules for safe denormalization:
57
58	1. Always keep the normalized source of truth - denormalized data is a derived cache
59	2. Define how and when the denormalized copy is updated (trigger, application event, batch job)
60	3. Monitor for drift between the source and the copy
61	4. Document why the denormalization exists and what access pattern it serves
62
63	---
64
65	## Choosing a Database Type
66
67	\| Type \| Strengths \| Fits When \|
68	\|---\|---\|---\|
69	\| Relational (PostgreSQL, MySQL) \| ACID transactions, complex queries, mature tooling, JOINs \| Structured data with relationships, transactional workloads, most CRUD applications \|
70	\| Document (MongoDB, DynamoDB) \| Flexible schema, nested data, horizontal scaling \| Aggregates accessed as a unit, rapidly evolving schemas, per-tenant isolation \|
71	\| Key-value (Redis, Memcached) \| Sub-millisecond reads, simple data model \| Session storage, caching, counters, rate limiting \|
72	\| Column-family (Cassandra, ScyllaDB) \| High write throughput, wide rows, linear scaling \| Time-series, IoT telemetry, append-heavy workloads \|
73	\| Graph (Neo4j, Neptune) \| Traversal queries, relationship-centric data \| Social networks, recommendation engines, fraud detection \|
74	\| Time-series (TimescaleDB, InfluxDB) \| Optimized for time-stamped data, automatic partitioning \| Metrics, monitoring, financial tick data \|

People who liked this also viewed

6 picks

Type

database-design

40installs1forks

View on GitHub

How to install database-design?

krzysztofsurdy/code-virtuoso/database-design

$npx -y skills add krzysztofsurdy/code-virtuoso --skill database-design

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# Database Design
2
3	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.
4
5	## Data Modeling Principles
6
7	### Start from Access Patterns
8
9	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:
10
11	1. What queries will run most frequently? - these determine table structure, indexes, and denormalization choices
12	2. What consistency guarantees does the data need? - these determine normalization level, constraints, and transaction boundaries
13
14	### Entity Relationships
15
16	\| Relationship \| Implementation \| When to Use \|
17	\|---\|---\|---\|
18	\| 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 \|
19	\| One-to-many \| Foreign key on the child table referencing the parent \| Orders to order items, users to addresses \|
20	\| Many-to-many \| Join table with composite primary key \| Tags to articles, students to courses \|
21	\| Many-to-many with attributes \| Join table with its own columns beyond the two foreign keys \| Enrollment with grade, membership with role \|
22	\| Self-referential \| Foreign key referencing the same table \| Org charts, category trees, threaded comments \|
23
24	### Naming Conventions
25
26	Consistent naming prevents confusion across teams and tools:
27
28	- Tables: plural nouns in snake_case (`order_items`, `user_addresses`)
29	- Columns: singular snake_case describing the value (`created_at`, `total_amount`)
30	- Foreign keys: `<referenced_table_singular>_id` (`user_id`, `order_id`)
31	- Indexes: `idx_<table>_<columns>` (`idx_orders_user_id_created_at`)
32	- Constraints: `chk_<table>_<rule>`, `uq_<table>_<columns>`, `fk_<table>_<referenced>`
33
34	---
35
36	## Normalization vs Denormalization
37
38	### Normal Forms
39
40	\| Form \| Rule \| Violation Example \|
41	\|---\|---\|---\|
42	\| 1NF \| Every column holds atomic values; no repeating groups \| Storing comma-separated tags in a single column \|
43	\| 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 \|
44	\| 3NF \| No non-key column depends on another non-key column \| Storing both `city` and `zip_code` when zip determines city \|
45	\| BCNF \| Every determinant is a candidate key \| A scheduling table where room determines building but room is not a key \|
46
47	### When to Denormalize
48
49	Normalization prevents anomalies but adds JOINs. Denormalize selectively when:
50
51	- Read-heavy workloads dominate and JOIN cost is measurable in profiling
52	- Reporting tables need pre-aggregated data that would otherwise require expensive queries
53	- Caching a computed value avoids recalculating on every read (e.g., `order_total` stored on the order row)
54	- Document-oriented access retrieves an entire aggregate in one read
55
56	Rules for safe denormalization:
57
58	1. Always keep the normalized source of truth - denormalized data is a derived cache
59	2. Define how and when the denormalized copy is updated (trigger, application event, batch job)
60	3. Monitor for drift between the source and the copy
61	4. Document why the denormalization exists and what access pattern it serves
62
63	---
64
65	## Choosing a Database Type
66
67	\| Type \| Strengths \| Fits When \|
68	\|---\|---\|---\|
69	\| Relational (PostgreSQL, MySQL) \| ACID transactions, complex queries, mature tooling, JOINs \| Structured data with relationships, transactional workloads, most CRUD applications \|
70	\| Document (MongoDB, DynamoDB) \| Flexible schema, nested data, horizontal scaling \| Aggregates accessed as a unit, rapidly evolving schemas, per-tenant isolation \|
71	\| Key-value (Redis, Memcached) \| Sub-millisecond reads, simple data model \| Session storage, caching, counters, rate limiting \|
72	\| Column-family (Cassandra, ScyllaDB) \| High write throughput, wide rows, linear scaling \| Time-series, IoT telemetry, append-heavy workloads \|
73	\| Graph (Neo4j, Neptune) \| Traversal queries, relationship-centric data \| Social networks, recommendation engines, fraud detection \|
74	\| Time-series (TimescaleDB, InfluxDB) \| Optimized for time-stamped data, automatic partitioning \| Metrics, monitoring, financial tick data \|

People who liked this also viewed

6 picks

Type