ER diagram based on Class diagram - bounswe/bounswe2025group2 GitHub Wiki

erDiagram
    USER {
        string  user_id PK
        string  username
        string  email
        string  password_hash
        string  role
    }
    PROFILE {
        string  user_id PK,FK
        string  bio
        string  profile_picture
    }
    BADGE {
        string  badge_id PK
        string  user_id FK
        string  name
        string  description
        string  icon
        string  source
    }
    GOAL {
        string  goal_id PK
        string  user_id FK
        string  type
        string  metric
        float   target_value
        float   current_value
        string  status
    }

    CHALLENGE {
        string  challenge_id PK
        string  name
        string  type
        date    start_date
        date    end_date
        string  status
    }
    USER_CHALLENGE {
        string  user_id PK,FK
        string  challenge_id PK,FK
        date    joined_at
        float   progress
    }
    LEADERBOARD {
        string  challenge_id PK,FK
        date    last_updated
    }
    LEADERBOARD_ENTRY {
        string  challenge_id PK,FK
        string  user_id PK,FK
        int     rank
        float   points
    }

    CHAT {
        string  chat_id PK
        date    created_at
    }
    CHAT_PARTICIPANT {
        string  chat_id PK,FK
        string  user_id PK,FK
        date    added_at
    }
    MESSAGE {
        string  message_id PK
        string  chat_id FK
        string  sender_id FK
        string  content
        date    sent_at
    }

    FORUM {
        string  forum_id PK
        string  name
    }
    THREAD {
        string  thread_id PK
        string  forum_id FK
        string  creator_id FK
        string  title
    }
    POST {
        string  post_id PK
        string  thread_id FK
        string  author_id FK
        string  parent_post_id FK
        string  content
        date    created_at
        int     upvotes
        int     downvotes
    }

    MENTOR { string user_id PK,FK }
    COACH  { string user_id PK,FK
             string verification_data }

    MENTOR_MENTEE {
        string mentor_id PK,FK
        string mentee_id PK,FK
        date   started_at
        date   ended_at
    }
    COACH_MENTEE {
        string coach_id PK,FK
        string mentee_id PK,FK
    }
    FEEDBACK {
        string feedback_id PK
        string sender_id FK
        string recipient_id FK
        string content
        bool   is_read
        date   sent_at
    }

    NOTIFICATION {
        string notification_id PK
        string recipient_id FK
        string message
        bool   is_read
        date   created_at
    }
    MAIL {
        string mail_id PK
        string recipient_id FK
        string subject
        string content
        date   timestamp
        bool   is_sent
    }

    %% Relationships
    USER ||--|| PROFILE           : owns
    USER ||--o{ BADGE             : earns
    USER ||--o{ GOAL              : sets
    USER ||--o{ USER_CHALLENGE    : joins
    CHALLENGE ||--o{ USER_CHALLENGE : has_participant
    CHALLENGE ||--|| LEADERBOARD   : has
    LEADERBOARD ||--o{ LEADERBOARD_ENTRY : records
    USER ||--o{ LEADERBOARD_ENTRY : scored
    CHAT ||--o{ CHAT_PARTICIPANT   : has
    USER ||--o{ CHAT_PARTICIPANT   : talks_in
    CHAT ||--o{ MESSAGE            : contains
    USER ||--o{ MESSAGE            : sends
    FORUM ||--o{ THREAD            : contains
    THREAD ||--o{ POST             : contains
    USER ||--o{ POST               : writes
    POST ||--o{ POST               : replies
    USER ||--o{ NOTIFICATION       : receives
    USER ||--o{ MAIL               : mailed_to
    USER ||--o{ FEEDBACK           : feedback_sent
    USER ||--o{ FEEDBACK           : feedback_received

    %% Sub-type option (comment out if using role field)
    USER ||--|| MENTOR             : is_a
    USER ||--|| COACH              : is_a
    MENTOR ||--o{ MENTOR_MENTEE    : mentors
    USER   ||--o{ MENTOR_MENTEE    : mentee
    COACH  ||--o{ COACH_MENTEE     : coaches
    USER   ||--o{ COACH_MENTEE     : coachee