Pricing System database design summary

Use. the billing system of the personal edition compiled by net is coming to an end, from the database design at the beginning to the system model diagram to the system code, which has encountered many problems, at the same time, it also revealed that the basic knowledge is not reliable enough. The previous blog summarized the database design of the charging system, this blog mainly summarizes the database design of the billing system in detail.

I. Data Type 1. Storage Type

At the beginning of database design, we should consider the storage form of database Field Values and the type of data to be stored. It is a headache for new users of database design, the storage type seems simple, but it is very important. Field storage is directly related to the efficiency of complex business logic computing for data in the database in the future. After multiple database design experiences, the following design types are summarized.

Store Data	Database Type
Date	Varchar (50)
Time	Varchar (50)
Money	Decimal
ID column	Int
Quantity	Decimal

The variable-length string type (varchar) is recommended when the date and time types are stored in the database, mainly because the date and time types in SQL are redundant in data storage, in particular, the date type stores the date and time at the same time, but we usually only need to store a certain date and only the year, month, and day. In this case, the data type in SQL is insufficient, in this case, we should use the varchar type. The advantage of string-type storage is that it can store data of the type uploaded to the database without changing the data, another advantage of the string type is that it performs comparative queries one by one based on character concatenation during comparative queries. This query will not report errors when encountering special types of data, instead, it implicitly converts the passed type to a string for comparison query. The storage of the time type is similar to that of the date type. If there are no special requirements, it is best to use the string type for storage.

The decimal type is recommended for monetary data, which is equivalent to the double type in programming languages. This type of data is computed in a variety of ways and can be used for complex logical computing, in addition, it can specify the number of decimal places of data, which is simple and flexible, and has a low chance of error when interacting with the program language.

2. type conversion

There are two types of data type conversion:

Implicit conversion is invisible to users. sqlserver automatically converts data from one type to another.

Explicit conversion: a custom conversion method. The cast or convert function is used for explicit conversion. When the number of decimal places is converted to different data types, the result value is sometimes truncated and sometimes rounded.

To use implicit conversion, we must remember the principle that a minority follows the majority. In a computing formula with different types of inclusions, most types replace a few.

/* Declares two variable parameters. When the decimal type data is converted to an integer, The result value is truncated */declare @ a decimal ), @ B intset @ A = 18.56 select @ B = cast (@ A as INT) Select @ B/* The result is 18 * // * two variable parameters are declared, when the decimal type data is converted to an integer, The result value is rounded to */declare @ a decimal (18.56), @ B intset @ A = select @ B = convert (decimal, @ A) Select @ B/* The result is 19 */

Ii. Business Logic

The previous blog briefly summarized the business logic. During business logic calculation, transactions, stored procedures, and triggers are used most often. A trigger is a special stored procedure, and a stored procedure is a special function that stores database operation statements. Transactions are widely used in database operations and can contain complex logical structures. When an error occurs, you can use rollback services to avoid database operations and ensure data integrity.

1. Parameters in logical statements

When using SQL statements to perform business logic operations, you are advised to use parameters for operations. Parameter operations can be divided into two parts:

① Use the declare keyword to declare the parameter and specify the type

② Assign values to parameters using the set or select statements,

When declaring a parameter, use @ to specify the User-Defined parameter type, and @ to specify the system parameter variable.

When parameterized query is used, the corresponding parameter values should be stored in the system memory. If no parameter value is specified in the SQL statement, when using stored procedures, we must save the parameters to the system memory.

The following is a stored procedure that uses parameters and transactions.

-- ===================================================== ====== -- Author: <author,> -- create Date: <2013-05-05,15: 00,> -- Description: <stored procedure, registered student, add student information> -- ======================================== ========== use chargesystemgocreate procedure proc_addstudent -- add the parameters for the stored procedure here @ cardid varchar (50 ), -- storage card number parameter @ professional varchar (50), -- storage of student Major Parameter @ Class varchar (50), -- storage of student class parameter @ Department varchar (50 ), -- store student Schools @ explain varchar (100), -- store student comments @ cashdecimal (), -- store student registration amount @ isused varchar (50 ), -- store student usage status @ sex varchar (50), -- Student gender @ studentname varchar (50), -- Student real name @ uid varchar (50 ), -- Student ID number @ grade varchar (50), -- student grade @ admin varchar (50) -- Administrator name asbegindeclare @ errno int -- error code begin transaction -- insert statements for procedure here -- insert database table 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) -- Record error code select @ errno = @ error -- if an error occurs, undo the operation if @ errno> 0 beginrollback transactionreturnendcommit transactionendgo.

2. Data computing datediff Function

The datediff function is used to calculate the value between two time periods. It can specify the time type between the two time periods, which can be hours, minutes, or days.

The following triggers

-- ===================================================== ========================================================== ===================-- Author: <author,> -- create Date: <2013-05-05,15: 00,> -- Description: <trigger, triggered when data in the tblonlinestu table is deleted, want to add the student computer record in tblstulinelog> -- ============================== ========================================================== =, write the student's computer information into the student's computer information table */use chargesystem go -- determines whether a trigger with this name exists. If so, delete if object_id ('chargesystem. tr_onlinedelete ', 'tr') is not null drop trigger chargesystem. tr_onlinedeletego -- create trigger named tr_onlinedelete create trigger [DBO]. [tr_onlinedelete] on [DBO]. [tblonlinestu] For deleteas declare @ chargeinfo decimal (), -- set billing information @ logintime time, -- save the logon time @ logouttime time, -- save the exit time @ logintimev varchar (10 ), -- save the logon time and insert it as a parameter to tblstulinelog @ logindate date, -- save the logon date @ logoutdate date, -- save the exit date @ consumecash decimal ), -- save the consumption amount @ consumetime decimal (18), -- save the consumption time @ cardidvarchar (50), -- student card number @ uidvarchar (50), -- Student ID number @ studentnamevarchar (50 ), -- Student name @ adminvarchar (50), -- Operator name @ balancedecimal (18, 2), -- Student balance @ preparetime decimal (18) -- machine preparation time -- the value of the row to be deleted is assigned to the required Parameter Select @ cardid = onstu_cardid, @ uid = onstu_uid, @ studentname = onstu_studentname, @ logindate = convert (date, onstu_logindate), @ logintime = convert (time, onstu_logintime), @ admin = onstu_adminfrom deleted -- assign a value to the computer charge, used to calculate the student's consumption amount select top 1 @ chargeinfo = se_settleduser, @ preparetime = se_preparetime from tblsetcharge order by se_serial desc -- assign select @ logoutdate = convert (date, getdate (), 23) Select @ logouttime = convert (time, getdate (), 8) -- calculate the consumption time of a student. Select @ consumetime = datediff (minute, @ logindate, @ logoutdate) + datediff (minute, @ logintime, @ logouttime) -- calculate the consumption amount of a student. Select @ consumecash = @ consumetime * @ chargeinfo/60 -- assign the select @ logintimev = convert (varchar (10), @ logintime, 8) value to the student Logon Time) -- determines that if the student's computer time is less than the system's start charging time, the student will not be charged if @ consumetime> @ preparetimeselect @ balance = (stu_balance-@ consumecash) from tblstudent as s inner join deleted as D on D. onstu_cardid = S. stu_cardidelse begin select @ consumetime = 0 select @ consumecash = 0 select @ balance = stu_balance from tblstudent where stu_cardid = @ cardid end begin -- write the student information on the machine to the student information record table values (@ uid, @ cardid, @ studentname, @ logindate, @ logintimev, @ logoutdate, convert (varchar (10), getdate (), 8), @ consumetime, @ consumecash, @ balance, @ admin) End