商城积分数据库设计

需求：①积分累计：获取积分的过期时间为半年，消耗积分遵循先进先出的原则（当然要在总积分够消耗的情况下）

            ②积分月结：每月一号汇总上月获取和消耗积分并且扣除过期积分。

思路：主要考虑到积分结算的压力问题，若活动用户产生的数据明细成千上百万结算时就会很麻烦，考虑把一些压力转移到明细操作中：

             ①获取积分时同时记录在当月的月记录（稍后把数据库脚本放上）里，每月一条记录。当然会在总积分表中增加

             ②消耗积分时会同时在某个字段中记录当月总消耗的数量，这样就可以减少月结时的操作步骤，

             ③月结时要根据先进先出的原则，先从月表中扣除上月消耗的积分，然后从积分总表中扣除过期积分。

数据库简介：

辅助配置表

明细表：AppID,ModleID为辅助配置表的内容，AccountID为用户ID

月表：GetNum当月获取积分，LeaveNum当月剩余积分若过期则设置为0。

总积分表：CostNum记录消耗的积分总和，每一次结算后则重置为0；

 

其它：这个存储过程实现了批量更新数据，因为一个用户有多个月的积分记录，所以要使用一对多的批量更新模式，

ALTER PROC [dbo].[gs_MonthBalance2] AS BEGINDeclare @ExpireDate varchar(10)SET @ExpireDate=convert(char(7),DATEADD(MONTH,DATEDIFF(MONTH,0,getdate())-1,0),120)--过期积分月份(当前月的上月);WITH t AS(SELECT rn=ROW_NUMBER()over(Order by BalanceDate),* FROM CodeMonth),t1 AS(SELECT *,tmpsum=(SELECT SUM(LeaveNum) FROM t WHERE AccountID=a.AccountID and rn<=a.rn) FROM t a)---批量更新数据UPDATE a SETa.LeaveNum=a.LeaveNum-case  WHEN  a.LeaveNum-(a.tmpsum-b.CostNum) <=0 THEN 0  WHEN a.tmpsum-b.CostNum <=0  THEN a.LeaveNum  ELSE a.LeaveNum-(a.tmpsum-b.CostNum)endFROM t1 a JOIN CodeTotle b on a.AccountID=b.AccountIDWHERE b.CostNum>0--排除未消耗数量---清除消耗记录UPDATE dbo.CodeTotle SET CostNum=0 WHERE CostNum>0---从总数中删除过期数据UPDATE ct SET ct.CodeNum=ct.CodeNum-cm.LeaveNumFROM dbo.CodeTotle ct,(SELECT * From CodeMonth  WHERE  convert(char(7),[ExpireDate],120)=@ExpireDate) AS cm--(SELECT * From CodeMonth) AS cmWHERE ct.AccountID=cm.AccountID  ---从月表清除过期数据UPDATE CodeMonth SET LeaveNum=0  WHERE  convert(char(7),[ExpireDate],120)=@ExpireDate--UPDATE CodeMonth SET LeaveNum=0END  

整个数据库脚本：

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[CodeDetail]([SeqID] [int] IDENTITY(1,1) NOT NULL,[AccountID] [int] NOT NULL,[AppID] [int] NOT NULL,[ModleID] [int] NOT NULL,[CodeNum] [int] NOT NULL,[Note] [varchar](200) NULL,[Des] [varchar](200) NULL,[CreateDate] [datetime] NOT NULL,[ExpireDate] [datetime] NOT NULL,[Creater] [varchar](50) NOT NULL,[CreateIP] [varchar](15) NOT NULL, CONSTRAINT [PK_CODEDETAIL] PRIMARY KEY CLUSTERED ([SeqID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGO/****** 对象:  Table [dbo].[CodeConfig]    脚本日期: 10/09/2011 14:15:50 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[CodeConfig]([SeqID] [int] IDENTITY(1,1) NOT NULL,[APPID] [int] NOT NULL,[AppName] [varchar](50) NOT NULL,[ModleID] [int] NOT NULL,[ModleName] [varchar](50) NOT NULL,[Note] [varchar](200) NULL CONSTRAINT [DF_CodeConfig_Note]  DEFAULT (''),[Status] [tinyint] NOT NULL CONSTRAINT [DF_CodeConfig_Status]  DEFAULT ((1)), CONSTRAINT [PK_CODECONFIG] PRIMARY KEY CLUSTERED ([SeqID] ASC,[APPID] ASC,[ModleID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGO/****** 对象:  Table [dbo].[CodeAccount]    脚本日期: 10/09/2011 14:15:47 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[CodeAccount]([UserID] [int] IDENTITY(1,1) NOT NULL,[UserName] [varchar](50) NOT NULL,[Password] [varchar](50) NOT NULL,[Status] [int] NOT NULL CONSTRAINT [DF_gs_Accounts_Status]  DEFAULT ((0)),[AllowIP] [varchar](100) NOT NULL CONSTRAINT [DF_gs_Accounts_AllowIP]  DEFAULT ('*'),[Created] [datetime] NOT NULL CONSTRAINT [DF__gs_Accounts_Created]  DEFAULT (getdate()),[LastLogin] [datetime] NOT NULL CONSTRAINT [DF_gs_Accounts_LastLogin]  DEFAULT (getdate()),[LastLoginIP] [varchar](50) NOT NULL CONSTRAINT [DF_gs_Accounts_LastLoginIP]  DEFAULT ('127.0.0.1'), CONSTRAINT [PK_GS_ACCOUNTS] PRIMARY KEY CLUSTERED ([UserName] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGO/****** 对象:  Table [dbo].[CodeMonth]    脚本日期: 10/09/2011 14:15:54 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[CodeMonth]([SeqID] [int] IDENTITY(1,1) NOT NULL,[AccountID] [int] NOT NULL,[GetNum] [int] NOT NULL,[CostNum] [int] NOT NULL CONSTRAINT [DF_CodeMonth_CostNum]  DEFAULT ((0)),[LeaveNum] [int] NOT NULL,[BalanceDate] [datetime] NOT NULL,[ExpireDate] [datetime] NOT NULL,[Note] [varchar](200) NULL,[Exp1] [varchar](100) NULL, CONSTRAINT [PK_CODEMONTH] PRIMARY KEY CLUSTERED ([SeqID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGO/****** 对象:  Table [dbo].[CodeTotle]    脚本日期: 10/09/2011 14:15:57 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[CodeTotle]([SeqID] [int] IDENTITY(1,1) NOT NULL,[AccountID] [int] NOT NULL,[CodeNum] [int] NOT NULL,[CostNum] [int] NOT NULL CONSTRAINT [DF_CodeTotle_CostNum]  DEFAULT ((0)),[Status] [tinyint] NOT NULL CONSTRAINT [DF_CodeTotle_Status]  DEFAULT ((1)),[Note] [varchar](200) NULL CONSTRAINT [DF_CodeTotle_Note]  DEFAULT (''),[UpdateDate] [datetime] NOT NULL,[Exp1] [varchar](100) NULL CONSTRAINT [DF_CodeTotle_Exp1]  DEFAULT (''), CONSTRAINT [PK_CODETOTLE] PRIMARY KEY CLUSTERED ([SeqID] ASC,[AccountID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGO/****** 对象:  StoredProcedure [dbo].[gs_CodeDetail_Add]    脚本日期: 10/09/2011 14:15:45 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER OFFGOCREATE PROCEDURE [dbo].[gs_CodeDetail_Add]@AccountID int,@AppID int,@ModleID int,@CodeNum int,@Note varchar(200),@Des varchar(200),@CreateDate datetime, @Creater varchar(50),@CreateIP varchar(15)ASDeclare @ExpireDate DateTimeSet @ExpireDate=DATEADD(MONTH,7+DATEDIFF(MONTH,0,@CreateDate),0)-1--明细添加INSERT INTO CodeDetail ([AccountID],[AppID],[ModleID],[CodeNum],[Note],[Des],[CreateDate],[ExpireDate],[Creater],[CreateIP]) VALUES (@AccountID,@AppID,@ModleID,@CodeNum,@Note,@Des,@CreateDate,@ExpireDate,@Creater,@CreateIP)--总积分IF exists(SELECT * FROM CodeTotle WHERE AccountID= @AccountID) BEGINUPDATE CodeTotle SET[CodeNum] = [CodeNum]+@CodeNum, [UpdateDate] = @CreateDate WHERE [AccountID] = @AccountIDENDELSEBEGININSERT INTO CodeTotle ([AccountID],[CodeNum], [UpdateDate] ) VALUES (@AccountID,@CodeNum, @CreateDate )END--月结表--已存在当月数据（Update）IF exists(select * from dbo.CodeMonth WHERE   convert(char(7),BalanceDate,120)=convert(char(7),@CreateDate,120) AND AccountID=@AccountID)--已经存在当月数据BEGINIF(@CodeNum>0)BEGINUPDATE dbo.CodeMonth SET GetNum=GetNum+@CodeNum,LeaveNum=LeaveNum+@CodeNumWHERE   convert(char(7),BalanceDate,120)=convert(char(7),@CreateDate,120) AND AccountID=@AccountIDENDELSEBEGINUPDATE dbo.CodeMonth SET CostNum=CostNum-@CodeNum WHERE   AccountID=@AccountID AND  convert(char(7),BalanceDate,120)=convert(char(7),@CreateDate,120)UPDATE dbo.CodeTotle SET CostNum=CostNum-@CodeNum WHERE   AccountID=@AccountIDENDEND--不存在当月数据（Insert)ELSEBEGINIF(@CodeNum>0)BEGININSERT INTO [dbo].[CodeMonth]([AccountID],[GetNum],[CostNum],[LeaveNum],[BalanceDate],[ExpireDate])VALUES(@AccountID,@CodeNum,0,@CodeNum,@CreateDate,@ExpireDate)ENDELSEBEGININSERT INTO [dbo].[CodeMonth]([AccountID],[GetNum],[CostNum],[LeaveNum],[BalanceDate],[ExpireDate])VALUES(@AccountID,0,@CodeNum,0,@CreateDate,@ExpireDate)UPDATE dbo.CodeTotle SET CostNum=CostNum-@CodeNum  WHERE   AccountID=@AccountIDENDENDselect SCOPE_IDENTITY()GO/****** 对象:  StoredProcedure [dbo].[gs_Month_Count]    脚本日期: 10/09/2011 14:15:45 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROC [dbo].[gs_Month_Count]ASBEGIN--先把上月获得数量放入数据库INSERT INTO CodeMonth(AccountID,GetNum,LeaveNum,BalanceDate,[ExpireDate])SELECT AccountID, SUM(CodeNum) as GetNum,SUM(CodeNum) as LeaveNum,getdate() as BalanceDate, CONVERT(char(10),[ExpireDate],120) as [ExpireDate]FROM CodeDetail WHERE CodeNum>0  AND  DATEDIFF(month,CreateDate,getdate())=1--当前结算月的上月GROUP BY AccountID, CONVERT(char(10),[ExpireDate],120)--convert(char(7),CreateDate,120)=convert(char(7),DATEADD(MONTH,DATEDIFF(MONTH,0,getdate())-1,0),120)--计算上月消耗数量和未消耗的人INSERT INTO dbo.CodeMonth_Temp(AccountID,ExpendCode)SELECT AccountID, -sum(CodeNum) as ExpendCodeFROM CodeDetail WHERE CodeNum<0 AND datediff(month,CreateDate,getdate())=1 GROUP BY AccountIDUnion SELECT distinct AccountID ,0 as ExpendCodeFROM CodeDetail where  AccountID not in(SELECT  distinct AccountIDFROM CodeDetail WHERE CodeNum<0 AND datediff(month,CreateDate,getdate())=1 ) ENDGO/****** 对象:  StoredProcedure [dbo].[gs_CodeConfig_Edit]    脚本日期: 10/09/2011 14:15:44 ******/SET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[gs_CodeConfig_Edit]@SeqID int,@APPID int,@AppName varchar(50),@ModleID int,@ModleName varchar(50),@Note varchar(200),@Status tinyintAS-- THIS STORED PROCEDURE NEEDS TO BE MANUALLY COMPLETED-- MULITPLE PRIMARY KEY MEMBERS OR NON-GUID/INT PRIMARY KEYDECLARE @Return intSET @Return =1 IF(@SeqID=0)  BEGIN IF exists(SELECT * FROM CodeConfig WHERE APPID= @APPID AND ModleID=@ModleID) BEGINSet @Return=-1ENDELSEBEGININSERT INTO CodeConfig ( [APPID],[AppName],[ModleID],[ModleName],[Note],[Status]) VALUES ( @APPID,@AppName,@ModleID,@ModleName,@Note,@Status)ENDENDELSE BEGINUPDATE CodeConfig SET[AppName] = @AppName,[ModleName] = @ModleName,[Note] = @Note,[Status] = @StatusWHERE[SeqID] = @SeqIDAND [APPID] = @APPIDAND [ModleID] = @ModleIDENDSelect @Return as [Return]GO/****** 对象:  StoredProcedure [dbo].[gs_MonthBalance2]    脚本日期: 10/09/2011 14:15:45 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROC [dbo].[gs_MonthBalance2] AS BEGINDeclare @ExpireDate varchar(10)SET @ExpireDate=convert(char(7),DATEADD(MONTH,DATEDIFF(MONTH,0,getdate())-1,0),120)--过期积分月份(当当前月的上月);WITH t AS(SELECT rn=ROW_NUMBER()over(Order by BalanceDate),* FROM CodeMonth),t1 AS(SELECT *,tmpsum=(SELECT SUM(LeaveNum) FROM t WHERE AccountID=a.AccountID and rn<=a.rn) FROM t a)---批量更新数据UPDATE a SETa.LeaveNum=a.LeaveNum-case  WHEN  a.LeaveNum-(a.tmpsum-b.CostNum) <=0 THEN 0  WHEN a.tmpsum-b.CostNum <=0  THEN a.LeaveNum  ELSE a.LeaveNum-(a.tmpsum-b.CostNum)endFROM t1 a JOIN CodeTotle b on a.AccountID=b.AccountIDWHERE b.CostNum>0--排除未消耗数量---清除消耗记录UPDATE dbo.CodeTotle SET CostNum=0 WHERE CostNum>0---从总数中删除过期数据UPDATE ct SET ct.CodeNum=ct.CodeNum-cm.LeaveNumFROM dbo.CodeTotle ct,(SELECT * From CodeMonth  WHERE  convert(char(7),[ExpireDate],120)=@ExpireDate) AS cm--(SELECT * From CodeMonth) AS cmWHERE ct.AccountID=cm.AccountID  ---从月表清除过期数据UPDATE CodeMonth SET LeaveNum=0  WHERE  convert(char(7),[ExpireDate],120)=@ExpireDate--UPDATE CodeMonth SET LeaveNum=0ENDGO

模拟测试数据库脚本：

DECLARE @MyCounter INTdeclare @the_date datetimedeclare @AID INTSET @AID=1000144SET @MyCounter = 0SET @the_date ='2011-08-01'WHILE (@MyCounter < 2000)BEGINWAITFOR DELAY '000:00:00'   EXECUTE  [CodeDBV1_2].[dbo].[gs_CodeDetail_Add]    @AccountID=@AID  ,@AppID=1  ,@ModleID=11  ,@CodeNum=-150  ,@Note='Test'  ,@Des='Test'  ,@CreateDate=@the_date  ,@Creater='System'  ,@CreateIP='127.0.0.1'SET @AID=@AID+1--SET @the_date =dateadd(d,1,@the_date)SET @MyCounter = @MyCounter + 1END

目前做到这种程度，还没具体使用不知道还有什么漏洞！