DSP® 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 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.

  • Capitalize field names in such a way that DSP® correctly displays the label. For example, for the column name ProductHistory, DSP® automatically inserts a space between Product and History.
  • Consider a naturally occurring and meaningful primary key.
  • 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 DSP®. 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.

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.

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 DSP®.

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.

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 DSP® 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 DSP®
  • 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 DSP® 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 DSP® 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