Designing Tables: Think in Documents, Not Joins

The problem

If you have designed databases before, your instinct for an order is probably two tables: an order_headers table and an order_lines table, linked by order number and joined when you read them.

Vince Platform Tables don't work that way. There is no join. A record in a Vince Platform table is a JSON document: it can hold plain values, but also lists and nested objects. Designs that work beautifully in a relational database can become awkward here — and designs that would be frowned upon in a relational database are often exactly right.

The solution

Design each record around how the data is used: what is written together, read together, and changed together belongs in one record. This guide shows how to apply that to a typical one-to-many relationship, how primary keys and data type enforcement work, and when to split data across tables after all.

Prerequisites

• A basic understanding of Tables — properties, Display Name, Primary Key.
• Familiarity with the Table Updater and Transform components.

Step 1 — Think of a record as a document

In a relational database, a row is a flat set of columns. In a Vince Platform table, a record is a JSON object. A property can hold a single value, a list, or a nested object:

{
  "orderNumber": "PO-10482",
  "supplier": "SUP-0091",
  "status": "RELEASED",
  "deliveryAddress": {
    "street": "Industrivegen 12",
    "postalCode": "0581",
    "city": "Oslo",
    "country": "NO"
  },
  "tags": ["urgent", "temperature-controlled"]
}

deliveryAddress is one property holding an object; tags is one property holding a list. You don't need an addresses table and a tags table to store this order.

That's why this guide talks about properties rather than columns. A property is a key in the record's JSON object, and its value can be nested as deeply as the data needs. "Column" suggests a flat grid, which a table is only when you choose to keep it flat.

Step 2 — Embed the "many" side when it belongs to the "one"

Back to the order with lines. Instead of a header table and a lines table, store the lines inside the order record as a list of objects:

{
  "orderNumber": "PO-10482",
  "supplier": "SUP-0091",
  "warehouse": "WH1",
  "status": "RELEASED",
  "lines": [
    { "lineNumber": 1, "item": "ITEM-2201", "orderedQty": 40, "receivedQty": 40 },
    { "lineNumber": 2, "item": "ITEM-2207", "orderedQty": 12, "receivedQty": 9 }
  ]
}

This one record now answers everything a typical workflow asks about the order — with a single read and no join:

• A workflow that sends the order to a supplier reads one record and has header and lines together.
• A workflow that writes the order writes one record; header and lines can never get out of step.
• An Event trigger on the table sees the whole order — header and lines — in the event, before and after the change.

Embedding is the right choice when the lines:

• are written together with the header (for example, the whole order arrives from M3 or an external system at once),
• are read together with the header (you rarely need a line without its order), and
• are bounded — an order has tens or hundreds of lines, not an ever-growing list.

Working with embedded lists in a Transform

Where you would have written a join or a WHERE clause on a lines table, you now filter and reshape the list inside the record with JSONata. For example, to list the lines not yet fully received in an order record that arrived through an Event trigger on the table:

(
  $order := $context.data.trigger.body.data.newData;
  {
    "orderNumber": $order.orderNumber,
    "openLines": $order.lines[receivedQty < orderedQty].{
      "item": item,
      "missingQty": orderedQty - receivedQty
    }
  }
)

The result:

{
  "orderNumber": "PO-10482",
  "openLines": [
    { "item": "ITEM-2207", "missingQty": 3 }
  ]
}

The JSONata Masterclass covers this style of expression in depth.

Step 3 — Choose the primary key deliberately

The Primary Key decides which records are "the same record". You can mark one property or several:

Primary key	A record is identified by	Good for
orderNumber	The order number alone	Orders that are unique across the whole company
warehouse + orderNumber	The combination of both values	Order numbers that are only unique per warehouse
item + warehouse + date	The combination of all three	A daily stock snapshot per item and warehouse

With several primary-key properties, it is the combination that identifies a record. WH1 + PO-10482 and WH2 + PO-10482 are two different records.

The Table Updater writes by primary key:

• An incoming record whose key combination is new is added as a new record.
• An incoming record whose key combination already exists updates the stored record.

That is why the choice matters so much: it decides whether writing data again updates what is there or creates duplicates. It also decides which Event trigger action fires — Create for a new key, Update for an existing one.

tip

Pick a primary key that comes from the business data itself — an order number, an item and warehouse — not something generated at write time like a timestamp. A key that is different every time turns every write into a new record.

Step 4 — Make sure every record carries its primary key

The Table Updater takes the output of the step before it and writes it to the table. It's up to you to make sure that output contains every primary-key property, with the exact property names the table uses.

The reliable way is to put a Transform directly before the Table Updater and build the records explicitly. Here, raw order data from a REST API step (rest_api_1) is reshaped into records for a table keyed on warehouse + orderNumber:

$context.data.all.rest_api_1.body.orders.{
  "warehouse": whse,
  "orderNumber": orderNo,
  "supplier": supplierId,
  "status": status,
  "lines": items.{
    "lineNumber": line,
    "item": itemNo,
    "orderedQty": $number(qty),
    "receivedQty": 0
  }
}

caution

If a record is missing a primary-key property — for example because the source field was empty, or was named orderNo instead of orderNumber — it can't be matched to a stored record. Check the output of the Transform in Execution Logs before you rely on the workflow.

Step 5 — Decide how strict the table should be

The properties you define for a table — their names, data types and primary key — are stored as a JSON Schema that describes what a record looks like. Because a record is a document, the table can still accept properties you never declared and values of any shape. Whether it does is controlled by the table's Enforce data types setting, in the Create table and Edit table dialogs:

Enforce data types	What happens when you write data
On	Each record is checked against the table's JSON Schema, and values must match the data types defined for its properties. Records with the wrong data type are rejected.
Off	The table accepts any shape — extra properties that aren't defined, lists, nested objects, and values of a different type than the one defined.

Choose based on where the data comes from:

• Turn it on when several sources or people write to the table, or when downstream workflows depend on properties always having the right type. Bad data is rejected at the door instead of surfacing later as a failed integration.
• Leave it off when you deliberately store flexible documents — for example raw payloads from an external system whose structure varies.

caution

With Enforce data types off, nothing stops a property defined as a number from receiving text in some records. Values of incompatible types in the same property are likely to break the table's indexing. Adding new properties and nested objects is fine; changing the type of an existing property is what causes trouble. If you leave it off, keep each property's type consistent yourself, in the Transform that builds the records.

Either way, shaping records with a Transform before the Table Updater (Step 4) is what keeps a table clean. Enforcing data types is the safety net, not the design.

Step 6 — Know when to split after all

Embedding isn't always right. Use a separate table for the "many" side when one of these is true:

Situation	Why a separate table is better
The lines change independently	If lines are received, updated, or cancelled one by one by different processes, every change rewrites the whole order record — and every reaction to the order fires for a change to any single line. A lines table keyed on orderNumber + lineNumber lets each line change on its own.
The list grows without limit	A log of events per item, or every transaction for a customer, keeps growing. Records have a size limit of 256 KB, so unbounded lists eventually don't fit. Store each entry as its own record.
The records are getting large	Large records are slower to read and write, and very large records may be delivered to Event triggers without their old data. Keep records comfortably below the limit.
You look the "many" side up on its own	If workflows regularly need "all lines for item X" across all orders, a table where each line is a record with the item as part of its identity fits that question.

When you do split, there is still no join. Give each record in the child table the parent's key (for example orderNumber on every line record), and combine the data in a workflow — read the records you need from both tables, then merge them in a Transform.

Quick comparison

Relational habit	Document approach in Vince Platform
One table per entity, linked by foreign keys	One record per business document, with related data embedded
JOIN headers and lines when reading	Read one record; header and lines are already together
WHERE on a lines table	Filter the embedded list in a Transform: lines[receivedQty < orderedQty]
Auto-increment ID as primary key	Primary key from business data — single property or a combination
Schema always enforced	Your choice: Enforce data types on or off
Split into child tables by default	Split only when the "many" side changes independently, grows without limit, or is looked up on its own

Further reading

• Tables — create tables, define properties and primary keys, and browse data
• Table Updater — write workflow output to a table
• Triggers in Depth — react to records being created, updated, or deleted
• JSONata Masterclass — filter and reshape embedded data in a Transform
• Custom Tables API — read, write, and search table data from outside a workflow