Best Practice for Creating Custom Tables

To make corrections or additions to this article, select the edit tab above.
To discuss or ask questions about this article, select the discussion tab above.

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
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. <source lang="javascript">//////////////////////////////////////////////////////////////////////// // 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;
 };

};</source>

See Also

Adding Nodes to Administration Tab