Data center charging system cooperation-view Database Design

Data center charging system cooperation-view Database Design

I am responsible for the simplest layer D, interface layer, and factory layer for data center cooperation. Anyway, I wrote the D layer, So I designed the database simply by the way. It's already the third time I have knocked on the data center charging system. It's about half a year between them. The demand is transparent, and the database is well designed. There is basically no big difference from the second one, that is, the Student table and the Card table are separated.

During Reconstruction, almost all of my databases were used: transactions, stored procedures, triggers, views, and joint queries. Therefore, this design database is still SO Easy .. In addition, in order to make it easier for zookeeper and Niu Migration Division, I wrote the Combined Query into the Stored Procedure !!!! It took a lot of effort, but layer D was much simpler. I still remember that I used more triggers during the last refactoring .. There was a magic thing: When I registered a Card, I charged 10 yuan, and the price will be doubled in the Card table .. This is a loss sale .. Later, I found that the trigger was overhead .. It is changed to the transaction + storage process. Looking at the database design again this time, it's easy to get started ~

The following are some examples ~ I 'd like to discuss it with you ~

I. Use stored procedures to solve combined queries:

12 parameters are required for a combined query: A table name, three fields, three operators, three conditions, and two joins or relationships. This is their commonality. Specifically, there are three different tables with different fields and different conditions. In this way, I encapsulate the common SQL query statements and write them into a stored procedure. When executing the combined query window, I only need to input different parameters from the UI Layer, of course, the generic set must return different types. The stored procedure is as follows:

 

USE [HezuoCharge] GO/****** Object: StoredProcedure [dbo]. [Proc_GroupQuery] Script Date: 05/27/2015 21:50:17 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO -- ================ =================================-- Author: zhou -- Create date: 2015-5-5 -- Description: composite query -- =============================================== ========= alter procedure [dbo]. [Proc_GroupQuery] -- Add the parameters for the stored procedure her E @ tablename varchar (40), -- the first group parameter @ ziduan1 varchar (40) = null, @ fuhao1 varchar (10) = null, @ tiaojian1 char (50) = null, @ zuhe1 char (10) = null, -- parameter of the Second Line @ ziduan2 varchar (40) = null, @ fuhao2 varchar (10) = null, @ tiaojian2 char (50) = null, @ zuhe2 char (10) = null, -- parameter of the third line @ ziduan3 varchar (40) = null, @ fuhao3 varchar (10) = null, @ tiaojian3 char (50) = nullAS 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 hereSET @ 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) elseexecute (@ SQL) return 1END

 

Layer D call (taking the returned Line type as an example ):

 

# Region "Operator -- View computer records -- Combined Query "'''''' Operator -- View computer records -- Combined Query '''''''''
 '''
 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 able defines dt Dim mylist S New List (Of LineEntity) 'defines generic 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 ("no record under the current condition! ") Return Nothing End If End Function # End Region

 

Ii. Trigger checkout:

During the checkout, The IScheck field data needs to be updated on the top of the three tables. Since the three tables are designed, the trigger is written. When the data is inserted, the execution is triggered. We recommend that you use the trigger with caution because it is too flexible. Once the trigger conditions Insert, update, and delete are met, the trigger will be executed. Compared with execution, it tends to use stored procedures, but flexibility is also its advantage.

 

USE [HezuoCharge] GO/****** Object: Trigger [dbo]. [Check] Script Date: 05/27/2015 21:57:11 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO -- ================ =================================-- Author: zhou -- Create date: 2015-5-5 -- Description: when the bill is settled, you must write the checkout -- ============================================== ============= alter trigger [dbo]. [Check] on [dbo]. [T_Check] for Insert as begin update T_Card set IsCheck = 'checkout 'where Carddate = CONVERT (varchar (10), getdate (), 120) update T_Recharge set IsCheck = 'checkout 'where Rechargedate = CONVERT (varchar (10), getdate (), 120) update T_Cancelcard set IsCheck = 'checkout 'where Canceldate = CONVERT (varchar (10), getdate (), 120) END

 

3. Clever Use of functions to help you settle accounts

SQLSERVER provides many functions to help us easily solve sorting, maximum value, minimum value, average value, sum, and so on. The following statements are used to calculate the total amount.

 

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

 

4. Check out on the current day and use views to list data

Using the recharge table data as an example:

 

SELECT     CardID, Addmoney, Rechargedate, Rechargetime, UserIDFROM         dbo.T_RechargeWHERE     (Rechargedate = CONVERT(varchar(10), GETDATE(), 120))

 

V. Summary:

I wanted to use the database during reconstruction. Later I saw a Sina Weibo post, which was a group of SQL questions interviewed by. NET programmers. It was a silly question !!! Only to find out how pediatric we are doing .. It is time-consuming to innovate on the database. In short, do not simply do it! Just like data import, I think it is physical, but I can really learn a lot. It depends on what kind of height you are standing on to review this matter! Not only is it a technical embodiment of Excel tables, but I was wrong at the beginning.