Source: http: //www.ccw.com.cn Author: Deng Ling did not want to develop a database-based application system, the most critical step is the whole system is based on the modeling database design, from logical to physical, a The entire application system is similar to the dangerous house, and the development process is constantly inseparable, it is necessary to face various difficulties, developers want to modify or redesign no design. The system is unpredictable. So, a good database design is necessary for high efficiency systems. First, the method of logically modeling database design varies depending on the specific database, but the modeling phase is the same, so it can be done with some universal tools, such as Rational Rose, PowerDesigner, etc., this phase is mainly based on the needs of the system. Gets the application system information to be implemented, performing data inside and external relationships, thereby effectively establishing the data structure of the entire system (typically referred to as a table structure in the relational database), on which the data amount on the database is , Data traffic, and estimation analysis of response speed, so data model is generated. Specific operating criteria are several paradigm of the database, the user's specific needs and analysts experience, from the aspects of the performance, safety, convenient management, easy development, etc., depending on the preferences and habits of analysts, Unexpected tools, it is best to use tools, which can make the analysis process simple, most important to generate some pictures, such as ER diagrams, let the analysis process clear. Second, physical design This step design and system will be related to the specific database, and the hard, software platform is related to the database, with the aim of assigning physical space to the database, which is important in database design. The performance of the database data, the performance of the database, in particular, this step includes the allocation of the corresponding table space on the disk, but also determines the redolog file, the size of the ROLLBACK segment according to the size of the data, and then assign it, these The assignment of documents should follow some principles, in accordance with the principles benefiting for backup, facilitating performance optimization, as follows (as an example of Oracle Database): 1. Establish different tablespaces for tables and indexes, prohibiting in system tablespaces The object of the core Oracle system component ensures that the data table space and index table space are located on a different disk disk drive. 2. Understand how end users access data, if possible, objects that often query and frequent queries are placed on different physical disks. 3. When the database contains a large object that allows the user to access different data elements, it is good to split the objects on multiple disks. Partitions can be used. When defining a table with millions of rows on a single operating system platform, it is more careful because the size of the database file is limited, which is caused by the operating system instead of Oracle. 4. Create at least two user-defined Rollback tablespaces on each of the individual discs to store users their own ROLLBACK segments. The order of the ROLLBACK segments is scheduled in the initialization file so that they switch between multiple disks. 5. Place the Redo Log file on a relay of less read and write. For each Oracle instance, more than two REDO LOG groups are established, two members of the same group are placed on different devices. 6. Establish the size of the table and index, which determines the size of the table space required to save them, and also determines which tablespaces are physically attached to which tablets can be combined. The specific estimation method can be in accordance with some formulas of Oracle, and the storage parameters such as (PCTFREE, PCTUSED) are also defined according to the specific features of each table.
Third, regarding the design of the database parameters Each database is established, there is default parameter settings, but for the specific application requirements parameter settings may be different, the default parameter settings often need to be changed according to the characteristics of the application system, such as Each database operating system platform, instance number, various memory size settings, the thread mode, the backup method, etc., the specific parameters must make the most appropriate modification, this step is very important to database performance, but also guarantees The database function required by the application system is implemented. IV. The interface problem with the development software is in the end to consider the interface problem between the development software to be selected. It is necessary to prepare the interface program, some are the third-party software already prepared, some is the database itself. Such as JDBC, BDE, ADO, etc., mainly to consider the availability and efficiency of the interface. This step is mainly from experience, because this product has been continuously introduced, and it has been touted through various merchants. It is best to decide in practice. The above is a rough analysis of several important steps for database modeling design. The entire design process is constantly improving. It is the database administrator, designer, and developers. It is only different, and the database administrator focuses on 2, 3 steps, designers focus on 1 step design, and developers focus on 4 steps, because some application systems are different, so they have to do two sets of design, and pay attention to two sets. Designed compatibility portability. By combining a database design in the actual development of labor management systems, further explanation of the above steps. The system is applied to large enterprise groups, including dozens of bracuses, basic data from all divisions, all branches have an Access database, store their own talent file information, and are maintained by each factory, regularly In the total database, all rooms are available, statistical, and This is a part-style database. First, the logic modeling is first for the data volume, data traffic, and response speed of the database. This step is also necessary for physical design. The main data of the labor system is a staff file, about 100 megabits, and someone Equipment and other information plus various codes such as work sequence, wage level, factory mine workshop, and the total amount of data is about 300 megabits of some respects. The data is input, and most of the maintenance is carried out in each plant, then it is concentrated, so the data traffic is large, and the response speed is required. On the tool on the logic modeling, the Randional Rose 2000 is used, according to the user's needs, design various USE Case View, generate the entity between the database, and the ER diagram between the entities, then generate Data Modeler, after determination The table is generated under the corresponding user in the built database with Schema Generation. The detailed picture is very cumbersome, and it is not shown here. Second, physical design 1, because the staff file information library is very large, there is a photo, so it is divided into two tables, put the photos separately in a table, and use partition technology, establish partitions on the archive table, photo table Different partitions are placed in different tablespaces, respectively. 2. Because there are many codes in the staff file information library, the query is to be operated with the encoding library, so they do not put the employee file information table and the encoding library in a table space. 3. Establish two rollback tablespaces, ten rollback segments, partitions in two tablespaces, establish a relatively large ROLLBACK segment, because employee file information table often has bulk modifications and addition. 4. Place the redo log file on a relay of less read and write. Establish three redo log groups, two members of the same group are placed on two disks.
