Data Modeling

Relational division answers one specific analytical question: which entities satisfy every requirement in a reference set. It is the right mental model when ordinary joins answer who matched something, but the business question asks who matched all required things.

What relational division answers

Relational division is easiest to understand as an all-requirements filter.

If one relation contains observed facts such as person completed module, and another relation contains the required modules, relational division returns the people for whom every required module has a matching completion fact.

In relational terms, if A contains pairs of entity and requirement, and B contains requirements, then A divided by B returns the entities where, for every requirement in B, the pair exists in A.

That is different from a normal join. A join can show that Alice completed a required module. Relational division shows whether Alice completed every required module.

  • Some requirement matched: use a join or semi-join.
  • No forbidden requirement matched: use an anti-join.
  • Every required requirement matched: use relational division.
Operator rule

If the phrase is every, all, each, or complete coverage, pause before writing a normal join. You are probably looking for relational division or an anti-missing pattern.

A small example with counterexamples

Use a hypothetical onboarding dataset. The business question is: which people have completed every required module for warehouse access?

The reference set is the list of required modules. The observed fact table records module completions by person.

The counterexamples matter because they catch common mistakes. Someone can complete some modules and still fail. Someone can complete extra modules and still fail. Someone can have duplicate completion records and should not pass twice or fail because of duplication.

Reference module id Required module
privacy Data privacy
phishing Phishing awareness
warehouse Warehouse basics
Person Observed completions Counterexample lesson
Alice privacy, phishing, warehouse Completes every required module.
Ben privacy, phishing Missing one required module.
Cara privacy, warehouse, advanced Extra completion does not compensate for missing phishing.
Dev privacy, phishing, warehouse, advanced Extra completion does not hurt when all required modules are present.
Eli privacy, phishing, warehouse, warehouse Duplicate completion should still count as one required module.

Work the example by hand

The required module set contains three requirements: data privacy, phishing awareness, and warehouse basics.

Now evaluate each person against the set, not against individual rows.

  • Alice: has data privacy, phishing awareness, and warehouse basics. Alice qualifies.
  • Ben: has data privacy and phishing awareness, but not warehouse basics. Ben does not qualify.
  • Cara: has data privacy, warehouse basics, and an extra advanced module, but not phishing awareness. Cara does not qualify. Extra facts do not replace missing required facts.
  • Dev: has all three required modules and an extra advanced module. Dev qualifies. Extra facts do not hurt.
  • Eli: has all three required modules, with a duplicate warehouse basics record. Eli qualifies once. Duplicate facts should not create extra qualification.

The answer is Alice, Dev, and Eli.

Person Passes relational division? Reason
Alice Yes All three required modules are present.
Ben No Warehouse basics is missing.
Cara No Phishing awareness is missing.
Dev Yes All required modules are present; advanced is ignored.
Eli Yes All required modules are present; duplicate warehouse does not change the set.

Translate the idea into SQL

There are two common SQL shapes for relational division. The first is the direct logical translation: return candidate entities for which there does not exist a required item that is missing.

In plain English, the anti-missing version says: choose each candidate person where no required module exists without a matching completion for that person.

A compact SQL shape is: select person from candidates p where not exists required module r such that not exists completion c for p and r.

The second common shape is aggregation: join completions to required modules, group by person, and keep only people whose count of distinct matched required modules equals the count of required modules.

Both can be correct when the inputs are modeled correctly. The anti-missing version often reads closer to the business rule. The aggregate version is familiar to many analytics teams and can be convenient in metric layers.

Practical checkpoint

The safest verbal test is: can I name a required item that this entity is missing? If yes, exclude the entity. If no, include it.

SQL shape How it thinks Best use Main risk
Double NOT EXISTS Exclude candidates only when a required item is missing. Clear expression of the all-requirements rule. Can be harder for newer SQL readers to parse.
Group and count distinct Compare matched required count with total required count. Convenient in analytics models and metric checks. Wrong if duplicates, nulls, or candidate scope are not handled.
Left join to missing requirements Generate candidate-requirement pairs, then find missing matches. Useful for debugging and explaining failures. Can become large if the candidate and requirement sets are both large.

Choose the right candidate set

The candidate set is the population you are testing. This is a modeling decision, not a syntax detail.

If the question is which employees completed every required module, the candidate set may be all active employees. If the question is which employees with warehouse access completed every required module, the candidate set is narrower. If the question is which people with any completion record completed every required module, people with no completions will never appear unless you add them from a separate candidate table.

Many wrong answers come from accidentally using the fact table as the candidate list. That silently excludes entities with no observed facts. Sometimes that is desired. Often it is not.

  • Use a candidate table when the business population exists independently of the observed facts.
  • Use the fact table as candidates only when the question is explicitly limited to entities with at least one fact.
  • Document the population because two correct relational division queries can answer different questions if their candidate sets differ.

Handle empty sets, duplicates, and nulls

Relational division is simple in principle, but three data modeling issues can change the answer.

Empty reference sets: In formal logic, every candidate satisfies every requirement when there are no requirements. For analytics, that may be surprising. If an empty requirement list means configuration is broken, add an explicit check that the reference set is not empty.

Duplicate facts: A person completing the same required module twice should usually count once. Use a distinct entity-requirement pair or enforce the intended key in the modeled fact table.

Null requirement values: A null module identifier is not a real requirement. Do not allow nulls in the reference key or the bridge key if the relationship is meant to be testable. Nulls make all-requirements logic ambiguous and often produce misleading non-matches.

The durable rule is to make the relationship table represent a set of valid entity-requirement pairs before asking an all-requirements question.

Warning

An empty requirement set is not the same as a satisfied business process. Decide whether it means everyone qualifies or the setup is invalid before publishing the result.

When relational division appears in analytics

Relational division shows up whenever a dashboard, metric, or operational list asks for complete coverage against a reference set.

  • Customer analytics: accounts that bought every product in a required bundle.
  • Product operations: SKUs available in every required region.
  • Compliance workflows: employees who completed every assigned training module.
  • Marketplace analytics: suppliers that support every required capability for a program.
  • Data quality: source tables that delivered every required field for a reporting period.

The same pattern applies across domains. Define candidates, define requirements, define observed entity-requirement facts, then test for missing requirements.

Checklist before trusting the result

Before you publish a relational division result into a dashboard or downstream model, check the question against the model.

  • What is the exact candidate population?
  • What table defines the complete reference set?
  • Can the reference set be empty, and what should that mean?
  • Is the observed fact table at one row per entity-requirement pair, or can duplicates occur?
  • Are requirement identifiers controlled and non-null?
  • Do extra observed facts help, hurt, or simply get ignored?
  • Is the result supposed to be evaluated at one point in time, within a period, or across all history?
  • Does the query return entities once, not once per matched requirement?

If the team cannot answer these questions, the issue is usually not SQL syntax. The issue is that the business rule has not been modeled clearly enough.

Key takeaways

  • Relational division finds entities that satisfy every requirement in a reference set.
  • The core question is not who matched something, but who is missing nothing required.
  • Define the candidate set explicitly or the query may silently exclude entities with no facts.
  • Treat duplicates, nulls, and empty reference sets as modeling decisions before writing the final query.
  • A useful implementation pattern is either anti-missing logic or count-distinct matched requirements against total requirements.

Next step

Create a small two-column bridge table from your own domain, such as customer-feature, employee-module, or supplier-region. Write the required reference set separately, then manually identify the entities with no missing requirements before translating the logic into SQL.

Recommended next reads