5 Replies Latest reply: Dec 18, 2009 11:16 AM by Philip Stoyanov-Oracle RSS

    Data Modeler: Naming

    brianray
      OK guys, I'm struggling.

      I've watched the on-line tutorials, read the white papers (especially "Naming Standardization"), and looked through the help pages. There are so many options for controlling naming that I think I'm lost in the trees trying to find the forest. Between the name, short name, synonym, synonym to display, preferred abbreviation, naming rules, glossaries, naming standards, templates, object name prefixes, name abbreviations, name translations, design rules, and classification types, I'm a bit overwhelmed.

      I'd like to take this logical model:
      ---------------------
      | facility category |
      ---------------------
      facility category identifier <PK>
           1:1
            |
            |
            |
           0:M
      ------------
      | facility |
      ------------
      facility identifier <PK>
      facility category id <FK>
      and create the following relational model:
      -------------
      | t_fac_cat |
      -------------
      fac_cat_id <PK>
        1:1
         |
         |
         |
        0:M
      ---------
      | t_fac |
      ---------
      fac_id <PK>
      fac_cat_id <NN,FK>
      which, in turn, generates this DDL:
      create table t_fac ( 
           fac_id integer  not null , 
           fac_cat_id integer  not null ) ;
      
      alter table t_fac add constraint pk_fac primary key ( fac_id ) ;
      
      create table t_fac_cat ( fac_cat_id integer  not null ) ;
      
      alter table t_fac_cat add constraint pk_fac_cat primary key ( fac_cat_id ) ;
      
      alter table t_fac 
          add constraint fk_fac_cat_2_fac foreign key ( fac_cat_id ) 
          references t_fac_cat ( fac_cat_id ) ;
      Note: I could live with the relational model table names not being prefixed with a 't_' as long as the DDL includes it.

      I think the tool has the ability to do what I want, but I'm missing how all the parts fit together to enable me to accomplish my goal.

      Does anyone have a simple approach that might decrease the learning curve a bit?
        • 1. Re: Data Modeler: Naming
          Philip Stoyanov-Oracle
          Well,

          I'll try to explain it in several posts.

          I. Name abbreviations tool
          this is something separate from big picture but can be useful in some cases.

          1. Create a text file containing <name,abbreviation> pairs - name forum.csv and content:
          facility,fac
          category,cat
          identifier,id

          2. Set following template for PK name (it's in Naming standard>Templates):
          pk_{table}

          3. Set following template for FK column name:
          {ref column}

          4. Set following template for FK name:
          fk_{parent abbr}_2_{child abbr}

          2), 3) and 4) are according your example

          5. Create tables (not entities) in relation model using full names, no abbreviations
          following DDL is generated:

          CREATE TABLE facility
          (
          facility_identifier INTEGER NOT NULL ,
          facility_category_identifier INTEGER NOT NULL
          )
          ;



          ALTER TABLE facility
          ADD CONSTRAINT pk_facility PRIMARY KEY ( facility_identifier ) ;


          CREATE TABLE facility_category
          (
          facility_category_identifier INTEGER NOT NULL
          )
          ;

          ALTER TABLE facility_category
          ADD CONSTRAINT pk_facility_category PRIMARY KEY ( facility_category_identifier ) ;


          -- ERROR: FK name length exceeds maximum allowed length(30)
          ALTER TABLE facility
          ADD CONSTRAINT fk_facility_category_2_facility FOREIGN KEY
          (
          facility_category_identifier
          )
          REFERENCES facility_category
          (
          facility_category_identifier
          )
          ;


          6. Invoke "Name abbreviations" and select forum.csv file, leave the rest as it is, press ok - message appear with list of changed objects and here is the new status:
          CREATE TABLE fac
          (
          fac_id INTEGER NOT NULL ,
          fac_cat_id INTEGER NOT NULL
          )
          ;

          ALTER TABLE fac
          ADD CONSTRAINT pk_fac PRIMARY KEY ( fac_id ) ;

          CREATE TABLE fac_cat
          (
          fac_cat_id INTEGER NOT NULL
          )
          ;

          ALTER TABLE fac_cat
          ADD CONSTRAINT pk_fac_cat PRIMARY KEY ( fac_cat_id ) ;

          ALTER TABLE fac
          ADD CONSTRAINT fk_fac_cat_2_fac FOREIGN KEY
          (
          fac_cat_id
          )
          REFERENCES fac_cat
          (
          fac_cat_id
          )
          ;


          7. Change abbreviations with related full name - it's possible to restore initial status if abbreviations in file (forum.csv in our example) are unique - they are in our case;
          - repeat step 6) but now for "Direction" select "Abbreviation to name".

          8. Well, if we want to have long names for table and columns (i.e. table "facility_category" and column "facility_category_identifier") we'll get into problem with names for objectors that use these names in their name template (see 'Naming standard>Templates"). We can use table abbreviation and column abbreviation in name templates however we have to define these abbreviations. This can be done manually using dialogs for each table/column or "Name abbreviations" toll can be used.
          8.1 change template for primary key to *pk_{table abbr}* - (we already set template for FK as fk_{parent abbr}_2_{child abbr}
          8.2 Invoke "Name abbreviations" and select forum.csv file, and select "Abbreviations" for "Scope"; press ok - report is shown for applied changes

          9. We need to generate names according changed templates and defined abbreviations. This could be done at relation model level or at table level - at least this is the idea however bug was introduced in patch 1 (build 584) and "Apply naming standards for key and Constraints" at model level works only for foreign key columns. So, we have to apply naming templates at table level. And new DDL is:

          CREATE TABLE facility
          (
          facility_identifier INTEGER NOT NULL ,
          facility_category_identifier INTEGER NOT NULL
          )
          ;

          ALTER TABLE facility
          ADD CONSTRAINT pk_fac PRIMARY KEY ( facility_identifier ) ;


          CREATE TABLE facility_category
          (
          facility_category_identifier INTEGER NOT NULL
          )
          ;

          ALTER TABLE facility_category
          ADD CONSTRAINT pk_fac_cat PRIMARY KEY ( facility_category_identifier ) ;

          ALTER TABLE facility
          ADD CONSTRAINT fk_fac_cat_2_fac FOREIGN KEY
          (
          facility_category_identifier
          )
          REFERENCES facility_category
          (
          facility_category_identifier
          )
          ;

          10. Logical model - at this point I will transform tables to entities in relational model in order to have starting point for next example. You can check that table abbreviation goes to "Short Name" for entity

          Philip
          • 2. Re: Data Modeler: Naming
            brianray
            Philip,

            Thanks for your detailed, helpful response. I'm sure it will not only help me, but many others utilizing the forums.

            I've worked through your example and have a few thoughts/questions.

            My idea was to start with the logical model and provide full names with no abbreviations and spaces between the names. This gives everyone a nice reference in which everything is spelled out and helps prevent confusion over abbreviated names. Thus, I assumed that I would start in the logical model (with full names and spaces) and then engineer to the relational model with the tool automatically creating abbreviations and constraint names, and replacing spaces with underscores.

            1) If I define a logical model with full names and spaces and then engineer to the relational model, applying the Name Abbreviations function to the relational model does not work consistently. In my example, only facility changed to fac and pk_facility changed to pk_fac. Everything else stayed the same.

            2) In your example, you started modeling with the relational model. Was that preference or is there an advantage to doing things in that order?

            3) Is there an advantage to using the Name Abbreviations versus using a Glossary?

            4) How would you apply the 't_' prefix to the tables? My thought was to classify each table (I like to use 'fundamental', 'lookup', 'associative', 'super/subtype', and 'system') and associate the 't_' prefix to each class. Then I could use the Change Object Names Prefix in the relational model.

            That's all for now. Thanks again for your time.

            Brian
            • 3. Re: Data Modeler: Naming
              Philip Stoyanov-Oracle
              Brian,

              I'll quote my post:
              I'll try to explain it in several posts.

              I. Name abbreviations tool
              this is something separate from big picture but can be useful in some cases.
              I'll come to the rest.
              2) In your example, you started modeling with the relational model. Was that preference or is there an advantage to doing things in that order?
              This explanation will be helpful for people that work mainly on relational and physical models.
              3) Is there an advantage to using the Name Abbreviations versus using a Glossary?
              No, Glossary is the way to go if you want to set your naming standard. "Name Abbreviations" is just a handy tool.

              Philip
              • 4. Re: Data Modeler: Naming
                Philip Stoyanov-Oracle
                II. Prefix manipulation

                it works on the whole relational model or can be applied on objects belonging to specific subview. The latter allow easy to set different prefix for each subject area. Wizard works in two modes (invoked in pop-up menu for relational model or its suview):
                1) add new prefix
                1.1 using classification types - data modeler comes with 5 predefined classification types (see "General options>Diagram>classification types") and new types also can be defined - color and prefix can be defined for each type
                select "Add classification prefix"
                1.2 select "add new prefix" - new prefix has to be defined in "New prefix" field; also it's good "cases sensitive" to be checked otherwise the names will go all capital letters (bug)

                2) change existing prefix with new one - the dialog is explanatory enough - old and new prefix, objects to which will be applied

                these changes are permanent - they become part of object name. However these prefixed can be changed during DDL generation process without changing the name of the object.
                Steps to do it - it's in DDL generation option dialog when DDL is generated:
                1) check "Apply Name substitution"
                2) At "Name substitution" tab :
                - go to "Object types" and select table
                - in "Name substitution" - create substitution definition and check selected, define old prefix (TT_) and new one (TX_)
                - generate DDL - there is no change in the model;

                Philip
                • 5. Re: Data Modeler: Naming
                  Philip Stoyanov-Oracle
                  III. Glossary and name translation

                  you need glossary in order to have name translation working during engineering between Logical and Relational models.

                  1. Start Glossary editor - it starts with open file dialog; we don't have glossary, just go to you working directory and type the name of new glossary (my is "forum") - Glossary editor appears

                  2. you can add your pairs (word,abbreviation) but I'll use shortcut here - select import, then select "Erwin exported names(*.csv)" as import type (it's "Files of Type" combo box); find previously created forum.csv file and open it.
                  report appear showing definition without classification - we don't have classification there:
                  Unclassified words:

                  facility,fac
                  category,cat
                  identifier,id

                  3. Close report and import dialog appears showing all definitions in the file - we'll import all of them

                  4. We have starting point for our glossary. At this moment we only should check "Incomplete Modifiers" - in short it says glossary is not complete and name parts (that constitute object name) not found in glossary are valid words - you can find more here http://www.oracle.com/technology/products/database/datamodeler/pdf/DataModelerNamingStandards.pdf
                  -well we can put something for name and description for our glossary. Save and close glossary editor

                  5. I'll open my existing design (I named it forum) and set preferences in "naming standards" page - settings here could be design specific depending on your settings on "General" page:
                  - add created glossary
                  - set _ as separator for relational model and space as separator for logical model

                  6. There is already relational model and I'll create new one; Select logical model diagram and engineer to relational model - select new one in engineering dialog.

                  7. Check "Apply name translation" (options tab of engineering dialog) and press "Engineer" button

                  8. Logical model is transformed to relational and name translation is applied to entities and attributes. The only remaining task is to generate proper names for foreign keys (and/or FK columns) - as I wrote before there is a bug that doesn't allow to set FK names at model level so the only choice is at table level

                  Well this is the simplest usage of glossary.

                  Tests:
                  you can use following pairs to make tests with SCOTT and SH schema
                  COUNTRY,CTRY,,,,,,,
                  IDENTIFICATION,ID,,,,,,,
                  REGION,REG,,,,,,,
                  DEPARTMENT,DEPT,,,,,,,
                  MANAGER,MGR,,,,,,,
                  LOCATION,LOC,,,,,,,
                  EMPLOYEE,EMP,,,,,,,
                  FIRST,FIRST,,,,,,,
                  LAST,LAST,,,,,,,
                  EMAIL,EMAIL,,,,,,,
                  PHONE,PH,,,,,,,
                  NUMBER,NO,,,,,,,
                  HIRE,HIRE,,,,,,,
                  DATE,DATE,,,,,,,
                  JOB,JOB,,,,,,,
                  SALARY,SAL,,,,,,,
                  COMMISSION,COMM,,,,,,,
                  PERCENTAGE,PCT,,,,,,,
                  TITLE,TITLE,,,,,,,
                  MINIMUM,MIN,,,,,,,
                  MAXIMUM,MAX,,,,,,,
                  START,START,,,,,,,
                  END,END,,,,,,,
                  STATE,STATE,,,,,,,
                  PROVINCE,PROV,,,,,,,
                  POSTAL,POST,,,,,,,
                  CODE,CODE,,,,,,,
                  STREET,STR,,,,,,,
                  ADDRESS,ADDR,,,,,,,
                  Lowest,LO,,,,,,,
                  Highest,HI,,,,,,,
                  Employee Name,ENAME,,,,,,,
                  Department Name,DNAME,,,,,,,
                  Calendar,CAL,,,,,,,
                  Fiscal,FIS,,,,,,,
                  Description,DESC,,,,,,,
                  Product,PROD,,,,,,,
                  Effective Date,EFF,,,,,,,
                  Source,SRC,,,,,,,
                  Customer,CUST,,,,,,,
                  Promotion,PROMO,,,,,,,
                  History,HIST,,,,,,,
                  Year Of Birth,YOB,,,,,,,
                  NAME,NAME,,,,,,,

                  Of course you have to import them in glossary.
                  Notes:
                  1) Default separator for composite words in glossary is '-' character. Change it to space and press "Apply New separator" button. Save
                  2) Names in SCOTT schema do not use separators - you have to delete separator character for relational model in Naming standards options
                  3) you have to set again _ as separator when work on relational model with tables from SH schema. If you engineer logical model to new relational model then you'll see "Effective Date" going to EFF and "Year Of Birth" transformed to YOB

                  I hope this will help.

                  Philip