> ## Documentation Index
> Fetch the complete documentation index at: https://docs.activeviam.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Relationship Optionality

Relationship optionality is the property of a `SqlJoin` which describes the `Optional` or `Mandatory` presence of a
row in the target table.

For instance, let's take the following example schema:

## Sample schema

```mermaid theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}

graph LR
;
    sales["sales
    ---------------------
    [string] **date**
    [string] **product**
    [double] quantity"] -->|many_to_one|products["products
    ---------------------
    [int] **date**
    [string] **product**
    [double] unit_price"]
```

There is a join from a source table `Sales` to a target table `Products`.\
This join is made along the date and product fields.\
This join is a many-to-one because we can sell the same product several times per day.

## Relationship `Optional`

The relationship is `Optional` if there could be sales on products which are missing from Products table.\
The relation is many-to-(one or zero). The join is "broken".

On this example the unit price of `P3` at `2024-01-01` is missing from the Products table:

`Sales`

| date           | product | quantity |
| -------------- | ------- | -------- |
| 2024-01-01     | P1      | 10       |
| 2024-01-01     | P2      | 20       |
| **2024-01-01** | **P3**  | **30**   |

`Products`

| date       | product | unit\_price |
| ---------- | ------- | ----------- |
| 2024-01-01 | P1      | 5.0         |
| 2024-01-01 | P2      | 2.0         |

## Relationship `Mandatory`

You can describe the target end of the join as `Mandatory`, if you are certain that at any point in time, the
relationship will always be satisfied.\
All the rows in `Sales` will match a row in `Products`. The relation is many-to-one.

<Info>
  ###

  This tells nothing about the other hand of the join (`Products` table).\
  There could still be some (product,date) keys in the `Products` table which are not referenced in the `Sales`
  table (orphans).
</Info>

<Info>
  ###

  The `Mandatory` nature of the relationship could allow some optimizations in some DirectQuery connector features
  like [Incremental refresh](./incremental_refresh).
</Info>