Jump to content
  • Sign in to follow this  

    Best Practice for Creating Custom Tables


    Gregg

    Overview

    This article contains best practices for creating custom tables. It is highly recommend that all custom tables are created via the Schema Designer utility available as a subcomponent of the Web Screen Painter. [Note] This process of creating custom table involves the stopping of Service Desk Application while publishing the table (pdm_publish)

    Basics

    Begin all Tables with a Z

    It is standard practice to add a z at the beginning of all custom table names. (zchg_type, zxfer_reason, etc.)

    Columns NOT to Add

    All custom tables created via the Schema Designer are given an id, last_mod_by, and last_mod_dt column when the table is published to the database. Keep this in mind when adding columns to your custom table.

    Columns to always Add

    The following columns should be added to all custom tables. While you are certainly free to deviate from this listing, you will find that most tables contain these fields with the parameters given and by adding them you are maintaining consistency.

    • delete_flag is used to set items active or inactive.
    • description provides an area for more detail.
    • sym is the field that is displayed.

     

    Column Properties
    Attribute delete_flag description sym
    Name delete_flag description sym
    Display Name delete_flag description symbol
    Schema Name del description sym
    DBMS Name del description sym
    Description      
    Field Type SREL STRING STRING
    String Length   240 100
    SRel Table actbool (Active Boolean)    
    On New Default Value 0    
    On Save Set Value 0    
    Required (check)   (check)
    Update only for new record      
    Key for pdm_userload     (check)
    Unique     (check)
    Ascending      
    Descending      

     

    Table Parameters

    After all the columns have been added, be sure to finalize the table properties. Often times things are overlooked here and require future changes.

    Table Properties
    Attribute Table Info Description
    Name zyour_table  
    Display Name My Table  
    Schema Name zyour_table  
    DBMS Name zyour_table  
    Description    
    Default Display Field
    (common name)
    sym (symbol) This identifies the field that is displayed. sym is the standard.
    Foreign Key Field (rel attr) id This determines the value from this table that is stored in other tables and used as a reference to this table. id is the standard.
    Function Group reference Determines the individual permissions granted to this item. The drop-down selections correspond with the Functional Access items found in the Access Type configurations.

     

    Example

    The above suggestions will generate generate the following code in the wsp.mods file located in $NX_ROOT\site\mods\majic. this is a good method of checking to ensure your table was configured properly.

    //////////////////////////////////////////////////////////////////////// 
    // Factory: zyour_table (My Table) 
    ////////////////////////////////////////////////////////////////////////
    
    OBJECT zyour_table {
      DISPLAY_NAME "My Table";
      ATTRIBUTES zyour_table {
        delete_flag del SREL actbool
          REQUIRED
          {
            ON_NEW DEFAULT 0;
          };
        description STRING 240;
        sym STRING 100
          REQUIRED 
          DISPLAY_NAME "symbol";
        last_mod_dt DATE { ON_CI SET NOW; };
        last_mod_by SREL cnt { ON_CI SET USER;
                               ON_NEW DEFAULT USER; };
      };
      FACTORY zyour_table {
        STANDARD_LISTS {
          MLIST OFF;
          RLIST OFF;
        };
        FUNCTION_GROUP "reference";
        REL_ATTR id;
        COMMON_NAME sym;
      };
    };

     

    History

    This article was originally a wiki article created by Gityerfix in 2008 and later modified by Paul Thomson.

    Sign in to follow this  


    User Feedback

    Recommended Comments

    There are no comments to display.


×