Developer's Tip

Getting the most from the Designer/2000 Database Design Utilities

Please note that while the suggestions below use Designer/2000 v1.3.2 terminology, this tip may also be applied to Designer/2000 v2.1.x.  For example, Database Design Wizard (v1.3.2) and Database Design Transformer (v2.1.x) may be used interchangeably.  Where significant differences in functionality or terminology exist between the two versions, this will be noted with a.

---

In an Oracle Designer/2000 development project, the analyst transforms the "logical" entity relationship model into a "physical" database model using the Database Design Wizard.  The DDW:

1. Is meant to be executed once, not multiple times in an iterative fashion.  To get the most benefit from the DDW, the analyst must describe the application's data requirements at the logical level as much as possible before creating the physical model, especially the first time.
   
   In Designer/2000 v2.1.x, this limitation has been lifted and it is possible to run the Wizard (or the Transformer in v2.1x parlance) multiple times.  Although this works very well for minor data model changes such as adding a column or index, when multiple tables and foreign keys are involved the resulting model, after being "transformed" by the Transformer, often requires manual cleanup and customization.
   
   
2. Is designed to create initial or 'first cut' database designs.   Modifications to the physical model will always be necessary after the DDW has executed.  For example, if the application requires server derived columns such as sequence numbers and date stamps (which most do), this can only be specified after the DDW has executed, in other words, at the physical level against specific database columns. 

It is crucial to thoroughly describe the Entity Model before the utility is run.  This will save the analyst time when development moves to the design stage and modules for screens and reports are being created.  Below is a checklist of the areas an analyst should examine before running the DDW.  The savvy reader may note that many of the areas described below can also be specified in the physical model after the DDW has executed.  While this is true, the purpose of the DDW is to transform the logical model into a physical model using the information that has been collected and entered at this point.  The advantage of specifying the information now is that the DDW will automatically populate areas in the physical model, saving time and effort.

  DDW Checklist

1. If Domains are being used, make sure to execute the 'Update Attributes in Domain' utility before running the DDW.  Certain modifications made to a domain after it has been assigned to an attribute are not automatically inherited by attributes that use that domain (DEFAULT value, for example).   Domains are used by the application generators for defining valid value constraints, for example, a poplist containing a finite list of values.  In addition, Domains are used to enforce standard definitions among common attributes in different entities, e.g. all LAST NAME attributes are defined as VARCHAR2 with a length of 30.  Also, if Check Constraints have not been explicitly defined during the Design Stage, the SQL DDL Generator can use Domains to generate Check Constraints provided the 'Valid value constraints' checkbox in 'Options' tab is checked.
   
   
2. Check the Unique Identifier (UID) definitions in the entities.  Ensure that when a foreign key relationship forms part of the Primary Key, that the relationship is included in the UID definition for the entity.  A classic example is a parent-child relationship in which a 'one to many' relationship exists between the two entities.  When the relationship forms part of the Unique Identifier (and you have included this relationship in the UID Definition), this will be visually represented in the Entity Relationship Diagram by a line drawn at the base of the "crows foot" at the 'many' side of the relationship.  If a UID is not specified for an entity, the DDW will automatically create one (with an associated Oracle sequence).
   
   
3. Do not record Foreign Key Relationships as attributes in the related entity.  This is a departure from other modeling tools such as Erwin in which foreign key columns do appear on the Entity Relationship Diagram.  The DDW will create foreign key columns in those entities at the 'many' end of the relationship.  If the foreign key is specified as an attribute of an entity which also has a foreign key relationship defined for it, then the foreign key column will be duplicated.  Also be sure to scrutinize the 'Optionality' of the relationship at the 'many' end of the entity.  The optionality indicates whether the columns that migrate to the foreign key table, i.e., the 'many' end of the relationship, are optional or mandatory.
   
   
4. Inspect the 'Transferable' checkbox of the relationships (the default is checked).  A relationship that is non-transferable will not allow the foreign key valued to be updated, i.e., assigned to a different Parent Entity.  For example, a typical example of a non-transferable relationship would be a Sales Order and Line Items relationship.  Each particular Line Item can only be associated with one Sales Order throughout its entire life.  The non-transferable relationship will be indicated visually on the Entity Relationship Diagram by a small diamond at the 'many' end of the relationship.  Many relationships, however, are 'Transferable'.  This means that the foreign key value can be assigned to another Parent Entity.  For example, an Employee may be assigned to only one Department at a time but is free to move from one Department to another over time.
   
   
5. Check the Plurals and Short Names for Entities.  The Plurals will become the table names and Designer/2000 will automatically generate Plurals and Short Names for Entities if they're not specified.  Even though it does a good job of making Entities plural (even those ending in 's'), its a good idea to check them over thoroughly. Also check the Short Names generated by Designer/2000.  The Short Names will be used for aliases in SELECT statements in the generated application.  If the names that Designer/2000 generated are not satisfactory, change them here.
   
   
6. By default both the Database Design Wizard and the Database Design Transformer will prefix foreign key columns with the entity name from which they migrated.   This can result in long, undesirable column names for foreign key columns.   In the Options tab of the DDW, make sure that the 'Prefix Foreign key columns' checkbox and the 'Prefix surrogate key columns' checkbox in the 'Naming Conventions' area are not checked.
   
   For Designer/2000 v 2.1.x users, this option can be found in the 'Settings' tab, under 'Other settings' in the Database Design Transformer dialog window.  Ensure that under the 'Elements that you want prefixes generated for' heading that the 'Foreign key columns' and 'Surrogate key columns' checkboxes are not checked.  Surrogate keys are generated by Designer/2000 automatically if no Unique Identifier is defined for an Entity.
   
   
7. In the Options tab of the DDW, check that the 'Constraint implementation level' is set to Both and not just Client or Server.  Do this if you want to generate application logic in addition to database logic for Primary and Foreign key constraints.  By specifying Both, validation logic for enforcement of constraints will be created in both the client application and in the database.  You won't see the result of this option in the database design itself but rather in the generated application code.  For example, in Developer/2000 Forms, WHEN-VALIDATE-ITEM triggers will be generated to enforce constraints at the client.  For Oracle Webserver, client-side Javascript code will be generated.
   
   For Designer/2000 v2.1.x users, this option is called 'Implementation level for constraints' and can be found under 'Settings' in the 'Keys' tab of the Database Design Transformer dialog window.
   
   
8. If attributes have Default Values, be sure to specify them either in the Domain or directly to the attribute.  Default Values will be implemented in the SQL DDL, e.g., COLUMN1  VARCHAR2(3) DEFAULT 'ZZZ'.  Although Default Values is intended to store fixed values, Designer/2000 will recognize SYSDATE for the Default Value.   In addition, the application generators use Default Values to insert default values into form fields.
   
   
9. Specify Volume characteristics, in particular the number of Start Rows and End Rows.  These will be used to determine sizing estimates for use with the 'Database Table and Index Sizing' Repository Report.  For those rows where an End value is not known or cannot be estimated, you can specify the 'Average Growth'.  Even though this field is not used by the 'Database Table and Index Sizing' Repository Report it is useful documentation for the DBA in sizing the Tables and Indexes.
   
   Designer/2000 v2.1.x now allows multiple 'block sizes' to be input to 'Database Table and Index Sizing' utility.   Previously, in v1.3.2, a 2K block size was hard coded into the report, rendering the utility much less useful if a block size of 4K or larger were needed.
   
   
10. Thoroughly describe entities with descriptions.   Descriptions, by default, will be transformed into the 'User Help Text'  Table property in the database design.  This property is used by the application generators to create context sensitive online help so be sure to make it meaningful to end users.  The Notes field provides an additional place to describe an entity or include technical notes for developers, however, this field is not used by the application generators.
    
    
11. Describe the attributes with descriptions and comments. Descriptions, by default, will be transformed into the 'User Help Text' Column property in the database design.  As with entities, this property is used by the application generators to generate context sensitive online help so again, be sure to make it meaningful to end users.  Comments will be used for item level Hint Text in generated applications so be sure to make the Comment meaningful and short (less than one line).  In addition, the SQL DDL generator will use Comments for column comments in the database. The Notes field provides an additional place to describe an attribute or include technical notes for developers, however, this field is not used by the application generators.8  return to Developer's Corner