ER 2026 · St. John's · Tutorial · Wed 7 Oct

Building Executable Entity-Relationship Models

We model a real palm oil supply chain, from Indonesian districts and mills to EU importers, directly as a TypeDB schema, then load and query it. Everything you need to follow along is on this page.

View the slides →

Setup

You need a TypeDB server and a web browser.

  1. Set up a TypeDB server, either:
  2. Note the address, username and password
    • Local: http://localhost:8000, admin, password by default
    • Cloud: copy them from the cloud dashboard
  3. Connect TypeDB Studio
    • Open studio.typedb.com
    • Connect (at the top) with your credentials
    • Create a new, empty database

Ready to go!

The model

1 · Initial definitions: places

Reading the header and a first row, the easiest things to model are the places: the country, the province and the kabupaten (district) the palm oil comes from.

Three kinds of place, each identified by its name (underlined = key), under an abstract place (dashed). Every *_name attribute is a subtype of one name attribute; ER has no notation for that, so it isn't drawn.
Places in TypeQL snippet 1

Places contain places

Presumably the places nest: a kabupaten lies in a province, which lies in a country.

One containment relation type, drawn once for each pair it connects. A province is a child of its country and a parent of its kabupaten.
Places hierarchy in TypeQL snippet 2

2 · Mills, refineries, exporters

The next columns are harder: the same names turn up as a mill's group, a refinery, an exporter and an importer. Is a refinery_group a different kind of thing from an exporter_group?

A few real rows (scroll sideways for more columns). Coloured: a name that appears more than once in the same row, each name in its own colour. NOT REFINED marks crude palm oil.

Option 1: one entity type per column

Taking the CSV header literally: six entity types, each with its own name.
Option 1 in TypeQL for discussion only, don't run it alongside option 2

Option 2: companies and facilities

The data sheet says each *_group column "groups the [X] column into parent companies where applicable". So there are really only companies and facilities (mills and refineries), linked by ownership, and exporter is a role a company plays, not a kind of company.

Dashed boxes = abstract types: legal_entity generalises companies and facilities. mill_name and refinery_name are both subtypes of facility_name (not drawn). The dashed owned line is added in snippet 4.
Option 2 in TypeQL snippet 3

Companies owning companies

In some rows LDC EAST INDONESIA is both the refinery and the exporter, and LOUIS DREYFUS is the refinery's group.

We read this as: the company LDC EAST INDONESIA owns the refinery of the same name and handles exports, and LOUIS DREYFUS is its parent company. So companies need to be ownable by other companies too.

Ownership extension snippet 4

3 · Commodities

The data has two commodities, PALM OIL and REFINED PALM OIL. How would you model them?

a) one entity type with an attribute
b) two independent entity types
c) one abstract type with two subtypes
Option a) in TypeQL snippet 5
Challenge: option a) allows at most two commodity instances, because the name is a key restricted to two values. How would you get the same guarantee with option c)?
Challenge: option c) in TypeQL one possible answer

4 · Trade flows

Each row of the data is one trade flow linking a district, a mill, sometimes a refinery (crude palm oil isn't refined), an exporter, an importer, a destination country and a commodity. How would you model the flow itself?

One 6- or 7-way relationship. Dashed line = optional role. company plays two roles: exporter and importer.
Trade flow in TypeQL snippet 6

Full model

Snippets 1–6 combined into a single define query. Use this if you want a clean start.

Data

The data comes from Trase's open supply-chain data: 81 real trade flows of palm oil from East Kalimantan, Indonesia, to EU countries in 2022.

Benedict, J. J., Biddle, H., Gollnow, F., Heilmayr, R., Mueller, C., Ribeiro, V., & Suavet, C. (2024). Indonesia palm oil supply chain (2018–2022) (Version 1.2) [Data set]. Trase. https://doi.org/10.48650/X83N-7M36. Licensed CC BY 4.0.

81trade flows (71 refined, 10 crude)
41mills
6refineries
37companies
6EU destination countries
5,505 ha10-year deforestation exposure
How this excerpt was made simplifications
  • Rows: from the full dataset (~1.7M rows), only 2022 flows from East Kalimantan to the EU with all actors known, then 81 of those, chosen so that every modelling case appears. No values were invented or edited.
  • Columns: 16 of ~35 kept. Dropped: year, Trase IDs and country codes, port of export, economic bloc, sustainability scores and commitments, emissions metrics.
  • Interpretation: NOT REFINED means the flow has no refiner. When importer_group equals importer, no parent is recorded. A refinery that shares its name with an exporter is read as owned by that company.
  • Caveat: Trase flows are modelled from customs, production and facility data, not observed shipments. The insert query below rounds values (volume 3 dp, deforestation 4 dp, USD 2 dp).
Browse the data 81 rows

View the CSV file

Load it

One insert query loads everything.

Querying

Results are for the demo data.

Pattern matching and polymorphism

Look up all countries and their names 7 results
Get all facilities, polymorphically, with their exact types 47 results
Extension: count facilities by type 41 mills, 6 refineries

Trade flows

Count trade flows by import country 6 countries
Find the unrefined (crude) trade flows 10 results
Challenge: total deforestation exposure (deforestation_ha) by importer.
Deforestation exposure by importer one possible answer

Facility ownership

A facility can sit under several layers of companies. Can one query find all of a facility's owners?

One or two hops, spelled out LDC EAST INDONESIA and LOUIS DREYFUS
Any number of hops, with a recursive function same answer, any depth

Hitting the constraints

These are meant to fail. The first two are rejected by the type checker before anything runs; the third breaks a schema constraint when it's committed.

Insert a facility (an abstract type) type error
Find countries playing the mill role in trade flows type error
Insert a country without a country name fails at commit: @key

Querying the model itself

Which types play which roles in trade_flow? 7 results
Which legal entity types cannot own other legal entities? includes legal_entity itself