| name | database-schema-design |
| description | 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. |
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.
- Identify Entities & Attributes: Analyze requirements to find the core nouns (entities) and their properties (attributes).
- Determine Relationships: Define how entities relate to each other (one-to-one, one-to-many, many-to-many).
- Create an ERD with Mermaid: Visualize the schema with an Entity-Relationship Diagram.
- Define Constraints: Specify rules to ensure data integrity (primary keys, foreign keys, unique, check).
- Generate DDL Scripts: Write the SQL code to create the schema.
- Develop Indexing Strategy: Plan indexes to optimize query performance.
- Consider Denormalization: Decide if and where to denormalize for performance reasons.
- 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
