Relationships

Tables connect through the relationship field, which creates a foreign-key link to another table. Once a relationship exists, lookup, rollup, and count derive data through it without duplicating values.

Defining a Relationship

- id: 1
  name: customer
  type: relationship
  relatedTable: Customers
  relationType: many-to-one
  displayField: full_name
  onDelete: set-null
  onUpdate: cascade
  reciprocalField: orders

Cardinalities

relationType sets the shape of the link. It defaults to many-to-one.

Cardinality	Meaning
one-to-one	Each record links to exactly one record, and vice versa.
many-to-one	Many records here link to one record in the related table (default).
one-to-many	One record here links to many in the related table (uses foreignKey).
many-to-many	Records on both sides link to many on the other (allowMultiple defaults to true).

Referential Actions

onDelete and onUpdate define what happens to dependent rows when the related row is deleted or its key is updated.

Action	Effect
cascade	Propagate the delete/update to dependent rows.
set-null	Set the foreign key to NULL on dependent rows.
restrict	Prevent the operation while dependents exist.
no-action	Defer the check; take no automatic action.

Reciprocal Links & Display Field

Property	Description
reciprocalField	Field name created on the related table for the inverse (bidirectional) link.
displayField	Field from the related table shown in the UI in place of the raw id (e.g. full_name).
foreignKey	Custom foreign-key column name (used for one-to-many). Auto-generated when omitted.
allowMultiple	Allows linking to multiple related records (defaults to true for many-to-many).
limitToView	Restricts selectable related records to those visible in a named view of the related table.

Worked Example

An Orders table linked to Customers, exposing the customer's email via lookup and a per-customer order total via rollup:

tables:
  - id: 1
    name: Customers
    fields:
      - { id: 1, name: full_name, type: single-line-text, required: true }
      - { id: 2, name: email, type: email, unique: true }
  - id: 2
    name: Orders
    fields:
      - { id: 1, name: amount, type: currency, currency: USD, required: true }
      - id: 2
        name: customer
        type: relationship
        relatedTable: Customers
        relationType: many-to-one
        displayField: full_name
        onDelete: restrict
        reciprocalField: orders
      - {
          id: 3,
          name: customer_email,
          type: lookup,
          relationshipField: customer,
          relatedField: email,
        }

And on the Customers side, a rollup of order amounts through the reciprocal orders link:

- {
    id: 3,
    name: lifetime_value,
    type: rollup,
    relationshipField: orders,
    relatedField: amount,
    aggregation: SUM,
    format: currency,
  }