Blog CMS Database ER Diagram: Posts, Tags, and Comments

Mermaid code

erDiagram
    USER ||--o{ POST : writes
    USER ||--o{ COMMENT : writes
    POST ||--o{ COMMENT : receives
    POST ||--o{ POST_TAG : tagged
    TAG ||--o{ POST_TAG : labels
    POST ||--o{ MEDIA : embeds
    USER {
        int id PK
        string username UK
        string email UK
        string role
    }
    POST {
        int id PK
        int author_id FK
        string slug UK
        string status
        datetime published_at
    }
    POST_TAG {
        int post_id FK
        int tag_id FK
    }

Paste this into any tool that renders Mermaid, or into the editor to change the theme and export PNG or SVG. Syntax reference: Entity Relationship guide.

What this diagram shows

The join entity POST_TAG carries the many-to-many. Both POST ||--o{ POST_TAG and TAG ||--o{ POST_TAG are one-to-many, which together express that a post has many tags and a tag has many posts. USER connects to both POST and COMMENT, and COMMENT points back to POST, so a comment always knows its post and its author. UK marks the unique columns: username, email, and slug. COMMENT, TAG, and MEDIA appear through relationships only; give them attribute blocks once the columns are decided.

How to adapt it

• Add int parent_id FK to COMMENT and a self-relationship COMMENT ||--o{ COMMENT : replies for threaded comments.
• Split string role into a ROLE entity if users can hold several roles.
• Add a CATEGORY entity with POST }o--|| CATEGORY : "filed under" for a single required category per post.
• Add int revision and a POST_REVISION entity if editors need version history.

FAQ