majiayu000 Database Visualization Skill
Expert knowledge for creating entity-relationship diagrams (ERDs) and visual representations of database schemas using Mermaid.js.
Mermaid.js ERD Syntax
Mermaid.js is a text-based diagramming tool that renders beautiful diagrams from markdown-like syntax.
Basic ERD Structure
erDiagram
CUSTOMER ||--o{ ORDER : places
ORDER ||--|{ LINE_ITEM : contains
CUSTOMER {
int customer_id PK
string name
string email UK
}
ORDER {
int order_id PK
int customer_id FK
date order_date
}
Relationship Cardinality
Mermaid uses special notation for relationship cardinality:
| Cardinality | Left | Right | Syntax | Example |
|---|---|---|---|---|
| Zero or one | ` | o` | `o | ` |
| Exactly one | ` | ` | ` | |
| Zero or more | }o |
o{ |
}o--o{ |
Many-to-many |
| One or more | `} | ` | ` | {` |
Common Patterns:
erDiagram
%% One-to-many (most common)
PARENT ||--o{ CHILD : has
%% Many-to-many (via junction table)
STUDENT }o--o{ COURSE : enrolls
STUDENT ||--o{ ENROLLMENT : has
COURSE ||--o{ ENROLLMENT : includes
%% One-to-one
USER ||--|| USER_PROFILE : has
%% Optional relationships
ORDER ||--o| SHIPMENT : "may have"
Entity Attributes
Define entity attributes with:
- Column name
- Data type
- Constraints (PK, FK, UK)
- Optional description
erDiagram
USERS {
int UserId PK "Auto-increment primary key"
nvarchar50 Username UK "Unique username"
nvarchar255 Email UK "Unique email"
nvarchar255 PasswordHash "Hashed password"
datetime2 CreatedAt "Account creation timestamp"
bit IsActive "Account status flag"
}
Relationship Labels
Add meaningful labels to relationships:
erDiagram
CUSTOMER ||--o{ ORDER : "places"
ORDER ||--|{ ORDER_ITEM : "contains"
PRODUCT ||--o{ ORDER_ITEM : "included in"
CATEGORY ||--o{ PRODUCT : "categorizes"
SQL Server to Mermaid Mapping
Data Type Mapping
| SQL Server Type | Mermaid Notation | Example |
|---|---|---|
INT |
int |
UserId int PK |
BIGINT |
bigint |
OrderId bigint PK |
NVARCHAR(n) |
nvarchar{n} |
Username nvarchar50 UK |
NVARCHAR(MAX) |
nvarcharMAX |
Content nvarcharMAX |
VARCHAR(n) |
varchar{n} |
Code varchar20 |
DECIMAL(p,s) |
decimal{p_s} |
Price decimal10_2 |
DATETIME2 |
datetime2 |
CreatedAt datetime2 |
DATE |
date |
BirthDate date |
TIME |
time |
OpenTime time |
BIT |
bit |
IsActive bit |
UNIQUEIDENTIFIER |
guid |
RowGuid guid PK |
Constraint Notation
PK- Primary KeyFK- Foreign KeyUK- Unique KeyPK_FK- Composite primary key + foreign key (junction tables)
Complete Examples
E-Commerce Schema
erDiagram
CUSTOMERS ||--o{ ORDERS : places
ORDERS ||--|{ ORDER_ITEMS : contains
PRODUCTS ||--o{ ORDER_ITEMS : "ordered in"
CATEGORIES ||--o{ PRODUCTS : categorizes
CUSTOMERS ||--o{ ADDRESSES : has
ORDERS ||--o| SHIPMENTS : "shipped via"
CUSTOMERS {
int CustomerId PK
nvarchar100 CustomerName
nvarchar255 Email UK
nvarchar20 Phone
datetime2 CreatedAt
bit IsActive
}
ADDRESSES {
int AddressId PK
int CustomerId FK
nvarchar200 Street
nvarchar100 City
nvarchar50 State
nvarchar20 ZipCode
nvarchar50 Country
}
ORDERS {
int OrderId PK
int CustomerId FK
datetime2 OrderDate
decimal10_2 TotalAmount
nvarchar20 Status
}
ORDER_ITEMS {
int OrderItemId PK
int OrderId FK
int ProductId FK
int Quantity
decimal10_2 UnitPrice
decimal10_2 Subtotal
}
PRODUCTS {
int ProductId PK
int CategoryId FK
nvarchar200 ProductName
nvarcharMAX Description
decimal10_2 Price
int Stock
bit IsActive
}
CATEGORIES {
int CategoryId PK
nvarchar100 CategoryName UK
nvarchar500 Description
}
SHIPMENTS {
int ShipmentId PK
int OrderId FK UK
nvarchar100 Carrier
nvarchar50 TrackingNumber
datetime2 ShippedDate
datetime2 DeliveredDate
}
Blog Platform Schema
erDiagram
USERS ||--o{ POSTS : writes
USERS ||--o{ COMMENTS : writes
POSTS ||--o{ COMMENTS : has
CATEGORIES ||--o{ POSTS : contains
POSTS }o--o{ TAGS : tagged
POSTS ||--o{ POST_TAGS : has
TAGS ||--o{ POST_TAGS : applied_to
USERS {
int UserId PK
nvarchar50 Username UK
nvarchar255 Email UK
nvarchar255 PasswordHash
nvarchar200 DisplayName
nvarcharMAX Bio
datetime2 CreatedAt
datetime2 LastLoginAt
bit IsActive
}
POSTS {
int PostId PK
int UserId FK
int CategoryId FK
nvarchar200 Title
nvarchar500 Slug UK
nvarcharMAX Content
nvarcharMAX Excerpt
datetime2 PublishedAt
datetime2 UpdatedAt
int ViewCount
nvarchar20 Status
}
COMMENTS {
int CommentId PK
int PostId FK
int UserId FK
int ParentCommentId FK
nvarcharMAX Content
datetime2 CreatedAt
bit IsApproved
}
CATEGORIES {
int CategoryId PK
nvarchar100 CategoryName UK
nvarchar200 Slug UK
nvarchar500 Description
}
TAGS {
int TagId PK
nvarchar50 TagName UK
nvarchar100 Slug UK
}
POST_TAGS {
int PostId PK_FK
int TagId PK_FK
}
Many-to-Many with Attributes (Enrollment System)
erDiagram
STUDENTS ||--o{ ENROLLMENTS : enrolls
COURSES ||--o{ ENROLLMENTS : has
INSTRUCTORS ||--o{ COURSES : teaches
STUDENTS {
int StudentId PK
nvarchar100 FirstName
nvarchar100 LastName
nvarchar255 Email UK
date DateOfBirth
datetime2 EnrolledDate
}
COURSES {
int CourseId PK
int InstructorId FK
nvarchar100 CourseName
nvarchar20 CourseCode UK
int Credits
decimal10_2 Price
}
ENROLLMENTS {
int EnrollmentId PK
int StudentId FK
int CourseId FK
datetime2 EnrollmentDate
char2 Grade
decimal5_2 Score
nvarchar20 Status
}
INSTRUCTORS {
int InstructorId PK
nvarchar100 FirstName
nvarchar100 LastName
nvarchar255 Email UK
nvarchar100 Department
}
Best Practices
1. Consistent Entity Naming
Choose either singular or plural and stick with it:
- ✅ Plural:
USERS,ORDERS,PRODUCTS - ✅ Singular:
USER,ORDER,PRODUCT - ❌ Mixed:
USER,ORDERS,PRODUCT
2. Clear Relationship Labels
Use verb phrases that read naturally:
erDiagram
CUSTOMER ||--o{ ORDER : "places"
%% Reads as: "Customer places Order"
ORDER ||--|{ ORDER_ITEM : "contains"
%% Reads as: "Order contains Order Items"
3. Show Important Attributes
Include enough detail to understand the schema, but don't overcrowd:
- ✅ Primary keys, foreign keys, unique constraints
- ✅ Key business attributes
- ✅ Important data types
- ❌ Every single column (too cluttered)
4. Group Related Entities
Organize entities logically in the diagram:
erDiagram
%% User-related entities
USERS ||--|| USER_PROFILES : has
USERS ||--o{ ADDRESSES : has
%% Order-related entities
ORDERS ||--|{ ORDER_ITEMS : contains
ORDERS ||--o| SHIPMENTS : "shipped via"
5. Use Composite Keys Appropriately
For junction tables in many-to-many relationships:
erDiagram
POST_TAGS {
int PostId PK_FK
int TagId PK_FK
datetime2 TaggedAt
}
Rendering Mermaid Diagrams
In Markdown Files
```mermaid
erDiagram
USERS ||--o{ POSTS : writes
...
```
In GitHub
GitHub automatically renders Mermaid diagrams in:
- README.md files
- Issue descriptions
- Pull request descriptions
- Wiki pages
In VS Code
Install the "Markdown Preview Mermaid Support" extension to see live previews.
Online Editors
Common Patterns
Self-Referencing Relationships
erDiagram
EMPLOYEES ||--o{ EMPLOYEES : "manages"
CATEGORIES ||--o{ CATEGORIES : "parent of"
EMPLOYEES {
int EmployeeId PK
nvarchar100 Name
int ManagerId FK "References EmployeeId"
}
Inheritance/Subtype Pattern
erDiagram
MEDIA ||--o{ POSTS : "is a"
MEDIA ||--o{ PHOTOS : "is a"
MEDIA {
int MediaId PK
nvarchar20 MediaType "Post or Photo"
datetime2 CreatedAt
}
POSTS {
int MediaId PK_FK
nvarchar200 Title
nvarcharMAX Content
}
PHOTOS {
int MediaId PK_FK
nvarchar500 Url
nvarchar200 Caption
}
Audit Columns Pattern
Show audit columns when relevant:
erDiagram
PRODUCTS {
int ProductId PK
nvarchar200 ProductName
decimal10_2 Price
datetime2 CreatedAt
int CreatedBy FK
datetime2 UpdatedAt
int UpdatedBy FK
bit IsDeleted
}
When to Use This Skill
Use this skill when:
- Designing new database schemas
- Documenting existing databases
- Creating technical documentation
- Explaining database structure to team members
- Planning schema migrations
- Reverse-engineering databases
- Teaching database design concepts
Simply mention "ERD", "diagram", "visualize schema", or "Mermaid" and this knowledge will be applied to create clear, professional database visualizations.