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
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.
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).
The Mandatory
nature of the relationship could allow some optimizations in some DirectQuery connector features
like Incremental refresh.