Difference between revisions of "Best Practice for Creating Custom Tables"
m |
m (→Columns to ''always'' Add) |
||
| Line 24: | Line 24: | ||
*'''delete_flag''' is used to set items active or inactive. | *'''delete_flag''' is used to set items active or inactive. | ||
*'''description''' provides an area for more detail. | *'''description''' provides an area for more detail. | ||
| − | *'''sym''' is the field | + | *'''sym''' is the field that is displayed. |
| Line 115: | Line 115: | ||
|- | |- | ||
|} | |} | ||
| − | |||
=== Table Parameters === | === Table Parameters === | ||
Revision as of 15:48, 25 July 2008
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.
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 give 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.
| 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.
| 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>
To discuss or ask questions about this article, select the discussion tab above.
