# Relationships

> How tables join in the fluid. Draw a relationship, say which end holds one row, see how sanda suggests them, and fix a wrong one.

A relationship tells sanda that two tables can be read together, on which columns, and which end holds a single row. `invoices.customer_id` and `customers.customer_id` hold the same customer. One `customers` row has many `invoices`. With that written down, "revenue by customer" is a question sanda can answer.

A relationship needs two different tables, both on the map and accepted.

## How a relationship is stored

sanda does not keep a list of joins. Both columns carry a shared key that names the identity they hold, such as `customer`. Every column carrying the same key is joined to every other.

That is why you never edit a join list. State once that `customers.customer_id` is the customer, and a third table with a `customer_id` joins both of the others when you give it the same key.

The key is a name for an identity, lowercase and usually one or two words: `customer`, `invoice`, `product`.

## Read a line on the map

Each line runs between two columns. Its ends use the notation anyone who has drawn a schema knows:

- A **bar** at an end means one row holds that value.
- A **crow's foot** means many rows can.
- A **dashed** line is [glue](#join-two-source-systems).

The legend sits at the bottom right of the map, under **reading a line**. Click a line, or hover over a relationship in the relationships panel, and it reads like a sentence: `customer · one customers to many invoices`.

When nobody has said which end holds one row, sanda works it out and adds "sanda's guess, until you say". See [cardinality](#cardinality).

## Create a relationship

### Drag one

:::steps
1. **Start on a column.** Press anywhere along a column row on one card and drag. The row's dot on the card's edge starts the drag at once.
2. **Drop it on a column of another table.** Columns whose types would join without a cast light up, and the rest dim. You can still drop on a dimmed one: a text `customer_ref` can genuinely join an integer `customer_id`.
3. **Check the dialog.** **create relationship** shows the two columns and asks for:
   - **Shared identity (join key)**, guessed from the column names, so `contact_id` on both sides suggests `contact`.
   - **Shown as**, the words used to describe the join.
   - **How it fans out**: `one customers → many invoices`, `one invoices → many customers`, or `one to one`.
4. **Press create relationship.**
:::

The fan-out is filled in from what the columns look like: a column that is its table's whole key holds one row per value, and everything else holds many. If both look like many, the dimension side is more likely the one. Change it if it is wrong.

### Relate two tables without dragging

When two cards are far apart on the board, open the **relationships** panel on the rail and press **relate two tables**. Choose a table and a column on each side, check the key and the fan-out, and press **create relationship**.

### Accept a suggestion

sanda proposes relationships too. They wait in the [review queue](https://docs.sanda-os.com.au/semantic-fluid/review) as one row each.

## Cardinality

Every relationship is **one to many** or **one to one**. There is no many to many, and that is a correctness rule, not a limitation. A many-to-many join multiplies rows before anything adds them up, so a total across it is silently too large and looks completely reasonable.

Two tables that genuinely relate many to many relate through a table that records the pairing, such as employees and territories through `employee territories`. That is one to many, twice. The pairing table has to be on the map, or the model is missing something.

When nobody has said which end holds one row, sanda reads it from the keys:

- A column that is its table's only key column holds one row per value.
- Anything else is assumed to hold many. This is the safe assumption. Reading a one-to-one as one-to-many costs a de-duplication nobody needed. Reading a one-to-many as one-to-one costs a total that is too large.
- If neither side can be the one, sanda promotes the more key-like side and marks the result as a guess.

To say which end is the one, open the **relationships** panel, find the relationship and choose from **how it fans out**. Both sides update in one press, and the guess flag clears.

## Why the shape matters

Say a customer has three invoices. Joining customers to invoices repeats the customer's columns three times, and any total of a customer column across that join counts the customer three times. Reaching the other way, from an invoice to its one customer, is a lookup: every invoice keeps exactly one customer, so nothing is counted twice.

sanda gives every join to the agent with its direction, for example `customer · one mapping.billing__customers to many mapping.billing__invoices`, and tells it to add up the many side to its own level before joining it to the one side. When sanda composes a query itself and a number sits on the side that repeats, it counts each of that table's rows once by its [key columns](https://docs.sanda-os.com.au/semantic-fluid/tables#grain-key-and-event-time). When a question would need two many sides joined to each other, sanda declines and says why. See [metrics](https://docs.sanda-os.com.au/semantic-fluid/metrics#what-sanda-does-with-a-metric) for an example sentence.

## How sanda finds them

There are three sources of evidence.

1. **What your names state.** sanda draws a join, without any AI, when a column is named exactly like another table's key (`customer_id` on invoices, and `customer_id` as the key of customers), when a `<table>_id` column points at a table keyed on a bare `id`, or when the source declares a foreign key. If two other tables both fit, sanda reports the ambiguity and draws nothing.
2. **What your values prove.** sanda measures what share of a column's sampled values are found in the other table's key. A name match with fewer than half found is not proposed. When it is, the reason says so, such as "100% of 91 sampled values found there".
3. **What an AI can add.** For keys spelled differently on each side, and joins the values prove that no name states, sanda asks the AI with the measured evidence in front of it: declared keys, keys checked against the rows, and the measurements. A handful of small whole numbers is found in every integer key, so a match alone never carries a proposal without a name or a type behind it.

Suggestions arrive:

- when you **add tables** (from names, and any declared keys),
- at the end of a [learn pass](https://docs.sanda-os.com.au/semantic-fluid/learn) (all three),
- when you press **suggest relationships** in the relationships panel, which needs at least two tables.

The third source is an AI call, and it is billed. See [Learn from my data](https://docs.sanda-os.com.au/semantic-fluid/learn#cost-and-time).

## Fix a wrong relationship

- **The shape is wrong.** Use **how it fans out**, as above.
- **The join should not exist.** Click its line and press **unlink**, or press the bin on its row in the relationships panel. Unlinking removes the shared key from those two columns. Other tables that carry the key keep theirs.
- **The key is wrong.** A key that names two different things joins tables nobody meant. Unlink, and create the relationship again under a key of its own.
- **You might unlink more than you mean to.** When either column also joins a third table on the same key, the dialog warns you first and lists the other relationships that would go with it. To keep one of them, unlink the pair you actually want rid of, or recreate the kept one afterwards under its own key.

## One key, one identity

Because the graph comes from the key's name, a key must name exactly one identity.

- **Reusing a key on purpose is fine.** A third table carrying `customer` joins the first two with no further work. When you type a key that other columns already carry, sanda lists the tables the new column would also be joined to before you save, and says: "Right if they are one identity; otherwise give this relationship its own key."
- **Two different joins with one key become a hub.** If orders-to-customers and orders-to-products were both given the key `id`, every column carrying `id` would join every other, and the map would draw joins nobody made. Give each identity its own key.
- **Two many sides on one key join each other.** Invoices and payments both carry `customer`, so they are joined to each other too. Neither is the one, so sanda has to guess which end is, and marks the join as a guess. That is what a shared key is for, but add up each side to its own level before combining.
- **A bridge takes its own key.** When a pairing table joins the same table as an ordinary one, it is given a key such as `employee via employee territories`, so sanda does not derive a join between the two many sides.

## Join two source systems

[Glue](https://docs.sanda-os.com.au/reference/glossary#glue) joins two datasets when they describe the same identity under different column names: a contact's `email` in your billing system and a customer's `email_address` in your shop. Where a key derives its joins from a name, glue states them directly.

:::steps
1. **Open glue.** Press **glue** on the rail, then **glue two datasets**. You need at least two [datasets](https://docs.sanda-os.com.au/semantic-fluid/tables#datasets).
2. **Choose the two datasets and name the glue.** For example "Customers across Xero and Shopify".
3. **Add column pairs.** For each pair, choose the left and right columns, a shared identity such as `customer`, and how many rows each side holds: sanda guesses, one row, or many rows. **Add pair** adds a row.
4. **Or let sanda propose pairs.** **Map with AI** matches the two datasets' columns by likeness of name, type and sample values. It is billed, and it saves nothing. Check each proposed pair, then continue.
5. **Press Create glue.** Saved pairs become joins an agent may use, and you are signing them.
:::

Glue lines are dashed on the map, coloured by the first dataset. Click one to remove a pair, or press **edit glue** to change the whole set. A glue's last pair cannot be removed from the line: edit the glue, or delete it from its row in the panel. The agent's join list marks a glued join with `glue:` and the glue's name.

:::links
- [The map](https://docs.sanda-os.com.au/semantic-fluid/the-map): Dragging, selecting and tidying.
- [Metrics](https://docs.sanda-os.com.au/semantic-fluid/metrics): What sanda does with a join when it answers.
- [Review what sanda proposes](https://docs.sanda-os.com.au/semantic-fluid/review): Decide suggested relationships.
:::