database-design

Database design principles and decision-making. Schema design, indexing strategy, ORM selection, serverless databases.

31,392 stars

Complexity: easy

About this skill

The 'database-design' skill equips AI agents with structured knowledge and decision-making frameworks for developing sound database architectures. It emphasizes understanding core principles over rote pattern application, enabling agents to intelligently design and optimize database solutions. Key areas covered include: - **Schema Design**: Principles of normalization, primary and foreign keys, and managing relationships between tables. - **Indexing Strategy**: Best practices for various index types, composite indexes, and performance tuning. - **Database Selection**: Guidance on choosing between relational databases like PostgreSQL, serverless options like Neon/Turso, or embedded solutions like SQLite. - **ORM Selection**: Criteria for selecting Object-Relational Mappers (ORMs) such as Drizzle, Prisma, or Kysely for different project needs. - **Optimization Fundamentals**: Insights into optimizing database performance and scalability. This skill functions as an internal knowledge base, allowing the AI agent to 'think' through design challenges by consulting relevant documentation rather than executing external commands. It's ideal for agents tasked with generating database designs, providing architectural recommendations, or advising on technology choices.

Best use case

When an AI agent needs to assist in designing a new database, optimizing an existing one, or recommending appropriate database technologies (e.g., specific database systems, ORMs, indexing strategies) based on project requirements.

Database design principles and decision-making. Schema design, indexing strategy, ORM selection, serverless databases.

A well-reasoned, principled database design or set of recommendations, including optimal schema definitions, indexing strategies, and appropriate technology selections (database platform, ORM), tailored to the input request.

Practical example

Example input

Design a robust database schema for a social media application. The application needs to handle user profiles, posts (with text and optional images), comments, likes, and follower relationships. Suggest a suitable database and ORM for a modern web backend.

Example output

Based on the request for a social media application with user profiles, posts, comments, likes, and follower relationships, I'll leverage the `database-design` skill, specifically consulting `schema-design.md`, `database-selection.md`, and `orm-selection.md`.

**1. Database Selection (from `database-selection.md`):**
For a social media application requiring strong data consistency, complex queries (e.g., fetching posts with comments and likes), and relationship management, **PostgreSQL** is an excellent choice. It's robust, open-source, and highly scalable. For serverless deployment or simplified operations, cloud-managed PostgreSQL services or a platform like Neon (which offers serverless PostgreSQL) could be considered.

**2. Schema Design (from `schema-design.md`):**
Applying normalization principles, here's a proposed schema:

*   **Users Table**
    *   `user_id` (UUID, Primary Key)
    *   `username` (VARCHAR, UNIQUE, NOT NULL)
    *   `email` (VARCHAR, UNIQUE, NOT NULL)
    *   `password_hash` (VARCHAR, NOT NULL)
    *   `profile_picture_url` (VARCHAR)
    *   `bio` (TEXT)
    *   `created_at` (TIMESTAMP WITH TIME ZONE, DEFAULT NOW())
    *   `updated_at` (TIMESTAMP WITH TIME ZONE, DEFAULT NOW())

*   **Posts Table**
    *   `post_id` (UUID, Primary Key)
    *   `user_id` (UUID, Foreign Key REFERENCES Users, NOT NULL)
    *   `content` (TEXT, NOT NULL)
    *   `image_url` (VARCHAR)
    *   `created_at` (TIMESTAMP WITH TIME ZONE, DEFAULT NOW())
    *   `updated_at` (TIMESTAMP WITH TIME ZONE, DEFAULT NOW())

*   **Comments Table**
    *   `comment_id` (UUID, Primary Key)
    *   `post_id` (UUID, Foreign Key REFERENCES Posts, NOT NULL)
    *   `user_id` (UUID, Foreign Key REFERENCES Users, NOT NULL)
    *   `content` (TEXT, NOT NULL)
    *   `created_at` (TIMESTAMP WITH TIME ZONE, DEFAULT NOW())

*   **Likes Table (junction table for many-to-many)**
    *   `user_id` (UUID, Foreign Key REFERENCES Users, Primary Key)
    *   `post_id` (UUID, Foreign Key REFERENCES Posts, Primary Key)
    *   `created_at` (TIMESTAMP WITH TIME ZONE, DEFAULT NOW())

*   **Follows Table (junction table for many-to-many follower relationships)**
    *   `follower_id` (UUID, Foreign Key REFERENCES Users (user_id), Primary Key)
    *   `following_id` (UUID, Foreign Key REFERENCES Users (user_id), Primary Key)
    *   `created_at` (TIMESTAMP WITH TIME ZONE, DEFAULT NOW())

**3. Indexing Strategy (from `indexing.md`):**
To optimize common queries like fetching a user's posts, posts by a followed user, or comments on a post:
*   `Posts`: Index on `user_id` (for fetching a user's posts).
*   `Comments`: Index on `post_id` (for fetching comments on a post) and `user_id` (for fetching comments by a user).
*   `Likes`: Composite index on `(post_id, user_id)` (already covered by PK) and individual indexes on `post_id` and `user_id` for efficient lookups.
*   `Follows`: Composite index on `(follower_id, following_id)` (already covered by PK) and individual indexes on `follower_id` and `following_id`.

**4. ORM Selection (from `orm-selection.md`):**
For a modern web backend, assuming TypeScript/JavaScript, **Prisma** is a strong recommendation. It offers a powerful type-safe query builder, clear schema definition, and excellent developer experience, integrating well with PostgreSQL. Drizzle ORM is another strong, lightweight alternative known for its performance and type safety, especially for greenfield projects.

When to use this skill

Use this skill when the agent is asked to: - Create a database schema for a new application. - Provide recommendations for optimizing database performance. - Advise on the selection of a database system (e.g., SQL vs. NoSQL, serverless vs. traditional). - Suggest an appropriate ORM for a given programming language and project context.

When not to use this skill

Do not use this skill for: - Direct execution of SQL queries or database administration tasks (e.g., backups, user management). - Tasks requiring real-time interaction with a live database instance. - Highly specialized database problems that require expertise beyond general design principles.

Installation

Claude Code / Cursor / Codex

$curl -o ~/.claude/skills/database-design/SKILL.md --create-dirs "https://raw.githubusercontent.com/sickn33/antigravity-awesome-skills/main/plugins/antigravity-awesome-skills-claude/skills/database-design/SKILL.md"

Manual Installation

Download SKILL.md from GitHub
Place it in .claude/skills/database-design/SKILL.md inside your project
Restart your AI agent — it will auto-discover the skill

How database-design Compares

Feature / Agent	database-design	Standard Approach
Platform Support	Claude	Limited / Varies
Context Awareness	High	Baseline
Installation Complexity	easy	N/A

Frequently Asked Questions

What does this skill do?

Database design principles and decision-making. Schema design, indexing strategy, ORM selection, serverless databases.

Which AI agents support this skill?

This skill is designed for Claude.

How difficult is it to install?

The installation complexity is rated as easy. You can find the installation instructions above.

Where can I find the source code?

You can find the source code on GitHub using the link provided at the top of the page.

Related Guides

Best AI Skills for Claude

Explore the best AI skills for Claude and Claude Code across coding, research, workflow automation, documentation, and agent operations.

AI Agents for Startups

Explore AI agent skills for startup validation, product research, growth experiments, documentation, and fast execution with small teams.

AI Agents for Coding

Browse AI agent skills for coding, debugging, testing, refactoring, code review, and developer workflows across Claude, Cursor, and Codex.

SKILL.md Source

# Database Design

> **Learn to THINK, not copy SQL patterns.**

## 🎯 Selective Reading Rule

**Read ONLY files relevant to the request!** Check the content map, find what you need.

| File | Description | When to Read |
|------|-------------|--------------|
| `database-selection.md` | PostgreSQL vs Neon vs Turso vs SQLite | Choosing database |
| `orm-selection.md` | Drizzle vs Prisma vs Kysely | Choosing ORM |
| `schema-design.md` | Normalization, PKs, relationships | Designing schema |
| `indexing.md` | Index types, composite indexes | Performance tuning |
| `optimization.md` | N+1, EXPLAIN ANALYZE | Query optimization |
| `migrations.md` | Safe migrations, serverless DBs | Schema changes |

---

## ⚠️ Core Principle

- ASK user for database preferences when unclear
- Choose database/ORM based on CONTEXT
- Don't default to PostgreSQL for everything

---

## Decision Checklist

Before designing schema:

- [ ] Asked user about database preference?
- [ ] Chosen database for THIS context?
- [ ] Considered deployment environment?
- [ ] Planned index strategy?
- [ ] Defined relationship types?

---

## Anti-Patterns

❌ Default to PostgreSQL for simple apps (SQLite may suffice)
❌ Skip indexing
❌ Use SELECT * in production
❌ Store JSON when structured data is better
❌ Ignore N+1 queries

## When to Use
This skill is applicable to execute the workflow or actions described in the overview.

Software DevelopmentClaude