This page describes important database design concepts and practices that will help you make effective use of declarative business logic.
The examples here are drawn from the Sample Database
, shown below. You can right click on the image to open it in a new page while you review the material below.
Registering your Database
To use API Creator you begin by registering your database as shown below. You can register multiple databases for a project (e.g, dev, integration)". Note the services to test your connection parameters, and use the Rest Lab to verify you can access your data.
Please see the Glossary for definitions of terms commonly used in database design, such as Relationship, Parent/Child, Role, and Link/Association Table.
Role Names and Foreign Key Validations
Role Names are the names Parent and Child tables use to refer to the other table. These names are used in the business logic
expressions for sum, count, parent reference and parent copy (reference
Role Name Defaulting
In the simplest and most common case:
- The Parent Role Name (name by which the child refers to the Parent table) is the Validation Name associated with the Foreign Key
- The Child Role Name is the parent table name appended with "List"
For example, your schema would specify a Validation Name of
So your Sum rule would use the Child Role Name like this:
Derive Customer.balance as sum(ordersList.amount_un_paid) where(is_ready = true)
And a Parent reference would use the Parent Role Name like this:
Role Name Duplicate Handling
As described in Multiple Relationships, the employees table has two foreign key validations to department, specified in the schema like this:
While the Parent Role Names are properly defaulted, the Child Role Name defaulting would result in duplicates. These are resolved by prepending the foreign key validation name to the table name, resulting in:
Encoding Role Names into Foreign Key Validations
For greater control, you can encode your Foreign Key Validation name like this:
So, your schema defines two foreign keys in
means you can specify logic like
Derive product.sum_components_value as sum(
Derive product_billofmaterials.value as product.price * kit_number_required
The Sample database was specifically designed to illustrate several classically complex examples, as described below.
Bill of Materials Structure
This is described in the Sample Database.
Department / Employee Structure
There are a number of interesting elements to this structure, as described in the subsections below.
, implemented by the
. Business Logic enables you to implement a Budget Rollup
There are multiple relationships between Department and Employee. Note the use of Role Names
to enable you to reference the intended relationship.