Analysis of data integrity problems

Data | questions

----in the development of C/s structure of large database application software, generally, software developers and database designers are not the same person, which needs to be negotiated some can be solved by the program designers can be solved by the database designers to ensure data integrity is one such problem.

----The author gives a simple example: the goods out of the library, the example of returns

----Here are inventory tables (KCB), Out of stock list (CKB) and return order form (THB)

----Inventory table includes the following: wpbh,kcsl(item number, inventory quantity)

----out of the library list contains the following: wpbh,cksl(item number, out of the number of stores)

----Return list contains the following: wpbh,thsl(item number, return quantity

----If we do the following transactions:

----1. Item A has 10 pieces out of the library

----2. Item A has five returns (can be out of the library)

----We all know that both transactions need to modify the inventory table, but who will do the job of modifying the inventory table. To be sure, programmers and database designers can do that.

----First we illustrate how the program designer completes:

----1. When the library is out, a new library record is inserted into the list of libraries, and the KCSL in the inventory table is replaced with the value of KCSL minus the number of the library.

----2. When a return is returned, a new return record is inserted into the return table, and the KCSL in the inventory table is replaced with the value of KCSL plus the return quantity.

----because of different development platform, the code to complete this work is not the same, so do not write specific code.

----Next look at how the Database Designer completes the work:

----Database Designer completes this work by a trigger.

----The Database Designer establishes the following SQL statement to create the insert trigger for the out of the Library list (CKB)

CREATE TRIGGER Forinsert
On CKB
For INSERT
As
UPDATE KCB
SET KCB.KCSL=KCB.KCSL-INSERTED.CKSL
From inserted
WHERE KCB.WPBH=INSERTED.WPBH

----Similarly, create a similar SQL statement for the return order form

CREATE TRIGGER Forinsert
On THB
For INSERT
As
UPDATE KCB
SET KCB.KCSL=KCB.KCSL + INSERTED.THSL
From inserted
WHERE KCB.WPBH=INSERTED.WPBH

----can clearly see that if two of people have done the work, the program will certainly go wrong during the run.

----But who should do the work? I personally believe that the Database designer should do this for the following reasons:

----1, a good portability of software should be independent of the structure of the data;

----2, the process of development and debugging more simple, take the above example, the programmer only need to complete the list and return form to insert the corresponding record work, the specific modification work by the trigger to complete.

----3, the operation of the trigger is on the server side, reducing the overhead of the client.

----4, the trigger will start regardless of when you are doing it, ensuring that the data is not wrong.

----In fact, the triggers written above are just one of the simplest examples, in the actual work is far more complex than this, the design of a good database will cost the database designers a lot of effort, if just set up a table, the work has not finished half, the next half work is to solve your data integrity problems.

----The successful design of a database can have a multiplier effect. The integrity of the data has been solved, what is the problem of programming?

----easy to say, but difficult to do, than in the actual work also often encounter many problems, hope and a lot of colleagues to progress together, your experience and good way to tell you.