database-schema-design

MUmerRazzaq's avatarfrom MUmerRazzaq

A guide for relational database schema design best practices. Use this skill when a user wants to design a new database schema, understand best practices, create an ERD, write DDL scripts, or develop an indexing or schema evolution strategy.

⭐0stars🔀0forks📁View on GitHub🕐Updated Dec 25, 2025
[SQL and Databases]

When & Why to Use This Skill

This Claude skill provides a comprehensive and structured framework for designing robust, scalable relational database schemas. It guides users through the entire lifecycle of database architecture—from identifying entities and defining complex relationships to generating optimized SQL DDL scripts and visualizing schemas with Mermaid ERDs—ensuring high data integrity and peak query performance.

Use Cases

  • Architecting a new relational database from scratch for web or mobile applications to ensure long-term scalability.
  • Visualizing complex data models by generating Mermaid-based Entity-Relationship Diagrams (ERDs) for technical documentation.
  • Optimizing existing database performance through strategic indexing, constraint definition, and selective denormalization.
  • Developing a schema evolution strategy to manage database migrations and versioning as application requirements change.
  • Generating production-ready SQL DDL scripts based on best practices for data types and relational constraints.
namedatabase-schema-design
descriptionA guide for relational database schema design best practices. Use this skill when a user wants to design a new database schema, understand best practices, create an ERD, write DDL scripts, or develop an indexing or schema evolution strategy.

Relational Database Schema Design

This skill provides a structured workflow for designing robust and scalable relational database schemas.

Schema Design Workflow

Follow these steps to design a database schema. For detailed principles and best practices for each step, consult the Design Principles Guide.

  1. Identify Entities & Attributes: Analyze requirements to find the core nouns (entities) and their properties (attributes).
  2. Determine Relationships: Define how entities relate to each other (one-to-one, one-to-many, many-to-many).
  3. Create an ERD with Mermaid: Visualize the schema with an Entity-Relationship Diagram.
  4. Define Constraints: Specify rules to ensure data integrity (primary keys, foreign keys, unique, check).
  5. Generate DDL Scripts: Write the SQL code to create the schema.
  6. Develop Indexing Strategy: Plan indexes to optimize query performance.
  7. Consider Denormalization: Decide if and where to denormalize for performance reasons.
  8. Plan for Schema Evolution: Establish a strategy for managing future schema changes.

ERD Creation Guide with Mermaid

Use Mermaid syntax to create an ERD. This provides a clear, version-controllable diagram of the schema.

Example: A simple blog schema.

erDiagram
    USER {
        int id PK "User ID"
        string name "User's full name"
        string email "Unique email"
    }
    POST {
        int id PK "Post ID"
        string title "Post title"
        string content "Post body"
        int user_id FK "Author of the post"
    }
    USER ||--|{ POST : "writes"

Cardinality Syntax:

  • |o--|| : One-to-one
  • |o--|{ : One-to-many
  • }o--|{ : Many-to-many

Additional Resources