PowerDesigner design database
Li Weihua
September 2000
Explanation: This document is the result of the accumulation of my own experience, non-departmental design document (copyright)
This document does not tell how to use PowerDesigner, but to describe how to combine PowerDesigner's characteristics to better design a database system.
Design database with PowerDesigner
PowerDesigner plays an important role in database system development as one of the database tools and designs of database modeling.
The database design is used to use PowerDesigner, which not only gives people an intuitive understanding of the model, but also fully uses the technology of the database, and optimizes the design of the database. PowerDesigner supports multiple database systems such as Sybase, Oracle, Informix, SQL Server, and does not have to maintain multiple database scripts when the application system is migrated.
For the use of structured analysis (SA), E-R, data flow diagrams until the last database physical map is an indispensable part of the system design, and when the database physical map is completed, the system's data dictionary should be generated. Use PowerDesigner to complete this design process.
For an object-oriented analysis (OOA), since the database is RDBMS, there is a mapping between objects and relational databases, and a database design is also required.
Two database models
PowerDesigner can design two database models: Database logic map (ie E-R diagram or conceptual model) and database physical chart (physical model), and both database diagrams are reversible.
Database logic is an abstraction of the real world, reflecting the relationship between the entities, can have 1 pair of 1, 1, and many-to-many relationships. Specially, there is a summary of this relationship in the expansion E-R map, reflecting a subset of subsets between types, which defines superclass and subclasses. In the E-R diagram designed in PowerDesigner, this relationship is not available in the model of E-RWIN design, so it is paid attention to this relationship when the model designed with E-RWIN is converted to the model of PowerDesigner.
The database physical diagram is a physical implementation of a logical model, reflecting the reference relationship of the intervals. There is a much more-to-many relationships in the physical model. When the logic map is converted to the physical diagram, many pairs of relationships becomes two 1-to-many relationships.
The logical model and physical model have a close relationship, and there is also a difference. The design of the logical model follows the third paradigm of the database design theory (in a general database application to reach the third paradigm), the logical model requires all the information expressed by the application system and eliminates data redundancy. The physical model is based on the logical model. In order to optimize the performance of the application system, it is used to increase redundancy, create indexing and other database technologies, which mainly uses some non-standardized theories.
Before considering any non-standardized, the database should be fully standardized, and non-standardization cannot be performed before the data and user needs are not fully understood. Otherwise, the organization that leads to data is more and more confusing, and the application is getting more complicated.
Therefore, logical models and physical models are contradictory and close contact, which requires designers to grasp.
PowerDesigner Design Database Physics
Design database physical graphs with PowerDesigner, including multiple objects, such as tables, fields (Domain), and the like. The design is mainly in the Dictionary and Database two menus of PowerDesigner.
Table (Table)
Table is a logical object of data storage, including other objects such as a field (COLUMN), index (INDEX), trigger (TRIGGER), stored procedure, etc., and the optimized design of the table is divided into the table's storage, If the access amount is large, the access frequency is high, consider placing the table on a different storage (Storage). When designing tables, the size and growth amount of the table should be estimated, which is easy to create a database empty key when creating a database, which reduces the generation of disk fragments.
When designing the primary key in the relational database, the meaningful primary key is a fatal error. If the user decides to change the business meaning of the field, you need to modify all where you use it. The role of the primary key should be to maintain uniqueness and use as a foreign key. Any changes to primary keys will result in huge database maintenance work, apparent that this is not suitable for design. In terms of relational databases, design primary key policies use the method of proxy primary key.
The "hotspot" phenomenon should be avoided when designing primary links, but it also needs to analyze concurrent users of specific application systems.
Field (Column)
Define a field mainly with field name, field type, and length, whether the main foreign key, whether it is empty, constrained, default, domain, etc.
Data types of growth and fixed length are more discussing in database design. As a general principle, if the data range in a column is expected to be large, the change is not frequent, and the end of the same size is most suitable for such columns. .
When you decide, you can't be too wasteful, you can't have a little less. Taking into account the future needs, and it is realized that if the leader is increased without changing the number of rows accommodated in a page, the increase in space is equal to free use.
When designing, the field is trying to use the domain to facilitate the type of field. Each field is best coupled to the default value, because in the database query, there is a NULL value affects the performance of the query.
The value of the field can be restricted by the Check constraint.
Domain
Simply, it is a user-defined type, but the domain can also define its range or default value, and the domain reduces the workload of the maintenance field type, and the data is reduced.
Reference (Reference)
Referring to a complicated issue in the database design, it is one of the main elements of the integrity of the data, and discusses the constraints of the rear data.
In PowerDesigner, the reference integrity can be set, the referenced base of the reference is from 0 to N, and the modification and deletion constraint can be set to None, Restrict, Cascade, Set Null, Set Default. Since INSERT is included in the UPDATE operation, there is no separate INSERT constraint.
Different settings of constraints produce different effects to modify as an example (delete the same):
None: The parent table is modified, and the sub-table does not affect.
Restrict: The parent table is modified, if the subthelet exists, an error.
Cascade: The parent table is modified, if the subthech exists, the corresponding modification.
Set null: The parent table is modified, if the subthech exists, the corresponding blank.
Set Default: The parent table is modified, if the sub-table exists, then set the default value.
Index
Index is an optimized query using a database technology, indexing cluster index, non-cluster index, unique index, and the like.
When designing an index, pay attention to the index width and minimize the width of the index. The width of the index is not determined by the field, but is determined by the length of the field. For narrow index keywords, placed more keywords and pointers on each index page, so that less I / O finds data. For composite indexes, select the first column is quite important, otherwise the index may not be taken when using a composite index query. It must be ensured that the query begins with the first column.
Index also has a fillfactor, the amount of data growth of the fill factor, and the case where the main key definition is defined.
Trigger and Storage Process (Trigger && Procedure)
Triggers play an important role in maintaining data integrity, it is more flexible than reference,
The business rules of the data layer in the three-layer structure can also be implemented.
The stored procedure is a script that uses SQL and process control statements to complete a certain business. The stored procedure has the advantages of fast processing speed and flexibility in the data processing.
However, the stored procedure greatly increases the coupling between the database. When the database migrates, it is necessary to rewrite the stored procedure, thereby increasing the workload of the version maintenance. If the database requires the use of stored procedures or triggers as much as possible from migration considerations.
If no one is a trigger that modifies PowerDesigner, its migration PowerDesigner is automatically resolved.
Storage (Storage)
Different concepts in different databases, Sybase is called device, SQL Server is called file or file group (File, FileGroup), and Oracle is called tablespace.
Create one or more storage according to the system, and store it according to certain optimization rules.
Database
The division of the database is divided by its physical distribution, while the amount of data, table type, etc. is divided. The database has little impact on the performance of the database. For a large amount of access to the volume, the I / O operation is easy to form a serious bottleneck, thus reducing the I / O operation and I / O operational blocking is the main problem of database design consideration, and the solution will be placed On multiple devices, the device needs to be created on different physical drives, preferably use intelligent or arrays.
Logs and data are stored separately on different devices, and if the index is more and the occupied space is large, this can be used.
There is less maintenance cost than the number of databases.
Therefore, the database is divided into physical distribution as the principle.
The PowerDesigner provides a computational database or table, which helps the designer complete the division of the database.
Database integrity
Database integrity can be made by stored procedures, declarative reference integrity (DRI),
Data types, constraints, rules, defaults, and triggers are implemented. In the database, these functions functions in a unique way. Comprehensive use of these integrity functions, you can make the database, easy to manage, and safe.
Data integrity concept is divided into several aspects.
◆ Sumeral integrity
The domain integrity of the table is enforced by the primary key.
◆ Quote integrity
Use reference to enhance the logical relationship between the tables.
◆ Value domain integrity
Any input data must match the specified data type on the type and range, only the column is allowed to enter NULL only when a column is explained.
Database performance test
After generating a database, the database performance test should be performed to optimize the design of the database, so you need to generate test data, due to performance testing, data specification requirements are not high. The test data is conveniently generated by PowerDesigner to complete performance testing.
Data constraint
O-O constraint
There is no limit to the INSERT, UPDATE, DELETE operation of the parent table.
M-O constraint
Constraints on the parent table:
The INSERT operation of the parent table, the records of the M-O constraint, the record in the middle of the parent table can be added to the table without any constraints, because there must be a child in this constraint.
The key value modification operation of the parent table is only modified only after all their children should be modified, that is, the method of cascading update can be used.
The father's deletion, the father can only be deleted after all the children are deleted or reassigned.
Force the optional (M-O) constraint
O-M constraint
The parent table is constrained:
The INSERT operation of the parent table is constrained to the O-M, and a father can only be added when the child is added to or at least one legal child simultaneously or at least when it is.
The key value modification operation of the parent table is only available when a child is created or has a child.
The deletion of the parent is theoretically deleted that the father is unlimited. In fact, when deleting a primary table record, the scheduler is not taken by the sub-table, and the outer key of the sub-table is used.
Optional to mandatory (O-M) constraint
M-M constraint
The parent table is constrained:
The INSERT operation of the parent table may then generate a child, which is created in the subtray. It is also possible to implement a complete row limit by reassignment of the sub-table.
The key value modification operation of the parent table is only available when the value of the foreign key corresponding to the subthech is modified to a new value. It is actually possible to create a new parent table record, then modify all the records of the sub-table, which is associated with the new record of the parent table, and finally delete the original parent table record.
The deletion of the parent table can only delete the record in the parent table after all the relevant rows in the child table delete or reassign, and the general pair subtaby is also deleted.
Forced forced (M-M) constraint
At four types of constraints: M-M, M-O, O-M, O-O. The modification of the key value may change the relationship between the tables and may violate some constraints. The operation of violating the constraint is not allowed. Specific applications must be appropriately selected according to the actual requirements and business rules. But when designing and developing, you must consider the analysis of the analysis.
Physical map organization
The organization of database physics is organized as well, which makes it easy to understand which tables are required. How data is flowing, but according to this organization, some reference can be established, and in fact, some references can not be established, such as When writing a table, the source of its data is obtained from another query, ensuring the correctness of the data, and organizes the physical map from functional division, and can not be established.
Database generation
With a database physical figure, you should pay attention to the following questions when generating a database or database script:
The implementation of the reference integrity can be implemented in declarative reference implementation or trigger, as for the advantages and disadvantages of the two implementations, the previous is discussed, here only one point, if the trigger is used to realize the need to regenerate the trigger after generating the database.
When referring to Cascade is deleted or modified, it is dealt with:
Sybase, SQL Server does not support declaration level (CASCADE) deletion or modification, can only be implemented by triggers (TRIGGER).
Oracle, Informix supports declaration level (cascade) delete, but does not support
Cascade modifications can only be implemented by triggers.
When defining a user-defined type, it is best to convert to a database base type when generating a database, which is advantageous for database performance and migration. Data Dictionary
Data Dictionary As an archived document of the product, it defines all aspects of the application system database. After the database physical model is built, you can generate a data dictionary, the contents and forms of the data dictionary can be defined in the PowerDesigner definition template, and the data dictionary is generated according to the template, and then processes the document format.
Generate a data dictionary in PowerDesigner with CREATE Report.
Currently, the system needs to handle and optimize
Primary key definition:
Since the depth of the business is insufficient, some of the main keys of some tables have some problems, and the business is gradually improved.
Reference establishment:
The imperfection of primary key has led to the imperfect reference establishment, which can only be imperfect later.
Database of the database:
The database has been talked before, because this division affects the server and client program, can only be solved in the new version later.
Table structure adjustment:
For some tables, such as the system setting table, it can be changed to the longitudinal structure, which increases the flexibility of the system.
