database-design
General database design principles: modeling, normalization, indexing, naming conventions, migrations, and query optimization. Trigger: Database design, data modeling, schema design, table design, or migration planning.
How do I install this agent skill?
npx skills add https://github.com/vekzz-dev/opencode-skills --skill database-designIs this agent skill safe to install?
- Gen Agent Trust Hubpass
This skill provides a comprehensive technical reference for database design principles, normalization, and optimization across various SQL dialects like PostgreSQL, MySQL, and MariaDB. It includes practical examples for schema migrations and local development setups using Docker. No security vulnerabilities or malicious behaviors were detected.
- Socketpass
No alerts
- Snykpass
Risk: LOW · No issues
What does this agent skill do?
When to Use
- Designing a new database schema
- Reviewing or refactoring existing schemas
- Planning migrations or data model changes
- Choosing data types, indexes, or constraints
- Defining naming conventions for tables, columns, or relationships
Instructions
1. Understand the Domain First
Before writing any DDL, map the business entities and their relationships. Use the conceptual → logical → physical flow:
- Conceptual: Entities, relationships, business rules (ER diagram)
- Logical: Attributes, keys, normal forms (independent of technology)
- Physical: Data types, indexes, partitions, engine-specific features
2. Naming Conventions
| Element | Convention | Example |
|---|---|---|
| Tables | snake_case, plural | users, order_items |
| Columns | snake_case, singular | created_at, email |
| PK | id | id BIGSERIAL PRIMARY KEY |
| FK | {singular_table}_id | user_id, order_id |
| Join table | {table1}_{table2} | users_roles |
| Indexes | idx_{table}_{column(s)} | idx_users_email |
| Unique constraint | uq_{table}_{column(s)} | uq_users_email |
3. Normalization Quick Reference
- 1NF: Atomic columns, no repeating groups
- 2NF: 1NF + all non-key columns depend on the whole PK
- 3NF: 2NF + no transitive dependencies (non-key → non-key)
- Denormalize only when query performance demands it, and document why
4. Data Type Selection
- Use the smallest type that fits the data range
- Prefer standardized types:
TIMESTAMPTZoverTIMESTAMP,UUIDorIDENTITYover natural keys - Avoid
TEXT/VARCHAR(255)as a default — size columns appropriately - Use
DECIMALfor money, notFLOAT - Dialect matters — see references/postgresql.md for pg-specific types (arrays, JSONB, intervals), references/mysql.md / references/mariadb.md for engine quirks, and references/sqlite.md for type affinity rules
5. Indexing Strategy
- PKs and FKs — always index FK columns
- Query-driven — index columns in
WHERE,JOIN,ORDER BY, not arbitrary columns - Composite indexes — leftmost prefix rule applies; order by selectivity
- Covering indexes — include columns to avoid table heap lookups
- Avoid over-indexing — every index slows writes
6. Constraints Over Code
Prefer database constraints over application-level validation:
CHECK (price > 0) -- domain integrity
NOT NULL -- required fields
UNIQUE -- uniqueness
FOREIGN KEY ... ON DELETE -- referential integrity
7. Migration Patterns
- Every migration must be reversible (have a
down/rollback) - One migration = one logical change (add column, create table, etc.)
- Never modify a migration that has already been applied to production
- Use timestamp-based or sequential naming:
20250531_create_users.sql
8. Query Design
- Avoid
SELECT *— always name columns - Use EXPLAIN ANALYZE before optimizing
- Prefer set-based operations over row-by-row (cursors)
- Use
LIMIT/OFFSETfor pagination, but prefer keyset pagination for large datasets - See references/query-optimization.md for execution plans, join strategies, CTEs vs subqueries, N+1, and dialect-specific tips
9. Decision Table
| Need | Approach |
|---|---|
| Simple CRUD | Normalized relational model |
| Reporting / analytics | Star schema (fact + dimension tables) |
| Time-series data | Partition by time, consider specialized DB |
| Full-text search | Dedicated index (GIN, or external search) |
| Hierarchical data | Adjacency list, nested sets, or LTREE |
| Multi-tenant | Row-level security or tenant discriminator column |
Commands
# Generate migration
migrate create -ext sql -dir migrations create_users
# Run migrations
migrate -database "$DATABASE_URL" up
# Rollback last migration
migrate -database "$DATABASE_URL" down 1
# Review dry-run
migrate -database "$DATABASE_URL" up 1 --dry-run
Dialect References
| Database | File | Key Differentiators |
|---|---|---|
| PostgreSQL | references/postgresql.md | Arrays, JSONB, GIN/GIST, CTEs, window functions, extensions, MVCC |
| MySQL | references/mysql.md | Storage engines, AUTO_INCREMENT, locking quirks, EXPLAIN variants |
| MariaDB | references/mariadb.md | Sequences, WITH optimizations, JSON vs LONGTEXT, thread pool |
| SQLite | references/sqlite.md | Type affinity, no ALTER COLUMN, WAL mode, embedded constraints |
Choose Your Database
| Requirement | Recommended DB |
|---|---|
| Complex queries, analytics, JSON | PostgreSQL |
| High-traffic web app, read-heavy | MySQL / MariaDB |
| Embedded, mobile, testing, local | SQLite |
| Need MariaDB-specific features (sequences, thread pool) | MariaDB |
| Full ACID with advanced indexing | PostgreSQL |
| Minimum ops overhead | SQLite (embedded) or managed MySQL/PostgreSQL |
Anti-Patterns
- EAV (Entity-Attribute-Value) — almost never the right choice; prefer JSONB or separate tables
- Polymorphic associations — breaks referential integrity; use concrete join tables
- Natural keys as PK — they change; use surrogate keys
- One table for everything — violates 1NF; split by entity
- Indexes on every column — write performance degrades
How can the creator link this skill?
Add the canonical catalog link to the repository README so users can inspect current installs and available audits. The publishing guide covers the complete discovery path.
<a href="https://skillzs.dev/skills/vekzz-dev/opencode-skills/database-design">View database-design on skillZs</a>