Data room charging system-database design summary

The charging system for the self-built IDC is in full swing. Although it is not difficult, there are still many minor faults.ProgramIsCodeIn implementation, let alone the program. Let's first summarize the database design.

1. Data Integrity

Constraints, rules, and default values can guarantee data integrity. To ensure system data integrity, primary keys, foreign keys, and check constraints are used during design, both rules and default values are used to ensure data correctness. When a primary key is used, a column is restricted. When a column in the table is used as the primary key, the data in the table that creates the primary key constraint cannot be empty or repeated, the index of a table is a column restricted by the primary key by default. The primary key is used to constrain the two columns in the same table, the data in the constrained table is sorted according to the sorting rules of the first column with the primary key set. This ensures that the data in the two columns cannot be empty and cannot be repeated, it also conforms to the second paradigm.

Primary Key constraints do not have much problems, mainly foreign key constraints. Foreign key constraints ensure data integrity. During database operations, they are triggered cascade to ensure data integrity, however, foreign key constraints must be created based on unique, non-empty, or primary key constraints. The columns in the restricted foreign key table must be non-empty and unique.

The main operation steps are not described in detail. The previous blog has summarized them.

2. Business Rules

A good database can reduce the time for program developers to write code, while cleverly using triggers during database design can ensure business rules while reducing the time required to write complex code. Although the efficiency of a trigger is lower than the constraint to ensure data integrity, the trigger owner is advantageous in processing business logic because it can be automatically triggered to implement cascading modification. Triggers can be divided into DDL, DML, and login triggers by category. Specific classifications include:

 

When you use DML data to operate a language database, it generates the corresponding deleted and inserted tables in the trigger table. The two tables respectively Save the data of the row to be deleted and add the data of the row, you can also apply the data in the two tables when using the trigger.

Unlike SQL2000, sql2008 creates a trigger in the corresponding table when creating the trigger. Of course, you can also directly run the create statement in the query to create the trigger. For example:

To improve the business logic, the system uses an insert trigger during design. when inserting data in a table, modify the data in another table. The main statement is as follows:

 

 
Use [chargesystem] Go/* Table recharge record trigger, which can trigger the change of student balance. The main implementation is when a student is charged the amount, the recharge amount is automatically added to the student balance */If object_id ('chargesystem. trchargeadd', 'tr') is not null drop trigger chargesystem. trchargeadd; gocreate trigger trchargeaddon tblchargelogfor insertas/* Add the added recharge amount to the student balance */update s set stu_balance = (S. stu_balance + I. ch_chargecash) from tblstudent as s inner join inserted as ion S. stu_cardid = I. the inner join query in the preceding statement of ch_cardidgo/* adds the rows in the inserted Table following the on statement to the tblstudent table */
 

 

to ensure database security, prevent SQL Injection occurs. When we use structured queries or operations, the application uses parameters and parameterized queries or operations instead of field concatenation, SQL Injection Status.