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.
Refer to Table Naming Conventions for more information.
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 BOA reserved columns to every table manually, or use the Append Columns feature to automatically include BOA reserved columns to all tables. Refer to Append BOA Reserved Columns to Tables for more information, including a list of BOA 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.
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 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.
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.