机房收费系统合作——再看数据库设计

红太狼 2022-08-05 00:56 163阅读 0赞

机房合作我负责了最简单的D层，接口层，工厂层。反正D层是我来写，于是数据库索性也就顺便设计了。已经是第三次敲机房收费系统了，每次都是相隔半年左右吧。需求搞得透透的了，数据库也就好设计了。基本跟第二次没什么大的区别，就是把Student表和Card表分开了。

重构的时候，我的数据库几乎什么都用到了：事务，存储过程，触发器，视图，联合查询等等。所以，这次设计数据库还是SO Easy的。。并且，为了让婵婵和牛迁迁师哥写的方便，我把组合查询都写成了存储过程！！！！费了一番功夫，但是D层简单了不少。还记得，上次重构的时候，用触发器用多了。。出现了一个神奇的事情：当我注册一张卡的时候，充值10块钱，会在Card表加1倍的价钱。。这是赔本买卖啊。。后来才发现，触发器用过头了。。就改成了事务+存储过程。这次再看数据库设计，就轻车熟路了~

下面举几个例子~跟大家讨论一下~

# 一、 巧用存储过程解决组合查询： #

在组合查询的时候需要用到12个参数：一个表名称，三个字段，三个操作符，三个条件，两个与或关系。这是他们的共性。个性的地方就是，分为三个不同的表，字段也不同，条件也不同。这样，我就把共同的SQL查询语句封装起来，写成一个存储过程，当执行组合查询窗口的时候，只有从UI层传入不同的参数即可，当然泛型集合需要返回不同的类型。存储过程如下：

USE [HezuoCharge]
    GO
    /****** Object:  StoredProcedure [dbo].[Proc_GroupQuery]    Script Date: 05/27/2015 21:50:17 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		周洲
    -- Create date:   2015-5-5
    -- Description:	 组合查询
    -- =============================================
    ALTER PROCEDURE [dbo].[Proc_GroupQuery]
    
    	-- Add the parameters for the stored procedure here
    	@tablename varchar(40),
    	--第一组参数
    	@ziduan1 varchar(40)=null,
    	@fuhao1 varchar(10)=null,
    	@tiaojian1 char(50)=null,
    	@zuhe1 char(10)=null,
    	
    	--第二行参数
    	@ziduan2 varchar(40)=null,
    	@fuhao2 varchar(10)=null,
    	@tiaojian2 char(50)=null,
    	@zuhe2 char(10)=null,
    	
    	
    	--第三行参数
    	@ziduan3 varchar(40)=null,
    	@fuhao3 varchar(10)=null,
    	@tiaojian3 char(50)=null
    	
    	
    
    AS
    
       declare @Sql varchar(400)
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    
    	SET @Sql ='SELECT * From '+@tablename +' where '+@ziduan1+@fuhao1+CHAR(39)+@tiaojian1 +CHAR(39)
    	if @zuhe1 !=''
    	SET @Sql=@Sql+@zuhe1 +CHAR(32)+@ziduan2+@fuhao2+CHAR(39)+@tiaojian2+CHAR(39)
    	else 
    	execute(@Sql)
    	
    	
    	if @zuhe2 !=''
    	SET @Sql=@Sql+@zuhe2 +CHAR(32)+@ziduan3+@fuhao3+CHAR(39)+@tiaojian3+CHAR(39)
    	else
    	execute(@Sql)
    	return 1
    END

D层调用（以返回Line类型为例）：

#Region "操作员--查看上机记录--组合查询"
    
    
        ''' <summary>
        ''' 操作员--查看上机记录--组合查询
        ''' </summary>
        ''' <param name="enGroup"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function Groupline(enGroup As GroupEntity) As List(Of LineEntity) Implements ILine.Groupline
            Dim sql As String = "Proc_GroupQuery"
    
            Dim para As SqlParameter() =
            {
                New SqlParameter("@tablename", enGroup.tablename),
                New SqlParameter("@fuhao1", enGroup.fuhao1),
                New SqlParameter("@ziduan1", enGroup.ziduan1),
                New SqlParameter("@tiaojian1", enGroup.tiaojian1),
                New SqlParameter("@tiaojian2", enGroup.tiaojian2),
                New SqlParameter("@fuhao2", enGroup.fuhao2),
                New SqlParameter("@fuhao3", enGroup.fuhao3),
                New SqlParameter("@zuhe1", enGroup.zuhe1),
                New SqlParameter("@zuhe2", enGroup.zuhe2),
                New SqlParameter("@ziduan2", enGroup.ziduan2),
                New SqlParameter("@ziduan3", enGroup.ziduan3),
                New SqlParameter("@tiaojian3", enGroup.tiaojian3)}
    
            Dim helper As New SqlHelper
            Dim dt As DataTable  '定义dt
            Dim mylist As New List(Of LineEntity) '定义泛型
    
            dt = helper.ExecSelect(sql, CommandType.StoredProcedure, para)
    
            If (dt.Rows.Count > 0) Then
                mylist = CType(EntityHelper.ConvertToList(dt, mylist), Global.System.Collections.Generic.List(Of Global.Entity.LineEntity))
                Return mylist
            Else
                Throw New Exception("当前条件下没有记录！")
                Return Nothing
            End If
    
        End Function
    #End Region

# 二、触发器帮你结账： #

结账的时候需要在三张表上边更新IScheck字段的数据，既然设计到三张表，就写了触发器。Insert数据的时候，就触发它的执行。建议大家：触发器要慎用，因为太灵活了，一旦满足Insert，update，delete这个触发条件，它就会执行。相比执行倾向于存储过程的使用，但是灵活也是它的优点，各有千秋吧。

USE [HezuoCharge]
    GO
    /****** Object:  Trigger [dbo].[Check]    Script Date: 05/27/2015 21:57:11 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		周洲
    -- Create date: 2015-5-5
    -- Description:	结账的时候，都要写上已结账
    -- =============================================
    ALTER TRIGGER [dbo].[Check]  
      on [dbo].[T_Check]  
      for Insert  
     AS  
      BEGIN  
        update T_Card set IsCheck ='已结账' where Carddate =CONVERT (varchar(10),getdate(),120)
    	update T_Recharge set IsCheck ='已结账' where Rechargedate =CONVERT (varchar(10),getdate(),120)
    	update T_Cancelcard set IsCheck ='已结账' where Canceldate =CONVERT (varchar(10),getdate(),120)
    	
      END

# 三、巧用函数，帮你算账 #

SQLSERVER提供好多函数，可以帮我们轻轻松松解决排序，最大值，最小值，平均值，求和等等一些事情。下面是我写的算总金额的语句。

select table1.remaincash + table2.alladdmoney -table3.allcancelcash from 
    (select top 1 convert(int,Allcash)remaincash from T_Check Order By CheckID desc)table1, 
    (select sum(convert(int,Addmoney  )) alladdmoney from V_TodayRecharge )table2,
    (select sum(convert(int,Cancelcash  )) allcancelcash from V_TodayCancel)table3

# 四、当日结账，用视图列出数据 #

以充值表数据列出为例：

SELECT     CardID, Addmoney, Rechargedate, Rechargetime, UserID
    FROM         dbo.T_Recharge
    WHERE     (Rechargedate = CONVERT(varchar(10), GETDATE(), 120))

# 五、小结： #

本想用重构时候的数据库，后来看到一篇新浪微博，是.NET程序员面试的一组SQL试题，简直看傻我眼！！！才发现，我们做的是多么小儿科的东西。。才费尽心思的在数据库上面创新的。总之，不要因为事情简单就不去做！就像导数据一样，以为是体力活，但是真的可以学到很多东西的，要看你站在什么样的高度上去审视这件事情！不仅仅是Excel表的技术体现，当初我的理解是错的。

就想起李社河师哥在三合班说过的一句话：别人不愿意做的事情，我去做，会发现，简单的小事情也会学到很多的东西！！（激励我，恩！！）

说多了。。END...