Overview

This screen details information regarding a specific database table that was created for your application using the Designer. Details such as data type, name, and state of the column are all outlined in the grid.

The page is utilized for Primary, Secondary (One-to-One), and Secondary (One-to-Many) tables.

Primary Table

The primary table of your application should host all of the top-level information within your application data record structure. This table will also provide all of the system-generated primary keys for storing data in your application.

System-Generated Fields

Several important system-generated columns will be added to your primary table automatically. These are outlined below.

All subsequent tables for the target application will share keys with your primary table to provide linkages. As a result, since the primary key to this table is {{ RecordId, EditId }}, each additional table will also maintain those keys.

Best Practices

A primary table should not necessarily be your primary storage for all information in the application, unless you are storing a relatively small amount of data. It is important to consider the actual conceptual structure of your data before simply adding all inputs to this single table.

If your data model is large, then you will want to consider appending additional secondary database tables (outlined below), apportioned by some conceptual delineation (e.g. general information vs. user information, policy-level vs. instance-level).

Jim is creating an application to quote insurance for an end-user with up to 15 vehicles. His application captures general data (e.g. name, date of birth), policy-level data (e.g. policy type, policy-level deductibles), and vehicular data (e.g. vehicle model, vehicle make).

Rather than storing all of the data on the primary table, Jim is going to store the general data on the primary table, the policy-level data on a one-to-one secondary table, and the vehicle schedule on a one-to-many secondary table.

More information on creating secondary tables is detailed in the subsequent sections.

Grid Actions

The table on this page allows users to perform various actions, as outlined below.

Edit

Database table columns can be modified by clicking on the Edit button in the corresponding row. Only user-generated columns can be modified.

Delete

Database table columns can be deleted by clicking on the Delete button in the corresponding row. Only user-generated columns that have been created during the current transaction can be deleted. Columns that exist from previous transactions must be deactivated if their use is to be discontinued. This is to prevent data loss in subsequent transactions, particularly in conjunction with viewing historical versions of records.

Activate/Deactivate

Database table columns that were created during earlier transactions cannot be deleted. However, there are scenarios where you may want to stop saving data to specific columns in the database. These target columns can be deactivated using the corresponding button. Upon publishing the current transaction, future save actions by end users will not trigger saving of the target deactivated data column. Reactivating the column will begin storing the target values again.

Add Used Columns

The Add Used Columns button is available for primary and secondary one-to-one tables.

Activating this action will take you through all named ranges used in the user interface controls that are not defined to be saved into the database.

The system will go through each field and allow creating database columns. Press Next on the Add Column screen to move onto the next unbound named range. If you don't want to add a column to database, click the Skip button.

Secondary Tables

To better apportion and consolidate data, you can generate secondary tables in two different formats: one-to-one and one-to-many.

One-to-One (OtO) Secondary Tables

A one-to-one secondary table acts as an extension to the primary table. For each record in the primary table, there is only a single record in this secondary table. As a result, the data follows a flat structure and each column is bound to a Single Named Range (SNR) (i.e. a named range that is one cell in width and one cell in height). In turn, the configuration, definition, and modification of one-to-one secondary tables is effectively identical to primary tables.

The most noticeable difference is that there are fewer system-generated columns on these tables. The primary table will hold the majority of system-generated information and the secondary one-to-one table will simply link to it using the required keys (the aforementioned {{ RecordId, EditId }} composite key).

As denoted by its name, for each record in the primary table, there will be a single record in these types of secondary tables.

One-to-Many (OtM) Secondary Tables

A one-to-many secondary table acts as a means to stored scheduled information. Some examples:

• A census application may stores sets of information regarding multiple individuals.
• A property insurance quoting tool may store sets of information regarding individual locations.
• An order form may store sets of information regarding a series of ordered items.

In these types of scenarios, your data may be formatted as a two-dimensional table within your Excel model: this is known as a Table Named Range (TNR). This is the type of named range that is required for secondary one-to-many tables.

Typically, this type of range is associated with a grid in the user interface. Unlike a secondary one-to-one table, one-to-many tables will have their columns configured by the columns within the table named range. As a result, the number of rows per record will vary depending on the number of rows in the table named range.

For example, take the following table:

A secondary one-to-many table would generate three columns based on the data above: Name, Age, and State. The number of rows would be dependent on the number of rows in the named range. In this case, there are four maximum users catalogued by this range, and thus there will be four rows in this table for every one row in the primary table.

Each record links to the primary table using the normal key construct: {{ RecordId, EditId }}. In addition, these records will include a SequenceId key that maintains the integrity of the row order.

Consolidate Columns

Since the columns in secondary one-to-many tables are defined by the named range itself, you will not be able to add columns directly to the table, although you can edit existing ones.

Instead, adding new columns must be performed through consolidation. If the bound named range changes in size (e.g. you add an additional row or column to the named range in Excel and then update the file in the Designer), then you will be prompted to consolidate the target one-to-many database table. This is a process of mapping new columns either to existing ones in the table or to new columns as necessary.

This process is further detailed in the Consolidate Named Ranges page in the Designer help module.