Relation Creation

## Prerequisites

Before creating relations, ensure you are on the correct workspace and branch. Use `get_session_workspace_and_branch` to check the current session context. For development work, create a branch with `create_workspace_branch` (the session switches automatically). See the `workspace-branch` skill for the full workspace/branch tool reference.

---

## Overview

A Honeydew **relation** defines how two entities join together.
Relations are not standalone objects — they live inside the **source entity's YAML** as a `relations:` block.
Every relation connects the "many" side to the "one" side: for example, `orders` (many) → `customers` (one).

Relations enable:

- Metrics on one entity to reference attributes from another
- Cross-entity calculated attributes (Multi-Entity type)
- Filter propagation between entities in BI queries

> Relations are defined on the source entity. To add a relation, use `update_object` on the entity that holds the foreign key (the "many" side).

---

## Creation Methods

### Primary: update_object on the Source Entity

There is no `create_relation` tool. Adding a relation means updating the source entity's YAML to include the `relations:` block.

Call `update_object` with:

- `yaml_text` — the full updated entity YAML with the `relations:` block
- `object_key` — the entity's object key (find via `list_entities` or `get_entity`)

Required permission: Editor or higher.

### After Creation/Update: Display the UI Link

After a successful `update_object` call, the response includes a `ui_url` field. **Always display this URL to the user** so they can quickly open the object in the Honeydew application.

### Remove a Relation: update_object

There is no `delete_object` for relations. To remove a relation, update the source entity's YAML with the relation removed from the `relations:` block.

1. **Find which entity defines the relation.** The relation lives in only one entity's YAML, but it could be on either side. Use `get_entity` on both entities and check which one has the relation in its `relations:` block.
2. Remove the target relation from that entity's `relations:` list (keep all other relations intact).
3. Call `update_object` with the updated YAML and the entity's `object_key`.

---

## Decision Flow

```
Need to define a relation?
    │
    ├─► Simple FK join (equality on one or more columns)?
    │       └─► Use field-based connection
    │               connection:
    │                 - src_field: <fk_column>
    │                   target_field: <pk_column>
    │
    ├─► Complex join (range, filter, SCD Type 2, multi-entity)?
    │       └─► Use expression-based connection
    │               connection_expr:
    │                 sql: |-
    │                   <custom SQL>
    │
    └─► Removing an existing relation?
            └─► Use update_object — remove relation from entity YAML
```

---

## Examples

See [examples.md](examples.md) for full worked examples covering: field-based join, composite key, expression-based (SCD Type 2), multiple relations, and removing a relation.

---

## Discovery Helpers

Use these MCP tools before defining relations:

- `get_entity` — Get entity details including existing relations, attributes, and YAML definition
- `list_entities` — List all entities (to identify source and target)
- `search_model` — Search for entities or fields by name (use `search_mode: EXACT` for known names, `OR` for broad discovery)

---

See [reference.md](reference.md) for: YAML schema, relation direction (rel_type), join types, cross-filtering options, and connection methods.

---

## Pre-Implementation: Present Modeling Options

**IMPORTANT: Before creating any relations, analyze the modeling options and present them to the user for decision.**

Steps:

1. **Examine the existing model** — Review current entities and relations using `list_entities` and `get_entity`
2. **Identify viable approaches** — There are often multiple valid ways to connect entities (e.g., direct connections, through intermediate entities, different join types, cross-filtering choices)
3. **Present trade-offs** — Explain the pros/cons of each approach in context of the user's data and use cases
4. **Get user confirmation** — Ask the user which approach they prefer before implementing

**Do not assume a single correct answer.** Relationship modeling involves trade-offs (query performance, maintainability, analytical flexibility) that depend on the user's specific needs.

---

## Documentation Lookup

Use the `honeydew-docs` MCP tools to search the Honeydew documentation when:

- You need to understand join types, cross-filtering behavior, or relationship modeling patterns in more depth
- The user asks about how relations affect query behavior or metric calculations
- You need guidance on advanced modeling scenarios like SCD Type 2 joins, self-referencing relations, or multi-path joins
- The user asks about relation performance implications or troubleshooting join issues
- The user needs advanced modeling patterns for complex relationship topologies

Search for topics like: "relations", "joins", "cross-filtering", "many-to-one", "relationship modeling".

---

## Best Practices

- **Define relations on the "many" side.** The entity holding the FK is the source; use `rel_type: many-to-one`.
- **Use field-based connections for simple FK joins.** Reserve `connection_expr` for cases that genuinely require custom SQL.
- **Include all key columns in field-based connections.** If the "one" side has a composite key, all parts must be listed in `connection`.
- **Default to `cross_filtering: both`** unless performance is a concern. Restrict to `one-to-many` or `none` for large entities.
- **Always include existing relations when using update_object.** Updating an entity's YAML replaces the full `relations:` block — omitting an existing relation will delete it.
- **Use get_entity before modifying.** Always inspect current entity YAML and relations before updating to avoid accidentally removing existing ones.
- **Minimize YAML changes.** Preserve the existing field order and formatting. Only change what you need to. Objects are versioned in git, so unnecessary reordering or reformatting creates noisy diffs.

---

## MANDATORY: Validate After Creating

**After creating ANY relation, you MUST invoke the `validation` skill to test and validate.**

See the `validation` skill (Entities → "Validating Relations") for:

- How to verify relation exists via `get_entity`
- How to test cross-entity queries work
- Sanity checks (join works, no fan-out, NULLs reasonable)
- When to alert the user about issues

**Quick validation:**

1. Verify relation exists using `get_entity` on the source entity, check its relations list.
2. Test cross-entity query using `get_data_from_fields`:

- `attributes`: `["<target_entity>.<attribute>"]`
- `metrics`: `["<source_entity>.<metric>"]`

---

## Common Pitfalls to Avoid

- **Replacing existing relations unintentionally.** `update_object` replaces the full entity YAML. If the entity already has relations, fetch the current YAML first (via `get_entity`) and include all of them in the update.
- **Defining relation on the wrong entity.** Relations should be defined on the entity that holds the FK (the "many" side). Defining on the "one" side inverts the semantics.
- **Missing key columns in field-based connections.** If the target entity has a composite key, all columns must appear in `connection`. A partial key join produces fan-out and incorrect aggregations.
- **Using `connection_expr` without enforcing cardinality.** Custom SQL joins bypass Honeydew's cardinality validation. A poorly written expression can silently produce many-to-many joins.
- **`many-to-one` cross-filtering on large tables.** Filtering from the "many" side back to the "one" side requires a reverse join and is expensive. Use `none` or `one-to-many` unless the use case requires it.