CRM Database

← Online Examination Database ERP Database Design →

∙ Topic

CRM Database

A CRM (Customer Relationship Management) Database is designed to manage customers, leads, sales opportunities, interactions, campaigns, and support tickets. It helps businesses track customer relationships from lead generation to conversion and post-sales support, enabling better sales performance and customer satisfaction.

📝Syntax

-- Create Database
CREATE DATABASE crm_system;

USE crm_system;

crm-database.sql

📝 Edit Code

-- Users (Sales Agents & Admins)

CREATE TABLE users (
    user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin','sales','manager') DEFAULT 'sales',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Customers Table

CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150),
    phone VARCHAR(20),
    company VARCHAR(150),
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Leads Table

CREATE TABLE leads (
    lead_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    customer_id BIGINT,
    source VARCHAR(150),
    status ENUM('new','contacted','qualified','lost','converted') DEFAULT 'new',
    assigned_to BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (assigned_to) REFERENCES users(user_id)
);

-- Opportunities Table

CREATE TABLE opportunities (
    opportunity_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    customer_id BIGINT NOT NULL,
    title VARCHAR(255),
    value DECIMAL(12,2),
    stage ENUM('prospecting','proposal','negotiation','won','lost') DEFAULT 'prospecting',
    expected_close_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Activities Table

CREATE TABLE activities (
    activity_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    customer_id BIGINT,
    user_id BIGINT,
    type ENUM('call','email','meeting','task') DEFAULT 'call',
    description TEXT,
    activity_date DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- Campaigns Table

CREATE TABLE campaigns (
    campaign_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    budget DECIMAL(12,2),
    start_date DATE,
    end_date DATE,
    status ENUM('planned','running','completed') DEFAULT 'planned',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tickets Table (Support System)

CREATE TABLE tickets (
    ticket_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    customer_id BIGINT,
    subject VARCHAR(255),
    description TEXT,
    status ENUM('open','in_progress','resolved','closed') DEFAULT 'open',
    priority ENUM('low','medium','high','urgent') DEFAULT 'medium',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

👁 Preview

💡 This preview does not execute SQL; it’s for reading/editing the query.

💡CRM Overview

1Customer data management.
2Lead generation and tracking.
3Sales pipeline management.
4Customer interaction history.
5Support ticket handling.

💡Core Tables

1Users.
2Customers.
3Leads.
4Opportunities.
5Activities.
6Campaigns.
7Tickets.

💡Customers Table

1Stores customer information.
2Includes contact details.
3Represents real business clients.
4Used across all CRM modules.

💡Leads Table

1Tracks potential customers.
2Stores lead source and status.
3Assigned to sales agents.
4Converted into customers later.

💡Opportunities Table

1Represents potential deals.
2Tracks sales pipeline stages.
3Includes expected revenue.
4Used for forecasting.

💡Activities Table

1Logs customer interactions.
2Includes calls, emails, meetings.
3Assigned to users.
4Maintains communication history.

💡Campaigns Table

1Stores marketing campaigns.
2Tracks budget and timeline.
3Measures campaign performance.
4Supports marketing analytics.

💡Tickets Table

1Handles customer support requests.
2Tracks issue status.
3Assigns priority levels.
4Improves customer service.

💡Database Relationships

1One Customer → Many Leads.
2One Customer → Many Opportunities.
3One Customer → Many Activities.
4One User → Many Assigned Leads.

💡Sales Workflow

1Lead is generated.
2Sales agent contacts lead.
3Lead is qualified.
4Opportunity is created.
5Deal is closed (won/lost).

💡Scalability Considerations

1Index customer and lead tables.
2Use caching for dashboards.
3Separate analytics database.
4Use message queues for activities.
5Optimize reporting queries.

💡Benefits of CRM Database

1Improves customer relationships.
2Increases sales efficiency.
3Centralized customer data.
4Better decision making.
5Enhances customer support.

🏢Real-world

1Used by sales and marketing teams to manage customers.
2Tracks leads from acquisition to conversion.
3Helps manage sales pipeline effectively.
4Stores customer communication history.
5Improves customer support and retention.
6Used in SaaS, retail, and enterprise systems.

⚠Common Mistakes

1Mixing leads and customers in a single table.
2Not tracking sales stages properly.
3Storing activities inside customer table.
4Missing assignment tracking for leads.
5Not indexing customer_id and assigned_to fields.

✅Best Practices

1Separate customers, leads, and opportunities.
2Use pipeline stages for sales tracking.
3Index frequently queried fields.
4Maintain activity logs for every interaction.
5Use role-based access for users.

✅Quick Summary

CRM databases manage customers, leads, opportunities, and support tickets.
They help businesses track sales pipelines and customer interactions.
Proper normalization improves scalability.
Activities log ensures complete interaction history.
CRM systems improve sales and customer retention.

🎯Interview Questions

Q1. What is the difference between leads and customers?

Answer: Leads are potential customers, while customers are converted and active clients.

Q2. Why are opportunities important in CRM?

Answer: They track potential deals and help in sales forecasting.

Q3. What is the purpose of activities table?

Answer: To log all customer interactions like calls, emails, and meetings.

Q4. How does CRM improve sales?

Answer: By tracking pipeline stages and improving customer engagement.

Q5. What is the biggest challenge in CRM systems?

Answer: Managing large-scale customer data and real-time sales tracking.

❓Quiz

Which table tracks potential sales deals in a CRM database?

Opportunities Customers Tickets Campaigns

←

PreviousOnline Examination Database

SQL Tutorial

NextERP Database Design →

CRM Database

CRM Database

Related topics