Docs‎ > ‎CA Live API Creator‎ > ‎Rules‎ > ‎

Database Design

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 customer:

CONSTRAINT customer FOREIGN KEY (customer_name) REFERENCES customers (name) ON UPDATE CASCADE,

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:

 CONSTRAINT reportsTo FOREIGN KEY (department_name) REFERENCES departments (name) ON DELETE....,
 CONSTRAINT onLoanTo FOREIGN KEY (on_loan_department_name) REFERENCES departments (name) ON DELETE ...

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 product_billofmaterials:
CONSTRAINT kit__components FOREIGN KEY (product_name_kit) REFERENCES products (name) ON DELETE ...
CONSTRAINT product__inKits FOREIGN KEY (product_name) REFERENCES products (name) ON DELETE ...

means you can specify logic like

Derive product.sum_components_value as sum(components.value)

Derive product_billofmaterials.value as product.price * kit_number_required

Key Examples

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.

Recursive relationship

Each Department can have Sub-Departments, implemented by the HeadDeptId.  Business Logic enables you to implement a Budget Rollup.

Multiple Relationships

There are multiple relationships between Department and Employee.  Note the use of Role Names to enable you to reference the intended relationship.