“

Series: S3 Data Foundation · Article 5 | Level: Advanced | Reading Time: 20 min

Three-Table Model Design: entity_common / entity_edge / entity_event

Tags: #ThreeTableModel #Ontology #SchemaDesign #QueryPatterns #EntityModel #coomia-dip

#TL;DR

At the heart of the coomia-dip data foundation lie three core tables — entity_common (entities), entity_edge (relationships), and entity_event (events) — that host all data for any Ontology. This "three-table model" is the critical bridge between upper-layer Ontology semantics and lower-layer storage engines. This article explains why we chose three tables over wide tables or graph databases, dissects every field-level design decision, and presents query optimization strategies for Doris and Iceberg. Real-world benchmark data proves the three-table model achieves an optimal balance between flexibility and performance.

#1. Why Three Tables

#1.1 The Essential Classification of Ontology Data

Data in an Ontology can be reduced to three fundamental types:

Code

Ontology Data Trichotomy:

┌─────────────────────────────────────────────────────────┐
│                    Ontology Data                         │
│                                                          │
│  ┌───────────────┐ ┌───────────────┐ ┌───────────────┐  │
│  │    Entity      │ │     Edge      │ │    Event      │  │
│  │               │ │  (Relation)   │ │               │  │
│  │               │ │               │ │               │  │
│  │ "What things" │ │ "Who relates" │ │ "What happened│  │
│  │               │ │               │ │  and when"    │  │
│  │ Person        │ │ WorksAt       │ │ Login         │  │
│  │ Company       │ │ Manages       │ │ Transaction   │  │
│  │ Device        │ │ ConnectedTo   │ │ Alert         │  │
│  │ Document      │ │ DerivedFrom   │ │ StatusChange  │  │
│  └───────┬───────┘ └───────┬───────┘ └───────┬───────┘  │
│          │                 │                 │           │
│          ▼                 ▼                 ▼           │
│   entity_common      entity_edge      entity_event      │
│                                                          │
└─────────────────────────────────────────────────────────┘

#1.2 Problems with Other Approaches

Before settling on the three-table model, we evaluated multiple alternatives:

Approach	Description	Pros	Fatal Flaw
Table-per-type	Person table, Company table...	Simple queries	Type explosion, uncontrollable DDL
Single wide table	All data in one table	Minimal	Sparse columns, schema bloat
EAV model	Entity-Attribute-Value	Fully flexible	Query performance disaster
Graph database	Neo4j / JanusGraph	Fast traversal	Weak aggregation, complex ops
Three-table	Entity + Edge + Event	Balanced	Moderate complexity

#1.3 Design Philosophy

Code

Three-Table Model Design Principles:

Principle 1: Type-agnostic table structure
  All entity types share one table; type distinction via entity_type column
  → Adding new Ontology types requires zero DDL changes

Principle 2: Properties as JSON
  Each entity's specific attributes live in a JSON column
  → Flexibility > query performance (mitigated by indexing)

Principle 3: Relationships as first-class citizens
  Relationships are not "foreign keys" — they have their own attributes
  → Supports weighted, temporal, attributed relationships

Principle 4: Event stream separated from state
  Entity table stores "current state"; event table stores "change history"
  → Natural support for event sourcing and time travel

#2. entity_common: The Universal Entity Container

#2.1 Table Schema

SQL

-- entity_common: Unified storage for all Ontology entities
-- Engines: Apache Doris (primary query) + Apache Iceberg (versioned)

CREATE TABLE entity_common (
    -- === Primary Key ===
    entity_id       VARCHAR(64)     NOT NULL    COMMENT 'Entity unique ID, UUID v7 (time-ordered)',
    world_id        VARCHAR(32)     NOT NULL    COMMENT 'Owning World (project space)',

    -- === Type Section ===
    entity_type     VARCHAR(128)    NOT NULL    COMMENT 'Ontology type, e.g. Person, Company',
    type_version    INT             NOT NULL    COMMENT 'Type version for schema evolution',

    -- === Display Section ===
    display_name    VARCHAR(512)    DEFAULT ''  COMMENT 'Display name (denormalized for search)',
    description     TEXT            DEFAULT ''  COMMENT 'Description (denormalized for full-text)',

    -- === Properties Section ===
    properties      JSON            NOT NULL    COMMENT 'Entity attributes (Ontology-defined fields)',
    computed_props  JSON            DEFAULT '{}' COMMENT 'Computed properties (derived property cache)',

    -- === Security Section ===
    security_label  VARCHAR(32)     DEFAULT 'internal'  COMMENT 'Security classification',
    owner_id        VARCHAR(64)     DEFAULT ''  COMMENT 'Owner user ID',

    -- === Audit Section ===
    created_at      DATETIME        NOT NULL    COMMENT 'Creation time',
    updated_at      DATETIME        NOT NULL    COMMENT 'Last update time',
    created_by      VARCHAR(64)     DEFAULT ''  COMMENT 'Creator',
    updated_by      VARCHAR(64)     DEFAULT ''  COMMENT 'Updater',
    version         BIGINT          DEFAULT 1   COMMENT 'Optimistic lock version',
    is_deleted      BOOLEAN         DEFAULT FALSE COMMENT 'Soft delete flag',

    -- === Vector Section (optional) ===
    embedding       ARRAY<FLOAT>    NULL        COMMENT 'Semantic vector (HNSW indexed)'
)
UNIQUE KEY (entity_id, world_id)
DISTRIBUTED BY HASH(entity_id) BUCKETS 32
PROPERTIES (
    "replication_num" = "3",
    "enable_unique_key_merge_on_write" = "true",
    "store_row_column" = "true"
);

-- === Indexes ===
CREATE INDEX idx_entity_type ON entity_common (entity_type) USING BITMAP;
CREATE INDEX idx_display_name ON entity_common (display_name) USING INVERTED
    PROPERTIES("parser" = "unicode", "support_phrase" = "true");
CREATE INDEX idx_description ON entity_common (description) USING INVERTED
    PROPERTIES("parser" = "unicode", "support_phrase" = "true");
CREATE INDEX idx_properties ON entity_common (properties) USING INVERTED;
CREATE INDEX idx_embedding ON entity_common (embedding) USING INVERTED
    PROPERTIES("index_type" = "hnsw", "metric_type" = "cosine", "m" = "16", "ef" = "200");
CREATE INDEX idx_created_at ON entity_common (created_at) USING INVERTED;
CREATE INDEX idx_updated_at ON entity_common (updated_at) USING INVERTED;

#2.2 Field Design Rationale

Code

Why each field is designed this way:

┌──────────────┬──────────────────────────────────────────────┐
│ Field         │ Design Decision                               │
├──────────────┼──────────────────────────────────────────────┤
│ entity_id    │ UUID v7 instead of auto-increment:             │
│              │  - No coordination needed in distributed env   │
│              │  - Time-ordered, B+ tree friendly               │
│              │  - VARCHAR(64) not BINARY: debug-friendly       │
├──────────────┼──────────────────────────────────────────────┤
│ world_id     │ Part of composite primary key:                  │
│              │  - Enforces data isolation                      │
│              │  - Queries auto-scoped to World                 │
│              │  - Supports multi-tenancy and branching         │
├──────────────┼──────────────────────────────────────────────┤
│ entity_type  │ VARCHAR(128) not ENUM:                          │
│              │  - New types need no ALTER TABLE                 │
│              │  - BITMAP index compensates query perf           │
│              │  - Stores Ontology fully-qualified name          │
├──────────────┼──────────────────────────────────────────────┤
│ properties   │ JSON instead of columnar storage:               │
│              │  - Different types have different attrs          │
│              │  - Doris JSON indexing provides acceleration     │
│              │  - Type checking done at SDK layer               │
├──────────────┼──────────────────────────────────────────────┤
│ display_name │ Denormalized field (derivable from properties): │
│              │  - Avoids JSON parsing overhead for search       │
│              │  - Full-text search requires standalone column   │
│              │  - List views skip parsing entire JSON           │
├──────────────┼──────────────────────────────────────────────┤
│ embedding    │ Optional ARRAY<FLOAT> column:                   │
│              │  - Only needed for semantic search               │
│              │  - 768/1536 dimensions depending on model        │
│              │  - HNSW index accelerates similarity             │
└──────────────┴──────────────────────────────────────────────┘

#2.3 Properties JSON Internal Structure

JSON

{
  "__type_meta": {
    "type": "Person",
    "version": 3,
    "schema_hash": "a1b2c3d4"
  },
  "name": "John Smith",
  "email": "john@example.com",
  "age": 35,
  "department": "Engineering",
  "hire_date": "2020-06-15",
  "skills": ["Python", "Java", "gRPC"],
  "address": {
    "city": "San Francisco",
    "state": "CA"
  },
  "__refs": {
    "avatar": "s3://onto-proj001-uploads/avatars/john.jpg",
    "resume": "s3://onto-proj001-uploads/docs/john-resume.pdf"
  }
}

#3. entity_edge: Relationships as First-Class Citizens

#3.1 Table Schema

SQL

-- entity_edge: Unified storage for all Ontology relationships
CREATE TABLE entity_edge (
    -- === Primary Key ===
    edge_id         VARCHAR(64)     NOT NULL    COMMENT 'Edge unique ID',
    world_id        VARCHAR(32)     NOT NULL    COMMENT 'Owning World',

    -- === Endpoint Section ===
    source_id       VARCHAR(64)     NOT NULL    COMMENT 'Source entity ID',
    source_type     VARCHAR(128)    NOT NULL    COMMENT 'Source entity type',
    target_id       VARCHAR(64)     NOT NULL    COMMENT 'Target entity ID',
    target_type     VARCHAR(128)    NOT NULL    COMMENT 'Target entity type',

    -- === Relationship Section ===
    edge_type       VARCHAR(128)    NOT NULL    COMMENT 'Relation type, e.g. WorksAt, Manages',
    edge_label      VARCHAR(256)    DEFAULT ''  COMMENT 'Relation label (for display)',
    direction       VARCHAR(16)     DEFAULT 'directed' COMMENT 'directed/undirected',

    -- === Properties Section ===
    properties      JSON            DEFAULT '{}' COMMENT 'Relationship attributes',
    weight          DOUBLE          DEFAULT 1.0 COMMENT 'Relationship weight',
    confidence      DOUBLE          DEFAULT 1.0 COMMENT 'Relationship confidence',

    -- === Temporal Section ===
    valid_from      DATETIME        NULL        COMMENT 'Relationship start time',
    valid_to        DATETIME        NULL        COMMENT 'Relationship end time',
    created_at      DATETIME        NOT NULL    COMMENT 'Creation time',
    updated_at      DATETIME        NOT NULL    COMMENT 'Last update time',

    -- === Audit Section ===
    created_by      VARCHAR(64)     DEFAULT ''  COMMENT 'Creator',
    is_deleted      BOOLEAN         DEFAULT FALSE COMMENT 'Soft delete flag',
    version         BIGINT          DEFAULT 1   COMMENT 'Optimistic lock version'
)
UNIQUE KEY (edge_id, world_id)
DISTRIBUTED BY HASH(edge_id) BUCKETS 16
PROPERTIES (
    "replication_num" = "3",
    "enable_unique_key_merge_on_write" = "true"
);

-- === Indexes ===
CREATE INDEX idx_source ON entity_edge (source_id) USING BITMAP;
CREATE INDEX idx_target ON entity_edge (target_id) USING BITMAP;
CREATE INDEX idx_edge_type ON entity_edge (edge_type) USING BITMAP;
CREATE INDEX idx_source_type ON entity_edge (source_type) USING BITMAP;
CREATE INDEX idx_target_type ON entity_edge (target_type) USING BITMAP;
CREATE INDEX idx_valid_from ON entity_edge (valid_from) USING INVERTED;
CREATE INDEX idx_valid_to ON entity_edge (valid_to) USING INVERTED;

#3.2 Relationship Types and Directionality

Code

Relationship Directionality:

1. Directed Relationships:
   Person ──WorksAt──> Company
   Person ──Manages──> Person
   Document ──DerivedFrom──> Document

2. Undirected Relationships:
   Person ──Colleagues──  Person
   Device ──ConnectedTo── Device

3. Temporal Relationships:
   Person ──WorksAt──> Company
     valid_from: 2020-06-15
     valid_to:   2024-03-01  (left the company)

   Person ──WorksAt──> Company
     valid_from: 2024-03-15
     valid_to:   NULL        (currently employed)

Relationship Properties Examples:
┌────────────────┬──────────────────────────┐
│ edge_type      │ properties example        │
├────────────────┼──────────────────────────┤
│ WorksAt        │ {"role": "Engineer",      │
│                │  "level": "Senior"}       │
├────────────────┼──────────────────────────┤
│ Manages        │ {"team_size": 8,          │
│                │  "since": "2023-01"}      │
├────────────────┼──────────────────────────┤
│ Transaction    │ {"amount": 50000,         │
│                │  "currency": "USD"}       │
├────────────────┼──────────────────────────┤
│ Similarity     │ {"score": 0.87,           │
│                │  "method": "cosine"}      │
└────────────────┴──────────────────────────┘

#3.3 Graph Traversal Query Patterns

SQL

-- 1. Single-hop query: Find which company a person works at
SELECT ec.*
FROM entity_common ec
JOIN entity_edge ee ON ee.target_id = ec.entity_id
    AND ee.world_id = ec.world_id
WHERE ee.source_id = 'person-001'
    AND ee.edge_type = 'WorksAt'
    AND ee.world_id = 'world-main'
    AND ee.is_deleted = FALSE
    AND (ee.valid_to IS NULL OR ee.valid_to > NOW());

-- 2. Two-hop query: Find projects of a person's colleagues
SELECT DISTINCT ec_project.*
FROM entity_edge ee1
JOIN entity_edge ee2 ON ee2.source_id = ee1.target_id
    AND ee2.world_id = ee1.world_id
JOIN entity_common ec_project ON ec_project.entity_id = ee2.target_id
    AND ec_project.world_id = ee2.world_id
WHERE ee1.source_id = 'person-001'
    AND ee1.edge_type = 'Colleagues'
    AND ee2.edge_type = 'WorksOn'
    AND ee1.world_id = 'world-main';

-- 3. Aggregation query: Headcount per department
SELECT
    JSON_EXTRACT(ec.properties, '$.department') AS department,
    COUNT(*) AS headcount
FROM entity_common ec
WHERE ec.entity_type = 'Person'
    AND ec.world_id = 'world-main'
    AND ec.is_deleted = FALSE
GROUP BY JSON_EXTRACT(ec.properties, '$.department')
ORDER BY headcount DESC;

-- 4. Path query (recursive CTE): Shortest path between two entities
WITH RECURSIVE paths AS (
    SELECT
        source_id,
        target_id,
        edge_type,
        1 AS depth,
        ARRAY[source_id, target_id] AS path
    FROM entity_edge
    WHERE source_id = 'person-001'
        AND world_id = 'world-main'
        AND is_deleted = FALSE

    UNION ALL

    SELECT
        p.source_id,
        ee.target_id,
        ee.edge_type,
        p.depth + 1,
        ARRAY_APPEND(p.path, ee.target_id)
    FROM paths p
    JOIN entity_edge ee ON ee.source_id = p.target_id
        AND ee.world_id = 'world-main'
        AND ee.is_deleted = FALSE
    WHERE p.depth < 5
        AND NOT ARRAY_CONTAINS(p.path, ee.target_id)
)
SELECT * FROM paths
WHERE target_id = 'person-099'
ORDER BY depth ASC
LIMIT 1;

#4. entity_event: The Faithful Timeline Recorder

#4.1 Table Schema

SQL

-- entity_event: Time-series storage for all events
CREATE TABLE entity_event (
    -- === Primary Key ===
    event_id        VARCHAR(64)     NOT NULL    COMMENT 'Event unique ID',
    world_id        VARCHAR(32)     NOT NULL    COMMENT 'Owning World',

    -- === Association Section ===
    entity_id       VARCHAR(64)     NOT NULL    COMMENT 'Associated entity ID',
    entity_type     VARCHAR(128)    NOT NULL    COMMENT 'Associated entity type',

    -- === Event Section ===
    event_type      VARCHAR(128)    NOT NULL    COMMENT 'Event type: Created, Updated, Alert...',
    event_subtype   VARCHAR(128)    DEFAULT ''  COMMENT 'Event subtype',
    event_source    VARCHAR(128)    NOT NULL    COMMENT 'Event source',

    -- === Data Section ===
    payload         JSON            NOT NULL    COMMENT 'Event payload (complete data)',
    summary         VARCHAR(1024)   DEFAULT ''  COMMENT 'Event summary (quick display)',
    severity        VARCHAR(16)     DEFAULT 'info' COMMENT 'Severity: info/warn/error/critical',

    -- === Time Section ===
    event_time      DATETIME        NOT NULL    COMMENT 'When the event occurred (business time)',
    ingestion_time  DATETIME        NOT NULL    COMMENT 'When the event was ingested (system time)',

    -- === Provenance Section ===
    caused_by       VARCHAR(64)     DEFAULT ''  COMMENT 'Trigger (user/system/pipeline)',
    correlation_id  VARCHAR(64)     DEFAULT ''  COMMENT 'Correlation trace ID',

    -- === Audit Section ===
    is_deleted      BOOLEAN         DEFAULT FALSE COMMENT 'Soft delete flag'
)
DUPLICATE KEY (event_id, world_id, event_time)
PARTITION BY RANGE(event_time) ()
DISTRIBUTED BY HASH(entity_id) BUCKETS 32
PROPERTIES (
    "replication_num" = "3",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-90",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.buckets" = "32"
);

-- === Indexes ===
CREATE INDEX idx_entity_id ON entity_event (entity_id) USING BITMAP;
CREATE INDEX idx_entity_type ON entity_event (entity_type) USING BITMAP;
CREATE INDEX idx_event_type ON entity_event (event_type) USING BITMAP;
CREATE INDEX idx_severity ON entity_event (severity) USING BITMAP;
CREATE INDEX idx_event_source ON entity_event (event_source) USING BITMAP;
CREATE INDEX idx_summary ON entity_event (summary) USING INVERTED
    PROPERTIES("parser" = "unicode");
CREATE INDEX idx_payload ON entity_event (payload) USING INVERTED;
CREATE INDEX idx_correlation ON entity_event (correlation_id) USING BITMAP;

#4.2 Event Type Taxonomy

Code

Event Type Hierarchy:

System Events (auto-generated):
├── EntityCreated        Entity creation
├── EntityUpdated        Entity update (with diff)
├── EntityDeleted        Entity deletion
├── EdgeCreated          Relationship creation
├── EdgeDeleted          Relationship deletion
├── PropertyChanged      Property change
└── ComputedPropUpdated  Computed property refresh

Business Events (external input):
├── Transaction          Financial transaction
├── Alert                Alert or alarm
├── StatusChange         Status transition
├── Measurement          Sensor/measurement data
├── Interaction          User/system interaction
└── CustomEvent          Custom business event

Audit Events (operation records):
├── DataAccess           Data access log
├── PermissionChange     Permission modification
├── ExportRequested      Data export request
└── QueryExecuted        Query execution log

#4.3 Event Writing and Querying

Python

# python-sdk/ontology_sdk/events/event_writer.py
from datetime import datetime, timezone
from uuid import uuid4

from pydantic import BaseModel, Field


class OntologyEvent(BaseModel):
    """Ontology event model"""
    event_id: str = Field(default_factory=lambda: str(uuid4()))
    world_id: str
    entity_id: str
    entity_type: str
    event_type: str
    event_subtype: str = ""
    event_source: str
    payload: dict
    summary: str = ""
    severity: str = "info"
    event_time: datetime = Field(default_factory=lambda: datetime.now(timezone.utc))
    ingestion_time: datetime = Field(default_factory=lambda: datetime.now(timezone.utc))
    caused_by: str = ""
    correlation_id: str = ""


class EventWriter:
    """Event writer"""

    def __init__(self, doris_connection):
        self._conn = doris_connection

    def write_entity_change_event(
        self,
        world_id: str,
        entity_id: str,
        entity_type: str,
        change_type: str,  # Created, Updated, Deleted
        old_values: dict | None = None,
        new_values: dict | None = None,
        caused_by: str = "system",
    ) -> OntologyEvent:
        """Write an entity change event"""
        diff = {}
        if old_values and new_values:
            for key in set(list(old_values.keys()) + list(new_values.keys())):
                old_val = old_values.get(key)
                new_val = new_values.get(key)
                if old_val != new_val:
                    diff[key] = {"old": old_val, "new": new_val}

        event = OntologyEvent(
            world_id=world_id,
            entity_id=entity_id,
            entity_type=entity_type,
            event_type=f"Entity{change_type}",
            event_source="ontology-engine",
            payload={
                "change_type": change_type,
                "diff": diff,
                "old_values": old_values or {},
                "new_values": new_values or {},
            },
            summary=f"{entity_type} {entity_id} was {change_type.lower()}d",
            caused_by=caused_by,
        )

        self._insert_event(event)
        return event

    def _insert_event(self, event: OntologyEvent) -> None:
        """Insert event into Doris"""
        sql = """
            INSERT INTO entity_event (
                event_id, world_id, entity_id, entity_type,
                event_type, event_subtype, event_source,
                payload, summary, severity,
                event_time, ingestion_time,
                caused_by, correlation_id
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        self._conn.execute(sql, [
            event.event_id, event.world_id, event.entity_id,
            event.entity_type, event.event_type, event.event_subtype,
            event.event_source, event.payload, event.summary,
            event.severity, event.event_time, event.ingestion_time,
            event.caused_by, event.correlation_id,
        ])

#5. Cross-Table Query Patterns

#5.1 Query Pattern Classification

Code

Query Pattern Matrix:

┌─────────────────────┬─────────────────────┬──────────────────┐
│  Single-Table        │  Two-Table Join      │  Three-Table Join │
├─────────────────────┼─────────────────────┼──────────────────┤
│ Filter by type       │ Entity + edge        │ Entity + edge +   │
│ Full-text search     │   traversal          │   event timeline  │
│ Property filter      │ Entity + event       │ (full context     │
│ Semantic vector      │   timeline           │  query)           │
│   search             │ Edge + event         │                    │
│ Event time range     │   correlation        │                    │
│ Event aggregation    │                      │                    │
└─────────────────────┴─────────────────────┴──────────────────┘

#5.2 Typical Query Examples

SQL

-- Pattern 1: Entity 360-degree view (three-table join)
-- "Show me everything about an entity: attributes, relationships, recent events"

-- Basic info
SELECT * FROM entity_common
WHERE entity_id = 'person-001' AND world_id = 'world-main';

-- All relationships (outbound and inbound)
SELECT
    ee.edge_type,
    ee.direction,
    CASE
        WHEN ee.source_id = 'person-001' THEN ee.target_id
        ELSE ee.source_id
    END AS related_entity_id,
    CASE
        WHEN ee.source_id = 'person-001' THEN ee.target_type
        ELSE ee.source_type
    END AS related_entity_type,
    ee.properties AS edge_properties,
    ee.weight
FROM entity_edge ee
WHERE (ee.source_id = 'person-001' OR ee.target_id = 'person-001')
    AND ee.world_id = 'world-main'
    AND ee.is_deleted = FALSE
ORDER BY ee.created_at DESC;

-- Recent 50 events
SELECT
    event_type, summary, severity,
    event_time, payload
FROM entity_event
WHERE entity_id = 'person-001'
    AND world_id = 'world-main'
ORDER BY event_time DESC
LIMIT 50;

-- Pattern 2: Relationship graph aggregation
-- "How many people per department? Collaboration strength between departments?"
SELECT
    JSON_EXTRACT(src.properties, '$.department') AS src_dept,
    JSON_EXTRACT(tgt.properties, '$.department') AS tgt_dept,
    COUNT(*) AS collaboration_count,
    AVG(ee.weight) AS avg_strength
FROM entity_edge ee
JOIN entity_common src ON src.entity_id = ee.source_id
    AND src.world_id = ee.world_id
JOIN entity_common tgt ON tgt.entity_id = ee.target_id
    AND tgt.world_id = ee.world_id
WHERE ee.edge_type = 'Collaborates'
    AND ee.world_id = 'world-main'
GROUP BY src_dept, tgt_dept
ORDER BY collaboration_count DESC;

-- Pattern 3: Event-driven entity discovery
-- "Devices with most alerts in the last 24 hours"
SELECT
    ec.entity_id,
    ec.display_name,
    JSON_EXTRACT(ec.properties, '$.device_type') AS device_type,
    COUNT(ev.event_id) AS alert_count,
    MAX(ev.severity) AS max_severity
FROM entity_event ev
JOIN entity_common ec ON ec.entity_id = ev.entity_id
    AND ec.world_id = ev.world_id
WHERE ev.event_type = 'Alert'
    AND ev.world_id = 'world-main'
    AND ev.event_time >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY ec.entity_id, ec.display_name, device_type
ORDER BY alert_count DESC
LIMIT 20;

#6. Performance Optimization on Doris

#6.1 Partitioning and Bucketing Strategy

Code

Partitioning and Bucketing Strategy:

entity_common:
  Partition: None (moderate data volume, frequent full scans)
  Bucket: HASH(entity_id) 32 buckets
  Reason: Balance between point lookups and range queries

entity_edge:
  Partition: None
  Bucket: HASH(edge_id) 16 buckets
  Reason: Edge count is typically less than entity count

entity_event:
  Partition: RANGE(event_time) dynamic daily partitions
  Bucket: HASH(entity_id) 32 buckets
  Reason:
    - Time range queries benefit from partition pruning
    - HASH by entity_id keeps one entity's events co-located
    - Dynamic partitioning auto-creates and cleans up

#6.2 Materialized Views for Acceleration

SQL

-- MV 1: Entity type counts (for dashboards)
CREATE MATERIALIZED VIEW mv_entity_type_count AS
SELECT
    world_id,
    entity_type,
    COUNT(*) AS entity_count
FROM entity_common
WHERE is_deleted = FALSE
GROUP BY world_id, entity_type;

-- MV 2: Daily event statistics
CREATE MATERIALIZED VIEW mv_daily_event_stats AS
SELECT
    world_id,
    entity_type,
    event_type,
    severity,
    DATE(event_time) AS event_date,
    COUNT(*) AS event_count
FROM entity_event
GROUP BY world_id, entity_type, event_type, severity, DATE(event_time);

-- MV 3: Edge type statistics
CREATE MATERIALIZED VIEW mv_edge_type_stats AS
SELECT
    world_id,
    edge_type,
    source_type,
    target_type,
    COUNT(*) AS edge_count,
    AVG(weight) AS avg_weight
FROM entity_edge
WHERE is_deleted = FALSE
GROUP BY world_id, edge_type, source_type, target_type;

#6.3 Performance Benchmarks

Query Type	Data Scale	Avg Latency	P99 Latency	QPS
Single entity lookup	100M entities	2 ms	8 ms	15,000
Type filter	100M entities	45 ms	120 ms	800
JSON property query	100M entities	85 ms	250 ms	400
Single-hop edge query	500M edges	12 ms	35 ms	5,000
Two-hop traversal	500M edges	180 ms	500 ms	200
Event time range	1B events	35 ms	95 ms	1,200
Three-table 360 join	Combined	120 ms	350 ms	300
Full-text search	100M entities	25 ms	75 ms	2,000

#7. Versioned Storage on Iceberg

#7.1 Dual-Write Architecture

Code

Three Tables on Doris + Iceberg Dual-Write:

┌─────────────┐
│  SDK / API  │
└──────┬──────┘
       │
       ▼
┌──────────────┐
│  Write Path  │
│              │
│  ┌────────┐  │     ┌──────────────────┐
│  │ Doris  │──│────>│ Doris Tables     │  <- Primary query (low latency)
│  │ Writer │  │     │ entity_common    │
│  └────────┘  │     │ entity_edge      │
│              │     │ entity_event     │
│  ┌────────┐  │     └──────────────────┘
│  │Iceberg │──│────>┌──────────────────┐
│  │ Writer │  │     │ Iceberg Tables   │  <- Versioned (time travel)
│  └────────┘  │     │ (via Nessie)     │
│              │     └──────────────────┘
└──────────────┘

#7.2 Schema Evolution Support

Python

# Three-table model schema evolution needs NO DDL changes
# because attributes live in the JSON column

# Old version entity (type_version=1)
old_entity = {
    "properties": {
        "name": "John Smith",
        "email": "john@example.com"
    }
}

# New version entity (type_version=2, added phone field)
new_entity = {
    "properties": {
        "name": "John Smith",
        "email": "john@example.com",
        "phone": "+1-555-0123"  # New field
    }
}

# SDK-layer compatibility via type_version
class EntityMigrator:
    """Entity version migrator"""

    def migrate_v1_to_v2(self, properties: dict) -> dict:
        """v1 -> v2: Add default phone value"""
        if "phone" not in properties:
            properties["phone"] = ""
        return properties

#8. SDK Integration

#8.1 ORM-Style Access

Python

# python-sdk/ontology_sdk/orm/entity.py
from ontology_sdk.models import Entity, Edge, Event


# Query entity
person = Entity.get("person-001", world_id="world-main")
print(person.display_name)
print(person.properties["email"])

# Traverse relationships
companies = person.edges("WorksAt").targets()
for company in companies:
    print(f"{person.display_name} works at {company.display_name}")

# View timeline
events = person.events(
    event_type="PropertyChanged",
    since="2024-01-01",
    limit=10
)
for event in events:
    print(f"[{event.event_time}] {event.summary}")

# Create relationship
Edge.create(
    source=person,
    target=companies[0],
    edge_type="Manages",
    properties={"team_size": 5}
)

# Write event
Event.create(
    entity=person,
    event_type="Promotion",
    payload={"new_level": "Staff Engineer"},
    summary="Promoted to Staff Engineer"
)

#Key Takeaways

The three-table model is schema-on-read best practice: JSON property columns provide complete schema flexibility. Adding new Ontology types requires zero DDL changes, while indexes ensure query performance.
Relationships as first-class citizens enable graph queries: The independent entity_edge table supports weighted, temporal, attributed relationship modeling, achieving graph traversal on a relational database.
The event table provides natural event sourcing: entity_event, stored separately from entities, supports complete change history tracking, timeline display, and audit compliance.
Dynamic partitioning optimizes time-series queries: Daily dynamic partitions for the event table auto-create future partitions and clean up expired ones, combined with HASH bucketing for entity-level event locality.
Dual-write architecture balances performance and versioning: Doris provides low-latency queries while Iceberg via Nessie provides data version control, together satisfying both online service and data governance needs.

#Next Article

The next article S3-06 "OQL: Our Ontology Query Language (Syntax)" introduces the query language we designed specifically for the three-table model — OQL (Ontology Query Language), covering BNF grammar definitions, core statements, metric expansion, and graph traversal syntax.

Tags: #ThreeTableModel #EntityCommon #EntityEdge #EntityEvent #Ontology #SchemaDesign #QueryPatterns #Doris #Iceberg #coomia-dip #DataFoundation