Knowledge Engineering
and the 'Shortcomings' of SQL

Where SQL fits, where it doesn't, and a practical path forward

By G. Sawatzky, embedded-commerce.com

Prefer prose? Read the full article

What is Knowledge Representation?

Knowledge Representation (KR): Formalization of information with explicit rules, relationships, and constraints enabling computational reasoning and inference.
Reasoning: Deriving conclusions implicitly contained within represented knowledge (deduction, induction, abduction).
Key distinction: Not just storing facts, but encoding the logic to derive new facts automatically.
Example: "Mary is 30" + "All persons over 18 are adults" → infer "Mary is an adult"

Knowledge Engineering Value Proposition

Automated Inference: Derive new facts without explicit programming.
Semantic Search: Find information by meaning, not just keywords.
Data Validation: Enforce complex business rules beyond structural checks.
Decision Support: Provide explicit reasoning steps for decisions.
System Integration: Shared ontology for disparate systems.
Explainable AI: Auditable reasoning chains for trust and compliance.

Six Technical Challenges with SQL for KR

Composability: Building reusable rule components and layered knowledge structures is cumbersome.
Procedural vs. Declarative: Complex rules often require stored procedures or triggers, scattering logic.
Integrity Constraints vs. Business Rules: Complex domain rules don't fit simple CHECK constraints.
Burdensome DDL: Schema changes in large RDBMS slow agile knowledge evolution.
Schema-on-write rigidity: Must define structure before data, clashing with exploratory knowledge discovery.
NULL/3-valued logic: Deviates from standard two-valued logic foundational to KR.

The Three-Layer Problem

Conceptual Model: Domain meaning and business rules, technology-independent.
Logical Model: Maps to a data model (relational, graph, object), abstracts from storage.
Physical Model: Actual storage, indexing, performance optimizations.

The Problem: Traditional SQL design forces blending these layers. Choosing VARCHAR size (physical) impacts how "name" (conceptual) is perceived. KE requires clean separation with loosely coupled mappings.

CWA vs. OWA: A Fundamental Divide

Closed World Assumption (CWA): SQL's default. If a fact isn't in the database, it's false.
Open World Assumption (OWA): Common in KR (e.g., OWL). If a fact isn't known, it's unknown (not false).

Impact: CWA simplifies querying but struggles with incomplete knowledge. OWA is better suited for dynamic, real-world knowledge. SQL can simulate OWA with LEFT JOIN or NOT EXISTS, but it's not native.

SQL ≠ The Relational Model

Relational Model (Codd, Date, Darwen): Powerful logical framework with rich declarative integrity constraints.
SQL implementations: Deviate from the formal model in ways that impact KR.
Key insight: Criticisms of SQL don't necessarily apply to the formal Relational Model itself.

The formal RM provides robust knowledge representation through declarative constraints. A "relvar" is a predicate stating truth about the world.

Date & Darwen: SQL's Deviations from RM

NULL values: Three-valued logic (true/false/unknown) complicates reasoning, deviates from two-valued logic in KR.
Lack of true assertions: Weak support for complex, cross-table integrity constraints. RM allows arbitrary assertions.
Duplicate rows/tuple ordering: RM uses mathematical sets (no duplicates, no order). SQL allows both, undermining logical purity.
Imperfect type system: Less sophisticated than needed for rich conceptual types and hierarchies.

The Composability Challenge

Date & Darwen: SQL lacks true closure—SELECT doesn't always produce relational results (duplicates, NULLs).
LINQ/Rx perspective: Impedance mismatch between SQL strings and host language type systems hinders modular composition.
Result: Building layered knowledge structures from reusable rule components is harder than in Datalog or relational calculus.

When SQL's declarative features fall short, developers resort to procedural code, scattering logical definitions.

Use SQL Where It Shines

Transactional integrity, joins, indexing, and execution plans.
Mature governance, operations, and performance tooling.
Excellent substrate for views, materializations, and access control.
Robust data storage and retrieval of foundational facts.

Strategy: Use SQL for what it does best, not as the sole KR solution.

SQL is Evolving (Stonebraker: "What Goes Around Comes Around")

SQL/PGQ (Property Graph Queries): Query graph structures directly, enabling natural relationship traversal.
JSON/XML/Array types: Semi-structured data support reduces rigid schema requirements.
User-defined functions/aggregates: Encapsulate custom domain logic within SQL.
DuckDB: Embedded analytical SQL for fast on-device processing of derived knowledge.

SQL absorbs good ideas from alternative data models, extending capabilities within the relational paradigm.

Declarative Alternatives for KR

Datalog: Logic programming language for deductive databases. Highly declarative, excellent for recursive rules and inference.
Logica: Modern Datalog-inspired language that compiles to SQL. Provides composability and expressiveness while leveraging RDBMS infrastructure.
Relational Calculus: Theoretical foundation for declarative queries, influencing Datalog and other KR languages.

These languages offer the declarative power and composability KE practitioners need.

Logica Example: Class Hierarchy & Inference

@Engine("duckdb");

# Define graduate students
GraduateStudent(person_id: 123);
GraduateStudent(person_id: 456);

# Rule: GraduateStudent implies Student
Student(person_id:) :- GraduateStudent(person_id:);

# Define undergraduates
Undergraduate(person_id: 789);
Student(person_id:) :- Undergraduate(person_id:);

# Rule: GraduateStudents have library access
HasLibraryAccess(person_id:) :- GraduateStudent(person_id:);

Result: Query Student returns 123, 456, 789. Query HasLibraryAccess returns 123, 456. Rules compose naturally.

Logica Example: Simulating Open World Assumption

@Engine("duckdb");

IsSweet("orange");
IsSweet("apple");
IsNotSweet("lemon");
IsNotSweet("lime");

IsFruit("orange");
IsFruit("kiwi");
IsFruit("lemon");
IsFruit("apple");
IsFruit("lime");

# Find fruits with unknown sweetness
UnknownSweetness(fruit:) :- 
  IsFruit(fruit), 
  ~IsSweet(fruit), 
  ~IsNotSweet(fruit);

Result: Returns "kiwi" - a fruit where sweetness is explicitly unknown, not assumed false.

Logica Example: Taxonomy with Transitive Closure

SubclassOf("citrus", "fruits");
SubclassOf("fruits", "foods");
SubclassOf("foods", "entity");

HasProperty("foods", "is_perishable");
HasProperty("fruits", "is_sweet");
HasProperty("citrus", "is_zesty");

# Direct subclass
TransitiveSubclass(x,y) :- SubclassOf(x, y);

# Indirect subclass (recursive)
TransitiveSubclass(x, y) :- 
  TransitiveSubclass(x, z), 
  TransitiveSubclass(z, y);

# Inherit properties from superclasses
HasAllProperties(class, property) :- 
  HasProperty(class, property);
HasAllProperties(class, property) :-
  SubclassOf(class, superclass),
  HasAllProperties(superclass, property);

KE Toolkit Evaluation Checklist (Part 1)

Criteria	Description
Declarative Expression	Express domain rules as logical statements (what is true), not procedural steps (how to compute).
Automated Inference	Native support for deriving new facts from existing knowledge and rules.
Semantic Richness	Capture complex semantics: hierarchies, part-whole relationships, N-ary associations.
Incomplete Knowledge (OWA)	Distinguish unknown from false; handle missing information robustly.

KE Toolkit Evaluation Checklist (Part 2)

Criteria	Description
Schema Flexibility	Evolve conceptual model without significant overhead or disruption.
Layer Separation	Clear separation of conceptual, logical, and physical concerns.
Explainability	Provide transparent reasoning traces for conclusions.
Composability	Combine smaller knowledge modules to build larger, complex systems.
Model Querying	Query schema, concepts, and relationships independent of data instances.

Model-First, Engine-Right Architecture

Keep meaning in ORM + KR. Generate or handcraft engine code as needed.
Expose typed interfaces so agents can query facts at inference time.

Orchestration Strategy for the ORM Toolkit

SQL (especially DuckDB): Transactional integrity, robust storage, efficient retrieval of foundational facts, fast analytical processing.
Prolog/Datalog/Logica: Complex inference, rule-based reasoning, logical deduction, explainable reasoning paths.
LNNs/LTNs: Neuro-symbolic integration, probabilistic reasoning, handling uncertainty and incomplete knowledge.
Python orchestration: Rich ecosystem for data science, AI, and interaction with databases, logic engines, and ML frameworks.

Key principle: Don't replace—orchestrate. Use each technology for its strengths.

Next Steps

Adopt the KE evaluation checklist and assess current gaps.
Map existing business rules to formal KR representations.
Choose engine targets pragmatically based on use case (SQL, Prolog, LNN/LTN).
Expose typed, discoverable endpoints for agent consumption (GraphQL, REST).
Maintain clean separation between conceptual, logical, and physical layers.

More background in the article: Knowledge Engineering and the "Shortcomings" of SQL

Knowledge Engineeringand the 'Shortcomings' of SQL

What is Knowledge Representation?

Knowledge Engineering Value Proposition

Six Technical Challenges with SQL for KR

The Three-Layer Problem

CWA vs. OWA: A Fundamental Divide

SQL ≠ The Relational Model

Date & Darwen: SQL's Deviations from RM

The Composability Challenge

Use SQL Where It Shines

SQL is Evolving (Stonebraker: "What Goes Around Comes Around")

Declarative Alternatives for KR

Logica Example: Class Hierarchy & Inference

Logica Example: Simulating Open World Assumption

Logica Example: Taxonomy with Transitive Closure

KE Toolkit Evaluation Checklist (Part 1)

KE Toolkit Evaluation Checklist (Part 2)

Model-First, Engine-Right Architecture

Orchestration Strategy for the ORM Toolkit

Next Steps

Knowledge Engineering
and the 'Shortcomings' of SQL