PowerDesigner Design Database

Design | data | database






PowerDesigner Design Database















Li Weihua





September 2000











Description: This document for my experience accumulated income, non-departmental design documents (All rights reserved)











This document does not describe how to use PowerDesigner, but rather describes how to better design a database system by combining the characteristics of PowerDesigner with the database design method.
Using PowerDesigner Design Database
As one of the case tools for database modeling and design, PowerDesigner plays an important role in the development of database system.

Using PowerDesigner to design the database, not only gives people an intuitive understanding of the model, but also makes full use of the database technology to optimize the database design. PowerDesigner supports a variety of database systems such as Sybase, Oracle, Informix, SQL Server, and so on, without having to maintain multiple database scripts when the application system does a database migration.

For the use of structured analysis (SA), E-R diagram, data flow diagram to the final database physical map is an indispensable part of the system design, when the database physical map is completed, the system should produce a dictionary. Using PowerDesigner can complete this design process completely.

For object-oriented analysis (OOA), because the database is based on RDBMS, there are mappings between objects and relational databases, and database design is also needed.
Two types of database models
PowerDesigner can design two kinds of database model diagrams: Database logic diagram (E-R diagram or concept model) and database physical diagram (physical model), and the two database diagrams are inverse.

The database logic diagram is a kind of abstraction to the real world, which embodies the relationship between entities, and can have 1 pairs of 1, 1 pairs of many, many pairs of relations. In particular, in an expanded E-R diagram There is a generalization of this relationship, which embodies a subset of types, which defines superclass and subclasses. In the E-R diagram of PowerDesigner design, this relationship is not available, but it is supported in the model of E-rwin design, so the relationship is paid attention to when the model of E-rwin diagram is converted to PowerDesigner model.

The physical realization of the logical model in the database physical diagram embodies the reference relationship between the tables. It is impossible to have a many-to-many relationship in a physical model. When a logical graph is converted to a physical diagram, the relationship between Many-to-many and Many-to-many becomes two 1 pairs.

There is a close relationship between logical model and physical model, and there are essential differences. The design of the logical model follows the third normal form of the database design theory (in the general application of the database to reach the third normal form), the logical model requires all the information expressed by the application system and eliminates data redundancy. The physical model is based on the logic model, in order to optimize the performance of the application system, the use of additional redundancy, the creation of index database technology, it mainly uses some non-standard theory.

Before considering any non-standard design, the database should be fully normalized before it can be normalized without fully understanding the data and user requirements. Otherwise the organization of the data becomes more and more confusing and the application becomes more complex.

Therefore, the logical model and the physical model are contradictory and closely related, which requires designers to grasp.
PowerDesigner Design Database Physics diagram
Design a database physical diagram with PowerDesigner, including multiple objects, such as tables (table), fields (Column), domains (domain), and so on. The design is mainly in the PowerDesigner dictionary and database two menus.
Tables (table)
A table is a logical object of data storage, including other objects such as fields (Column), index, Trigger (Trigger), stored procedure (Procedure) and so on, table optimization design has the technology of segmentation, for table storage, if the amount of access to data is large, If you have a high frequency of access, consider placing the table on different storage (Storage).

When designing a table, you should estimate the size and growth of the table to make it easier to allocate database null keys when you create the database, thus reducing the production of disk fragmentation.

When designing a primary key in a relational database, using a meaningful primary key is a fatal error. If the user decides to change the business meaning of the field, it needs to be modified where all the information is used. The primary key is to remain unique and used as a foreign key. Any modification of the primary key can result in a huge database maintenance effort, which is clearly an inappropriate design. As far as relational databases are concerned, the primary key strategy is designed using the Proxy primary key method.

The "hotspot" phenomenon should be avoided when designing primary keys, but it is also necessary to analyze the concurrent users of specific application systems.
Field (Column)
Defines a field with a field name, field type and length, whether it is a primary foreign key, whether it is empty, constraints, default values, fields, and so on.

Variable length and fixed-length data types are discussed in database design, as a general principle, if the range of data in a column is expected to vary greatly, but the change is not frequent, then it is most appropriate for such columns to use variable-length data types.

When deciding the president, neither too wasteful nor too stingy. Taking into account future needs and realizing that if you increase the length of the president without changing the number of rows that are accommodated in one page, the additional space is equivalent to free use.

At design time, fields are used as much as possible to maintain the type of fields. It is best to add a default value for each field, because a null value in a database query can affect the performance of the query.

You can restrict the value of a field with a check constraint.
Domains (Domain)
Simply put, it is a user-defined type, but the domain can also define its range or default value, using a domain to reduce the amount of effort to maintain field types, and to reduce inconsistencies in data.
Reference (Reference)
Reference is a complicated problem in database design, it is one of the main elements to realize data integrality, and the constraint of reference data is discussed in detail.

In PowerDesigner, referential integrity can be set to the base of references from 0 to N, and the modification and deletion constraints can be set to None, Restrict, Cascade, set Null, set Default, respectively. Because insert is included in the update operation, there is no separate insert constraint.

The different settings of the constraint produce different effects to modify as an example (delete the same):

None: The parent table is modified and the child table does not affect.

Restrict: The parent table is modified and an error occurs if the child table exists.

Cascade: Parent table modification, if child table exists, modify accordingly.

Set null: The parent table is modified, and if the child table exists, the corresponding null is placed.

Set default: The parent table modifies the default value if the child table exists.
Indexes (Index)
Index is a kind of database technology in optimizing query, Index has clustered index, nonclustered index, unique index and so on.

When designing an index, be aware of the index width and minimize the width of the index. The width of the index is determined not by the number of fields, but by the length of the field. For narrow index keywords, more keywords and pointers are placed on each index page, which can cost less I/O to find the data.

For composite indexes, it is important to select the first column, or you may not be able to take advantage of the index when querying with a composite index. You must make sure that the query starts with the first column.

The index also has a fill factor (FillFactor), which depends on the amount of data growth of the table and the definition of the primary key.
Triggers and stored procedures (trigger&&procedure)
Triggers play an important role in maintaining data integrity, and they are more flexible than references,

It can also implement the business rules of the data layer in the three-tier structure.

Stored procedures are scripts written with SQL and Process Control statements to complete a business. The stored procedure has the advantages of fast processing speed and flexible processing in data processing.

However, the stored procedure greatly increases the coupling with the database, which requires rewriting the stored procedure when the database migrates, thereby increasing the workload of version maintenance. If the database requires migration considerations, you should avoid using stored procedures or triggers as much as possible.

If the PowerDesigner trigger is not modified, its mobility PowerDesigner is resolved automatically.
Storage (Storage)
Different databases have different concepts, Sybase is called a device (Device), SQL Server is called a file or filegroup (file, FileGroup), and Oracle is called a tablespace (tablespace).

According to the system to create one or more storage, according to a certain optimization rules to store.
The Division of the database
The classification of database is based on the principle of its physical distribution, but not on the principles of data Quantity, table type and so on, the database has little influence on the performance of database. For a large amount of access data, for frequently accessed tables, I/O operations can easily create serious bottlenecks, so reducing I/O operations and blocking of I/OS is a major concern in database design, where solutions are placed on multiple devices, and devices are created on different physical drives, preferably in smart or array mode.

Logs and data are stored separately on different devices, and can be used in this way if there are many indexes and occupy a large amount of space.

The number of database maintenance costs is much smaller than the quantity.

Therefore the database division takes the physical distribution as the principle.

PowerDesigner provides a way to compute a database or table (Compute database Size) to help designers complete the partitioning of databases.
Integrity of the database
Database integrity can be achieved through stored procedures, declarative referential integrity (DRI),

Data types, constraints, rules, default values, and triggers to implement. Within the database, these functions work in a unique way. Comprehensive utilization of these integrity features enables the database to be flexible, manageable, and secure.

The concept of data integrity is divided into several aspects.

Table Domain Integrity

Enforces the table's domain integrity through primary keys.

Referential integrity

Use references to strengthen the logical relationship between tables.

Numerical domain integrity

Any entered data must match the specified data type in type and scope, allowing NULL to be entered into the column only if a column is described to allow null values.
Performance testing of the database
After the database is generated, database performance tests should be performed to optimize the design of the database, so that test data needs to be generated, and because of performance testing, the data is not prescriptive. The PowerDesigner makes it easy to generate test data (Generate test) to complete performance testing.
Constraint o-o constraint of data
There are no restrictions on insert, UPDATE, and delete operations on the parent table.
M-o constraints
Constraints on the parent table operation:

Insert operation of the parent table, for M-o constraints, records in the middle of the parent table can be added to the table without any constraints, because the constraint does not necessarily have to have children.

The key value modification operation of the parent table can only be modified if all of its children in the child table are modified, that is, a cascade Update method is generally used.

Deletion of the parent table, the father can be deleted only after all his children have been deleted or reassigned.

Force an optional (M-O) constraint
o-m constraints
Constraints for parent table operations:

Insert operation of parent table, constraint on o-m, a father can only be joined if at least one of its children is joined at the same time or at least one legal child is present.

The key value modification operation of the parent table, only if a child is created or if a child is already present.

The deletion of the parent table, theoretically deleting the father is unrestricted, in fact, deleting a primary table record does not take a cascade deletion of the child table, but instead uses the foreign key of the child table to empty.

Optional pair coercion (O-M) constraint


m-m constraints
Constraints for parent table operations:

The insert operation of the parent table may then need to generate a child, that is, to create a new row in the child table. It is also possible to enforce full row limits by reallocating the child tables.

The key value modification operation of the parent table, which can only be performed if the value of the foreign key corresponding to the child table is modified to a new value. The actual possibility is to create a new parent table record, then modify all the corresponding records of the child table to associate it with the record of the parent table, and then delete the original parent table record.

The deletion of the parent table, only after all related rows in the child table have been deleted or reassigned, can the records in the parent table be deleted, and the general subdatasheet also be deleted.

Force force (M-M) constraint



In class four constraints: M-m, M-o, O-m, O-o. Modifying a key value may change the relationship between tables and may violate some constraints. The operation that violates the constraint is not allowed. The specific application must be based on the actual requirements and business rules of the appropriate choice. However, when designing and developing, you must consider the constraints that are analyzed.
Organization of the physical diagram
The organization of database physics is organized to function well, it is easy to understand which tables the function needs to operate, and how the data flows, but by this organization, some references may be created in a messy way, but some references may not have to be established, for example, when writing a table, the source of the data is obtained from another query, can guarantee the correctness of the data, from the functional division to organize the physical map, you can not establish this reference.
Building a Database
With a database physical diagram, you should be aware of the following issues when generating database or database scripts:

The implementation of referential integrity can be implemented by declarative references or triggers, and the pros and cons of both implementations have been discussed before, and here's just a point where a trigger implementation needs to be generated after the database is generated.

When a reference contains a cascade (Cascade) deletion or modification, its implementation is handled in a separate case:

Sybase, SQL Server does not support declarative cascading (Cascade) deletions or modifications, but only through triggers (Trigger).

Oracle, Informix supports declarative cascading (Cascade) deletes, but does not support

Cascading (Cascade) modifications can only be implemented through triggers (Trigger).

When a user-defined type is defined, it is best to convert to a database base type when building a database, which is beneficial for both database performance and migration.
Data dictionary
A data dictionary is an archived document of a product that defines various aspects of the application system database. After the database physical model is built, the data dictionary can be generated, the content and form of the data dictionary can be defined in the PowerDesigner template, the data dictionary is generated according to the template, then the document format is processed.

The PowerDesigner is used to generate a data dictionary using the Create.
Where the current system needs to be addressed and optimized
Definition of PRIMARY key:

Due to the depth of the business understanding is not enough, some of the table's primary key to establish some problems, with the depth of the business gradually improved.

Establishment of reference:

The definition of primary key is imperfect, which leads to the imperfect of the reference establishment, which can only be imperfect after the group.

Classification of databases:

The division of the database has been discussed before, because this division affects the server and the client's program, can only be solved later in the new version.

Adjustment of Table structure:

For some tables, such as system setup tables, you can change its horizontal structure to a vertical structure, which increases the flexibility of the system.