客户关系管理系统的系统需求分析与数据库设计

矫情吗；* 2021-10-01 01:34 552阅读 0赞

**一 需求分析**

本项目是一个基于MVC实现的客户管理CRM系统，预先设置了三个角色，即系统管理员、信息管理员和客服人员。

本系统包括主要五大模块，即权限管理、业务信息、任务管理、公告管理和客户管理。

功能结构图如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70][]

**二 数据库分析和设计**

1 概念模型

本系统设定了八大重要的实体：权限、权限组、角色、用户、客户、任务、公告、登录。

它的E-R图如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 1][]

2 关系模型的分析与设计

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 2][]

3 数据库脚本

-- 删除数据库
    DROP DATABASE IF EXISTS crmdb ;
    -- 创建数据库
    CREATE DATABASE crmdb CHARACTER SET UTF8 ;
    -- 使用数据库
    USE crmdb ;
    -- 创建数据表
    -- 1、创建权限组表
    CREATE TABLE groups (
       gid                  INT	AUTO_INCREMENT,
       title                VARCHAR(50),
       img                  VARCHAR(50) ,
       type                  VARCHAR(50) ,
       CONSTRAINT pk_gid PRIMARY KEY (gid)
    );
    -- 2、创建权限表
    CREATE TABLE action(
       actid                INT	AUTO_INCREMENT,
       title                VARCHAR(50),
       menu                 INT,
       url                  VARCHAR(200),
       CONSTRAINT pk_actid PRIMARY KEY (actid) 
    );
    
    -- 3、权限组-权限
    CREATE TABLE groups_action (
       gid                  INT ,
       actid                INT ,
       CONSTRAINT fk_gid11 FOREIGN KEY(gid) REFERENCES groups(gid) ON DELETE CASCADE ,                
       CONSTRAINT fk_actid11 FOREIGN KEY(actid) REFERENCES action(actid) ON DELETE CASCADE 
    ) ;
    -- 4、创建角色表
    CREATE TABLE role (
       rid                  INT 	AUTO_INCREMENT ,
       title                VARCHAR(50),
       CONSTRAINT pk_rid PRIMARY KEY (rid) 
    );
    
    -- 5、创建角色-权限组
    CREATE TABLE role_groups (
       rid                  INT,
       gid                  INT ,
       CONSTRAINT fk_rid4 FOREIGN KEY(rid) REFERENCES role(rid) ON DELETE CASCADE ,
       CONSTRAINT fk_gid4 FOREIGN KEY(gid) REFERENCES groups(gid) ON DELETE CASCADE
    );
    
    -- 6、创建用户表
    CREATE TABLE member (
       mid                  VARCHAR(50) NOT NULL,
       rid                  INT,
       password             VARCHAR(32),
       tel                  VARCHAR(50),
       lastdate             DATETIME,
       photo                VARCHAR(200),
       flag                 INT,
       locked               INT,
       CONSTRAINT pk_mid PRIMARY KEY (MID) ,
       CONSTRAINT fk_rid FOREIGN KEY(rid) REFERENCES role(rid) ON DELETE CASCADE 
    );
    -- 7、创建登录日志表
    CREATE TABLE logs (
       logid                INT	AUTO_INCREMENT ,
       mid                  VARCHAR(50),
       indate               DATETIME,
       CONSTRAINT pk_logid PRIMARY KEY (logid)  ,
       CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE CASCADE 
    );
    
    -- 8、公告信息表
    CREATE TABLE news (
       nid                  INT	AUTO_INCREMENT ,
       mid                  VARCHAR(50),
       title                VARCHAR(50),
       type                 INT,
       pubdate              DATETIME,
       note                 TEXT,
       CONSTRAINT pk_nid PRIMARY KEY (nid) ,
       CONSTRAINT fk_mid7 FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE SET NULL 
    );
    
    -- 9、用户公告阅读
    CREATE TABLE member_news (
       mid                  VARCHAR(50),
       nid                  INT,
       rdate                DATETIME ,
       CONSTRAINT fk_mid8 FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE CASCADE ,
       CONSTRAINT fk_nid8 FOREIGN KEY(nid) REFERENCES news(nid) ON DELETE CASCADE
    );
    
    -- 10、客户信息登记表
    CREATE TABLE client (
       cid                  INT 	AUTO_INCREMENT ,
       mid                  VARCHAR(50),
       name                 VARCHAR(50),
       sex                  VARCHAR(10),
       email                VARCHAR(200),
       tel                  VARCHAR(200),
       qq                   VARCHAR(200),
       type                 INT,
       reg                  DATETIME,
       note                 TEXT,
       CONSTRAINT pk_cid PRIMARY KEY (cid) ,
       CONSTRAINT fk_mid9 FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE SET NULL 
    );
    
    -- 11、客户回访记录表
    CREATE TABLE task (
       tid                  INT 	AUTO_INCREMENT ,
       mid                  VARCHAR(50),
       cid                  INT,
       title                VARCHAR(50),
       tdate                DATE,
       visit                INT,
       type                 INT,
       note                 TEXT,
       status               INT,
       level                INT,
       CONSTRAINT pk_tid PRIMARY KEY (tid)  ,
       CONSTRAINT fk_mid10 FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE SET NULL ,
       CONSTRAINT fk_cid10 FOREIGN KEY(cid) REFERENCES client(cid) ON DELETE CASCADE
    );
    
    
    
    
    -- 测试数据 
    INSERT INTO role(title) VALUES ('系统管理员') ;
    INSERT INTO role(title) VALUES ('信息管理员') ;
    INSERT INTO role(title) VALUES ('客服人员') ;
    -- 权限组信息
    INSERT INTO groups(title,img,type) VALUES ('业务信息','leftico01.png' ,'客服人员') ;
    INSERT INTO groups(title,img,type) VALUES ('任务管理','leftico02.png' ,'客服人员') ;
    INSERT INTO groups(title,img,type) VALUES ('公告管理','leftico03.png' ,'客服人员') ;
    
    INSERT INTO groups(title,img,type) VALUES ('公告管理','leftico03.png' ,'信息管理员') ;
    INSERT INTO groups(title,img,type) VALUES ('客户管理','leftico05.png' ,'信息管理员') ;
    
    INSERT INTO groups(title,img,type) VALUES ('公告管理','leftico03.png' ,'系统管理员') ;
    INSERT INTO groups(title,img,type) VALUES ('权限管理','leftico04.png' ,'系统管理员') ;
    INSERT INTO groups(title,img,type) VALUES ('客户管理','leftico05.png' ,'系统管理员') ;
    
    -- 权限信息
    INSERT INTO action(title,menu,url) VALUES ('添加客户',1,'/pages/back/client/ClientServletBack/addPre') ;
    INSERT INTO action(title,menu,url) VALUES ('客户列表',1,'/pages/back/client/ClientServletBack/listSplit') ;
    INSERT INTO action(title,menu,url) VALUES ('客户修改',0,'/pages/back/client/ClientServletBack/editPre') ;
    INSERT INTO action(title,menu,url) VALUES ('客户删除',0,'/pages/back/client/ClientServletBack/rm') ;
    
    INSERT INTO action(title,menu,url) VALUES ('添加任务',1,'/pages/back/task/TaskServletBack/addPre') ;
    INSERT INTO action(title,menu,url) VALUES ('任务列表',1,'/pages/back/task/TaskServletBack/listSplit') ;
    INSERT INTO action(title,menu,url) VALUES ('任务修改',0,'/pages/back/task/TaskServletBack/editPre') ;
    INSERT INTO action(title,menu,url) VALUES ('任务完成',0,'/pages/back/task/TaskServletBack/finish') ;
    INSERT INTO action(title,menu,url) VALUES ('任务关闭',0,'/pages/back/task/TaskServletBack/over') ;
    INSERT INTO action(title,menu,url) VALUES ('任务删除',0,'/pages/back/task/TaskServletBack/rm') ;
    
    
    INSERT INTO action(title,menu,url) VALUES ('公告列表',1,'/pages/back/news/NewsServletBack/list') ;
    INSERT INTO action(title,menu,url) VALUES ('发布公告',1,'/pages/back/news/NewsServletBack/addPre') ;
    INSERT INTO action(title,menu,url) VALUES ('公告修改',0,'/pages/back/news/NewsServletBack/editPre') ;
    INSERT INTO action(title,menu,url) VALUES ('公告删除',0,'/pages/back/news/NewsServletBack/rm') ;
    
    INSERT INTO action(title,menu,url) VALUES ('创建用户',1,'/pages/back/member/MemberServletBack/addPre') ;
    INSERT INTO action(title,menu,url) VALUES ('用户列表',1,'/pages/back/member/MemberServletBack/listSplit') ;
    INSERT INTO action(title,menu,url) VALUES ('用户修改',0,'/pages/back/member/MemberServletBack/editPre') ;
    INSERT INTO action(title,menu,url) VALUES ('用户删除',0,'/pages/back/member/MemberServletBack/rm') ;
    INSERT INTO action(title,menu,url) VALUES ('角色增加',1,'/pages/back/role/RoleServletBack/addPre') ;
    INSERT INTO action(title,menu,url) VALUES ('角色列表',1,'/pages/back/role/RoleServletBack/list') ;
    INSERT INTO action(title,menu,url) VALUES ('角色修改',0,'/pages/back/role/RoleServletBack/editPre') ;
    INSERT INTO action(title,menu,url) VALUES ('角色删除',0,'/pages/back/role/RoleServletBack/rm') ;
    INSERT INTO action(title,menu,url) VALUES ('权限组列表',1,'/pages/back/groups/GroupsServletBack/list') ;
    INSERT INTO action(title,menu,url) VALUES ('权限组详情',0,'/pages/back/groups/GroupsServletBack/show') ;
    INSERT INTO action(title,menu,url) VALUES ('权限列表',1,'/pages/back/action/ActionServletBack/list') ;
    
    INSERT INTO action(title,menu,url) VALUES ('客户列表',1,'/pages/back/mclient/ManagerClientServletBack/listSplit') ;
    INSERT INTO action(title,menu,url) VALUES ('客户详情',0,'/pages/back/mclient/ManagerClientServletBack/show') ;
    INSERT INTO action(title,menu,url) VALUES ('用户任务列表',1,'/pages/back/mtask/ManagerTaskServletBack/listSplit') ;
    INSERT INTO action(title,menu,url) VALUES ('查看客户任务',0,'/pages/back/mtask/ManagerTaskServletBack/listByClient') ;
    
    INSERT INTO action(title,menu,url) VALUES ('客户任务列表',0,'/pages/back/task/TaskServletBack/listByClient') ;
    INSERT INTO action(title,menu,url) VALUES ('任务详情',0,'/pages/back/task/TaskServletBack/show') ;
    INSERT INTO action(title,menu,url) VALUES ('用户任务详情',0,'/pages/back/mtask/ManagerTaskServletBack/show') ;
    INSERT INTO action(title,menu,url) VALUES ('公告详情',0,'/pages/back/news/NewsServletBack/show') ;
    INSERT INTO action(title,menu,url) VALUES ('角色详情',0,'/pages/back/role/RoleServletBack/show') ;
    
    -- 客服人员权限组
    INSERT INTO groups_action(gid,actid) VALUES(1,1) ;
    INSERT INTO groups_action(gid,actid) VALUES(1,2) ;
    INSERT INTO groups_action(gid,actid) VALUES(1,3) ;
    -- INSERT INTO groups_action(gid,actid) VALUES(1,4) ;
    INSERT INTO groups_action(gid,actid) VALUES(2,5) ;
    INSERT INTO groups_action(gid,actid) VALUES(2,6) ;
    INSERT INTO groups_action(gid,actid) VALUES(2,7) ;
    INSERT INTO groups_action(gid,actid) VALUES(2,8) ;
    INSERT INTO groups_action(gid,actid) VALUES(2,9) ;
    INSERT INTO groups_action(gid,actid) VALUES(2,10) ;
    INSERT INTO groups_action(gid,actid) VALUES(2,30) ;
    INSERT INTO groups_action(gid,actid) VALUES(2,31) ;
    INSERT INTO groups_action(gid,actid) VALUES(3,11) ;
    INSERT INTO groups_action(gid,actid) VALUES(3,33) ;
    
    -- 公告管理员
    INSERT INTO groups_action(gid,actid) VALUES(4,11) ;
    INSERT INTO groups_action(gid,actid) VALUES(4,12) ;
    INSERT INTO groups_action(gid,actid) VALUES(4,13) ;
    INSERT INTO groups_action(gid,actid) VALUES(4,14) ;
    INSERT INTO groups_action(gid,actid) VALUES(5,26) ;
    INSERT INTO groups_action(gid,actid) VALUES(5,27) ;
    INSERT INTO groups_action(gid,actid) VALUES(5,28) ;
    INSERT INTO groups_action(gid,actid) VALUES(5,29) ;
    INSERT INTO groups_action(gid,actid) VALUES(5,32) ;
    INSERT INTO groups_action(gid,actid) VALUES(5,33) ;
    
    -- 系统管理员
    INSERT INTO groups_action(gid,actid) VALUES(6,11) ;
    INSERT INTO groups_action(gid,actid) VALUES(6,12) ;
    INSERT INTO groups_action(gid,actid) VALUES(6,13) ;
    INSERT INTO groups_action(gid,actid) VALUES(6,14) ;
    INSERT INTO groups_action(gid,actid) VALUES(7,15) ;
    INSERT INTO groups_action(gid,actid) VALUES(7,16) ;
    INSERT INTO groups_action(gid,actid) VALUES(7,17) ;
    INSERT INTO groups_action(gid,actid) VALUES(7,18) ;
    INSERT INTO groups_action(gid,actid) VALUES(7,19) ;
    INSERT INTO groups_action(gid,actid) VALUES(7,20) ;
    INSERT INTO groups_action(gid,actid) VALUES(7,21) ;
    INSERT INTO groups_action(gid,actid) VALUES(7,22) ;
    INSERT INTO groups_action(gid,actid) VALUES(7,23) ;
    INSERT INTO groups_action(gid,actid) VALUES(7,24) ;
    INSERT INTO groups_action(gid,actid) VALUES(7,25) ;
    INSERT INTO groups_action(gid,actid) VALUES(7,34) ;
    INSERT INTO groups_action(gid,actid) VALUES(8,26) ;
    INSERT INTO groups_action(gid,actid) VALUES(8,27) ;
    INSERT INTO groups_action(gid,actid) VALUES(8,28) ;
    INSERT INTO groups_action(gid,actid) VALUES(8,29) ;
    INSERT INTO groups_action(gid,actid) VALUES(8,32) ;
    INSERT INTO groups_action(gid,actid) VALUES(8,33) ;
    
    -- 管理员角色
    INSERT INTO role_groups(rid,gid) VALUES (1,6) ;
    INSERT INTO role_groups(rid,gid) VALUES (1,7) ;
    INSERT INTO role_groups(rid,gid) VALUES (1,8) ;
    -- 信息管理员角色
    INSERT INTO role_groups(rid,gid) VALUES (2,4) ;
    INSERT INTO role_groups(rid,gid) VALUES (2,5) ;
    -- 客户人员角色
    INSERT INTO role_groups(rid,gid) VALUES (3,1) ;
    INSERT INTO role_groups(rid,gid) VALUES (3,2) ;
    INSERT INTO role_groups(rid,gid) VALUES (3,3) ;
    
    -- 管理员数据
    -- 超级管理员：admin / hello
    INSERT INTO member(mid,rid,password,tel,lastdate,photo,flag,locked) VALUES ('admin',1,'5D41402ABC4B2A76B9719D911017C592','110','2016-10-19','nophoto.jpg',1,0) ;
    -- 信息管理员：mldn / java
    INSERT INTO member(mid,rid,password,tel,lastdate,photo,flag,locked) VALUES ('mldn',2,'93F725A07423FE1C889F448B33D21F46','110','2016-10-19','nophoto.jpg',0,0) ;
    -- 客户人员：mermaid / hello
    INSERT INTO member(mid,rid,password,tel,lastdate,photo,flag,locked) VALUES ('mermaid',3,'5D41402ABC4B2A76B9719D911017C592','110','2016-10-19','nophoto.jpg',0,0) ;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70]: /images/20210724/181ac7b1355e42fc85e023907125c69d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 1]: /images/20210724/c08af3dc02e04653816511db8b31bee2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 2]: /images/20210724/7a0299567a0c4ea2a32855a314087cf3.png