Powerdesigner Design Database

This document does not describe how to use powerdesigner, but describes how to design a database system better by combining the features of powerdesigner with database design methods.

Use powerdesigner to design databases

As one of the Case tools for database modeling and design, powerdesigner plays an important role in database system development.
Using powerdesigner for database design 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 multiple database systems, such as Sybase, Oracle, Informix, and SQL server. You do not need to maintain multiple database scripts during database migration in the application system.
For the use of structured analysis (SA), E-R diagram, data flow diagram until the final database physical diagram is an indispensable part of the system design, when the database physical diagram is complete, the system data dictionary should be generated. This design process can be completed with powerdesigner.
For the use of Object-Oriented Analysis (OOA), because the database uses RDBMS, there is a ing between the object and the relational database, it also needs to be designed.

Two database models

Powerdesigner can design two types of database model diagrams: Database logic diagram (E-R diagram or conceptual model) and database physical diagram (Physical Model), and these two database diagrams are reciprocal.
The database logic diagram is an abstraction of the real world, reflecting the relationship between entities. It can have 1-to-1, 1-to-many, and many-to-many relationships. Specifically, there is a generalization of this relationship in an extended E-R graph that represents a seed set link between types, which defines the superclass and subclasses. This relationship is not available in the E-R diagram designed by powerdesigner, but is supported in the model designed by e-rwin, therefore, pay attention to this relationship when converting the model designed with e-rwin diagram to the powerdesigner model.
The physical graph of the database is the physical implementation of the logical model, reflecting the reference relationship between tables. There cannot be many-to-many relationships in the physical model. When a logical graph is converted to a physical graph, the multi-to-many relationship becomes two one-to-many relationships.
Logical and physical models are closely linked and essentially different. The design of the logical model follows the third paradigm of the database design theory (only when the general database application reaches the third paradigm). The logical model requires that all information expressed by the application system be used and data redundancy be eliminated. The physical model is based on the logic model. In order to optimize the performance of the application system, database technologies such as adding redundancy and creating indexes are used. It mainly uses non-standardized theories.
Before designing any non-standardization, the database should be fully normalized, and data and user requirements should not be fully understood. Otherwise, Data Organization becomes increasingly chaotic and ApplicationsProgramMore and more complex.
Therefore, the logical and physical models are closely related to each other, which requires the designers to grasp.

Powerdesigner design database physical Diagram

Use powerdesigner to design database physical diagrams, including multiple objects, such as tables, columns, and domains. It is designed mainly in the dictionary and database menus of powerdesigner.

table
A table is a logical object of data storage, including other objects such as columns, indexes, and triggers) and procedure. The table optimization design is divided into other technologies. For Table Storage, if a large amount of data is accessed, if the access frequency is high, you can consider placing tables on different storage.
when designing a table, you should estimate the table size and growth volume to facilitate the allocation of database null keys during database creation, which reduces the generation of disk fragments.
when designing a primary key in a relational database, using a meaningful primary key is a fatal error. If you decide to change the commercial meaning of a field, you need to modify it in all the places where the information is used. The primary key must be unique and used as a foreign key. Any modification to the primary key will lead to a huge workload for database maintenance, which is obviously inappropriate. For relational databases, the primary key policy is designed using the proxy primary key method.
when designing a primary key, you should avoid the "Hotspot" phenomenon, but you also need to analyze the concurrent users of the specific application system.

Field (column)
Define a field mainly including the field name, field type and length, whether it is the primary foreign key, whether it is null, constraints, default values, fields, etc.
Data Types with Variable Length and fixed length are discussed in the database design. As a general principle, if the data range in a column is expected to change greatly but not frequently, then it is most suitable for such columns to use variable-length data types.
It is not a waste of time, but a pity. Considering future needs, we are aware that if we increase the number of rows on a page without changing the number of rows, the added space will be free of charge.
During design, fields should be used as much as possible to maintain the field type. It is best to add the default value for each field, because a null value in the database query will affect the query performance.
You can use the check constraint to restrict the value of a field.

Domain)
To put it simply, it is a user-defined type, but the domain can also define its value range or default value. Using a domain reduces the workload of maintaining the field type and reduces data inconsistency.

Reference)
Reference is a complex problem in database design. It is one of the main elements of data integrity. For details, refer to data constraints later.
In powerdesigner, you can set the integrity of the reference. The base number of the reference ranges from 0 to n, you can set the modification and deletion constraints to none, restrict, cascade, set null, and set default. Because insert is included in the update operation, there is no separate insert constraint.
Different settings of constraints have different effects. For example, modify the settings (delete the same settings ):
None: the parent table is modified, and the child table is not affected.
Restrict: Modify the parent table. If the child table exists, an error occurs.
Cascade: Modify the parent table. If the child table exists, modify the table accordingly.
Set NULL: Modify the parent table. If the child table exists, it is set to null.
Set Default: Modify the parent table. If the child table exists, set the default value accordingly.

Index)
Index is a database technology used to optimize queries. indexes include cluster indexes, non-cluster indexes, and unique indexes.
When designing an index, pay attention to the index width to minimize the index width. The index width is determined not by the number of fields, but by the length of the field. For narrow index keywords, place more keywords and pointers on each index page, so that you can spend less I/O to find data.
It is very important to select the first column for a composite index. Otherwise, you may not be able to use this index. Make sure that the query starts from the first column.
The index also has a fillfactor. The size of the fill factor depends on the table's data growth volume and primary key definition.

Trigger and stored procedure (trigger & procedure)
Triggers play an important role in maintaining data integrity. They are more flexible than references, and can also implement business rules at the data layer in a three-tier structure.
A stored procedure is a script that uses SQL statements and flow control statements to complete a certain business. Stored Procedures have the advantages of fast processing speed and flexible processing in data processing.
However, the storage process greatly increases the coupling with the database. During database migration, the storage process needs to be rewritten to increase the version maintenance workload. If the database requires migration, avoid using stored procedures or triggers whenever possible.
If you do not manually modify the trigger of powerdesigner, its migration capability is automatically solved by powerdesigner.

Storage)
Different databases have different concepts. Sybase is called a device, SQL Server is called a file or a file group, and Oracle is called a table space ).
One or more storage types are created based on the system and stored according to certain optimization rules.

Database Division
The database should be divided based on its physical distribution, rather than the data volume and table type. The database size has little impact on the database performance. For tables with large data volumes and frequent access, I/O operations can easily cause serious bottlenecks, therefore, reducing I/O operations and blocking I/O operations are the primary concerns of database design. The solution is to place the tables on multiple devices, devices must be created on different physical drives. It is best to use smart devices or arrays.
Logs and data are stored separately on different devices. This method can also be used if there are many indexes and the occupied space is large.
The maintenance cost of a database is lower than that of a database.
Therefore, database division is based on physical distribution.
The compute database size (compute database size) provided by powerdesigner can help designers divide databases.

Database integrity
Database integrity can be achieved through stored procedures, declarative referential integrity (DRI), data types, constraints, rules, default values, and triggers. In the database, these functions play a unique role. The comprehensive use of these Integrity functions can make the database flexible, easy to manage, and very secure.
The concept of data integrity is divided into several aspects.
◆ Table domain integrity
Use primary keys to enhance the domain integrity of tabulation.
◆ Integrity of reference
Use references to enhance the logical relationship between tables.
◆ Integrity of numeric Domains
Any input data must match the specified data type in the type and range. null is allowed only when a column is specified to allow null.

Database Performance Testing
After the database is generated, the database performance test should be conducted to optimize the database design. Therefore, the test data must be generated. Because it is a performance test, the normative requirements of the data are not high. Powerdesigner allows you to easily generate test data to complete performance testing.

Data constraints
O-O Constraints
There are no restrictions on the insert, update, and delete operations on the parent table.

M-O Constraints
Restrictions on parent table operations:
Insert operation on the parent table, constraints on the M-O, the records in the parent table can be added to the table without any constraints, because these constraints do not necessarily have children.
The key-value Modification Operation of the parent table can be modified only after all the children of the child table are modified. That is, cascade update is generally used.
The parent table can be deleted only after all its children are deleted or reassigned.
Enforce optional (M-O) Constraints

O-M Constraints
Restrictions on parent table operations:
The insert operation on the parent table, constraints on the O-M, a father can be added only when at least one of its children is added at the same time or at least one legal child exists.
To modify the key value of a parent table, only one child is created or has one child.
In theory, there is no limit on deleting a parent table. In fact, when deleting a primary table record, the cascade deletion scheme is not used, the foreign key of the sub-table is left empty.
Optional Mandatory (O-M) Constraints

M-M Constraints
Restrictions on parent table operations:
The insert operation of the parent table may need to generate children, that is, create a new row in the child table. The full row restriction may also be implemented through the redistribution of sub-tables.
The key value Modification Operation of the parent table can only be performed when the foreign key value corresponding to the child table is changed to a new value. It is actually possible to create a new parent table record first, then modify all corresponding records of the child table, associate it with the new records of the parent table, and finally Delete the records of the original parent table.
To delete a parent table, you can delete records in the parent table only after all the relevant rows in the child table are deleted or re-allocated. Generally, you can also delete records in the child table.
Force a force (M-M) Constraint

There are four types of constraints: m-m, M-O, O-M, and o-o. Modification of key values may change the relationship between tables and may violate some constraints. Operations that violate the constraints are not allowed. Specific applications must be selected based on actual requirements and business rules. However, the constraints analyzed must be considered during design and development.

Physical graph Organization
The physical organization of the database is better organized by functions. It is easy to understand which tables the function needs to operate and how the data flows. However, the Organization may be messy by reference, in fact, some references do not need to be created. For example, when writing a table, the data source is obtained from another query, which ensures the correctness of the data and organizes the physical graph by function division, you do not need to create this reference.

Database generation
With the database physical diagram, you should pay attention to the following issues when generating database or database scripts:
With reference to the implementation of integrity, you can use declarative reference implementation or trigger implementation. As for the advantages and disadvantages of the two implementations, we have discussed earlier. Here we only talk about one point, if the trigger is used, the trigger must be generated after the database is generated.
When you refer to cascade for deletion or modification, the implementation should be handled in different situations:
Sybase and SQL Server do not support cascade deletion or modification. They can only be implemented through triggers.
Oracle and Informix support cascade deletion, but they do not support cascade modification or trigger.
When you define a user-defined type, it is best to convert it to the basic type of the database during database generation, which is advantageous for both database performance and migration.

Data Dictionary
As an archive document of the product, Data Dictionary defines all aspects of the application system database. After the physical database model is created, you can generate a data dictionary. the content and form of the data dictionary can be defined in the powerdesigner template, generate a data dictionary based on the template, and then process the document format.
Use create report in powerdesigner to generate a data dictionary.
Currently, the system needs to be processed and optimized.

Primary Key definition:
Due to lack of in-depth understanding of the business, primary key creation of some tables has some problems. As the business goes deeper, it is gradually improved.

References:
The definition of the primary key is incomplete, resulting in imperfect reference establishment, which can only be imperfect in the future.

Database Division:
The division of databases has been discussed before. As this division affects the programs of servers and clients, it can only be solved in later versions.

Table Structure Adjustment:
For some tables, such as the system setting table, you can change its horizontal structure to a vertical structure, which increases the flexibility of the system.