Chapter Five: The n-ary relation

The trade flow: one relation, seven roles

Schema so far

If you are starting here, run these queries in order in an empty database.

#!test[schema]
define

attribute name, value string;

attribute country_name, sub name;
attribute province_name, sub name;
attribute kabupaten_name, sub name;

entity place @abstract;

entity country,
  sub place,
  owns country_name @key;

entity province,
  sub place,
  owns province_name @key;

entity kabupaten,
  sub place,
  owns kabupaten_name @key;
#!test[schema]
define

relation containment,
  relates parent @card(1),
  relates child @card(1);

entity country,
  plays containment:parent;
entity province,
  plays containment:child @card(0..1),   # at most one parent
  plays containment:parent;
entity kabupaten,
  plays containment:child @card(0..1);   # at most one parent
#!test[schema]
define

attribute facility_name, sub name;
attribute mill_name, sub facility_name;
attribute refinery_name, sub facility_name;
attribute company_name, sub name;

entity legal_entity @abstract;

entity facility @abstract,
  sub legal_entity,
  plays ownership:owned @card(0..1);   # at most one owner
entity mill,
  sub facility,
  owns mill_name;
entity refinery,
  sub facility,
  owns refinery_name;

entity company,
  sub legal_entity,
  owns company_name @key,
  plays ownership:owner;

relation ownership,
  relates owner @card(1),
  relates owned @card(1);
#!test[schema]
define

entity company,
  plays ownership:owned @card(0..1);   # a company has at most one parent company
#!test[schema]
define

attribute commodity_name,
  sub name,
  value string @values("PALM OIL", "REFINED PALM OIL");

entity commodity,
  owns commodity_name @key;

The flow

Each row of the data is one trade flow. Stated as constraints:

  • Each trade flow has exactly one district of origin, one mill, one exporter, one importer, one destination country and one commodity.

  • Each trade flow has at most one refinery. Crude palm oil is not refined.

  • Each trade flow has exactly one volume, one deforestation exposure and one value.

  • A company can be the exporter or the importer of a trade flow.

ER diagram: trade_flow relation with roles origin (kabupaten), mill (mill), optional refiner (refinery), exporter and importer (company), destination (country) and commodity (commodity), and attributes volume_tonnes, deforestation_ha and usd

One relationship of degree seven. The dashed line marks the optional role, and company participates twice, in two different roles.

#!test[schema]
define

attribute volume_tonnes, value double;
attribute deforestation_ha, value double;
attribute usd, value decimal;

relation trade_flow,
  relates origin @card(1),
  relates mill @card(1),
  relates refiner @card(0..1),   # optional
  relates exporter @card(1),
  relates importer @card(1),
  relates destination @card(1),
  relates commodity @card(1),
  owns volume_tonnes @card(1),
  owns deforestation_ha @card(1),
  owns usd @card(1);

entity kabupaten, plays trade_flow:origin;
entity mill, plays trade_flow:mill;
entity refinery, plays trade_flow:refiner;
entity company,
  plays trade_flow:exporter,
  plays trade_flow:importer;
entity country, plays trade_flow:destination;
entity commodity, plays trade_flow:commodity;

  • relation trade_flow declares seven roles. Six are required (@card(1)) and the refiner is optional (@card(0..1)).

  • The relation owns three attributes of its own, each required. double and decimal are numeric value types; decimal is exact, which suits money.

  • Each plays line says which type can fill which role. company plays two.

The model is now complete. Open the Schema page in Studio to compare it with the diagrams so far.

Reading cardinalities

ER and UML notations differ on where a cardinality is written and what it counts, especially for n-ary relationships. In TypeQL, each cardinality is attached to one of two declarations, and each counts one thing:

Declaration Counts Example

relates …​ @card

How many role players fill this role in one relation

trade_flow relates mill @card(1): each trade flow has exactly one mill.

plays …​ @card

How many relations of this type one instance plays this role in

province plays containment:child @card(0..1): each province is the child in at most one containment.

plays cardinality is (min,max) participation, counted from the participating instance. relates cardinality has no direct equivalent in a notation where a relationship is identified by its participants, because it counts the participants of one relationship. It is available because a TypeDB relation is an object.

If you leave a cardinality out, the defaults are:

  • relates: @card(0..1). Each role is optional and single-valued.

  • plays: @card(0..). An instance can play a role in any number of relations, including none.

  • owns: @card(0..1). Each attribute is optional and single-valued.

So mandatory participation, such as "every mill supplies at least one trade flow", is written on the plays side: entity mill, plays trade_flow:mill @card(1..);.

Comparison with tables and graphs

Here is the same relationship in tables and in a property graph.

As a table

A relational design makes the flow a table, with one foreign key per role:

CREATE TABLE trade_flow (
  id               INTEGER PRIMARY KEY,
  origin_id        INTEGER NOT NULL REFERENCES kabupaten,
  mill_id          INTEGER NOT NULL REFERENCES mill,
  refinery_id      INTEGER REFERENCES refinery,      -- nullable: optional role
  exporter_id      INTEGER NOT NULL REFERENCES company,
  importer_id      INTEGER NOT NULL REFERENCES company,
  destination_id   INTEGER NOT NULL REFERENCES country,
  commodity_id     INTEGER NOT NULL REFERENCES commodity,
  volume_tonnes    DOUBLE PRECISION NOT NULL,
  deforestation_ha DOUBLE PRECISION NOT NULL,
  usd              DECIMAL NOT NULL
);

This is the usual mapping of an n-ary relationship, but the roles survive only as column names, the optional role becomes a NULL, and the model’s generalizations have to be mapped separately. If mills and refineries share a facility table, mill_id can no longer be constrained to reference only mills without extra machinery. Participation constraints on the other side, such as every mill supplying at least one flow, need triggers or application code: the SQL standard’s CREATE ASSERTION would express them, but mainstream database systems don’t implement it.

As a graph

In a property graph, edges connect exactly two nodes. There are two ways to store a seven-way fact.

Chain binary edges, such as mill → refinery → exporter → importer. This adds facts that are not true. Each edge is true, but a path through them need not be: in this dataset, following the edges for the 71 refined flows connects 102 mill–importer pairs, but only 63 of them appear together in an actual flow. The other 39 are not in the data.

Reify the flow as a node, with one edge per role. This keeps the facts, but the schema-less graph does not know that a flow node needs exactly one mill edge, or that a mill edge may only point at a mill, or that the flow node represents a relationship rather than an entity.

TypeDB stores the flow as one relation with seven typed roles.

Challenge

Trase also records the port each flow was exported from. Add a port entity identified by name, and make it an optional role of trade_flow.

Answer

Don’t run this if you are following along, as later chapters assume the original model:

#!test[schema, rollback]
define
  attribute port_name, sub name;
  entity port, owns port_name @key, plays trade_flow:port_of_export;
  relation trade_flow, relates port_of_export @card(0..1);

@card(0..1) is also the default for relates, so it could be left out; writing it down documents the decision.

Next: Chapter Six: Population and conformance.

For LLMs & AI agents: this page as Markdown · llms.txt · llms-full.txt (TypeQL summary)