收费系统数据库设计总结

使用.NET编写的个人版的收费系统接近尾声，从一开始的数据库设计到系统模型图再到系统编码，这其中着实遇到了很多问题，同时也暴露了自身对基础知识掌握的不够牢靠，前篇博客对收费系统的数据库设计进行了部分总结，该篇博客主要对收费系统的数据库设计进行较详细的总结。

一、数据类型

1、存储类型

在对数据库进行设计之初我们要考虑数据库字段值的存储形式，存储数据究竟要使用何种类型，对于数据库设计的新手来说是件让人头疼的问题，存储类型看似简单实则关系重大。字段存储的形式会直接关系到以后对数据库中数据进行复杂业务逻辑计算时的效率，经过多次的数据库设计的经验，总结了如下的设计类型。

存储数据	数据库中类型
日期	Varchar(50)
时间	Varchar(50)
金钱	Decimal
标识列	Int
数量	Decimal

在数据库中存储日期和时间类型时提倡使用可变长度的字符串类型(Varchar)，主要是因为SQL中的日期和时间类型在存储数据时比较冗杂，尤其是日期date类型的会同时存储日期和时间，但是我们在存储时往往只需要存储某一个日期只需要其中的年月日，这时SQL中的数据类型就显得不足，此时我们应该使用Varchar类型。字符串类型存储的好处是它能够存储类型传到数据库中类型的数据而且不会对数据进行更改，字符串类型的另外一个优点是在进行比较查询时它是根据字符拼接查询，一个个的进行比较查询，这种查询在遇到特殊类型的数据时不会报错，而是会把传进来的类型隐式转换为字符串进行比较查询。时间类型的存储也和日期类型相似，没有特殊要求时最好采用字符串类型进行存储。

对于金钱类型的数据提倡使用Decimal类型，它其实等价于程序语言中的Double类型，这种类型的数据计算方法多样，能够进行复杂的逻辑计算，而且它能够指定数据的小数个数，使用简单灵活，在和程序语言交互时出错几率小。

2、类型转换

数据类型转换有两种类型：

隐性转换，对用户不可见，SQLServer自动将数据从一种类型转换成为另一种类型。

显性转换，用户自定义的转换方式，使用Cast或Convert函数进行显性转换，转换小数位数不同的数据类型时，结果值有时被截断，有时被舍入。

使用隐性转换我们要记住一个原则：少数服从多数。在一个不同类型相互夹杂的计算式中，多数的类型会取代少数类型。

/*声明了两个变量参数，把decimal型的数据转换为整型时结果值被截断*/
declare @a decimal(18,2),
	@b int
set @a=18.56
select @b=CAST(@a as int)
select @b
/*结果为18*/
	
/*声明了两个变量参数，把decimal型的数据转换为整型时结果值被四舍五入*/
declare @a decimal(18,2),
	@b int
set @a=18.56
select @b=convert(decimal,@a)
select @b
/*结果为19*/

二、业务逻辑

上篇博客已经简单的对业务逻辑进行总结，在进行业务逻辑的运算时，用的最多的是事务、存储过程和触发器。其中触发器是一种特殊的存储过程，而存储过程是存储数据库操作语句的特殊函数。事务在操作数据库时应用较广泛，可以包括复杂的逻辑结构，当出现错误时可以使用回滚业务，避免对数据库的操作，保证数据的完整性。

1、逻辑语句中的参数

用SQL语句实现业务逻辑运算时提倡使用参数进行操作，对参数的操作主要可分为两部分

① 使用Declare关键字声明参数，并指明类型

② 使用set或Select语句为参数赋值，

声明参数时使用@来指明是用户自定义的参数类型，@@是系统的参数变量。

当使用参数化查询时应把相应的参数值存储到系统内存中，如果在SQL语句中没有指定参数值，那在使用存储过程时我们必须把参数保存到系统内存中。

如下为一存储过程，使用了参数和事务。

-- =============================================
-- Author:	<Author,,>
-- Create date: <2013-05-05,15:00,>
-- Description:	<存储过程，注册学生，增加学生信息>
-- =============================================
use chargesystem
go

CREATE PROCEDURE PROC_AddStudent
	-- Add the parameters for the stored procedure here

	@CardID  varchar(50),		--存储卡号参数
	@Professional varchar(50),	--存储学生专业参数
	@Class   varchar(50),		--存储学生班级参数
	@Department  varchar(50),	--存储学生学院
	@Explain     varchar(100),	--存储学生注释
	@Cash		decimal(18,2),	--存储学生注册金额
	@Isused      varchar(50),	--存储学生使用状态
	@Sex         varchar(50),	--学生性别
	@Studentname  varchar(50),	--学生真实姓名
	@UID          varchar(50),	--学生证号
	@Grade      varchar(50),	--学生年级
	@Admin      varchar(50)		--管理员名称

AS

BEGIN
	declare @ErrNo  int  --错误代号

	begin transaction
    		-- Insert statements for procedure here
    		--插入数据库表
		insert into ChargeSystem.dbo.tblStudent 
			values( @UID ,@CardID ,@Studentname ,@Sex ,@Grade,
				@Department ,@Professional ,@Class,CONVERT(varchar(50),getdate(),23),
				CONVERT(varchar(50),getdate(),8),@Cash ,@Cash,@Admin,'Yes','False',@Explain )
	
		--记录错误号
		select @ErrNo=@@ERROR 
		--如果出错，撤销操作
		if @ErrNo>0 
			begin
				rollback Transaction
				return
			end
			
	Commit transaction
END
GO	

2、数据计算DateDiff函数

DateDiff函数计算两个时间段间的值，它能够指定返回两个时间段间的时间类型，可以是小时、分钟、日等的类型。

如下面的触发器

-- ==============================================================================================
-- Author:	<Author,,>
-- Create date: <2013-05-05,15:00,>
-- Description:	<触发器，删除tblOnlineStu表中数据时触发，想tblStuLineLog中增加学生上机记录>
-- ==============================================================================================
	
/*学生下机时的触发器，实现将学生的上机信息写入学生上机信息表中*/
use ChargeSystem 
Go
	
--判断是否存在该名称的触发器，存在的话删除
if OBJECT_ID('chargesystem.tr_OnlineDelete','TR') is not null 
	drop trigger chargesystem.tr_OnlineDelete
go
	
--创建名为tr_OnlineDelete的触发器
create TRIGGER [dbo].[tr_OnlineDelete]
	ON [dbo].[tblOnlineStu]
	for DELETE
AS 
	declare @chargeinfo     decimal(18,2),     --设置收费信息
		@logintime      time,              --保存登录时间
		@logouttime     time,              --保存退出时间
		@logintimev     varchar(10),       --保存登录时间，作为参数插入到tblStuLineLog中
		@logindate      date,              --保存登陆日期
		@logoutdate     date,              --保存退出日期
		@consumecash    decimal(18,3),     --保存消费金额
		@consumetime    decimal(18),       --保存消费时间
		@CardID		varchar(50),       --学生卡号
		@UID		varchar(50),       --学生证号
		@Studentname	varchar(50),       --学生姓名
		@Admin		varchar(50),       --操作员名称
		@Balance	decimal(18,2),     --学生余额
		@Preparetime    decimal(18)        --上机准备时间
				
	--将要删除的行的值赋给所需要的参数
	select  @CardID=onstu_CardID,
		@UID=onstu_UID,
		@Studentname=onstu_Studentname,
		@logindate=CONVERT(date,onstu_LoginDate),
		@logintime=CONVERT(time,onstu_logintime),
		@Admin=onstu_Admin
	from deleted
		
	--为上机收费赋值，用于计算学生的消费金额
	select top 1 @chargeinfo=se_settleduser,@Preparetime=se_preparetime from tblSetCharge order by se_Serial desc

	--为学生退出时间和日期赋值
	select @logoutdate=CONVERT(date,getdate(),23)
	select @logouttime=CONVERT(time,getdate(),8)

	--计算学生的消费时间
	select @consumetime=DATEDIFF(minute ,@logindate,@logoutdate)+DATEDIFF(minute,@logintime,@logouttime)

	--计算学生的消费金额
	select @consumecash=@consumetime*@chargeinfo/60

	--为学生登录时间赋值
	select @logintimev=convert(varchar(10),@logintime,8)
		
	--判断如果学生的上机时间小于系统开始收费时间，则不会对学生进行收费
	if @consumetime>@Preparetime
		select @Balance=(stu_Balance-@consumecash) from tblStudent as S inner join Deleted as D on D.onstu_CardID=s.stu_CardID
	else
               Begin
                    select @consumetime=0
		            select @consumecash=0 
		            select @Balance=stu_Balance from tblStudent where stu_CardID=@CardID
               End

       BEGIN
		--将学生上机信息写入学生上机信息记录表中
		insert into tblStuLinelog values(@UID,@CardID,@Studentname,
						@LoginDate,@LoginTimev,@Logoutdate,
						convert(varchar(10),getdate(),8),@consumetime,@consumecash,
						@Balance,@Admin)
	END