Data model

Introduction to the Data Model

The data model screen allows you to visually design the data model of your application.

• A data model consists of tables, containing columns, and references, to create relations between tables. Rows (records) can be stored in a table. Each row consists of fields (columns).

• Views can also be included in a data model along with physical tables. A view gives a runtime presentation of data from one or more tables in one concentrated overview and is therefore always up-to-date. There are no rows stored in a view.

• It is also possible to create a snapshot . In a snapshot, just as in a view, data from one or more tables can be combined, though in a snapshot this data is actually saved. This can considerably improve performance. The RDBMS has provisions to easily refresh the data in a snapshot (sometimes even automatically).

• Not yet available A function-typed table is read-only and uses a table-valued function generated by the Software Factory as a data source. It can solve performance problems if data in a query is filtered based on parameters instead of retrieving all data and then filtering.

• A reference is a relation between tables. The integrity between these tables is automatically guaranteed on the basis of a column comparison. A reference consists of a target and source table. The source table is the lookup table.

The Data model screen

Create AI-generated data model

To create an AI-generated data model:

menu Data > Data model > tab Data model

1. Start the task Create AI-generated data model.
2. Describe the desired data model.

Upon execution, the task creates and activates the data model. The AI then generates a diagram with tables, references, and new domains as needed. Unlike the enrichment Create a sample data model with AI, the task does not allow you to select the specific tables, columns, and references to generate. See the example of the enrichment Create a sample data model with AI.

The data model description

Diagrams

menu Data > Data model > tab Design

Since a data model can consist of many tables with columns, an overview can quickly become very unclear. To avoid this, it is possible to produce several diagrams each describing a subset of the complete model. A task is available to produce one's own diagram. This diagram is always at the top of the list of diagrams and can only be used by the user who is logged in. This prevents two developers getting in each other's way when designing.

It is possible to create a personal diagram. This diagram is not visible for other developers and is not bound by any rules. It is also possible to import existing diagrams into a personal diagram. When a personal diagram already exists, the choice is provided whether the personal diagram should be replaced or merged with the diagram that must be imported.

Design

Diagrams, and therefore the data model, can be designed graphically via the Design tab

Tables and views in the Design tab page

This screen has a number of functions that help when designing the data model. A list is visible in the left hand column with all the tables and views. Besides this, there is a similar list with all the references. These can be checked and unchecked to use them within the diagram. In both lists it is possible to find objects using the find-option in the context menu (or by using the shortcut CTRL + F).

Under this list, there is an overview of the diagram and there are a number of standard parameters that can be modified.

Tasks are available above the tables to automatically add extra levels or link tables to the diagram.

The One level deeper and One level higher tasks select all linked tables above or below the selected table. This allows fast development of a diagram, which displays linked tables.

The Link table task automatically creates a link table between two tables. It can be indicated in the pop-up which table is the main table and which is the subordinate.

To create a link table between two tables, both tables have to have a primary key

Diagram options

The context menu (right-hand mouse button in the work sheet) offers the following options:

• Refresh - re-load the data model, for instance, to see modifications from other designers.

• Diagram - has a sub-menu to save, load and rename diagrams.

• Arranging - produces a graphical display by manually positioning the tables yourself. The distance is determined through an option in Settings.

• Arrange references - leave the tables alone, but position the references so that they do not run through the tables.

• Fit to screen - remove the white edges around the data model.

• Align - has a sub-menu to align selected objects in various directions.

• Print - the data model is printed on the standard printer.

• Print preview - displays a print preview.

• Export - has a sub-menu to export the graphical data model to various formats such as GIF, PNG, PDF, etc.

• Normal/Reference - switch between Normal and References. If References is selected, it is possible to draw references between columns.

• Add - a table or reference can be directly added to the model.

• Delete - delete a table or reference from the diagram and/or model. With delete table, the table is not deleted but only removed from the data model. With delete reference, there is a choice between removing the reference just from the diagram or also from the model.

Adding new tables, views and snapshots can be done using the tab Tables next to Design but can also be accessed easily via the buttons in the diagram.

Tables

An overview of all tables is accessible via the Tables tab page.

menu Data > Data model > tab Tables

Overview of the 'Tables' tab

Go to related screens

This screen contains tasks that take you to related screens for the selected table. See Go to a related screen for an object.

Copy a table

It is possible to make a copy of a table.

menu Data > Data model > tab Tables

1. Execute the Copy table task.

2. Enter a name for the copy in the field To table.

   By default, the table is copied including all columns and settings, such as grid and form order, sorting, conditional layout, and prefilters.

3. Select what else you want to include in the copy:

   • References (details) - References to details. Default not included. See References.
   • References (lookups) - References to lookups. Default not included. See References.
   • Components - Include component data: maps, schedulers, and cubes.
   • Table reports - Include the links to reports. See Table reports.
   • Table tasks - Include the links to tasks. See Table tasks.
   • Functionality assignments - Include the functionality assignments. If you deselect this option, the copied table will not be assigned to any control procedure. Use this to create a bare object (the copied table). Then, create a new control procedure, and assign the copied table to it.

Copy a table

Database storage location

You can divide the database into separate files and store them on separate drives. For example, you can store invoices on the SSD and customer data on the HDD. You can do this by selecting a Storage location for a table to retrieve data faster.

Memory optimized

It is possible to specify if the database management system should keep a table into memory to optimize performance. Available options are:

• No
• Yes (durable)
• Yes (transient) - only available for SQL server models

More information about memory optimized tables in SQL server can be found see here.

For DB2 models, the property KEEPINMEM will be used. More information about this property can be found here.

For Oracle models, the option CACHE will be used. More information about this option can be found here.

Create application logic

You can use AI to create application logic.

menu Data > Data model > tab Tables

1. Start the task Generate application logic with AI .
2. Describe the desired business rule in natural language. The AI will automatically create the application logic and open the control procedure.

The AI generates application logic based on the selected table. However, if your text describes something unrelated to the data in that table, the AI might assign the logic to a different table.

See also the enrichment Generate application logic based on natural language business rules.

Create application logic

System versioning for SQL Server

SQL Server supports temporal tables (also known as system-versioned temporal tables) as a database feature.

Temporal tables are a type of table designed to keep a full history of data changes. They automatically track the history of data changes, making it easier to analyze historical data or audit changes. Temporal tables consist of two tables: a current table (the main table) and a history table. The current table stores the latest data, while the history table stores all the previous versions of the data. More information about system versioning can be found in the Microsoft documentation.

To activate system versioning for a table:

menu Data > Data model > tab Tables > tab Form

1. Select the checkbox System versioning.
2. Enter a Retention period. The default is Indefinitely. To prevent potential issues with storage costs and performance, enter a reasonable value in years, months, weeks, or days. The retention period is applied to the table when creating the database or executing an upgrade. During an upgrade, only historical data within the specified retention period is copied.

Activating system versioning for a table is a datamodel change. As a result, the Software Factory:

• Generates code in the CREATE and UPGRADE scripts. The two date fields (tsf_valid_from and tsf_valid_to) are generated in the script automatically, as hidden fields. They are not created in the Column list in the Software Factory.
• Creates a history table with the name [table_id]_history. This table appears in the CREATE and UPGRADE scripts, but not in the list of tables in the Software Factory. Because of this, the settings in Data mapping for the original table will also apply to the history table.

To query data from a table for a certain point in time, you can use for system_time as of. For example:

select *
from customer
for system_time as of '2019-09-01 T10:00:00.0000000'

Note that in the result of this example, columns tsf_valid_from and tsf_valid_to are not shown because they are hidden. To see the values of these columns, you have to make them explicit in the select list. For example:

select customer_name, tsf_valid_from, tsf_valid_to
from customer
for system_time as of '2019-09-01 T10:00:00.0000000'

Data partitioning

note

Warning: This is an advanced feature that should not be used without a proper understanding of SQL Server's implementation of partitioning.

Partitioning allows data in a table to be stored in separate chunks on the file system. For very large tables this could lead to a performance increase.

To get started with partitioning for your end application, a partition scheme needs to be created:

menu Data > Data model > tab Tables > tab Form

1. Open the pop-up next to the Partition scheme field.

2. The following information is required:

   • Partition Scheme: A name for the partition scheme.
   • Domain: A domain to determine which data type will be used to partition the data. This domain needs to be of the same data type as the column that is going to be used as the partition column.
   • Range: The alignment for the boundaries of the partition ranges. The boundaries can be aligned either left or right.

   Enter a partition scheme

3. Open the Partition boundaries detail tab. Boundaries determine where the data is going to be split. In the example below, every year on January 1st.

4. Set the partition boundaries.

   Set the partition boundaries

5. Return to the underlying table.

   To apply the partitioning to a table:

6. Select one of the created partitions in the field Partition scheme, and select a column in the field Partition column. The partition column is the column that contains the data that will be used to partition the table.

   

7. A database upgrade is necessary to apply or remove data partitioning, since this concerns a change in the data model.

Columns

The columns are defined within a table:

menu Data > Data model > tab Tables > tab Columns

A column has the following properties:

Columns tab page

General

menu Data > Data model > tab Tables > tab Columns

• Table - the table within which the column is created (ID).
• Column - the column name in the database (ID).
• Column alias - a column alias can be optionally added here (ID).

Model settings

menu Data > Data model > tab Tables > tab Columns

• Sequence number - determines the physical sequence in the database.
• Domain - determines the data type and control of the field (ID).
• Case type - determines whether this field is only filled with upper case letters, lower case letters or a combination of both.
• Identity - the database will automatically issue a number for this field.
  • Seed - begin value of the number.
  • Increment - steps with which the number is increased.
• Primary key - determines whether the field is part of the primary key. A table's first column is by default marked as a primary key. Clear the checkbox if you want to deviate from this.
• Column type - determines how the column is displayed (Editable / Read only / Hidden). Combined with the Access type the column display can also be Unauthorized (see Access types).
• Mandatory - determines whether a field is mandatory or optional.
• Foreign key - indicates whether a field is a foreign key from another table.
• Use for optimistic locking - indicates whether Indicium includes the column in a comparison to determine whether the record has been changed while editing. You will be notified if the comparison determines that the row has been edited.
• Default value - See Default value for a column.

Default value for a column

menu Data > Data model > tab Tables > tab Columns

In the field Default value type, you can select a Constant value or an Expression:

• Default constant value - A fixed value that is filled when adding a record. With a change, this value is not filled.
• Query - The expression used to determine the default value. The query cannot use values from a new record.

For the following functions, the Software Factory will not generate a function but instead use the given Query as the default value. To use these functions as a default value query, you should specify them without select.

• newid()
• getdate()
• getutcdate()
• sysdatetime()
• dbo.tsf_user()
• dbo.tsf_original_login()

note

• Default values for columns will be placed in the database.
• Default queries may be placed in the database to be used by insert statements in application logic. This depends on whether or not the database platform supports this. SQL Server databases are currently the only databases that support this.

Calculated field

menu Data > Data model > tab Tables > tab Columns

• Calculation type - indicates with which calculation the field is filled. A choice can be made from:

  • None - the user should enter a value.

  • Expression - the GUI executes the calculations. This is not stored in the database, neither is the column. Using the T1 alias is possible.
    Example: concat_ws(' ', t1.first_name, t1.last_name)

  • Calculated column - a virtual column that calculates its values. Do not specify an alias for columns.
    Example: (100.0 + tax_percentage) / 100.0

  • Calculated column (function) - the database executes the calculation with help of a function. Specify a query that should be wrapped by the Software Factory into a function. This function will then be used for the calculated field's value. Use an @[col_id] for its value.
    Example: select sum(order_total) from sales_order where sales_order_id = @sales_order_id

• Query - specify a query to have a value in this field calculated by the GUI or database. This value is not stored in the database. Example: price * quantity.

Expression dependencies

It is possible in the Software Factory to define virtual fields that are not stored in the database but are evaluated by the user interfaces. This type of field is called an expression field. For these expression fields, that are sometimes also called lookup info fields, no upgrade of the end product is necessary.

Example

A person can be displayed as first name + prefix + surname. This name is composed and therefore does not have to be stored in the database but is often used in the GUI.

With calculated fields, a query has to be provided with which the value is retrieved by the user interface. For instance, this can be a composition of several fields, a (range) function or a subquery. Reference can be made in the query to other fields from the table by using the alias t1.

The query that is provided with expression fields will be added to the select clause of the query. For example:

select 
t1.[customer_id], 
t1.[customer_naam], 
(  select t1.street + ' ' + t1.number_number + ', ' + c.name
   from country c 
   where c.country_id = t1.country_id
) as t1.[address]
from customer t1 

The columns on which the expression is dependent are shown on the Expression dependencies tab. If one of these columns changes in the end application, then the expression field will also be updated. If, for instance, street changes, then the composite address has to use the new street.

Calculated field with a Query

Copy columns

Columns for a table or view can be copied from all existing tables and views using the task Copy on the tab Copy columns.

When executing the task Copy columns, a pop-up screen is displayed to optionally enter a prefix for the copied columns. For example, the column name can be copied from the employee table using the prefix employee, resulting in column ID employee_name. When the column ID already starts with the specified prefix, then the prefix is ignored.

In addition to copying the prefix, you can also copy the identity settings (Identity, Seed, and Increment) and the settings Primary key and Mandatory. This provides more control over the desired configuration of the new column. For example, including the setting Mandatory copies the mandatory status (on or off) from the source column.

Once you have copied columns to a table or view, all columns in the table or view are automatically renumbered.

Pop-up for copying tables

After all columns are defined and/or copied and Automatic or Modifiable is chosen for the view option, then the from clause of the view can be constructed by executing the task Generate view from clause.

Views

A view is a logical table that is composed from fields from other tables. The data in a view is not stored in the database but composed at runtime on the basis of a query.

• Automatic: use this method when only data from the linked (source) tables need to be used in the view. The from clause is created automatically. This is generated, but it can also be forced by executing the Generate view from clause task.
• Modifiable: use this method to be able to modify the from clause, where clause, group by clause or having clause of the view. The select clause is built up on the basis of the view table and view column fields in the columns.
• Template: the last option provides the possibility to create a template in which the entire select query is written, which then needs to be woven into the view. The Template option provides the freedom to use the most complex selection structures or to construct them dynamically. Creating this template is explained here.

Table-valued function as a table

This functionality is not yet available

It is possible to use a table-valued function as a table. A function-typed table is read-only and uses a table-valued function generated by the Software Factory as a data source.

A function-typed table can solve performance problems, for example, when retrieving the history in a query within a specific timeframe. The performance improves if data in a query is filtered based on parameters instead of retrieving all data and then filtering. Another example is the selection of a specific model or company throughout the entire application.

note

This feature is only available for SQL Server (and not yet for DB2 or Oracle) and only supports inline statements.

To create a table-valued function:

menu Data > Data model > tab Tables > tab Form

1. Select Table type 'Function'.

   Table type 'Function'

2. In tab Tables, open tab Columns > tab Form.

   Now set the input parameters for the function table:

3. For the relevant columns, check the Function input parameter box.

   Function input parameter

   Now add code to the function with a control procedure:

   menu Business Logic > Functionality > tab Control procedure

4. Add the code. A return value has to contain the table as declared in the data model.

   To fill the function input parameters:

   menu User interface > Subjects > tab Data > tab Prefilters > tab Prefilter > tab Form

5. Add a prefilter to the table with "equal to" or "is null" conditions.

Generated code for a function table

The numbers [1], [2], etc refer to the steps as described above.

/* Drop function my_own_project first. */

if exists (select 1 from sysobjects
           where name = 'my_own_project' and (type = 'FN' or type = 'TF' or type = 'IF'))
   drop function my_own_project
go

create function my_own_project                          --[1]
(
   @project_id       project_id                         --[2, 3]
)
returns table
-- This function returns an inline table with the following definition:
--(
--   project_description   description   primary key,   --[2]
--   amount_of_hours       amount        null           --[2]
--)


    --control_proc_id:      function_my_own_project     --[4]
    --template_id:          function_my_own_project
    --prog_object_item_id:  function_my_own_project
    --template_description: Select my project from project.
    return (                                            --[4]  
        select 
            project_description,
            amount_of_hours
        from project
       where project_id = @project_id
           );

go

grant select on my_own_project to public
go

Indexes

The Software Factory automatically creates indexes for foreign keys and sort sequences. To optimize performance, it might be necessary to add additional indexes to a table.

menu Data > Data model > tab Tables > tab Indexes

1. Select a Table.

2. Enter an Index name.

3. In field Type of index, select the type. The following types are supported:

   • Clustered
   • Non-clustered
   • Full-text (SQL Server)
   • Encoded vector (DB2)
   • Columnstore (Clustered)
   • Columnstore (Non-clustered)
   tip

   Columnstore indexes store data by column instead of by row, offering significant data compression and reducing storage needs. This leads to faster queries because less data is accessed during query execution.

   • An example of columnstore indexing is available in the Thinkstore. It is called Columnstore index and AI search. The data model in this example consists of a product table that can compare itself to other products within that table and provide a matching score. A higher score indicates that the product is more similar to the selected product. This is also called LLM embedding.
   • For more general information about columstore indexes, see: Microsoft

4. Optional: You can divide the database into separate files and store them on separate drives. For example, you can store invoices on the SSD and customer data on the HDD. You can do this by selecting a Storage location for an index to retrieve data faster.

5. Optional: Select the checkbox Unique if an index has to be unique. Unique indexes can be marked to Exclude null values. In that case, if any of the row's indexed columns contains a null value, the row will not be checked by the unique index.

6. Optional: If you are using SQL Server, it is possible to enter a Where clause. This can be beneficial, for example, for performance when only active records need to have an index.

An index for a table

References

Create a reference

A reference is a relation between two tables. The target table has one or more fields (the foreign key) of which the combined values have to appear as the primary key in the source table. This column comparison is specified in the reference columns.

To create a reference:

menu Data > Data model > tab Design

• In Reference status, drag with the mouse from the source table to the target table.
• In Normal status, hold down the CTRL key and drag with the mouse from the source table to the target table.

Tables and views

If you release the mouse button, the pop-up Create reference appears. Specify the column comparison:

• The column comparison is based on the primary key of the source table, which therefore has to be present.
• Source and target table - The tables you connected.
• Integrity - If you clear the checkbox Check integrity, the reference is only used to display details or lookups in the user interfaces. The integrity between tables is not checked by the RDBMS. If the source or target table is a view, the integrity cannot be checked.
• Source and target columns - If a column from the source table is not available in the target table, it gets a checkmark to indicate that it will be created. If you deselect the column, you can choose an existing column from the target table.
• User interface - You can also select whether the reference has to be used as a lookup (Show lookup) or a detail (Show detail).

Pop-up for creating references

Reference column details

After creating a reference, you can further specify or change the reference columns.

To change the configuration:

menu Data > Data model > tab References > tab Reference columns

You can change the following:

• Reference
• Source table
• Source column
• Target table
• Target column
• Sequence number
• Use detail null comparison - See Use detail null comparison.

Use detail null comparison

Usually, when details are loaded, the value of the parent record (source) must be equal to the value of the table (target). If both are NULL, they are not considered a match.

However, it is possible to allow NULL values in the reference columns. This can result in a detail record being shown even even if the parent record has a NULL value.

For example, in this situation:

ref	Parent (source): employee	Detail (target): project
ref_col	employee.expertise	project.required_expertise

• By default, if the employee.expertise is NULL, no projects are shown:

  employee.expertise	Resulting details
  (No record selected)	No projects
  Record with expertise: 1	All projects with required_expertise: 1
  Record with expertise: 2	All projects with required_expertise: 2
  Record with expertise: NULL	No projects

• If a NULL comparison is allowed, projects that do not require expertise are shown for employees with no expertise:

  employee.expertise	Resulting details
  (No record selected)	No projects
  Record with expertise: 1	All projects with required_expertise: 1
  Record with expertise: 2	All projects with required_expertise: 2
  Record with expertise: NULL	All projects with required_expertise: NULL

To allow a NULL comparison:

menu Data > Data model > tab References > tab Reference columns

1. Select the checkbox Use detail null comparison.

Validation

Validating the data model is possible in the Validation tab. Through this, errors can already be identified at an early stage. The Validation component is explained in more detail here.

Example of the validation screen

Starting the application

When the data model is completed, the database can be created or upgraded. This is described in Creation. When there is no user interface set up, there is nothing visible in the end product. Tables are accessible after a menu has been set up.

Starting the user interface is possible once the database has been created or by using the Mock Database Provider to display a preview without an end product database.

View an object's history

Historical overviews are available on a large number of screens in the Software Factory. For example, in:

• menu Business logic > Functionality > tab Control procedure
• menu Data > Data model > tab Tables
• menu User interface > Messages

These overviews are based on temporal tables. They include each point in time a record was created, updated, or deleted. This offers insight into a record's status in the various model versions. All changes are in the model's timeline, making it also visible if no changes have been made in a model version.

Changed columns will be shaded yellow, and records that have been added, changed, or deleted will be marked.

To see the history of an object:

1. Select an object.
2. Activate the history with the CTRL+H.

Tab Form contains the object's query fields if present. These are not shown in the list to maintain readability.

Tab Associated work contains an overview of the work items linked to this object. See the Work overview manual.

Use CTRL+H to see the history and linked work items for an object