Application Development

Create Tables in SQL

The majority of the pages in a WebApp are Dynamic page types and require an underlying table and a view.

Refer to Create Views in SQL and SQL Code Guidelines for more information.

Well-designed tables allow for an efficient and easy to develop WebApp. Both naming conventions and standard SQL design practices can contribute to the longevity and extensibility of an application.

When creating tables.

  • Include a primary key in tables. These are hard required.

  • Consider a naturally occurring and meaningful primary key.
  • Include an ‘ID” and a descriptor in the primary key name, such as CustomerID or OrderID.

  • Use descriptive and simple names when naming tables and columns.

  • Limit table names to 24 characters or less to meet the 30 character limit for views.

  • Do not add columns for button, toolbar, image, label or tab controls to the table; these should be created in the view.

  • Capitalize the first letter of each word so that the Stewardship Tier correctly displays the label. For example, for the column name ProductHistory, Stewardship Tier automatically inserts a space between Product and History. Do not use spaces in the names, use underscores if needed.
  • Add Syniti reserved columns to every table manually, or use the Append Columns feature to automatically include Syniti reserved columns to all tables. Refer to Append Syniti Reserved Columns to Tables for more information, including a list of Syniti reserved columns. 
  • Use default field sizes or common field sizes as much as possible unless the exact size of the field contents is known, e.g., NVARCHAR(50) instead of  NVARCHAR(45) or NVARCHAR(255) instead of NVARCHAR(200).
  • Normalize data:
    • Avoid tables that have common keys; put all the data in one table.
    • Avoid repeating data groups in tables.

This topic contains the following sections:

Data Types

Data types are an important characteristic from both a back-end and front-end perspective. Minimizing data types to their smallest relevant size can improve performance and reduce database bloat. One example of appropriate size limiting is within the System Administration WebApp in the Stewardship Tier. Fields that contain SQL View Names (e.g., Horizontal View) will only ever contain view names directly from SQL Server. Since SQL Server object names are limited to 128 characters, this field can and should be limited to 128 characters.

It’s important to select the correct Data Type for a column. Many SQL Data Types have Stewardship Tier assigned properties; i.e., bit is automatically a checkbox.

Though not all inclusive, the most commonly used data types are:

  • Bit—Used for checkboxes
  • Datetime—Used for most dates. Include the time portion even if it is not used, as time may be added later.
  • Decimal (18,4)—Used for anything with a decimal. Precision and scale can be updated. See the decimal and numeric section of SQL Docs for more information on precision and scale.
  • Int—Used for numeric primary keys, some reserved columns and any numeric value without a decimal
  • Money—Most commonly used with currency
  • Nvarchar (50)—Most common Data Type. General use column for alpha, numeric and special characters. Scale can be changed. For example, scale of 255 must be used for system paths, such as for a File control.
  • UniqueIdentifier—Used for PageID’s, WebAppID’s, MenuID’s, etc.

Character data types are important for application behavior as well. Most times, nvarchar is preferred over varchar due to the Unicode support of nvarchar. Names and descriptive fields should be nvarchar to ensure they support the widest range of character sets.

Auto-Incrementing Primary Key

When testing, it is often helpful to have an auto-incrementing primary key.

The primary key must be an Int.

Select the primary key in Column Properties and set “Is Identity” to ‘Yes’ in the Identity Specification section.

Database Default

This defaults the value of a column, which can be changed on the UI in Stewardship Tier.

Sometimes the default is set in Stewardship Tier as the value is easily configurable, but database defaults are also used when the default is unlikely to change.

Select the column to default, and update “Default Value or Binding” column in Column Properties.

Column Collation

Column collation allows for overriding the database's default collation with a custom one, which can impact behaviors like case-sensitive comparisons. This can be considered during application design to determine if a field’s case sensitivity is relevant at a business-logic level.

Optimization

Indexes and data type minimization can both lead to a high performing table even with millions of records. Everything else in the application will be built on these tables, so if this foundation is rushed or not given enough thought, the result may be excessive technical debt in the future.

Table Naming Conventions

Across the applications, a common naming convention is to name a table with the singular form of the underlying data. For instance, if the table is meant to store a data set representing customers, name the table “Customer.” Additionally, prefixes are commonly attached to the table name. “zt” represents application configuration data, whereas “tt” represents expected user data. Configuration data may include user data, but it’s still utilized by the application as configuration. For the Customer example, the full table name is “ttCustomer.” This allows developers to quickly identify and filter tables to learn and develop the application further.

When naming tables:

  • Consistently name tables with small data prefix (2 characters) followed by a descriptive name of the contents of the table.
  • Capitalize the first letter of each word, if multiple words are used in the table name, e.g., OrderDetail.
  • Do not abbreviate.
  • Use singular table names.
  • Do not use hyphens (“-“), spaces or slashes (“/”, “\”) in the table name. Underscores are acceptable.

NOTE: Special characters in SQL Object names will break functionality in Stewardship Tier.

The following table outlines the basic table naming conventions:

Condition

Convention

Example

Tables delivered with the application without data.

NOTE: These tables often store user-entered data.

ttXXX, where tt defines the application and XXX describes the purpose of the table.

nwOrder

Tables delivered with the application with configuration data.

ztXXX, where XXX describes the purpose of the table.

ztParam

Dynamic cross reference tables used as a base and configured onsite.

xtXXX, where XXX describes the purpose of the table.

xtControlStatus

Archive tables

rtXXX, where XXX describes the purpose of the table.

rtRequst

Add Reserved Columns to Tables

Reserved columns have assigned behaviors and can be added to any table. These columns can be added manually or automatically. Refer to Append boa Reserved Columns to Tables for more information.

The Data Type indicated must be used for the assigned behavior to work as expected.

This table lists the most commonly used Reserved columns.

Column

Data Type

Notes

boaStatus

int

  • As a best practice, should be added to all page tables to record the event status of a record

NOTE: This column is required if there is an OnValidate event on the page where the Stewardship Tier needs to set the status of the field.

  • Can be added to a web view to display the current event status of a record

boaAudit

int

  • Can be added to a web view to control the accessibility of the Audit column when Audit is set up for that table within the Stewardship Tier
  • Accepts standard Control Status values (0, 1, 2)

boaAdd

int

  • Can be added to a web view to control the ability to add records when Insert is supported for the page
  • Accepts standard Control Status values (0, 1 ,2)

boaEdit

int

  • Can be added to a web view to control the ability to edit records when Update is supported for the page
  • Accepts standard Control Status values (0, 1, 2)

boaDelete

int

  • Can be added to a web view to control the ability to delete records when Delete is supported for the page
  • Accepts standard Control Status values (0, 1, 2)

AddedOn

smalldatetime

  • As a best practice, should be added to all page tables to record the date and time when the record was added
  • Generally not included in the web views

AddedBy

nvarchar (50)

  • As a best practice, should be added to all page tables to record the UserID who added the record
  • Generally not included in the web views

AddedVia

nvarchar(50)

As a best practice, should be added to all page tables to record the Stewardship Tier process that added the record.

ChangedOn

smalldatetime

  • As a best practice, should be added to all page tables to record the date and time when the record was changed
  • Generally not included in the web views

ChangedBy

nvarchar (50)

  • As a best practice, should be added to all page tables to record the UserID who changed the record
  • Generally not included in the web views.

ChangedVia

nvarchar(50)

As a best practice, should be added to all page tables to record the Stewardship Tier process that changed the record.

LockedBy

nvarchar (50)

  • As a best practice, should be added to any page table where multiple users are expected to update the same page
  • This records the UserID of the person currently locking a record (has the record in EDIT mode)
  • This also prevents the record from being edited by another user at the same time
  • Generally not included in the web views

LockedOn

smalldatetime

  • As a best practice, should be added to any page table where multiple users are expected to update the same page
  • This records the date and time when a record was locked
  • Generally not included in the web views

boaLockType

integer

Used for record locking with Excel integration.

Values are:

  • 0—The record is being edited by another user via the page in the UI.
  • 1—Locked via Excel Integration, the record is being edited via a downloaded record set.
    Refer to Lock Records for more information.

WorkflowedOn

smalldatetime

  • As a best practice, should be added to all page tables to record the date and time when workflow event was executed from this record
  • Generally not included in the web views

WorkflowedBy

nvarchar(50)

  • As a best practice, should be added to all page tables to record the UserID who executed the workflow event from this record
  • Generally not included in the web views