Large database design principle

Large Database Design Principles Large Database Compared with the previous file type database has its own advantages, and it is also the preferred product developed in today's MIS system. However, the design of the design of the database model will greatly affect the performance of the MIS system. According to the authors of many years of experience in database design, this paper explains some guidelines for database design. With the intensification of market competition and the development of information social needs, people put forward higher performance requirements for information processing, acquisition, release, storage, etc. Mainly reflected in the following aspects: First, the storage of information, how to face various forms of information (such as characters, graphics, sound, etc.), as well as increasing historical data. Second, the search, how a wide variety of users quickly queries the required information from a database. Third, how data is integrity and consistency guarantee. Obviously traditional database systems are difficult to meet this needs. With the development of the client / server (Client / Server) technology, some famous database products such as Oracle, Sybase, Informix, DB2, MS SQLServer have developed, making these problems perfect solve. However, a good database product is not equal to having a good application system. If you cannot design a reasonable database model, it will not only increase the programming and maintenance of the client and server segment program, but will affect the actual operation of the system. performance. Generally speaking, in a MIS system analysis, design, testing, and trial operation, because the amount of data, the design personnel and testers often only note the implementation of the function, and it is difficult to note the weakness of performance, etc. After investing in actual operation, with the increasing inflation of the data, it is found that the performance of the system is decreasing. At this time, consider improving system performance, it takes more human material, and the entire system is inevitably formed a patch. engineering. The author is based on the design and use of database experience, and puts forward some of the following design guidelines for reference. * Named specification. Different database products have different requirements for the object's naming, so the naming of various objects in the database, the code writing of the background program should be in size, and the number of object naming should not exceed 30 characters, so that it is easy Application system adapts to different database platforms. * Currura (CURSOR). The cursor provides a means of scanning on a particular collection, generally uses the cursor to cross data, and different conditions are performed according to the different conditions of the extracted data. Especially for multi-table and big data sheet defined cursors (large data sets) cycles easy to enter a long waiting and even crash, the author in a city "housing provident fund management system" When processed, the proceedings of a 100,000 accounts have entered an indefinite-free special (48 hours after the measurement) (hardware environment: alpha / 4000 128mram, sco unix, sybase 11.0), then Changes in different conditions to be completed within twenty minutes depending on different conditions. Examples are as follows: Declare mycursor cursor for select count_no from count open mycursorfetch mycursor @vcount_nowhile (@@ sqlstatus = 0) beginif @ vcount_no = 'condition 1 Operation 1IF @ vcount_no =' condition 2 operation 2. . . Fetch mycursor INTO @vcount_noend. . . . . .

Change to Update Count Set Operation 1 for Condition 1UPDATE COUNT SET Operation 2 for Condition 2. . . . . . In some cases, sometimes the cursor is also available, and it can also be considered to transfer the eligible data line into the temporary table, and then operate the temporary table definition cursor, which can make performance significantly improved. The author in the design of a city post and telecommunications service system database background program (30,000 lines of eligible 30 multi-line data) cursor operation (hardware environment: PC server, PII266 64mRam, NT4.0 MS SQLServer 6.5). Examples are as follows: create #tmp / * Define Temporary Table * / (Field 1 Field 2 ...) Insert Into #tmp Select * from Total Where Condition / * Total 300,000 Board of Volumes Effects only dozens of lines * / Declare MyCursor Cursor For select * from #tmp / * Define the cursor * /. . . * Index (INDEX) principle. Creating an index typically has the following two purposes: Maintaining the uniqueness of the index columns and provides a policy that provides data in the quick access table. The large database has two indexes, the cluster index and the non-clustered index, and a table without cluster index is stored in the stack structure. All data is added to the tail of the table, and the table of cluster index is established, and its data is physically The table is stored in the order of the cluster index, and a table only allows a cluster index. Therefore, according to the B tree structure, it is understood that any index can improve the speed of the index query, but will reduce insertion, update, delete The performance of the operation, especially when the Fill Factor is large. So the frequent insertion, update, deletion of the index is frequently inserted, updated, deleted, the meter, and index should be set to leave more free space in each data page, reduce page splitting and reorganization. The probability of the generated work. * The consistency and integrity of the data. In order to ensure the consistency and integrity of the database, designers tend to design too much intervals and reduce data redundancy as much as possible. The intervalvement is a mandatory measure. After the establishment, the insertion, update, and delete operation of the parent table and the child table (Child Table) should take up the overhead of the system, and it is best not to use the Identify property field as an identity field. The primary key is associated with the child table. If the data is redundantly redundant, the integrity of the data is easy to guarantee, but the operation of the interval connection query is added, and the reasonable data redundancy is also necessary in order to improve the response time of the system. Using Rules and Constraints to prevent system operator erroneous input caused data error is another common means of designers, but unnecessary rules and constraints will take up unnecessary overhead of the system. It is that the validity verification of the data is faster than the rules. All of this, the designer should be balanced according to the type of system operation in the design phase. * Trap for transactions. The transaction is a set of operations in one-time. Although these operations are single operations, SQL Server guarantees that all operations are all done, or they don't do it. It is this feature of a large database that makes the integrity of the data have been greatly guaranteed.