Tables
A Table definition describes the metadata needed by the application to work with and access a database table or view.
-
The table's description in the application.
-
The list of fields defined on the table and any foreign key constraints they establish with other tables.
-
The table's prime key and how it is generated.
-
Whether the table holds administrative, master or transactional data.
-
Whether the table is released with product owned system data rows or not.
-
The table's role in the conversion process.
-
How should table content be upgraded.
-
and more
The following sections further discusses concepts and guidelines related to defining tables.
System Tables
Tables released with rows that are owned by the base product are referred to as system tables. These tables include the Owner (OWNER_FLG) field to capture the owner of each row.
Refer to System Data for more information about naming conventions and standards around row record ownership.
The Upgrade setting on the table definition record controls what happens to the rows in a table when the system is upgraded to a new release. The most common setting allows customer owned rows to be merged with rows owned by the base product. Other settings may keep the content of the table as is, i.e. rows are not touched by the upgrade, or fully refresh the content of the table with base product rows.
Some fields on certain system owned tables are explicitly marked to allow customization. This means implementation are allowed to change these specific fields for a base owned record. Changes to the field value of one of these types of fields by an implementation are maintained when upgrading to a new version of the product.
Designated Table Roles
-
Key Tables
-
A key table holds a copy of a table's prime key only and is only applicable to tables with system generated keys.
-
Tables with a system generated keys reference their Key Table as part of their Table definition record.
-
Records in the key table remain in the system even when the related entity is archived, preventing the same key from being allocated to a new record. Retaining the key allows the system to keep the integrity of any references that may still exist to the archived entity.
-
-
Language Tables
-
To support multi-language implementations, all labels and descriptions that are presented to a user are kept in a designated language table where a row exists for each enabled language.
-
The product is released with the English content provided in the various language tables. An additional row is added to these tables for each enabled language along with the translated content for that language. Refer to Support For Different Languages for more information.
-
Tables associated with language sensitive fields reference their corresponding Language Table as part of their Table definition record.
-
-
Characteristics Tables
-
Many entities in the system support one or more Characteristics tables, which are used to capture additional fields for the object that are not already supported by the entity's provided attributes.
-
A Characteristics table is designed to capture characteristics applicable to its specific entity only. The entity associated with a Characteristics table is referenced on the Table's definition record.
-
Refer to Setting Up Characteristic Types & Values for more information.
-
-
Entity Log Tables
-
Many entities in the system support the ability to capture lifecycle and other important business events on a designated log table.
-
A log table captures the event details in the form of an application message and stores any parameters for the message in a child log parameters table. All log and log parameters tables follow a predefined structure allowing them to be consistently and efficiently implemented.
-
Typically log tables do not participate in the conversion process.
-
Table Constraints
Foreign key relationships between tables are not enforced at the database level but rather at the application level, providing the application more flexibility around data manipulation and referential integrity.
-
Primary Key represents the field or set of fields that represent the unique identifier of a record stored in a table.
-
Logical Key represents an alternate unique identifier of a record based on a different set of fields than the Primary key.
-
Foreign Key represents a field or set of fields that specifies identifying and non-identifying relationships to other tables in the application. A foreign key constraint references the primary key constraint of another table.
-
Conditional Foreign Key represents rare relationships between tables where a single field (or set of fields) may reference multiple primary key constraints of other tables within the application as a foreign key.
The system leverages a table's foreign key constraints information to performs referential integrity validation when rows in this table are deleted. These validations can be disabled by checking off the Enable Referential Integrity field on the Table definition record.
Table Indexes
The indexes delivered by the base product for a given table are visible in the index metadata. In addition, table index information may be defined as part of the cloud services support for generation of custom tables in certain use cases. Refer to the section Generating Custom Tables below for more information.
Primary Key Type
A table’s primary key may be of the following types:
-
User-defined means the user specifies the key when a record is added. This type of key is typically used for configuration and administrative tables.
-
System-generated means a program generates a random numeric key for the record when it is added.
-
This type of table is typically associated with a key table. Refer to the Designated Table Roles section for more information.
-
System generated keys may be fully random or partially inherited from a parent table’s key for performance reasons. Refer to the System Generated Keys for more information.
-
-
Sequential means a sequence number is incremented whenever a record is added to the table. The next number in the sequence determines the key value.
-
Database Native Sequential Key means a unique number is assigned using a database sequence object.
-
Other means some other business rule allocates the table's prime-key or the prime key is made of multiple parts.
System Generated Keys
In general, randomly generated numeric system keys are used to attempt to evenly distribute records across a full range of possible IDs. Batch programs that use multiple threads will typically divide the threads using ID ranges and evenly distributed keys will help spread out the work across these threads.
If a table using a system generated key is a child of another table, the child table’s key may inherit a portion of the random number from its parent table’s key. Key inheritance for child records further extends the performance benefit. When considering partitioning, the recommendation for DBAs is to range partition data based on the primary key so that different batch threads operate on different partitions which reduces contention for hot blocks. Ideally the number of batch threads will be an exact multiple of the number of partitions. Batch programs that insert child data (for example batch Billing creation) also benefit from this design especially when the child tables are partitioned in the same way. The parent is often the driver of the batch process. If this is multi-threaded, then each thread is processing a set of parent records in a given ID range and all child records are being inserted into the same ID range.
While most entities with system generated keys either use fully random keys or partially inherited keys from a single parent, entities may be designed to implement more specific key generation rules.
-
The number of most significant digits from the parent record's primary key to use as the prefix for the child record's key value.
-
The parent key field name to inherit the key from. This information is provided for display and documentation purposes only and is only provided when the inheritance rule implemented by the entity may be documented this way. Typically the child key value is inherited from a single parent key but in some cases the inheritance rule may be more complex. For example, different types of records in the same child table may inherit their key from different parent key fields. In this case, multiple parent field names may be listed to describe the various inheritance options. When the rule is too complex, this information may not be provided on the table definition record.
Conversion Options
The conversion process manages the process by which master and transaction legacy data, loaded into a staging schema, is validated and assigned new system generated keys before it is copied to the production schema. Refer to Understanding The Conversion Process for more information.
-
Administrative tables. These only exist in the production schema. Instead of a table, a view to the production table exists in the staging schema. These views are necessary to enforce foreign key references when data is validated in the staging schema
-
Convertible tables that retain their prime keys. Data conversion is supported for these tables and their prime key values remain unchanged.
-
Convertible tables that require a new system generated key to be assigned. Data conversion is supported for these tables and their prime key is reassigned by the system during conversion.
-
Not convertible tables. Assigning new system generated keys for a record and replacing all foreign key references it might have with the new key is a complicated process that may not be supported for all tables.
. The Data Conversion Role setting on the table definition record controls if / how the table is used by conversion processing.
Date/Time Configuration
Fields capturing date/time information may be designed to store data in either local legal time or local standard time. Local legal time is captured as entered and displayed as captured in the database. It also assumes the date/time information refers to the time zone defined on the installation options. Refer to Standard vs. Legal for more information.
Generating Custom Tables
For cloud customers and for certain use cases, the product supports defining table metadata for custom tables and generating the related DDLs for that metadata in the database. For these custom tables, there is a Generation Status visible on the Table metadata that identifies where the Table is in the lifecycle for this supported feature. In addition, the index tab on the table portal is used to define custom indexes that are included in the DDL as part of this feature. If you are a cloud customer and would like more information, refer to the Cloud Services Implementation Guide.