Computer room charge system cooperation--look at Database design again

Computer room Cooperation I am responsible for the simplest D layer, interface layer, factory floor. Anyway d layer is I to write, so the database simply also by the way design. It is the third time to knock the computer room charge system, each time is separated by about six months. The requirements have been thoroughly, the database is also good design. Basically the second time is no big difference, that is, the Student table and card table separated.

Refactoring, my database used almost everything: transactions, stored procedures, triggers, views, federated queries, and so on. So, this design database is still so easy. And, in order to make the Chan Chan and the cow moved brother Siang write convenient, I put the combination of queries are written in the stored procedure!!!! It took a while, but the D layer was a lot simpler. Remember, the last time you were refactoring, you used more triggers. There is a magical thing: when I register a card, Recharge 10 dollars, will add 1 time times the price on the card table. This is a loss of trade AH. Later found that the trigger was overdone. is changed into a transaction + stored procedure. This time to look at the database design, IT Pro ~

Here are a few examples ~ to discuss with you ~

first, skillfully use stored procedures to solve combinatorial queries:

There are 12 parameters to use when combining queries: A table name, three fields, three operators, three conditions, and two relationships. This is their commonality. The place of individuality is that it is divided into three different tables with different fields and different conditions. In this way, I enclose the common SQL query statement as a stored procedure, and when the Combined Query window is executed, only the different parameters are passed from the UI layer, but the generic collection needs to return different types. The stored procedures are 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: Eric--Create date:2015-5-5--Description: Combination Query--============================================ =alter PROCEDURE [dbo]. [proc_groupquery]--Add the parameters for the stored procedure here@tablename varchar (,--the first set of parameters @ziduan1 varchar (40) = NULL, @fuhao1 varchar (=null), @tiaojian1 char (=null), @zuhe1 char (Ten) =null,--second row parameter @ziduan2 varchar =null,@ Fuhao2 varchar (=null), @tiaojian2 char (=null), @zuhe2 char (Ten) =null,--the third row of parameters @ziduan3 varchar =null, @fuhao3 varchar (=null), @tiaojian3 char (=nullas) declare @Sql varchar (+) begin--SET NOCOUNT on added to prevent extra resul T sets from--interfering with SELECT statements.    SET NOCOUNT on; --Insert statements for procedure Hereset @Sql = ' SELECT * from ' [email protected] + ' where ' [email protected][e Mail&nbsP;protected]+char [email protected] +char (All) if @zuhe1! = ' SET @[email protected][email  protected] +char [[Email protected][email protected]+char] [Email protected]+char] Else Execute (@Sql) if @zuhe2! = ' SET @[email protected][email protected] +char (+) [Email protected][email  protected]+char (Email protected]+char) Elseexecute (@Sql) return 1END

D-Layer invocation (with the return line type as an example):

#Region "operator-View on-machine record-combination query" ' <summary> ' operator--view on-machine record--combination query ' </summary> ' <param name= "Engroup" ></param> "<returns></returns>" <remarks></remarks> public functi On Groupline (Engroup as Groupentity) as List (of lineentity) Implements iline.groupline Dim sql as String = "Proc_gr            Oupquery "Dim para as SqlParameter () = {New SqlParameter (" @tablename ", Engroup.tablename), New SqlParameter ("@fuhao1", Engroup.fuhao1), New SqlParameter ("@ziduan1", Engroup.ziduan1), Ne            W SqlParameter ("@tiaojian1", Engroup.tiaojian1), New SqlParameter ("@tiaojian2", engroup.tiaojian2), New SqlParameter ("@fuhao2", Engroup.fuhao2), New SqlParameter ("@fuhao3", Engroup.fuhao3), new Sqlpar Ameter ("@zuhe1", Engroup.zuhe1), New SqlParameter ("@zuhe2", Engroup.zuhe2), New SqlParameter ("@zidua  N2 ", engroup.ziduan2),          New SqlParameter ("@ziduan3", Engroup.ziduan3), New SqlParameter ("@tiaojian3", Engroup.tiaojian3)}        Dim helper As New SqlHelper Dim dt As DataTable ' definition dt Dim mylist as New List (of lineentity) ' definition generics dt = Helper. Execselect (SQL, CommandType.StoredProcedure, para) If (dt. Rows.Count > 0) Then mylist = CType (entityhelper.converttolist (DT, mylist), Global.System.Collections.Generi C.list (of Global.Entity.LineEntity)) Return mylist Else Throw New Exception ("No record under current conditions!") ") Return nothing End If end Function#end Region

second, trigger to help you checkout:

You need to update the data for the Ischeck field on the top of the three tables when you check out, and since you have designed to three tables, you have written the trigger. When you insert the data, it triggers its execution. Suggestions: Trigger to use with caution, because it is too flexible, once the insert,update,delete is satisfied with this trigger condition, it will be executed. Compared to execution tends to the use of stored procedures, but flexibility is also its advantages, it has its merits.

Use [hezuocharge]go/****** Object:  Trigger [dbo].[ Check]    Script date:05/27/2015 21:57:11 ******/set ansi_nulls ongoset quoted_identifier ongo--==================== =========================--Author: Eric--Create date:2015-5-5--Description: When you check out, you have to write on the bill-======================== =====================alter TRIGGER [dbo]. [Check]    on [dbo]. [T_check]    For Insert   as    BEGIN      update t_card Set ischeck = ' checkout ' where carddate =convert (varchar), GETDATE (), 120) Update T_recharge Set ischeck = ' checkout ' where rechargedate =convert (varchar (), GETDATE (), +) Update T_cancelcard set Ische ck = ' checked out ' where canceldate =convert (varchar (), GETDATE (), +)  END

three, skillfully use the function, help you to settle the accounts

SQL Server provides a number of functions that can help us to solve some things such as sorting, maximums, minimums, averages, sums and so on. Here is the statement I wrote 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) tabl E2, (select SUM (CONVERT (Int,cancelcash  )) Allcancelcash from V_todaycancel) table3

four, the same day checkout, with the View list data

Take the top table data list as an example:

SELECT     CardID, Addmoney, Rechargedate, rechargetime, Useridfrom         dbo. T_rechargewhere     (rechargedate = CONVERT (varchar), GETDATE (), 120))

v. Summary:

I want to use the reconstruction of the database, and later saw a Sina Weibo, is a. NET programmer interview A group of SQL questions, simply look silly my eyes!!! Just to find out, what we do is what the pediatrics thing. To innovate on the database. In short, don't do it because it's simple! Just like the data guide, thought is the physical life, but really can learn a lot of things, depends on what height you stand to look at this matter! Not only the technical manifestation of Excel table, I was wrong at the beginning.

Just think of Li Shi brother Siang in the Triad class said a sentence: others do not want to do things, I do, will find that simple small things will learn a lot of things!! (Inspire me, eh!!) ）

That's a lot. END ...

Computer room charge system cooperation--look at Database design again