Data source table/repository design

Data source table/repository design

Introduction

This document is a guideline to how you can design repository tables when designing a new application or application component, which can be the base for generating tables in the database. The guideline will help you to build a data model for the application which simplifies the building of application with less coding.

This guideline have information about different types of tables and a also a list of standard columns or column types to use.

Tables

Tables can be set in different categories, which has different requirements in features, which then can be solved with different setups of columns. 

Table types

The defined table types are:

• Master table
• Transaction table
• Settings table
• Control table
• Work table
• History table
• Virtual table

Note that not all have values in the actual table editor. Settings, Control are like Master table.

Master table

A Master table is the "home" of an entity. It normally has one key-field (code) for the entity. It can have no or limited lifecycle, like active/inactive. Normal features are an identifier, a designation, a description, a note field for the administrator and modification attributes. More attributes can be added to support the application.

Standard Master table columns are thereby:

• Enterprise
• Code
• Name
• Description
• Text
• CreatedBy
• CreatedDate
• CreatedDateTime
• ChangedBy
• ChangedDate
• ChangedDateTime

Key values are:

• Enterprise
• Code

Transaction table

A Transaction table is the "home" of an entity describing a transaction fully or partly. It normally has one key-field (identity) for the entity. If it describes sub-entities it can have additional key fields like LineIdentitiy. The transaction normally have a lifecycle, like different states and statuses. Normal features are an identifier, a note field for the administrator and modification attributes. More attributes can be added to support the application.

Standard Master table columns are thereby:

• Enterprise
• Identity
• Text
• CreatedBy
• CreatedDate
• CreatedDateTime
• ChangedBy
• ChangedDate
• ChangedDateTime

Key values are:

• Enterprise
• Identity

Settings table

A Settings table is the wher you have the controlling attributes of an entity or relations of entities. It normally have one or more key-fields (Codes) for the entites. It can have no or limited lifecycle, like active/inactive. Normal features are a identifiers,  a note field for the administrator and modification attributes. More attributes can be added to support the application.

Standard Master table columns are thereby:

• Enterprise
• Code (s)
• Text
• CreatedBy
• CreatedDate
• CreatedDateTime
• ChangedBy
• ChangedDate
• ChangedDateTime

Control table

A Control table is the wher you have the controlling attributes of a transaction entity. It normally have one key-field (Type) for the entity. It can have no or limited lifecycle, like active/inactive. Normal features are an identifier, a designation, a description, a note field for the administrator and modification attributes. More attributes can be added to support the application.

Standard Master table columns are thereby:

• Enterprise
• Type (or Policy)
• Name
• Description
• Text
• CreatedBy
• CreatedDate
• CreatedDateTime
• ChangedBy
• ChangedDate
• ChangedDateTime

Key values are:

• Enterprise
• Type

Work table

To be described

History table

To be described

Virtual table

To be described

Standard columns

• Enterprise
• Code
• Identity
• Name
• Designation
• Description
• Category
• Type
• 
  
• Note
• Text
• 
  
• CreatedBy
• ChangedBy
• Createddate...
• 
  
• Quantity
• Amount
• Indicator
• 
  
• State
• Status
• ActiveIndicator
• Userid

Here is a short definition of the standard columns. xxx means the qualfying prefix to the field, like "Customer", "SalesOrder".

Enterprise 

Enterprise is the built in session column in Comflow applications as a divider of totally different businesses in one and the same database. The values is set at log on in SessionWorkspace via the Session-manager and is used to key tables, where it is set automatically.

Code

xxxCode, like CurrencyCode, is the key value for an entity, which can be set manually to an appropriate value. The field is normally of the type String and 3 to 10 characters long.

Identity

xxxIdentity, like ActivityIdentity, is the key value for a transaction entitiy, who's value normally is set automatically. The field is normally of the type String and 10 to 18 characters long.

Name

Name or xxxName is the designation of an entitiy. Together with the code/identity, the name defines the entity for a user. The field is of the type String and 25 to 50 characters long.

Designation

Designation is practically the same as Name.

Description

Description or xxxDescription is the short description of an entity, giving more information to the entity than the entity Name. The field is of the type String and 50 to 100 characters long.

Type

Type or xxxType is a code that classifies entities being of the specific type. The Type can also have attributes that control the creation and/or management of the related entity. The field is normally built as a Code-field.

Policy

Policy or xxxPolicy is a code that classifies entities being managed in a certain way The Policy thereby have attributes that control the creation and/or management of the related entity. The field is normally built as a Code-field.

Category

Category or xxxCategory is a logical typing of an entity, aimed to be used for programming control of entities. A Type or a Policy can have a Category as an attribute, in order to divide them into different categories, where Program logic can be applied to the value of the Category. The field is of the type Integer and 1 to 11 characters long with defined constant values.

Note

Note or xxxNote is the "what ever text"-feature of an entity, giving the user the possibility to write what ever in relation to an entity. The field is of the type Clob and 512 to 1024 characters long.

Text

Text or xxxText is the same as Note.

CreatedBy

CreatedBy is the user identity of the user who created the record in the database, which is managed automatically when inserting the record. The field is of the type CreatedBy and 10 to 256 characters long.

ChangedBy

ChangedBy is the user identity of the user who last changed the record in the database which is managed automatically when updating the record. The field is of the type ChangedBy and 10 to 256 characters long.

CreatedDate

CreatedDate is the date when the record is created in the database, which is managed automatically when inserting a record. The field is of the type CreatedDate and 10 characters long.

Createdtime

CreatedTime is the time when the record is created in the database, which is managed automatically when inserting a record. The field is of the type CreatedTime and 6 characters long.

CreatedDateTime

CreatedDate is the date and time when the record is created in the database, which is managed automatically when inserting a record. The field is of the type CreatedDateTime and 14 characters long.

ChangedDate

ChangedDate is the date when the record is updated in the database, which is managed automatically when updating a record. The field is of the type ChangedDate and 10 characters long.

ChangedTime

ChangedTime is the time when the record is updated in the database, which is managed automatically when updating a record. The field is of the type ChangedTime and 6 characters long.

ChangedDateTime

ChangedDateTIme is the date and time when the record is updated in the database, which is managed automatically when updating a record. The field is of the type ChangedDateTime and 14 characters long.

Quantity

To be described

Amount

To be described

Indicator

To be described

State

To be described

Status

To be described

ActiveIndicator

To be described

Userid

To be described