如何在mysql中设计简单CRM系统_mysql CRM系统项目实战

明确CRM系统核心功能模块

一个简单的CRM系统主要围绕客户信息管理、跟进记录、销售机会和基础用户权限展开。设计前先理清需要支持的核心功能：

这些功能决定了数据库表结构的基本方向。

设计MySQL数据表结构

基于功能需求，创建以下几张核心表：

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  name VARCHAR(100),
  role ENUM('sales', 'manager') DEFAULT 'sales',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  company VARCHAR(100),
  phone VARCHAR(20),
  email VARCHAR(100),
  owner_id INT,
  status ENUM('new', 'contacted', 'qualified', 'lost', 'converted') DEFAULT 'new',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (owner_id) REFERENCES users(id)
);

CREATE TABLE interactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  user_id INT NOT NULL,
  contact_date DATE NOT NULL,
  type ENUM('call', 'email', 'meeting') DEFAULT 'call',
  notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE opportunities (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  title VARCHAR(200) NOT NULL,
  amount DECIMAL(10,2),
  stage ENUM('prospecting', 'proposal', 'negotiation', 'won', 'lost') DEFAULT 'prospecting',
  expected_close_date DATE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

表之间通过外键关联，确保数据一致性。比如客户归属某个销售员，每条跟进记录对应具体客户和操作人。

常用查询语句示例

系统运行后，常需统计分析。以下是几个实用的SQL查询：

SELECT 
  c.name AS customer_name,
  c.company,
  c.status,
  i.contact_date,
  i.notes
FROM customers c
LEFT JOIN interactions i ON c.id = i.customer_id 
  AND i.id IN (
    SELECT MAX(id) FROM interactions GROUP BY customer_id
  )
WHERE c.owner_id = 1;

SELECT 
  stage,
  COUNT(*) AS deal_count,
  SUM(amount) AS total_amount
FROM opportunities 
WHERE stage != 'won' AND stage != 'lost'
GROUP BY stage;

SELECT 
  c.name,
  c.phone,
  MAX(i.contact_date) AS last_contact
FROM customers c
LEFT JOIN interactions i ON c.id = i.customer_id
GROUP BY c.id
HAVING last_contact < DATE_SUB(CURDATE(), INTERVAL 30 DAY) OR last_contact IS NULL;

开发建议与扩展思路

实际使用中可以逐步增强功能：

customers(owner_id)

interactions(customer_id)

is_deleted

安全方面注意对用户密码使用bcrypt或SHA2加密，避免明文存储。