Skip to content
TheChatArchiveThe Chat Archive
Geminigemini-2.5-flash· GoogleHugging Face LMArena Human Preference 140K

create a ER diagram of a CRM data base

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with o3-2025-04-16’s answer.
  1. User

    create a ER diagram of a CRM data base

  2. Gemini Assistant

    An Entity-Relationship (ER) diagram for a CRM (Customer Relationship Management) database visualizes the core entities involved in managing customer interactions and sales processes, along with their relationships.

    Here's a conceptual ER Diagram for a typical CRM database, followed by detailed explanations of each entity and its attributes.


    Conceptual ER Diagram for a CRM Database

    erDiagram
        USER {
            int user_id PK
            varchar username UK
            varchar password_hash
            varchar first_name
            varchar last_name
            varchar email UK
            varchar role (e.g., Sales, Marketing, Support, Admin)
            datetime created_at
            datetime updated_at
        }
    
        ACCOUNT {
            int account_id PK
            varchar account_name UK
            varchar industry
            varchar website
            varchar phone
            varchar address_street
            varchar address_city
            varchar address_state
            varchar address_zip
            varchar address_country
            datetime created_at
            datetime updated_at
            int owner_user_id FK "Owner"
        }
    
        CONTACT {
            int contact_id PK
            varchar first_name
            varchar last_name
            varchar email UK
            varchar phone
            varchar title
            varchar lead_source
            varchar status (e.g., New, Active, Inactive)
            datetime created_at
            datetime updated_at
            int account_id FK "Associated Company"
            int owner_user_id FK "Owner"
        }
    
        LEAD {
            int lead_id PK
            varchar first_name
            varchar last_name
            varchar email UK
            varchar phone
            varchar company_name
            varchar lead_source
            varchar status (e.g., New, Qualified, Disqualified, Converted)
            datetime created_at
            datetime updated_at
            int owner_user_id FK "Owner"
            int converted_contact_id FK "Converted To"
            int converted_account_id FK "Converted To"
        }
    
        OPPORTUNITY {
            int opportunity_id PK
            varchar opportunity_name
            decimal amount
            date close_date
            varchar stage (e.g., Prospecting, Qualification, Proposal, Negotiation, Closed Won, Closed Lost)
            varchar description
            datetime created_at
            datetime updated_at
            int account_id FK "Belongs To"
            int primary_contact_id FK "Primary Contact"
            int owner_user_id FK "Owner"
        }
    
        PRODUCT {
            int product_id PK
            varchar product_name UK
            varchar description
            decimal price
            varchar sku UK
            boolean is_active
            datetime created_at
            datetime updated_at
        }
    
        OPPORTUNITY_PRODUCT_LINE_ITEM {
            int line_item_id PK
            int opportunity_id FK
            int product_id FK
            int quantity
            decimal unit_price
            decimal discount
            datetime created_at
        }
    
        ACTIVITY {
            int activity_id PK
            varchar activity_type (e.g., Call, Meeting, Task, Email)
            varchar subject
            varchar description
            datetime due_date
            varchar status (e.g., Planned, Completed, Canceled)
            datetime created_at
            datetime updated_at
            int created_by_user_id FK "Created By"
            int assigned_to_user_id FK "Assigned To"
            int related_to_id "Polymorphic FK"
            varchar related_to_type "Polymorphic Type (e.g., Account, Contact, Opportunity, Case)"
        }
    
        CASE {
            int case_id PK
            varchar subject
            varchar description
            varchar status (e.g., New, Open, Pending, Closed)
            varchar priority (e.g., Low, Medium, High)
            datetime created_at
            datetime updated_at
            int account_id FK
            int contact_id FK
            int assigned_to_user_id FK "Assigned To"
        }
    
        CAMPAIGN {
            int campaign_id PK
            varchar campaign_name UK
            varchar type (e.g., Email, Social Media, Event)
            date start_date
            date end_date
            decimal budget
            varchar status (e.g., Planning, Active, Completed)
            datetime created_at
            datetime updated_at
            int created_by_user_id FK "Created By"
        }
    
        CAMPAIGN_CONTACT {
            int campaign_contact_id PK
            int campaign_id FK
            int contact_id FK
            varchar status (e.g., Sent, Opened, Clicked, Responded)
            datetime created_at
        }
    
        NOTE {
            int note_id PK
            varchar content
            datetime created_at
            int created_by_user_id FK
            int related_to_id "Polymorphic FK"
            varchar related_to_type "Polymorphic Type (e.g., Account, Contact, Opportunity, Case, Lead)"
        }
    
        USER ||--o{ ACCOUNT : owns
        USER ||--o{ CONTACT : owns
        USER ||--o{ LEAD : owns
        USER ||--o{ OPPORTUNITY : owns
        USER ||--o{ ACTIVITY : "created by"
        USER ||--o{ ACTIVITY : "assigned to"
        USER ||--o{ CASE : "assigned to"
        USER ||--o{ CAMPAIGN : "created by"
        USER ||--o{ NOTE : "created by"
    
        ACCOUNT ||--o{ CONTACT : "has"
        ACCOUNT ||--o{ OPPORTUNITY : "has"
        ACCOUNT ||--o{ CASE : "has"
    
        CONTACT ||--o{ OPPORTUNITY : "is primary contact for"
        CONTACT ||--o{ CAMPAIGN_CONTACT : "is targeted by"
        CONTACT ||--o{ LEAD : "is converted from"
    
        LEAD }|--o| CONTACT : "converts to (1:1)"
        LEAD }|--o| ACCOUNT : "converts to (1:1)"
    
        OPPORTUNITY ||--o{ OPPORTUNITY_PRODUCT_LINE_ITEM : "contains"
        PRODUCT ||--o{ OPPORTUNITY_PRODUCT_LINE_ITEM : "is included in"
    
        CAMPAIGN ||--o{ CAMPAIGN_CONTACT : "targets"
    
        ACTIVITY }o--|| ACCOUNT : "relates to"
        ACTIVITY }o--|| CONTACT : "relates to"
        ACTIVITY }o--|| OPPORTUNITY : "relates to"
        ACTIVITY }o--|| CASE : "relates to"
        ACTIVITY }o--|| LEAD : "relates to"
    
        NOTE }o--|| ACCOUNT : "relates to"
        NOTE }o--|| CONTACT : "relates to"
        NOTE }o--|| OPPORTUNITY : "relates to"
        NOTE }o--|| CASE : "relates to"
        NOTE }o--|| LEAD : "relates to"
    

    Explanation of Entities and Relationships:

    1. USER

    • Purpose: Represents an internal user of the CRM system (e.g., a sales rep, marketing manager, support agent).
    • Attributes:
      • user_id (PK): Unique identifier for the user.
      • username (UK): Unique login username.
      • password_hash: Hashed password for security.
      • first_name, last_name: User's name.
      • email (UK): User's email address.
      • role: Defines user's permissions and access level (e.g., Sales, Marketing, Support, Admin).
      • created_at, updated_at: Timestamps.
    • Relationships:
      • 1:M with ACCOUNT, CONTACT, LEAD, OPPORTUNITY: A user can own multiple Accounts, Contacts, Leads, and Opportunities. (e.g., owner_user_id FK in these tables).
      • 1:M with ACTIVITY, CASE, CAMPAIGN, NOTE: A user can create or be assigned multiple Activities, Cases, Campaigns, and Notes.

    2. ACCOUNT

    • Purpose: Represents a company or organization that is a customer, partner, or prospect.
    • Attributes:
      • account_id (PK): Unique identifier for the account.
      • account_name (UK): Name of the company.
      • industry, website, phone, address_*: Company details.
      • created_at, updated_at: Timestamps.
      • owner_user_id (FK): The user responsible for this account.
    • Relationships:
      • 1:M with CONTACT: An Account can have multiple Contacts associated with it.
      • 1:M with OPPORTUNITY: An Account can have multiple Opportunities.
      • 1:M with CASE: An Account can have multiple Support Cases.

    3. CONTACT

    • Purpose: Represents an individual person associated with an Account or a standalone individual.
    • Attributes:
      • contact_id (PK): Unique identifier for the contact.
      • first_name, last_name: Contact's name.
      • email (UK): Contact's email address.
      • phone, title: Contact details.
      • lead_source: How this contact was acquired (e.g., Web, Referral, Event).
      • status: Current status (e.g., New, Active, Inactive).
      • created_at, updated_at: Timestamps.
      • account_id (FK, Nullable): The Account this contact belongs to (can be null if it's a standalone contact or not yet linked to an account).
      • owner_user_id (FK): The user responsible for this contact.
    • Relationships:
      • M:1 with ACCOUNT: Many Contacts can belong to one Account.
      • 1:M with OPPORTUNITY: A Contact can be the primary contact for multiple Opportunities (though usually one per opportunity).
      • M:M with CAMPAIGN (via CAMPAIGN_CONTACT): Contacts can be targeted by multiple Campaigns.
      • 1:1 with LEAD: A Contact can be created from a converted Lead.

    4. LEAD

    • Purpose: Represents a potential customer or prospect that has not yet been qualified or associated with an existing Account/Contact.
    • Attributes:
      • lead_id (PK): Unique identifier for the lead.
      • first_name, last_name, email (UK), phone, company_name: Lead's details.
      • lead_source: How this lead was acquired.
      • status: Current stage of the lead (e.g., New, Qualified, Disqualified, Converted).
      • created_at, updated_at: Timestamps.
      • owner_user_id (FK): The user responsible for this lead.
      • converted_contact_id (FK, Nullable): If converted, links to the new Contact.
      • converted_account_id (FK, Nullable): If converted, links to the new Account.
    • Relationships:
      • 1:1 with CONTACT and ACCOUNT: When a Lead is "converted," it typically creates a new Contact and/or a new Account, and these FKs track the conversion.

    5. OPPORTUNITY

    • Purpose: Represents a potential sales deal with an Account.
    • Attributes:
      • opportunity_id (PK): Unique identifier.
      • opportunity_name: Name of the deal.
      • amount: Estimated revenue.
      • close_date: Expected close date.
      • stage: Current sales stage (e.g., Prospecting, Negotiation, Closed Won).
      • description: Details of the opportunity.
      • created_at, updated_at: Timestamps.
      • account_id (FK): The Account this opportunity belongs to.
      • primary_contact_id (FK): The main Contact associated with this opportunity.
      • owner_user_id (FK): The user responsible for this opportunity.
    • Relationships:
      • M:1 with ACCOUNT: Many Opportunities belong to one Account.
      • M:1 with CONTACT: Many Opportunities can share a primary Contact.
      • M:M with PRODUCT (via OPPORTUNITY_PRODUCT_LINE_ITEM): An Opportunity can involve multiple Products, and a Product can be part of many Opportunities.

    6. PRODUCT

    • Purpose: Represents products or services that can be sold.
    • Attributes:
      • product_id (PK): Unique identifier.
      • product_name (UK): Name of the product/service.
      • description: Product details.
      • price: Unit price.
      • sku (UK): Stock Keeping Unit.
      • is_active: Boolean indicating if the product is currently active.
      • created_at, updated_at: Timestamps.
    • Relationships:
      • M:M with OPPORTUNITY (via OPPORTUNITY_PRODUCT_LINE_ITEM): Products are linked to Opportunities through a junction table.

    7. OPPORTUNITY_PRODUCT_LINE_ITEM (Junction Table)

    • Purpose: Resolves the M:M relationship between OPPORTUNITY and PRODUCT, and stores details specific to that instance of a product in an opportunity.
    • Attributes:
      • line_item_id (PK): Unique identifier for the line item.
      • opportunity_id (FK), product_id (FK): Composite PK linking to Opportunity and Product.
      • quantity: Number of units of the product in this opportunity.
      • unit_price: The price at which it was added (might differ from PRODUCT.price due to discounts, etc.).
      • discount: Discount applied to this line item.
      • created_at: Timestamp.

    8. ACTIVITY

    • Purpose: Represents any interaction or task related to a customer (e.g., calls, meetings, emails, tasks).
    • Attributes:
      • activity_id (PK): Unique identifier.
      • activity_type: Type of activity (e.g., Call, Meeting, Task, Email).
      • subject, description: Details of the activity.
      • due_date: When the activity is due/happened.
      • status: Status of the activity (e.g., Planned, Completed, Canceled).
      • created_at, updated_at: Timestamps.
      • created_by_user_id (FK): The user who created the activity.
      • assigned_to_user_id (FK): The user responsible for completing the activity.
      • related_to_id: Polymorphic Foreign Key - Stores the ID of the related entity.
      • related_to_type: Polymorphic Type - Stores the type of the related entity (e.g., 'Account', 'Contact', 'Opportunity', 'Case', 'Lead'). This allows an activity to be associated with different types of records.
    • Relationships:
      • M:1 with USER: An Activity is created by one user and assigned to one user.
      • Polymorphic M:1: An Activity can be related to one Account, one Contact, one Opportunity, one Case, or one Lead.

    9. CASE

    • Purpose: Represents a customer support issue or inquiry.
    • Attributes:
      • case_id (PK): Unique identifier.
      • subject, description: Details of the issue.
      • status: Current status of the case (e.g., New, Open, Pending, Closed).
      • priority: Urgency of the case (e.g., Low, Medium, High).
      • created_at, updated_at: Timestamps.
      • account_id (FK, Nullable): The Account associated with the case.
      • contact_id (FK, Nullable): The Contact who raised the case.
      • assigned_to_user_id (FK): The support agent assigned to the case.
    • Relationships:
      • M:1 with ACCOUNT, CONTACT, USER: Many Cases belong to one Account/Contact and are assigned to one user.

    10. CAMPAIGN

    • Purpose: Represents a marketing campaign or initiative.
    • Attributes:
      • campaign_id (PK): Unique identifier.
      • campaign_name (UK): Name of the campaign.
      • type: Type of campaign (e.g., Email, Social Media, Event).
      • start_date, end_date: Campaign duration.
      • budget: Allocated budget.
      • status: Campaign status (e.g., Planning, Active, Completed).
      • created_at, updated_at: Timestamps.
      • created_by_user_id (FK): The user who created the campaign.
    • Relationships:
      • 1:M with CAMPAIGN_CONTACT: A Campaign can target many Contacts.

    11. CAMPAIGN_CONTACT (Junction Table)

    • Purpose: Resolves the M:M relationship between CAMPAIGN and CONTACT, tracking which contacts participated in or were targeted by which campaigns.
    • Attributes:
      • campaign_contact_id (PK): Unique identifier.
      • campaign_id (FK), contact_id (FK): Composite PK linking to Campaign and Contact.
      • status: Contact's status in relation to the campaign (e.g., Sent, Opened, Clicked, Responded).
      • created_at: Timestamp.

    12. NOTE

    • Purpose: General notes that can be attached to various CRM records.
    • Attributes:
      • note_id (PK): Unique identifier.
      • content: The text of the note.
      • created_at: When the note was created.
      • created_by_user_id (FK): The user who created the note.
      • related_to_id: Polymorphic Foreign Key - Stores the ID of the related entity.
      • related_to_type: Polymorphic Type - Stores the type of the related entity (e.g., 'Account', 'Contact', 'Opportunity', 'Case', 'Lead').
    • Relationships:
      • M:1 with USER: A Note is created by one user.
      • Polymorphic M:1: A Note can be related to one Account, one Contact, one Opportunity, one Case, or one Lead.

    Key Concepts in this ER Diagram:

    • Primary Key (PK): Unique identifier for each record in a table.
    • Foreign Key (FK): A field in one table that uniquely identifies a row of another table. It establishes a link between tables.
    • Unique Key (UK): Ensures all values in a column are different (e.g., email, username).
    • Cardinality:
      • 1:M (One-to-Many): One record in Table A can relate to many records in Table B, but one record in Table B can only relate to one record in Table A. (e.g., One User owns Many Accounts).
      • M:N (Many-to-Many): Many records in Table A can relate to many records in Table B. This is resolved using a Junction Table (or Associative Entity). (e.g., Opportunity_Product_Line_Item resolves M:N between Opportunity and Product).
      • 1:1 (One-to-One): One record in Table A relates to one record in Table B. (e.g., a Converted Lead converts to exactly one Contact).
    • Polymorphic Association: A single foreign key column can refer to multiple different tables. This is common in CRMs for entities like Activity or Note that can relate to Account, Contact, Opportunity, etc. It's implemented using two columns: related_to_id (the ID of the record) and related_to_type (the name of the table or entity type).

    This diagram provides a solid foundation for a CRM database. Real-world CRMs often include more complex features like custom fields, integrations, reports, forecasting, and more granular security roles, which would expand this diagram significantly.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.