This page describes how to generate a database table at the same time that you're creating your record type.
Already have a source configured? Learn how to choose an existing data source for your record type.
Creating relational database tables usually involves specialized knowledge of SQL commands and best data practices. With codeless data modeling in Appian, however, it's not just database administrators who can create and configure database tables. In this streamlined process, any developer can build and structure data perfectly for an application while configuring a record type.
Using our guided experience, you can quickly define the fields you want to add to your record type. We'll provide default fields like a primary ID field, and we'll suggest other commonly-used fields like a start date field. We'll also walk you through adding relationships to the record type and save you time by generating the common field that enforces each relationship.
Then, we'll use the fields you've defined for your record type to automatically generate a database script that will create the corresponding fields in a database table. In the script, we'll use the same names you give the record fields, but we'll change them to meet the naming requirements of the database. For example, for the record field startDate, we'll create a
START_DATE field in the database table.
Next, we'll execute that database script to create the table in your selected data source and give you the opportunity to download the auto-generated database script for future deployments.
Codeless data modeling supports MariaDB, MySQL, Oracle, SQL Server, PostgreSQL, and Aurora MySQL databases.
Once you finish, your new data model will be reflected in your record type, and the database table will be synced in Appian.
To take full advantage of codeless data modeling, you'll need permissions to create a table in your selected database. If you don't have those permissions, you can still use the guided experience to generate a database script, which you'll then need to run in the database yourself.
To generate a database table from a record type:
Select New Data Model.
In this step, you'll select the relational database where Appian will create your table.
For Data Source, you can choose any MariaDB, MySQL, Oracle, or SQL Server database connected to your Appian environment.
If your environment contains a single relational database, that database is selected and cannot be unselected.
When you've selected a database, click NEXT.
In this step, you'll add and configure the fields you want to include in your record type.
When you've finished configuring your data structure, click NEXT.
For your new record type, we'll automatically provide fields like a primary ID field, so that you can follow data modeling best practices by default.
The default fields include the following:
|id||Integer||The primary key for the record. Data sync requires a single primary key field per record type.|
|createdBy||User||The user who created the record.|
|createdOn||Date and Time||The date on which the user created the record.|
|modifiedBy||User||The user who last modified the record.|
|modifiedOn||Date and Time||The date on which the user modified the record.|
In the Commonly Used Fields pane, we provide a list of fields that are commonly used in data structures.
The commonly used fields include:
To add a commonly used field to the data structure, click the field in the Commonly Used Fields pane. The system adds the commonly used field as follows:
In the Choice List Fields pane, we provide some commonly-used categories of lookup data (for example, "status"). This lookup data represents a list of static values, which you can then allow your end users to select from as a list of choices.
When you select one of these categories, Appian creates a related record type to store the static values you define, and adds a common field to the current record type that links to the new related record type.
To add a new choice list field:
Configure the following properties:
|Record Type Name||The name of the related record type that will be generated to store your reference data. This is populated by default with your application prefix.|
|List Items||The static values that end users can select from a list. Enter one item per line without any comma separators. Do not wrap individual values in quotation marks unless you want the quotation marks to be included in the value.|
|Preview database script||A preview of the SQL script that will be generated once you save your changes. You can download this script later in this process.|
To add a single field to the data structure:
The system adds the new field as follows:
To add a new field below a specific existing field, click next to the existing field.
To add multiple fields:
For each field in the data structure, you can configure the following field properties:
|1||In the first box, enter a name for the field. This will be the name of the source field and the record field.|
|2||In the dropdown list, select a primitive data type appropriate to the field.|
|3||If you selected the Text data type and anticipate that the field will need to contain data longer than 255 characters, select the Long Text checkbox.|
To reduce the likelihood of exceeding limits that MariaDB and MySQL enforce on row data, we recommend adding no more than two Long Text fields to a record type. If you configure more than two Long Text fields, a warning displays in the field configuration dialog. You'll see a "row size too large" error if you attempt to run the generated script in the database.
You cannot change the primary key designation for the id field in this confirmation dialog. To change it after exiting this dialog, you'll need to modify the data source, then sync the record type.
To reorder fields in the data structure:
You cannot change the order of the primary key field. Following best practice, that field is always retained as the first field in the table.
Keeping the other default fields as the last fields in the table also aligns with database maintenance best practices. However, you can change the order of those default fields as needed.
To remove a field from the data structure, click in the field row.
If you remove a common field (a field with ), you can choose to delete the corresponding relationship as well.
In this step, you'll add relationships to your record type. Record type relationships allow you to reference related data from your record type, which makes development faster and easier.
You can add relationships at any point in development, but defining a relationship here has many benefits. A relationship requires that your record type contains a common field that's used in both your record type and the related record type. If you add a relationship at this point, Appian can write that common field to the data source as part of the initial table creation.
What's more—when you add the relationship now, Appian will automatically add a foreign key constraint for the common fields in the generated database script. Your database will then enforce referential integrity as records are modified to ensure your data remains consistent and up-to-date.
Appian will automatically create a record type relationship when you add certain types of fields to your data model:
|If you added a…||Then…|
|Choice list field||Appian will create a relationship to the related record type. For example, the status relationship appears in the image above because the status category was selected from the Choice List Fields pane.|
|User type field||Appian will create a relationship to the User record type. For example, the createdByUser and modifiedByUser relationships appear in the image above because the createdBy and modifiedBy fields are of type User.|
To add a relationship:
The Add Relationship to <Record Type> dialog displays. For example:
Under Common Fields, choose the fields to enforce the relationship type. The common fields can be of type Text, Number (Integer), User, or Group, and the common fields must be the same data type.
In this dialog, the common fields default to the following settings, based on the relationship type:
|Relationship Type||Common Field in Current Record Type||Common Field in Related Record Type|
|One-to-Many||The primary key field of the current record type.||No default setting. Select a field of the same data type as the primary key field of the current record type.|
|Many-to-One||An auto-generated field that matches the primary key field of the related record. For example, see
||The primary key field of the related record type.|
|One-to-One||An auto-generated field that matches the primary key field of the related record.||The primary key field of the related record type.|
You can change the names of auto-generated fields and select different common fields for both the current and related record types. However, you can only select fields that meet the criteria for the relationship type. For example, the common field in the one-side record type of a one-to-many relationship must be the primary key field.
In this step, you'll review the data model you've configured and make changes as necessary.
The review page includes the following elements:
If you're experienced with database scripts, you might want to preview the auto-generated database script at this point. To do so, click Preview database script.
If you used the Choice List Field option to generate a related record type, note that the table for the related record type is not included in this database script because it was already created in your database.
Before you continue, verify whether you want Appian to create a database table at this time.
By default, the system will use the auto-generated database script to create a table in the database you selected earlier. You'll just need to keep the Create Table option selected.
However, under certain conditions, you might want to prevent Appian from creating the table. For example, you might need to make modifications to the script like editing the field names to meet database administration requirements or adding foreign keys and constraints.
In that case, you can prevent the system from creating the table, while still taking advantage of the guided experience to generate the basic script.
To prevent table creation, unselect the Create Table checkbox.
Click SAVE CHANGES to save your changes to the record type.
If the Create Table checkbox is selected, Appian will save the data model for the record type and create a new table in the database.
If the Create Table checkbox is unselected, Appian will save the data model for your record type, but make no changes to the database itself. Because the table does not yet exist in the database, the sync will fail for your record type. As a result, error indicators of the failed sync will display in the record type until you create the table in the database and trigger a full sync.
After Appian successfully saves the changes to your record type, you have only one step left: deciding whether to download the auto-generated database script.
You can use the database script to:
By default, the Download database script is selected, and the system will download the script when you click FINISH.
If you do not want to download the script, unselect the Download database script checkbox before clicking FINISH.
When you unselect this checkbox, a banner will appear asking if you want to save your download preferences. You can choose to skip the database script download each time you update to the source, or return to the default download setting so the script is downloaded whenever you update the source.
The auto-generated database script is not saved in Appian, so we strongly recommend downloading the script now and storing it locally.
Generate a Source for Your Record Type