Rai Ontology Design

Ontology Design

Summary

What: Domain modeling decisions — concepts, relationships, properties, data mapping, model composition, and model enrichment for optimization.

When to use:

• As design authority — consulted by rai-build-starter-ontology during greenfield builds for concept, relationship, and property design decisions
• Enriching an existing model — adding properties, relationships, or subtypes to a model that already loads and queries
• Reviewing or evolving a model — assessing model gaps, examining ontology inventories, applying advanced patterns
• Mapping schema columns to model properties and relationships
• Assessing model gaps for optimization (READY / MODELGAP / DATAGAP)
• Designing cross-product decision concepts for optimization

When NOT to use:

• Building a first ontology from scratch — start with rai-build-starter-ontology, which uses this skill as its design authority
• PyRel syntax reference (imports, types, f-string patterns, stdlib) — see rai-pyrel-coding
• Optimization formulation (variables, constraints, objectives) — see rai-prescriptive-problem-formulation
• Query construction (select, aggregation, joins) — see rai-querying

Overview:

1. Analyze source tables (PKs, FKs, soft-joins, business purpose)
2. Identify and validate concepts with their identities (brainstorm, validate against data, define keys)
3. Identify relationships (FKs, shared keys, business links)
4. Assign remaining columns as properties
5. Define subtypes for recurring filters (optional)

---

Quick Reference

| Decision | Choose | Pattern | |----------|--------|---------| | Has own PK / identity? | Concept | model.Concept("Name", identify_by={"id": Type}) | | Scalar value on entity? | Property | model.Property(f"{Concept} has {Type:name}") | | Link to another concept? | Relationship | model.Relationship(f"{A} links to {B}") | | Boolean flag? | Unary Relationship | model.Relationship(f"{Concept} is active") | | Fundamental category of a concept? | Subtype | model.Concept("Supplier", extends=[Business]) | | Recurring .where() filter? | Subtype | model.Concept("Sub", extends=[Parent]) | | Many-to-many with data? | Junction concept | Concept with compound identity |

Layer structure: Core (source→semantic) → Computed (derived logic) → Application (queries, reports)

Naming: Singular business nouns for concepts (Customer), lowercase for properties (amount), related concept name for relationships (customer).

---

Design Decision Sequence

These are the design decisions that underlie any ontology, whether built from scratch via rai-build-starter-ontology or evolved incrementally. They are listed in dependency order — each phase depends on the one before it. For a complete greenfield build workflow, use rai-build-starter-ontology.

Start from the business domain — what concepts exist, what questions must the model answer — then find data mappings. Domain-first modeling produces better models than table-to-concept mapping because user intent drives concept selection rather than table structure.

1. Scope first (for new models) -- Define 1-3 concrete questions the model must answer and explicitly list what is out of scope. Keep the first version to ~10-15 must-have properties. A tight scope keeps the model small enough to implement and validate without rework. Example:

| Goal | In scope | Out of scope | |---|---|---| | Identify delayed orders | Orders, shipments, delay timestamps | Returns, carrier contracts, inventory |

1. Analyze sources -- Understand each table's business purpose, identify likely PKs (identity/auto-increment columns, NOT NULL, id/*_id patterns), infer FKs (columns whose names match other tables' PKs), and spot soft-joins (shared codes or categories across tables). Note STATUS/TYPE/CATEGORY columns with repeated string values — these should be typed enum concepts, not raw strings. Pay attention to column nullability, identity flags, and column comments — these are stronger signals than column names alone.

1. Identify and validate concepts with their identities -- Brainstorm the business entities present in the data. Think broadly: include both obvious entities (tables with clear PKs) and implied entities (referenced but not directly represented). For each proposed concept, map it to at least one authoritative source table -- if it cannot be grounded, reject it. Then define the concept's identity from the authoritative source's PK columns. Identity is intrinsic to concept definition, not a separate step: a concept without an identity key is not yet a concept. Check that proposed concepts are orthogonal (see Concept Orthogonality below).

1. Identify links -- Find relationships between two or more concepts. Look for FK columns, shared keys, and business associations. Each link should connect independently meaningful concepts.

1. Identify properties -- Assign remaining columns as attributes of their parent concepts, grouped by topic for readability. Not every column needs a property -- omit columns with no business meaning for the problem.

1. Define subtypes for recurring filters (optional) -- If you find yourself writing the same .where() filter repeatedly (e.g., active customers, open orders), promote the filter to a named subtype using extends. This makes the filter reusable and queryable as a first-class concept. See Categorization Patterns in [categorization-and-advanced.md](references/categorization-and-advanced.md).

1. Validate data scale before setting thresholds -- When defining subtypes or computed flags with numeric thresholds (e.g., "high traffic" locations, "high value" customers), explore the actual data distribution first. Run model.select(aggs.min(X.prop), aggs.max(X.prop), aggs.avg(X.prop)).to_df() to understand the value range before choosing a cutoff. Assuming a 0-100 scale when the data is actually 0-10 (or vice versa) produces zero results or captures everything.

Key pattern: identify then validate. Brainstorm broadly in steps 2 and 3, then validate against the schema. This avoids premature commitment to a modeling choice that doesn't survive contact with the data.

Quality gates: After steps 3-5, apply the checks from [fact-decomposition-and-validation.md](references/fact-decomposition-and-validation.md) (irreducibility, sample data validation, counterexample) to catch structural errors early.

---

Concept Design Principles

When to create a new concept vs. adding a property

Create a new concept when the thing has its own identity, its own properties, and participates in multiple relationships. A Customer, a Product, and an Order are each concepts because they are independently meaningful.

Add a property when the value only makes sense in the context of its parent concept. A Customer's credit limit, an Order's amount, and a Product's weight are properties because they describe, not identify.

Decision rule: If you would give the thing a primary key in a relational schema, it is a concept. If it would be a column on someone else's table, it is a property.

Concept orthogonality

Before committing to a set of concepts, verify they are truly independent:

• No-overlap test: If two proposed concepts always have the same entity set (every instance of A is an instance of B and vice versa), merge them into one concept. Two names for the same thing is not two concepts.
• Not-a-property-group test: If a proposed concept is just a bundle of attributes that belong to another concept and has no independent identity, make them properties on the parent concept instead.

Exception to the property-group test: If the "property group" has its own identity and is shared across multiple parent entities, it IS a concept. For example:

• Address columns (street, city, zip) on a Customer table -- if each customer has exactly one address and addresses are never shared, these are Customer properties.
• Address as a shared entity (multiple customers at the same address, addresses with their own lifecycle) -- this is a separate Address concept with its own identity.

Hidden intermediate concept heuristic: If multiple columns in a table share the same 1:N join pattern to another concept but aren't individually meaningful FK references, a concept is likely missing between them. For example: an ORDERS table with warehouse_id, warehouse_name, warehouse_city all describing the same entity suggests a missing Warehouse concept. Those columns should be Warehouse properties, not Order properties.

Domain-driven design: business concepts first

Model the business domain, then map to physical schema during data loading. Never mirror table or column names directly. This keeps constraints self-documenting (sum(Order.amount).per(Customer) 0 | Float (or Number.size(p,s) for precision) | | NUMBER, INT (no scale) | Integer | | FLOAT, DOUBLE | Float | | DATE | Date | | TIMESTAMP_NTZ, TIMESTAMP | DateTime | | BOOLEAN | Boolean property, or unary Relationship` for flag-style |

Always verify column types against INFORMATION_SCHEMA.COLUMNS before writing property declarations. A mismatch causes a TyperError at query time with no detail about which property failed. Let the schema dictate the type, not the column name.

Load data using model.define(C.new(id=TABLE.key)), bind properties and relationships using model.define(...).where(...). For complete data loading API reference (CSV, Snowflake, lookup, model.where, required vs optional columns, boolean flags), see rai-pyrel-coding [data-loading.md](../rai-pyrel-coding/references/data-loading.md). For a minimal worked example, see [examples/valuetypefkresolution.py](examples/valuetypefkresolution.py).

Key rules:

• Property for many-to-one (functional) associations. Relationship for many-to-many associations.
• Domain names for concepts and properties, not schema names
• Define inverses for navigable relationships — see Additional relationship patterns below

Authoritative vs. joinable sources

Each concept has two kinds of source table relationships:

• Authoritative source: The table where the concept's identity is defined (where the PK lives). Use this for model.define(C.new(id=TABLE.key)).
• Joinable source: A table that references the concept via FK but does not define it. Use this for relationship binding with model.define(...).where(...).

Why this matters for enrichment: When a MODEL_GAP fix needs to map a new property, the source_table must be the authoritative source for the target concept (or a table with a reliable FK to it). Mapping from the wrong table produces incorrect or missing data. For example, if CUSTOMERS is the table where Customer identity is defined (model.define(Customer.new(id=CUSTOMERS.customer_id))), it is the authoritative source for Customer; ORDERS, which references Customer via FK, is a joinable source for that concept.

Decision rule for enrichment source selection:

1. Find the authoritative table for the target concept (the table used in model.define(C.new(id=TABLE.key)))
2. Check if the needed column exists in that table -- use it directly
3. If the column is in a different table, verify there is a reliable join path (FK relationship) to the target concept
4. If no reliable join path exists, flag it -- the enrichment may require an intermediate relationship first

Handling mismatches between data and model

| Mismatch | Strategy | |----------|----------| | One table represents multiple concepts | Load the same table into multiple concepts with different .where() filters | | One concept needs data from multiple tables | Load each table, then bind with model.define(...).where(...) | | Column has no business meaning for the problem | Omit it from the model; not every column needs a property | | Column naming is cryptic | Map to descriptive property names during loading | | Data has no explicit FK but concepts are related | Use model.define(A.relationship(B)).where(A.shared_field == B.shared_field) |

Subconcept creation from filtered data

When a single table contains multiple entity types, create subconcepts filtered by a type column:

_Business = Concept("Business")
_Business.id = Property(f"{_Business} has id {String:id}")

_Supplier = Concept("Supplier")
_Supplier.id = Property(f"{_Supplier} has id {String:id}")
_Supplier.name = Relationship(f"{_Supplier} named {String:name}")

# Filter by type column during entity creation
define(_Supplier.new(id=TABLE__BUSINESS.id)).where(TABLE__BUSINESS.type == "SUPPLIER")
define(_Supplier.name(TABLE__BUSINESS.name)).where(_Supplier.id == TABLE__BUSINESS.id)

This pattern is common when a single source table contains multiple business roles differentiated by a TYPE column (e.g., suppliers, customers, manufacturers, warehouses all in one table).

---

Layering Principles

When to split into layers

A single file is fine for starter ontologies and small projects. Split into a package when:

• Multiple reasoners (solver + graph) share the same base model
• Derived/computed logic is reused across 2+ applications
• The file exceeds ~300 lines and has distinct data-loading vs. business-logic sections

Layer Responsibilities

When splitting, structure models in three layers with one-way dependencies: core → computed → apps.

> Constraint: The package directory name and the Model(...) variable name must differ. If model = Model("my_project"), the package cannot be called model/. Python resolves import model.core to the directory, shadowing the variable. Use a domain-specific directory name (e.g., sc_model/, fraud_model/).

| Layer | Purpose | Contains | Avoids | |-------|---------|----------|--------| | Core (/core.py) | Physical-to-semantic translation | Concept declarations, properties bound to source columns, structural FK relationships | Aggregations, derivations, business rules | | Computed (/computed.py) | Reusable business logic | Derived properties, segmentations, calculated metrics, entity subtypes | Application-specific filters or one-off calculations | | Application (apps/) | Feature delivery | Queries, aggregations, parameterized reports, optimization | Redefining concepts or business rules |

Decision rule: Raw source data mapping → Core. Reused across 2+ apps or fundamental business semantics → Computed. Everything else → Application.

Computed Layer Design Principles

Add computed concepts only when they represent reusable derived semantics, not one-off application logic. If you write the same .where() filter 3+ times, promote it to a derived concept:

ActiveOrder = model.Concept("ActiveOrder", extends=[Order])
model.define(ActiveOrder(Order)).where(Order.status != "cancelled", Order.ship_date > today)

Rules:

• Core owns identity and source-column bindings.
• Computed derives from core facts rather than binding directly to source columns.
• Prefer recomputing from base facts; see Computed vs. pre-computed data above.

---

Model Gap Identification

Model gaps are ONLY for data that exists in the schema but isn't mapped to the model. Check the schema info:

If there are "Available for PROPERTY enrichment" or "Available for RELATIONSHIP enrichment" columns:

• Property gaps (gap_type="property") -- unmapped scalar columns (costs, capacities, quantities)
• Relationship gaps (gaptype="relationship") -- unmapped FK columns (ending in ID)
• Each gap should reference a specific sourcetable and sourcecolumn from the schema info — without these, the enrichment tool cannot generate the correct define() rule

How to detect relationship gaps:

1. Check schema info for "Available for RELATIONSHIP enrichment (FK columns)" entries
2. Check "Relationship Semantics" in mo

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: RelationalAI
• Source: RelationalAI/rai-agent-skills
• License: Apache-2.0
• Homepage: https://relational.ai

Install and usage instructions live in the source repository linked above.