Database Concepts

The following diagram provides a background of key database concepts, including API Creator terminology. Nodes represent tables, or entities, and lines represent relationships, also often referred to as associations:


Best Practice: Maintain a diagram of your domain objects.

Note: You can produce database diagrams from a database tool, such as MySqlWorkbench.

Table - No Repeating Lists of Values

Tables correspond to "domain objects," but what you think of as a domain object is (quite often) several tables. For example, you may think an of Order as a table, but it is really two, Order and Items. Column values cannot be a repeating list. In this example, the items purchased in an order is a repeating list and must be broken out into a child table.

Keys

In the majority of cases, database rows are identified by a key, one or many columns that uniquely identify a row. In most cases, key is taken to mean "unique" key, which is named and identified to the database management system (DBMS).

There are two common approaches:

  • Natural key. Choose a key from attributes (columns) that exist in the real world.
For more information about natural keys, see the Natural key wikipedia page.
  • Primary key/Surrogate key. Let the database assign a unique ID for a row (named differently in different databases, for example "Identity" in MySql).
For more information about primary key/surrogate key, see Automatic Key Generation.

Relationships

Relationships are a foundation of data modeling. Although there are many kinds, the most fundamental concept is a one-to-many relationship between two tables in a relational database. These are sometimes called hasA relationships. Represented by a line (arc) in the diagram above, it represents one parent object that is related to many child objects.

Relationships are core to many other services, including rules, creating joins in custom resources, JavaScript programming with the object model, and the Data Explorer. It is therefore important that you define relationships and define them properly. Relationships are discovered by foreign keys defined in your database. Within a relationship, the table containing the foreign key on the "many" side is the child. For example, Purchaseorder is a child to Customer. 

The table containing the primary key on the "one" side is the parent. For example, Purchaseorder is a parent to Lineitem.

For more information:

The following sections describe how API Creator computes role names for relationships in non-managed databases.

For more information about role names for managed databases, see Managed Database Architecture.

Parent Role

A parent role is the name a child role uses to access its parent within a one-to-many relationship. In the previous example, customer represents the Customer for a given purchase order.

API Creator creates an JavaScript object model, providing access to related data (caching is provided to optimize repeated access) by using the foreign key name, for example:

var theCustomer = anOrder.Customer;

For more information about the JavaScript object model, see Row Objects.

Children Role

A children role is the name a parent role uses to access a collection of related children within a one-to-many relationship. In the following example, orders represents the Orders for a given Customer.

The JavaScript object model provides access to child data by using:

<Child Table Name> + "List":

For more information about the JavaScript object model, see Row Objects.

Note: API Creator normalizes <Child Table Name> to include only alpha, numeric, and underscore.

Duplicate Child Role Names

If there are multiple relationships between two tables, the previous default algorithm results in duplicate names. API Creator disambiguates the names by computing the Child Role Name as:

ForeignKey + <ChildTableName> + List

For example, the employees table has two foreign key validations to department, specified in the schema, for example:

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

API Creator computes the Child Role Names:

count(reportsToemployeesList)
count(onLoanToemployeesList)

You can encode role names into the foreign key using double underscore ("__", shown as blanks in the following example) to separate parentRole from childRole:

CONSTRAINT reportsTo__ReportingEmployees FOREIGN KEY (department_name) REFERENCES departments (name) ON DELETE....,
CONSTRAINT onLoanTo__OnLoanEmployees FOREIGN KEY (on_loan_department_name) REFERENCES departments (name) ON DELETE ...

Terminology Note: What the Accessor Returns

While the concept is usually obvious, the terminology can be troublesome. There are two reasonable (but opposite!) explanations to defining a child role:
  • Origin-based. The child role is a role in a child (that gets parents).
  • Returns-based. The child role is in a parent (that gets children).
Role names correspond to accessor names. row.<Parent> returns a parent and is a parent accessor.

Relationship Examples

The Logic Sample database includes classic examples of the kinds of parent/child relationships present in virtually all databases.

For more information about key patterns, see Relationship Examples.

More Information

For more information about associating a child with its parent using PUT/POST JSON mechanisms, see PUT.