CDS Associations Part 3: To-One vs To-Many — Semantics and Performance

📌 KEY POINTS
✅ To-one paths keep one row per source row; to-many paths can multiply rows.
✅ Need one value from many rows? Aggregate in a separate view, or use a filtered path with "1:".
✅ Texts and time-dependent data become to-one through language/date conditions.
⚠️ Declared cardinality is a promise — the database does not check it.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Part 2 showed that paths become joins. This part is about the most important property of an association: how many target rows it can have per source row.

━━━━━━━━━━━━━━━━━━━━
▶ 1. WHAT CARDINALITY DOES
Cardinality tells the compiler, the optimizer and frameworks (OData, Fiori, analytics) what to expect. It is a promise, not a constraint: if you declare [0..1] but the target has two rows, you get duplicates anyway — and consumers that trusted the promise produce wrong results.

━━━━━━━━━━━━━━━━━━━━
▶ 2. TO-ONE: SAFE IN LISTS
Customer of an order, currency of an amount, plant of an item: at most one target row. You can use these paths anywhere in the element list without changing the number of rows.

━━━━━━━━━━━━━━━━━━━━
▶ 3. TO-MANY: DATA FOR NAVIGATION OR AGGREGATION
Items of an order, texts in all languages, price conditions: many target rows. Use to-many associations for:

• navigation ($expand, object pages),
• aggregation (count, sum, min, max),
• existence-style filtering in a controlled way.

Do not put unfiltered to-many paths into list views.

━━━━━━━━━━━━━━━━━━━━
▶ 4. GETTING ONE ROW WHEN YOU NEED A SINGLE VALUE
Options, from best to worst:

1. An aggregation view grouped by the source key (e.g. item count and net value per order and currency), joined as a to-one association.
2. A filtered path that really returns one row: _Item[ 1: SalesOrderItem = '000010' ].
3. MIN/MAX in an aggregation for a representative value.

⚠️ WATCH OUT: "DISTINCT" over a multiplied result hides the symptom but keeps the cost and often the wrong sums.

━━━━━━━━━━━━━━━━━━━━
▶ 5. TEXTS AND TIME DEPENDENCY

• Text tables have one row per language → add Language = $session.system_language to the ON condition, or use a text association annotated for text (CDS Annotations Part 2).
• Time-dependent data (validity periods) → filter with a key date parameter or $session.system_date between ValidFrom and ValidTo.

Both turn a to-many relation into a to-one relation.

━━━━━━━━━━━━━━━━━━━━
▶ 6. PERFORMANCE CONSEQUENCES
Correct to-one cardinality lets the optimizer skip unnecessary joins (join pruning) when no field of the target is requested. Wrong cardinality can prevent that or lead to bad plans. To-many paths in large lists create large intermediate results.

━━━━━━━━━━━━━━━━━━━━
▶ STEP BY STEP

1. Create ZI_SalesOrderItemAgg (code example): count and sum per order and currency.
2. Associate it from ZI_SalesOrder as to-one (per order and currency, or pick the order currency).
3. Replace any item fields in the list view with the aggregated values.
4. Compare row counts and runtime before and after.

✅ CHECKPOINT: you can decide, for every association, whether it may appear in a list — and what to do when it may not.

A price list view declared its association to price conditions as [0..1]. In reality, overlapping validity periods existed for about 2% of materials. A Fiori app trusted the cardinality and showed one price, but an analytics query summing order values over the same view returned inflated totals because of duplicate rows.
The team added the key-date condition to the ON clause so the association really became to-one, fixed the overlapping data, and added a data-quality check that reports overlapping validity periods monthly.

• Declaring to-one for relations that are sometimes to-many.
• Unfiltered to-many paths in list views.
• Using DISTINCT to hide row multiplication.
• Text associations without language, time-dependent data without a date condition.
• Summing amounts across currencies in aggregation views.

• Verify cardinality against real data before declaring it.
• Aggregate to-many data in dedicated views and associate them to-one.
• Make texts and validity-based relations to-one with conditions.
• Keep to-many associations for navigation and aggregation.
• Group sums by currency or unit.

Questions: "What is the impact of cardinality in CDS?", "How do you show an item total in an order list?", "Does the database enforce cardinality?"
Strong answer: cardinality is a promise used by compiler, optimizer and frameworks; the database does not enforce it, so wrong declarations cause duplicates and wrong aggregates. Show item totals via an aggregation view grouped by order and currency, associated to-one. Make text and time-dependent relations to-one with language or date conditions. Mention join pruning for correct to-one associations.