客户关系管理系统的数据库设计.doc

客户关系管理系统的数据库设计

———————————————————————————————— 作者：
———————————————————————————————— 日期：

12

个人收集整理 勿做商业用途
系统名称：客户关系管理系统
一、 系统需求分析：
客户管理系统是为了方便管理客户基本信息，完成公司决策的重要手段，
通过该系统,
系统管理员可以通过次系统查询客户的基本信息，以及客户所购产品的信息。并可以对新老客户进行添加和删除，也可对现有用户的基本信息进行更改。从而实现公司对其客户的管理.
客户可以通过该数据库查看自己的基本信息和消费情况。
职员可以通过该数据库查看自己的基本情况、业绩情况和工作计划。
该数据库包含5个实体，分别是客户，联系人，产品,任务计划，反馈信息。

通过该数据库,可以实现以下功能（举例）

简单查询:

① 查询客户的基本信息
② 查询公司职员的业绩情况（即联系人发展的客户情况）
③ 查询公司职员的任务计划

复杂查询：

① 查询客户对某一类型产品的反馈信息（通过产品表和反馈信息表连接实现)
② 查询客户反馈信息的受理情况

插入功能：

增添新客户,新产品，新联系人，新反馈信息以及其相关属性

修改功能：

系统管理员修改客户的基本信息（如积分，联系方式等），修改联系人的工作计划，反馈信息的受理情况

二、E-R图
客户
联系人
反馈信息
产品
任务计划
联系人编号
所购产品
编号
购买
反馈
发展
制定
信用度
姓名
所属部门
姓名
工资
客户编号
联系方式
联系地址
积分
产品类型
工作计划编号
产品类型
质量级别
优惠情况
名称
生产日期
编号
产品价格
内容
反馈时间
反馈者
反馈编号
解决情况
产品编号
编号
客户数量
计划利润
计划时间
实施情况
职工编号
消费时间
任务计划编号
联系人编号
制定时间

n

n

1 n 1 n

1

1

客户编号
反馈
反馈编号

购买
购买数量
购买时间
客户编号
产品编号

发展
联系人编号
客户编号

三、关系模式
系统中的实体有：客户，联系人，反馈信息，任务计划，产品
关系模式如下：
客户（客户编号,姓名，联系方式，联系地址，积分，信用度，所购产品，消费时间，联系人编号）
联系人（联系人编号，姓名，所属部门，产品类型，任务计划编号,工资）
反馈信息（反馈编号,反馈人姓名，反馈时间，反馈内容，产品编号，解决情况）
任务计划（任务计划编号，联系人编号，客户数量,计划利润，计划时间，实施情况）
产品（产品编号，产品名称，生产日期,产品类型，质量级别，价格，优惠情况）
反馈（客户编号，反馈编号）
发展(联系人编号，客户编号）
购买（客户编号，产品编号，购买时间，购买数量）
制定（联系人编号，任务计划编号，制定时间)
四，物理设计
客户表结构
客户编号
Char(10)
主键
姓名
Char（8）
Not null
联系方式
Char(12)

积分
Char（8)
Check约束，在0到100内取整
信用度
Char（8）
Check约束，在0到100内取整
所购产品
Char (20)
Not null
消费时间
datetime
Not null
联系地址
Char（10）

联系人编号
Char（10)
外键

联系人表结构
联系人编号
Char(10）
主键
姓名
Char(8)
Not null
所属部门
Char(10)
Not null
产品类型
Char(20)
Check’电器'，’化妆品’,'炊具’
任务计划编号
Char(10)
外键
工资
Char（10）
Not null

反馈信息表结构
反馈编号
Int
主键，identity（1,1）
反馈人姓名
Char(8)

反馈时间
Datetime
Not null
反馈内容
Char（200）
Not null
产品编号
Char(10）
Not null
解决情况
Char(8）
Default 缺省值为“否”

任务计划表结构
任务计划编号
Char（10)
主键
联系人编号
Char（10）
Not null
客户数量
Char（20)
Not null
计划利润
Char（20）
Not null
计划时间
Datetime
Not null
实施情况
Char(2）
Default 缺省值为“否”

产品表结构
产品编号
Char（10）
主键
产品名称
Char(20)
Not null
产品价格
Char（10）
Not null
产品类型
Char(10)
Chec约束，取'电器’，'化妆品','炊具之一'
生产日期
Datetime
Not null
质量级别
Char（8)
Check约束，取A，B ,C,D,E之一
优惠情况
Char(10)

反馈联系表结构
反馈编号
Char(10)
Not null
客户编号
Char(10)
Not null

制定联系表结构
工作计划编号
Char(10）
Not null
联系人编号
Char(10）
Not null
制定时间
datetime
Not null

购买关系表结构
客户编号
Char（10）
Not null
产品编号
Char（10）
Not null
购买时间
datetime
Not null
购买数量
Char（20)
Not null

发展关系表结构
联系人编号
Char（10)
Not null
客户编号
Char（10)
Not null
五，系统实现
1. 数据库及其基本表的建立
数据库的建立：
create database CustomerMS
on
（
name='客户关系管理系统',
filename=’d：\DAI\客户关系管理系统.mdf’，
size=10mb,
maxsize=20mb,
filegrowth=1mb
）
log on
(
name=’客户关系管理系统'，
filename='d：\DAI\客户关系管理系统。ldf’,
size=10mb,
maxsize=20mb，
filegrowth=1mb
)
建立基本表：

use CustomerMS

create table 任务计划
（
任务计划编号 char（10） primary key,
联系人编号 char（10） not null，
客户数量 char(20) not null，
计划利润 char(20） not null，
计划时间 datetime not null,
实施情况 char（2) default（’否')
）

create table 联系人
(
联系人编号 char(10) primary key,
姓名 char（8) not null，
所属部门 char（10） not null，
产品类型 char（20） check（产品类型 in(’电器’，'化妆品','炊具')),
任务计划编号 char(10) references 任务计划(任务计划编号),
工资 char(10) not null
)
create table 客户
（
客户编号 char（10) PRIMARY KEY，
姓名 char(8) not null，
联系方式 char（12），
积分 char(8） check(积分 between ’0 'and '100')，
信用度 char（8) check(信用度 between 0 and 100)，
所购产品 char（20） not null,
消费时间 datetime not null，
联系地址 char(10),
联系人编号 char（10） references 联系人(联系人编号)
）

create table 反馈信息
（
反馈编号 int primary key identity(1,1），
反馈人姓名 char(8)，
反馈时间 datetime not null,
反馈内容 char(200) not null，
产品编号 char（8） not null,
解决情况 char(8) default（’否’)
)

create table 产品
（
产品编号 char（10） primary key，
产品名称 char（20） not null,
产品价格 char（10） not null,
产品类型 char(10) check（产品类型 in（'电器’，’化妆品’,’炊具'）），
生产日期 datetime not null,
质量级别 char(8) check（质量级别 in（'A’,’B’,'C’,'D','E')),
优惠情况 char（10）
）

create table 反馈
(
反馈编号 int identity(1，1)，
客户编号 char（10） not null
）

create table 制定
(
工作计划编号 char(10) not null,
联系人编号 char(10） not null，
制定时间 datetime not null
）

create table 购买
（
客户编号 char（10) not null,
产品编号 char（10) not null，
购买时间 datetime not null,
购买数量 char（20) not null
）

create table 发展
(
联系人编号 char（10) not null，
客户编号 char(10） not null
）
2、创建视图
1.客户视图
（客户编号，姓名，联系方式,联系地址，积分，信用度，所购产品,消费时间，联系人姓名，产品名称,生产日期,产品类型,质量级别，价格， 解决情况反馈内容）

代码如下：

Create view 客户视图
As
Select 客户。客户编号,客户。姓名，客户。联系方式,客户.积分，客户.信用度，客户.所购产品,客户。消费时间,联系人。姓名 as ”联系人姓名"，产品.产品名称,产品。生产日期,产品.产品类型,产品.质量级别,产品。产品价格，反馈信息。解决情况， 反馈信息。反馈内容
From 客户 join 产品 On 客户.所购产品=产品.产品名称
Join 联系人 On 客户.联系人编号=联系人。联系人编号
Join 反馈信息 on 客户。姓名=反馈信息。反馈人姓名

2. 职工视图
(联系人编号，姓名，所属部门,产品类型,任务计划编号，工资，姓名,联系方式，联系地址，任务计划编号，计划利润，计划时间,实施情况）

代码如下：

Create view 职工视图
As
Select 联系人。联系人编号，联系人。姓名,联系人。所属部门，联系人.产品类型,联系人.工资，客户.姓名 as ”客户姓名”，客户。联系方式,客户.联系地址，任务计划.任务计划编号，任务计划。计划利润，任务计划.计划时间，任务计划。实施情况
From 联系人 join 客户 on 联系人。联系人编号=客户。联系人编号
Join 任务计划 on 联系人. 任务计划编号=任务计划。任务计划编号

3、创建索引
客户（在客户表中客户编号列建立索引）

Create index 客户 on 客户（客户编号)

产品（在产品表上产品编号列建立索引）

Create index 产品 on 产品（产品编号)

4、存储过程的创建
①简单查询：

1 查询客户的基本信息

Create procedure Scustomer @name char(8)
As
Select *from 客户
Where 客户.姓名=＠name

2查询公司职员的业绩情况（即联系人发展的客户情况）

Create procedure workerperformance ＠workercode char(10)
As
Select ”total"=count(客户.客户编号 ）

From 客户 join 联系人 on 客户。联系人编号=联系人.联系人编号
Where 联系人.联系人编号=@workercode

3查询公司职员的任务计划

Create procedure 查询任务计划 ＠work char(8）
As
Select ＊from 任务计划
Where 任务计划.联系人编号=@work

②复杂查询:

1查询客户对某一类型产品的反馈信息（通过产品表和反馈信息表连接实现）

Create procedure 查询反馈信息 @productcode char（8)
As
Select 产品。产品类型，反馈信息。反馈内容
From 产品 Join 反馈信息 on 产品。产品编号=反馈信息。产品编号
Where 产品。产品类型=@productcode

2查询客户反馈信息的受理情况

Create procedure 反馈信息受理 @customercode char（8) ,@customername char(20）
As
Select 客户.姓名，客户.客户编号，反馈信息.解决情况
From 客户 Join 反馈信息 on 客户.姓名=反馈信息。反馈人姓名
Where 客户.客户编号=＠customercode and 客户.姓名=＠customername

③插入功能:

增添新客户，新产品，新联系人,新反馈信息以及其相关属性

增添新用户:

Create procedure insertcustomer ＠客户编号 char(10），＠姓名 char（20）,@联系方式 char（20)，@联系地址 char(20）,@积分 char（10），＠信用度 char(10)，@所购产品 char(20)，＠消费时间 datetime，@联系人编号 char(10)
As
Insert into 客户 values(@客户编号，＠姓名,＠联系方式,＠联系地址，@积分,＠信用度,@所购产品，＠消费时间，＠联系人编号）

③ 修改功能:

系统管理员修改客户的基本信息（如积分，联系方式等)，修改联系人的工作计划，反馈信息的受理情况

修改客户的基本信息（例：给特定客户增加积分）
Create procedure addmarks @mark char（10),＠customercode char(10）
As
Update 客户
set 客户。积分=客户.积分+＠mark
Where（客户。客户编号=＠customercode)

5。创建触发器
客户的信用度取在0到100之间，当对信用度进行更新操作时，执行触发器
Create trigger credit
On 客户
For update
As
Declare @total int
Select ＠total=信用度
From 客户
If(@total〉100)
Begin
Print '信用度不能超过100’
Rollback transaction
End
Return

完成日期:2011年12月15日