skillZs
★ LIVE SKILL TAGS ★
>>> LIVE SKILLS INDEX <<<
* OPEN SOURCE *
NO LOGIN, NO TRACKING
※ REAL INSTALL DATA ※
← back to all skills
vekzz-dev/opencode-skills58 installs

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-design
view source ↗

Is 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:

  1. Conceptual: Entities, relationships, business rules (ER diagram)
  2. Logical: Attributes, keys, normal forms (independent of technology)
  3. Physical: Data types, indexes, partitions, engine-specific features

2. Naming Conventions

ElementConventionExample
Tablessnake_case, pluralusers, order_items
Columnssnake_case, singularcreated_at, email
PKidid BIGSERIAL PRIMARY KEY
FK{singular_table}_iduser_id, order_id
Join table{table1}_{table2}users_roles
Indexesidx_{table}_{column(s)}idx_users_email
Unique constraintuq_{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: TIMESTAMPTZ over TIMESTAMP, UUID or IDENTITY over natural keys
  • Avoid TEXT/VARCHAR(255) as a default — size columns appropriately
  • Use DECIMAL for money, not FLOAT
  • 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

  1. PKs and FKs — always index FK columns
  2. Query-driven — index columns in WHERE, JOIN, ORDER BY, not arbitrary columns
  3. Composite indexes — leftmost prefix rule applies; order by selectivity
  4. Covering indexes — include columns to avoid table heap lookups
  5. 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 / OFFSET for 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

NeedApproach
Simple CRUDNormalized relational model
Reporting / analyticsStar schema (fact + dimension tables)
Time-series dataPartition by time, consider specialized DB
Full-text searchDedicated index (GIN, or external search)
Hierarchical dataAdjacency list, nested sets, or LTREE
Multi-tenantRow-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

DatabaseFileKey Differentiators
PostgreSQLreferences/postgresql.mdArrays, JSONB, GIN/GIST, CTEs, window functions, extensions, MVCC
MySQLreferences/mysql.mdStorage engines, AUTO_INCREMENT, locking quirks, EXPLAIN variants
MariaDBreferences/mariadb.mdSequences, WITH optimizations, JSON vs LONGTEXT, thread pool
SQLitereferences/sqlite.mdType affinity, no ALTER COLUMN, WAL mode, embedded constraints

Choose Your Database

RequirementRecommended DB
Complex queries, analytics, JSONPostgreSQL
High-traffic web app, read-heavyMySQL / MariaDB
Embedded, mobile, testing, localSQLite
Need MariaDB-specific features (sequences, thread pool)MariaDB
Full ACID with advanced indexingPostgreSQL
Minimum ops overheadSQLite (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

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>