Table configurations are done in the Table Definitions application.
Name tables so that they are free of numbers, special characters, and spaces. Once a table is created, the name cannot be changed at a later date. All words must be written in upper camel case, which means the first letter of each word should be capitalized. The name should reflect the type of information in the table. For example, GeneralLedgerBalances.
The table category for application and solution developers must be set to Customer - Business Data. To set to a different category, require approval.
Design your table based on the requirements for a family or module and with consideration for any extensions or features that you expect to add in the future. Spend enough time on your table design to minimize future disruptive changes, but do not add fields until you have functionality to use them. Make sure to think about interactive reports and views when creating a new table and when making changes. Customers will heavily use interactive reports and views over these tables.
Design changes to be backwards compatible. Backwards compatible changes are changes that do not require any user downtime during an upgrade. For example, backwards compatible changes include:
- Adding a field that is not used in a logic block.
- Adding a field that is not required and doesn't have a default value.
Disruptive changes are changes to the table that require any existing records in the table to be updated to match the new table settings. For example:
- Adding a new required field. When the table in production is updated, the required field is added to existing records without a value. This means older records cannot be saved until a value is entered into the new field. When records are modified through logic blocks, this can cause the logic block to error when the older record is saved.
- Adding a new True False field that has a default value of
True. Any records created before the field was added will have a default value ofFalse. This can cause a variety of issues with logic blocks, or even with application settings or user searches or filters
Ensure that the Create Audit check box is selected in tables built to capture setup and transaction data. This captures when records are created, updated, and deleted in those tables.
There are tables that do not require the Create Audit check box to be selected. If the check box is selected in these tables, you are creating unnecessary records that are often not actionable. Do not select the Create Audit check box if:
- Your table is not built to capture setup and transaction data. For example, if it's built to capture flat report, report parameters, integration, or hierarchy flattened data (table is used in an Hierarchy Definition).
- The table type is Component, Sub, Join, Header Detail, Work Table, View, or Summary.
Naming conventions and standards
Table names should reflect the type of information in table and follow the upper camel case convention. For example, GeneralLedgerBalances, SalesSettings, and RoutingOperations are all acceptable names for tables.
When a table performs a specific purpose, additional naming conventions may be necessary. The naming convention should only be in the Table Name field, not the Description field.
| Table purpose | Naming convention | Example |
|---|---|---|
Join | If the table is used as a join table, include Join in the name.Start the name with the primary table followed by the secondary table and then include Join.If there are more than two tables in the join or multiple joins over the same 2 tables, include a word that will provide context after the second table name. Optionally, name the table for the application that is using it. | CashReceiptsInvoiceJoinBOMComponentsBOMHeadersJoinDispatchListJoin |
Integration | If the table is used for integrations, end it with Integration. | StudentScheduleIntegrationCashReceiptsIntegration |
Header | If the table is used as a header table for a header detail table, include Header in the name. | GeneralLedgerHeaderWorkOrderHeaders |
Detail | If the table is used as a detail table for a header detail table, include Detail in the name.Optionally, choose a name that is descriptive of the details. | GeneralLedgerDetailWorkOrderOperationsWorkOrderComponentsWorkOrderOutputs |
| Header Detail | If the table is used as a header detail table, the table name should be plural. | GeneralLedgerTransactionsWorkOrders |
Work Table | If the table is a logic block table, use the name of the logic block and end it with LB. (work tables were previously known as Logic Block tables, hence the LB) | ExchangeRatesLB |
Main | The table name should be plural. Note the exceptions below:
| AccountTypes
BalanceSheetReport
BalanceSheetFlat
|
Settings | The table should begin with the module name followed by Settings. | FixedAssetSettings
|
View | The view table can be created to limit the fields from the base table. It is common to use the views for interactive reporting or as a related view in a data item or a related view in a table. See the Data Item Definitions application and Table Definitions application For a view that will be used for reporting the naming standard should be: <Table><TransactionType/Doc Type/Entity (if needed)>View For a view that will be used for as a related view in a data item or table: <Table><Entity(if needed)>TLView or <Table><Entity(if needed)><Product Family/Module>TLView | Reporting view examples:
Related view on data item or table examples:
|
The Description field entry may match the name of the table, but should include spaces. Table descriptions should always reflect the data stored in the table. Don't use complete sentences when writing descriptions. For example:
| Table Name | Table Description |
|---|---|
SalesOrderHeader | Sales Orders, Sales Quotes, and Sales Credits |
SalesOrderDetail | Sales Order, Sales Quote, and Sales Credit Details |
SalesOrderFlat | Sales Orders, Sales Credits, Sales Quotes, Deposit Invoice Reports |
ProcurementOrderIntegration | Supplier Quotes, Purchase Orders, Subcontract Orders, Value Only Purchase Orders, and Value Only Subcontract Orders Integrations |
ReceivablesHeader | Customer Invoices and Customer Credit Memos Headers |
ReceivablesDetail | Customer Invoice and Customer Credit Memo Details |
If you are configuring a join table, the Specification text entry should list the two tables used in the join table and the intended use of the join table.
When creating indexes, the Index ID should be named A, B, C, and so on.
Name the Unique key index as UK1, UK2, and so on.
When creating a table, it is best practice to create a table lookup over the table that can be used as a foreign key reference to the table. On tables that have a plural name, create the table lookup using the singular name. For example, the table lookup for WorkOrderTypes is WorkOrderType.
It is best practice for most tables to use the Customization Pattern of AllowAdditionsAndTriggers. This helps partners extend and customers customize the base tables. It is also best practice for tables to use the Customization Pattern of AllowAdditions. In this case, tables usually need the ability to use Customer defined attributes making this Customization Pattern necessary, but not allowing the customization of triggers for performance implications. As with all standards, there could always be an exception.