Make Relationship Cardinality Explicit

[mullinsms]

OSI models relationships primarily through join keys (entities) and inferred join behavior. This works well for many star-schema cases, but it leaves key relationship semantics implicit, which can make SQL generation and portability across semantic layers (e.g., Coginiti, Cube, AtScale, BI tools) either lossy or error-prone.

Suggested enhancement
Introduce an optional relationship block (or relationship annotations) that can be attached to an entity pair / join key mapping, such as: cardinality: one_to_one | one_to_many | many_to_one | many_to_many

[PioMen]

To your point, maybe this should be even extended by custom joining criteria? Currently there is from_column and to_column. In some joins you need to add custom logic (like join on pk + date (either current or between other dates).

[djwaldo]

+1 from the ThoughtSpot converter work (#285), with a data point: this isn't hypothetical loss. Every shipped converter is already working around it. The Databricks converter derives cardinality from from/to orientation, and ThoughtSpot has four cardinality values and five join types that orientation alone cannot encode, so we stash both in custom_extensions. No other tool can see them. An optional type/cardinality on Relationship (defaulting to today's inferred behavior for backward compatibility) would let converters stop hiding semantics that affect generated SQL.

[MarioDeFelipe]

Field evidence in favor of making cardinality explicit and first-class:
Tested, we generate an ontology from a live SAP landscape and project it into agent platforms. Same model, but two production-style targets: one carries cardinality natively on its relationship construct; the other has no field for it anywhere in its relationship schema
Result: we verified the schema itself, not just the docs — so a rule as basic as "a customer has many sales areas; each sales area belongs to one customer" survives only as prose inside a description, which an agent may read or may not.
The lesson we took: when cardinality is explicit in the interchange, a platform that can't represent it becomes a documented, visible limitation. When it's implicit, the same gap becomes a silent per-project discovery — and silent is the expensive kind once agents act on the model.

One request from the closed-world side of the house: keep the declaration cheap for sources that always know their cardinality (SAP and ERP-class models never guess it), so emitters can declare rather than infer. Open for discussion