AppFrame Database Solutions
In all AppFrame-based solutions, the business logic needs to be implemented in the database. This could be done by using triggers, views, functions and stored procedures.
Business Logic
It is also possible to use VB.NET to create business logic on the SQL Server (SQL Server 2005 and later supports integration with CLR). However, we recommend in general to utilize standard SQL stored procedures, as this typically will perform better, and is easier to setup and maintain in most cases. Transact SQL has become very powerful, and it is not much you are not able to do. It is essential to develop a good database structure to be able to develop a high quality AppFrame solution.
Naming Convention
An AppFrame solution might have hundreds, even thousands, of database objects. Naming the objects according to a defined standard is therefore very important. This will help both yourself and other AppFrame developers to easier understand the application.
Some basic rules for naming of objects:
| Object | Naming convention | Example |
| Table | atbl_Module(_SubModule)_Table | atbl_PC_Projbaseline_WorkPacks |
| Triggers | (TableName)_ITrig/UTrig/DTrig | atbl_PC_Projbaseline_WorkPacks_ITrig |
| Table Views | Current Domain: atbv_Module(_SubModule)_Table Cross Domain: atbx_Module(_SubModule)_Table |
atbv_PC_Projbaseline_WorkPacks atbx_PC_Projbaseline_WorkPacks |
| Custom Views | aviw_Module(_SubModule)_Name | aviw_PC_Projbaseline_CostStatus_ByWBS |
| Report-specfic View | arpt_Module(_SubModule)_Name | arpt_PC_Projbaseline_CostSummary |
| Stored Procedures | astp_Module(_SubModule)_Name | astp_PC_Projbaseline_ActivateWBS |
| User Defined Functions | afnc_Module(_SubModule)_Name | anfc_PC_Projbasline_WBSLevel |
AppFrame database tables should be named in plural forms. E.g. atbl_Contracts_CostElements.
CamelCasing is used for all objects (e.g. ContractNo, WorkPackID).
When it comes to naming conventions of variables in stored procedures and functions, we recommend to prefix the variables with a v. E.g.: DECLARE @vWBS as VARCHAR(36).
Database Design
Good database design is crucial for both the quality and the performance of your application. Designing a database means that you must visualize the relationships between the chunks of data you will be using in your application, and create your tables with these relationships in mind. Standard normalization principles must be applied when you design your database.
When you create a table in AppFrame, a few standard fields are created. One of them is called Primkey, and is a uniqueidentifier. Prior to R3 this primkey field was typically chosen as the primary key. But this should only be done in rare cases. Generally, try using a natural key as your primary key. If you don't have a natural key, or the natural key is very wide (consists of many fields), you should instead an ID-field, that is an integer, and use that as your primary key.
The name "Primkey" might therefore be somewhat confusing. Consider it more as a unique row identificator. The great thing with the AppFrame Primkey field is that it is totally unique. You won't find it in any other database around the world (at least as long as it is generated using the newid() function). This could be useful in many cases.
To ensure that your database get the data integrety required, ensure that you define foreign key relations wherever applicable.
Note: In previous versions of AppFrame, the "stbl_System_Codes" table was widely used. In R3 you will not find the Codes table anymore. The reason for that is that a codes table can not be used for defining foreign key relations in a proper way.
Example: In R2 you might have had a "ContractType" field in the atbl_Contracts_Contracts table. The different available ContractTypes was available in stbl_System_Codes, in the codetype: ContractType. If a user changed any of the values in the codes, causing contracts to have invalid values, no error message will be return (unless custom made trigger was implemented). Instead of using the stbl_System_Codes table a "atbl_Contracts_ContractsTypes" table should be designed, with a foreign key constraint to atbl_Contracts_Contracts. A cascade update rule should also be defined.
In addition to foreign key relations, it is also in certain cases, an option to use "constraints" on the fields. Keep in mind that in some environments, deploying an updated databaes object from development to production might take quite some time. Therefore only use field constraints in cases where the values of the field is used in business logic processing.
Before you even start coding your application, spend a lot of time designing your database! Changing the database design after development of business logic, forms and reports has begun is always much more costly.
AppFrame Tables
Tables should be created using the CR3 Database Objects form, or using the stored procedure sstp_Database_CreateTable from SQL Server Management Studio. The reason why you should not create your tables manually, is that several fields are required for AppFrame to function as required.
The standard fields are:
| Field | Type | Description |
| Primkey | Uniqueidentifier | A row id, and uniquely identifies the records |
| Timestamp | Timestamp | Binary field that indicate the relative sequence in which data modifications took place in a database |
| Created | DateTime | DateTime value showing when the record was created |
| CreatedBy | nvarchar(..) | User Name (SUSER_SNAME()) of the user creating the record |
| Updated | DateTime | DateTime value showing when the record was updated |
| UpdatedBy | nvarchar() | User Name of the user last updating the record |
| CUT | bit | Bit field indicating if the trigger should be canceled (CUT = Cancel Update Trigger). The security check will be executed, regardless of the value of CUT. |
| CDL | bit | Bit field indicating if the changes should not be logged. |
The CDL field could drastically improve performance when you are updating bulks of records. If you have a stored procedure that are updating thousands of records, it could be better to log that this stored procedure was executed, instead of logging each individual record.
Views, and other databaes objects, that have a "SELECT * ..." needs to be refreshed, after you have added or removed any fields from the table it is selecting from. In CR3 Database object, there are commands are available for this.
Numeric fields
Traditionally in AppFrame solutions, the "float" field type has been used. Float numbers biggest drawback is that it is not necesarly giving an exact representation of a number. Floating data is approximate; therefore, not all values in the data type range can be represented exactly. You may for example do some calculations on a field, and expecting the sum to be exactly 0. Instead it might be stored as 0.00e-23. This might cause confusions for the end-user when filtering for certain data based on these numbers.
Instead of float we generally advice using "decimal" instead. When creating a field of decimal type, you need to set scale and precision. What precision the field should have need some consideration. E.g. in an accounting system you might be sufficient with 2 decimals. However, if you do multiple mathematical calculations on this field, you might end up with unexpected results, because of rounding errors. Example: 10 / 3 = 3.33. Multiple it with 3, and you will end up with 9.99. By using more decimals you will reduce the risk for such problems. On the other hand, that means that you will need to spend more time when desiginging forms to ensure that only the appropiate number of decimals are shown.
NVARCHAR vs VARCHAR
NVARCHAR is used to store unicode data. To ensure that you application potentially could be used in languages requiring unicode (e.g. Russian), we recommend you to use the nvarchar field. Keep in mind that nvarchar uses twice as much storage in the database as varchar.
SearchColumn
SearchColumn is a field that was introduced with R3. The CR3 search functionality is using this field for searching (available in the forms datanavigator) in the recordsource. The SearchColumn should be a computed field, concatatenating all fields applicable for searching. You should use the CR3 database objects form to generate the SearchColumn field.
The Database Namespace
In the Database namespace there are several important stored procedures and functions that you should know about.
The most important ones are listed below.
| Object | Description |
| sstp_Database_AddSearchColumn | Adds the search column field to a database table. Parameters are table and a comma-separated list of fields to be included in the search column. If a search column already exists, it will be replaced. |
| sstp_Database_CreateTable | Creates a database table with the standard fields included |
| sstp_Database_CreateTriggers | Create Insert, Update and Delete triggers (impelemented as separate stored procuedres) |
| sstp_Database_CreateView | Creates a standard atbv or atbx view |
| sstp_Database_GetBaseTables | Returns the base tables for a given view |
| sstp_Database_GetDependentViews | Returns all dependent views for a table. Similar stored procedures are available for stored procedures, functions. |
| sstp_Database_Maintainance_ApplyPermissions | Applies proper permissions to database objects that has been changed since last time this stored procedue has been run. Note! Ensure that you have set up a job to execute this stored procedure. The job should be set up to be run every 10 minute. |
| sstp_Database_Maintanance_ApplyPermissionsAll | Same as the stored procedure above, expect that this stored procedure will set permissions to all database objects. |
| sstp_Database_Maintenance_RefreshAllViews | This will refresh all views that has changed since last time it was executed. It will also syncronize the stbl_Database-tables. This should be executed in a job, running every 10 minute or similar. |
| sstp_Database_RefreshView | Refreshes a view. If you have modified a table, the stored procedure needs to be executed to ensure that the dependent views are refreshed. This is normally done from the CR3 Database Objects form. |
Versioning of database objects
When you modify a database objects, a DDL-trigger is executed. We use this to keep track of the versions of the database objects. The trigger (strg_Database_ChangeLog) adds a record to the stbl_Database_Objects and one record for each version in stbl_Database_Versions, where the T-SQL script is stored. These tables could give you valuable information that could be of great help in many cases.
Example:
You have a PIMS Procurement database that was based on a core version developed at the HQ in Omega. The client needed several modifications to the system that you implemented. At the same time new features has been built into the core solution that will be beneficial for your client to incorporate in their version.
This is a challenging situation, and requires careful evaluation by the system engineer. Quering stbl_Database_Objects to get an exact overview of what modifications has been implemented in the core version, and in your client's version, since you detached from the core version, would help you in making the right decisions on how to incorporate these changes.
Database Change Requests
In a multi-developer environment, keeping the database structure well organized can be challenging. Demands from clients and users might force you to do database modifications that are not evaluated well enough. In an environment with multiple developers, we recommend to utilize the AppFrame database change requests concept, and that the number of developers allowed to do updates directly in prodcution environment is limited to a minimum.
Dev-Test-Production environments
In many cases a development-test-production environment is required for database servers. The recommended way to push database changes to the different levels is to use SQL Compare and R3Upgrader. Basically following the same procedure as when doing build updates.