Application Development
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
- Auto-Incrementing Primary Keys
- Database Default
- Column Collation
- Optimization
- Table Naming Conventions
- Add Reserved Columns to Tables
Data Types
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 |
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.
|
|
boaAudit |
int |
|
|
boaAdd |
int |
|
|
boaEdit |
int |
|
|
boaDelete |
int |
|
|
AddedOn |
smalldatetime |
|
|
AddedBy |
nvarchar (50) |
|
|
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 |
|
|
ChangedBy |
nvarchar (50) |
|
|
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) |
|
|
LockedOn |
smalldatetime |
|
|
boaLockType |
integer |
Used for record locking with Excel integration. Values are:
|
|
WorkflowedOn |
smalldatetime |
|
|
WorkflowedBy |
nvarchar(50) |
|
