CRM客户管理系统搭建中的数据库设计与性能优化

-- 客户表创建示例
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY IDENTITY(1,1),
    CompanyName NVARCHAR(100) NOT NULL,
    ContactName NVARCHAR(50),
    Phone NVARCHAR(20),
    Email NVARCHAR(100),
    CreateDate DATETIME DEFAULT GETDATE(),
    INDEX IX_Customers_CompanyName (CompanyName)
);

-- 交互记录表
CREATE TABLE Interactions (
    InteractionID INT PRIMARY KEY IDENTITY(1,1),
    CustomerID INT NOT NULL FOREIGN KEY REFERENCES Customers(CustomerID),
    InteractionType TINYINT NOT NULL, -- 1:电话 2:邮件 3:会议
    InteractionDate DATETIME NOT NULL,
    Notes NVARCHAR(MAX),
    INDEX IX_Interactions_CustomerID (CustomerID),
    INDEX IX_Interactions_Date (InteractionDate)
);

-- 优化后的查询示例
CREATE PROCEDURE GetCustomerInteractions
    @CustomerID INT,
    @StartDate DATETIME,
    @EndDate DATETIME
AS
BEGIN
    SELECT 
        i.InteractionDate,
        CASE i.InteractionType 
            WHEN 1 THEN '电话'
            WHEN 2 THEN '邮件'
            WHEN 3 THEN '会议'
        END AS InteractionType,
        i.Notes
    FROM Interactions i
    WHERE i.CustomerID = @CustomerID
    AND i.InteractionDate BETWEEN @StartDate AND @EndDate
    ORDER BY i.InteractionDate DESC;
END