# Database Design and BO2DDL

reviewed: 6 April 2025

Required Tables in Primary Database

There are a number of tables required in the primary database. These are:

Table	Usage	Sequence
audit	Audit entry	auti
document	Instance of document in document management module	document
documentType	Document type in document management module	-
emailOutProfile	Profile to send emails	emailOutProfile
sequence	Next sequence number for automatics	-
user	User profile	user
userAccessByIP	Blacklist / whitelist users by IP	userAccessByIP
userEmailAddress	Email address for user profiles	userEmailAddress
userGroup	User group	-
userGroupMember	Membe rof user group	userGroupMember
userLogin	Login settings for a user profile	-
userPasswordHistory	Password history for a user profile	userPasswordHistory
userPreference	User profile preference	userPreference
userSession	Details of a user session	-
userSessionContextEntry	Entry stored in user session context	userSessionContextEntry

Constraints

Typically, we advice not to have constraints defined in the database. These are handled by CaseMaster and handling them by the database has a number of drawbacks:

• The solution is less portable; it may not run correctly on databases that do not support constraints
• The developer has to consider the constraints for example when deleting records or inserting records related to other records

Indexes

Indexes are essential for a good performance on larger databases.

Most RDBMS-es have great tools for suggesting the creation of missing indexes. However, you can also give CaseMaster some hints with adding an indexes section to your BO.

This is used by the BO2DDL utility (see next paragraph).

    indexes: <
        <@bo/index
            name: 'search'              // Name of the index
            group: 'id,name,summary'    // Attribute included the index
            include: 'status'           // Only relevant Where RDBMS supports include columns
            unique: false()             // Is index unique or not (defaults to false)
        >
        <@bo/index
            name: 'status'
            group: 'status,orderDate' 
            unique: false()
        >
    >

BO2DDL

BO2DDL is a utility to generate the DDL (Data Defintion Language) statements to bring a datbase table in line with the associated BO definition.

The BO2DDL is implemented as a function in VSCode (CaseMaster: Generate DDL). This function should be invoked when the current file is a BO. It will open a new window that will lists the DDL statements to bring the table in line with the BO definition.

The following is s useful script that you may wish to add to your system that will loop over all entities and invoke BO2DDL:

function bo2ddlAll( indexes:false(), sequences:false() )

    set( 'sb', sb.create() )

    iterate iterator.ofPB( api.descriptors(), 'descriptor' )

        // Exclude //layer based inheritance descriptors and descriptors without a table
        if and(
            isNotNull( boDesc.getTable( [descriptor] ) ),
            not( startsWith( boDesc.name( [descriptor] ), '//' ) )
        )
            set( 'ddl', devUtils.bo2ddl( boDesc.entity( [descriptor] ), indexes:[indexes], sequences:[sequences] ) )

            if isNotNull( [ddl] )
                sb.appendLineFormat( [sb], '-- {0}', boDesc.entity( [descriptor] ) )
                sb.appendLine( [sb], [ddl] )
            end-if
        end-if
    end-iterate

    return sb.get( [sb] )

end-function

<End of document>