Powerdesign Advanced Application Design Database specification __ Database

Powerdesign Advanced Applications

1, remove the Oracle generated SQL creation statements in double quotes
When you export the table SQL for a Orale database with PowerDesigner, the table name and field name are enclosed in double quotes, as shown in the following figure:

This is a great inconvenience to the operation of the database, the solution is to set database menu,

Then click on the edit current DBMS menu, turn on Script->format, and then find Casesensitivityusingquote
Set it to no, you can. The following figure:

If you have a package, select the table in the package when you export.

2, Powerdesign advanced application
Write the relevant VBS scripts in powerdesign custom commands and operations, and so on, you can refer to the C:/Program files/sybase/powerdesigner 9/vb Scripts Directory of the script sample. How to use these scripts.
You can operate in Tools->execute commands. Specific instructions in the help to write very clearly.  Position of help in PowerDesigner General Features guide-> Part 2.  Modeling Guide->chapter 8. Managing objects->accessing Objects using Vbscript->vbscript uses in PowerDesigner

The use of Powerdesign is primarily the configuration of DBMS
3, modify the Build table script generation rules.
If each table has the same field, you can modify it as follows:
The Database-> Edit current DBMS expands the Script-> Object-> Table-> Create See the value of the lower right, which can be directly modified as follows:

/* TableName:%tname% * *
CREATE TABLE [%qualifier%]%table% (
%tabldefn%
TS char (a) NULL default convert (char (), GETDATE (), 20),
Dr smallint null default 0
)
[%options%]

The TS, Dr Two columns are automatically inserted into each table when the SQL script is generated, where the%tname% variable is to add a name value annotation for each table's SQL.

4, modify the field generation rules .
To add a comment to each field, the value of the expand Script-> Object-> Column-> Add in the same window is modified to:

%20:column% [%compute%?as (%compute%):%20:datatype% [%identity%?%i dentity%:[%null%][%notnull%]][default%default%]
[[Constraint%constname%] Check (%constraint%)]]/*%colnname%*/

The%colnname% is the name value of the column (Can be Chinese)

5, modify the foreign key naming rules.
Select Database->edit Current DBMS
Choose scripts-"objects-" reference-"Constname
You can see that the value on the right is:

fk_%. u8:child%_%. u9:refr%_%. u8:parent%

Visible, the naming method is: ' Fk_ ' + 8-bit child table name + 9-bit reference name + 8-bit parent table name, which you can customize according to this pattern:

fk_%. u7:child%_relations_%. u7:parent%,

You can change the FK name to Fk_table_2_relations_table_1
After mastering this method, you can revise it according to your own ideas.

Generating the header comments in the Build Library script SQL file is annoying, you can select the options card in the databse-> Generate Database (ctrl+g) window and remove the usage title Hook option.

6. Add foreign key
Model-> references New foreign key, double-click into the foreign key properties, in the "joins" card can select the Foreign key field of the child table. The following figure:

Then the following screen appears:

Follow the steps.

7, cancel name and code linkage
When you modify name, the value of the code will change, which is inconvenient. Modification method: Powerdesign In the Options menu in the [tool]-->[general options]->[dialog]->[operating modes]->[name to Code Mirroring], where the default is to synchronize the name and code, the previous check box to remove the line. Figure:

 
Writing related VBS scripts to customize some commands and operations in Powerdesign, the specific reference can be C:/Program Files/sybase/powerdesigner 9/vb Examples of scripts in the scripts directory. How to use these scripts. The
can be manipulated in tools-Execute commands. Specific instructions in the help to write very clearly. Help location in   PowerDesigner General Features guide-> part 2.  modeling guide->chapter 8.  managing Objects The use of the->accessing objects using Vbscript->vbscript uses in PowerDesigner

Powerdesign is primarily the configuration of the DBMS
1, Modify the Build Table script generation rule. If each table has the same field, you can modify it as follows: The
Database-> Edit current DBMS expands the Script-> Object-> table-> Create See the value of the lower right, you can directly Modified as follows:

/* TableName:%tname% * *
CREATE TABLE [%qualifier%]%table% (
%tabldefn%
TS char (a) NULL default convert (char (), GETDATE (), 20),
Dr smallint null default 0
)
[%options%]

The TS, Dr Two columns are automatically inserted into each table when the SQL script is generated, where the%tname% variable is to add a name value annotation for each table's SQL.

2, modify the field generation rules. To add a comment to each field, the value of the expand Script-> Object-> Column-> Add in the same window is modified to:

%20:column% [%compute%?as (%compute%):%20:datatype% [%identity%?%i dentity%:[%null%][%notnull%]][default%default%]
[[Constraint%constname%] Check (%constraint%)]]/*%colnname%*/

The%colnname% is the name value of the column (Can be Chinese)

3, modify the foreign key naming rules. Select Database->edit Current DBMS
Choose scripts-"objects-" reference-"Constname
You can see that the value on the right is:

fk_%. u8:child%_%. u9:refr%_%. u8:parent%

Visible, the naming method is: ' Fk_ ' + 8-bit child table name + 9-bit reference name + 8-bit parent table name, which you can customize according to this pattern:

fk_%. u7:child%_relations_%. u7:parent%,

You can change the FK name to Fk_table_2_relations_table_1
After mastering this method, you can revise it according to your own ideas.

Generating the header comments in the Build Library script SQL file is annoying, you can select the options card in the databse-> Generate Database (ctrl+g) window and remove the usage title Hook option.

4. Add foreign key
Model-> references New foreign key, double-click to enter the foreign key properties, in the "joins" card can select the child table foreign key field

5, remove the generated SQL script double quotes: ORACLE 8i2::script/sql/format/casesensitivityusingquote change to No, the default is yes so there will be double quotes. When you modify name, the value of the code will change, which is inconvenient. Modification method: Powerdesign In the Options menu in the [tool]-->[general options]->[dialog]->[operating modes]->[name to Code Mirroring], where the default is to synchronize the name and code, the previous check box to remove the line.

PowerDesigner Design Database Specification

----First draft by Rojun

Demand:

Although our database design model can meet the development requirements, but also save the development time, but this design will be the maintenance of the project after the revision of the cost of the increase. The database table field will affect the main program on the modification, and for development and maintenance personnel, the field encoding also needs to be normalized, so that the field encoding can be unified, so that the maintenance staff to understand, the addition of the field for later to take over the project is easy to use.

To address these issues, I suggest that data development needs to define a concise set of specifications. The specification includes three parts: one is the code specification, the other is the definition of the field type length, and the third is the special description of the table design.

Design:

Database development process Oracle: In PowerDesigner, we mainly design the physical data model (PDM file), we need to define a project's data field fields, the definition of field has one advantage is that when designing a datasheet later, the type of field can be chosen with the type defined in the field. Changed the type information for the field, and the field information associated with the field reference in the table changes accordingly, which facilitates the modification of the field and also unifies the field type and length.

Once you have defined the fields, you can design the data tables and fields. Design the table, you can select the type of database exported, and generate data and build SQL, and then through the database connection tool to execute SQL statements, a project database is established. Later, as soon as there is a database modification requirement, the SQL statement is rebuilt and executed. Note: Before you rerun SQL, you need to decide whether to keep the data from the original table, or you can only modify it with the alert statement, otherwise the data for the original table will be flushed out.

Diagram I database design flowchart

As shown in figure I in the database design flowchart, if there are modifications, we only need to repeat the three steps indicated in arrow 2.

Some of the requirements in the database design process are described below.

1 Coding Specification

Coding is mainly to pay attention to the prefix of the encoding, in order to see the encoding to know what type.

A. Primary key Pk_ [string or Long integer] For example: Pk_company

B. Foreign key Fk_ [string or Long integer] For example: Fk_employee

C. String V For example: Vdetail

D. Character C For example: Ccolor

E. Time D For example: Ddatetime

F. Logic B For example: bisdeleted

G. Integral I for example: Itype

H. Digital (floating-point row) N For example: Nprice

I. Text type T for example: tdescription

J. Object Type O For example: opicture

When encoding each table and field, try to keep the length of the code within 20 characters, and the coding requirements are meaningful and understandable.

2 field type length definition

Primary key, foreign key if it is a string line, it is recommended to use a length of 20 characters. The length of the string is generally defined as 20, 30, 50, 100, 256, 512, 1024, 2048, 4096, and more than 4,096 characters can be suggested by text type. Defining the length of a field is primarily used to define the field length of the field, thus standardizing the type and length of the table fields.

3 Special description of table design

In general, if a record in a table is a fake deletion, then each table requires a uniform delete field. In addition, the Time field in general each table also required to appear, so that the design can consider the current or later to do multithreading access control judgment (that is, allow more read, but not allow more write), there may be a class of tables have a fixed required field requirements , such requirements can be added uniformly using powerdesigner. In addition, each table suggests that only the primary key Non-null, the rest of the field can be empty, the field of the Non-null judgment to process, not the database to judge.