Docs‎ > ‎API Creator‎ > ‎Specify your Business 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.

Register your Database

To use  API Creator you begin by registering your database. You can register multiple databases for a project (e.g, dev, integration)". Live API Creator includes services to test your connection parameters. You can use the Rest Lab to verify you can access your data.

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 specifies a Validation Name of customer:
CONSTRAINT customer FOREIGN KEY (customer_name) REFERENCES customers (name) ON UPDATE CASCADE,

So your Sum rule uses the Child Role Name like this:
Derive Customer.balance as sum(ordersList.amount_un_paid) where(is_ready = true)

And a Parent reference uses the Parent Role Name like this:
if row.customer.credit...

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:
count(employeesList)
count(onLoanToEmployeesList)

Encoding Role Names into Foreign Key Validations

For greater control, you can encode your Foreign Key Validation name like this:
<parentRoleName>__<childRoleName>

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

Examples

The sample database illustrates several classically complex examples.

Bill of Materials Structure

The Bill of Materials structure 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.