Creating APIs‎ > ‎Database Creation‎ > ‎

Managed Data Servers

As an application developer and author of your API project, you want to create a new model (schema) that captures your business entities (for example, orders and products) and figures (for example, revenue, and sales). You also want to create an API for this model so that client applications or partner integrations can consume this model.

Managed data servers are the connections to a database server that are high-level administrative credentials. You must have dba or root account permissions for the connection credentials to create other databases and other users.

You can create managed data using the following database types:

  • Derby
  • MySQL
  • PostgreSQL

Manage the Content in your Data Source

Data sources can point to managed databases. Data sources for a managed database are not different than any other data source except that the databases they represent are managed databases.

Managing the content in your data source involves manipulating the managed data schema, either using Data Explorer or using API Creator. In API Creator, you do this by way of the @tables@columns, and @relationships system endpoint requests. The following procedures describe how to do this using Data Explorer.

Prerequisite: You have set a managed data server.

For more information:

Create Tables

You can create tables in your data source using the top left "+" icon in the Grid. You can insert records into managed tables. The tables you create display as tabs in the Child Grid. These tabs represent existing child tables for the parent table in the Form.

Creating a new table creates a new tab in the Child Grid that represents a new/existing child table for the parent table in the form.

    1. Go to Execute, Data Explorer, Tools, Toggle Author Mode.
      You are accessing Data Explorer in Author Mode.
    2. Click the plus sign (+) next to the Endpoints drop-down in the Grid, which defaults to the Tables endpoint.
      The Create Table window opens.
    3. Enter a unique name for the table in the scope of your schema, select a data source, and then click Create.
      Note: In Data Explorer, the table's full name is the data source prefix:table_name.
The managed table is created, containing the ident and Name columns and a sample row.

Add Relationships between Tables

Creating a child table or parent table defines the association, or relationship, between the tables. Data Explorer uses this defined relationship when performing lookups linking rows from one table to another. A lookup is a relationship in the database that a child table has to a parent table. The relationship between the child and parent must exist before you can create rows in the child table that is associated with the parent. For example, the relationship between Line Items to Orders.

You must relate tables in the same data source. If you want to define relationships that cross databases, create virtual foreign keys from API Creator.

For more information:

Create a Parent Table

You can relate a row to a specific parent record by creating a parent table or relationship. Defining the association creates the following:

    • A managed parent table.
    • A relationship between the child and the parent table.
    • A new field in the child table that Data Explorer can use to perform lookups on the parent table.

For more information about relationships, parent roles, and children roles, see Database Concepts.

Prerequisite: A table is selected in the Grid.

    1. In Data Explorer (in Author Mode), click Add Field in the Form or in the Grid.
      The Field Details window opens. The default name for the field displays in the Name field.
    2. Select Lookup as the Type, enter a table name in the Parent Table Name field or select a table that already exists from the Table drop-down, and then click Create.

The parent table is created.

Create a Child Table

Creating a child table creates the following:
  • A managed child table.
  • A relationship between the child and the parent table.
  • A tab representing the child relationship in the Child Grid. This relationship creates a foreign key in the child table.

For more information about relationships, parent roles, and children roles, see Database Concepts.

Prerequisite: The parent must be a member of your data source for a managed database.

  1. In Data Explorer (in Author Mode), click a new tab (indicated with the plus (+) sign) in the Child Grid.
    The Create Child Table window opens.
  2. Enter a name for the new table, a name that is unique to your data source in the Table Name field, and then click Create.
The managed child table is created. In Data Explorer, the table's full name is the data source prefix:table_name.

Relate a Child Table to Another Parent

Relating a child table to another parent adds another parent lookup.

Prerequisite: A table is selected in the Grid.

  1. In Data Explorer (in Author Mode), click Add Field in the Child Grid.
    The Field Details window opens. The default name for the field displays in the Name field.
  2. Select Lookup as the Type, enter the parent table name or select a table that already exists, and then click Create.
The child table is related to another parent.

Add Fields

Fields represent the columns in your database. The term field is used to stay within the metaphor of applications and screens, and not database terminology.

Prerequisite: A table has been created in your data source for a managed database.

  1. In Data Explorer (in Author Mode), select the managed table to which you want to add field.
  2. Do one of the following:
    • To add a field to the table that you are viewing in the Form, click Add Field in the Form.
    • To add a field to the table that you are viewing in the Grid, click Add Field in the Grid.
    • To add a field to the table that you are viewing in the Child Grid, click Add Field in the Child Grid.
    The Field Details window opens.
  3. Enter a name for the field (or accept the default), select a field type, and then click Create.
The field is added.

Update and Delete Tables, Fields, and Relationships from your Data Source

You can update and delete tables, fields, and relationships using the widgets:

  • To change a table setting, click the table icon in the Form and in the Grid.
For more information about changing table settings, including more information about the table icon and grouping form fields, see Author Mode.
  • To change a field setting, click the gear icon next to the field name.
    Prerequisite: The field must not be a primary key or foreign key.
For more information about changing field settings, including more information about the gear widget and controlling the fields to display, see Author Mode.
  • To change a relationship setting, click the child tab settings icon next to the child tab.

For more information about changing a relationship setting, including more information about this icon and controlling the Child Grid, see Author Mode.

For more information about relationships, parent roles, and children roles, see Database Concepts.

Best Practices

  • Define your lookup fields first. For example, in defining Products for Items, define the lookup from Item to Product.
  • Factor common data to the Lookup table. For example, define fields associated with Product with the product, not on the Item table.

Note: You can "join" in the Product information so you can see it.

For more information about how to join in parent tables, see the "Control Fields to Display" section on the Author Mode.

More Information

For more information:

  • About using Author Mode with an existing databases, see Author Mode.
  • About the Data Explorer functions, see Data Explorer.