Forward-Declared JOINs for Database Relationship Navigation

Solution Overview

Problem

Conventional relational database systems, such as those using SQL, are limited in expressing relationships between data items, primarily relying on foreign key constraints for consistency checking and lacking semantic relationship expression at the database level, which hinders efficient definition and querying of complex data relationships.

Innovation Solution

The introduction of 'associations' as forward-declared JOINs within the database model, allowing metadata information to define relationships between tables and views, enabling path expressions that navigate between entities and simplify query formulations without explicit JOIN declarations.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If foreign key constraints are used to express relationships in relational databases, then data consistency checking is improved, but the ability to express semantic relationships at the database level deteriorates

Engineering Contradiction:

Improvedata consistency checkingVSAvoidsemantic relationship expression

Core Design Contradiction:

ReliabilityVSAdaptability or versatility

Solution Approach 1:

The patent applies preliminary action by pre-defining association relationships between tables at the database schema level using forward-declared JOINs. These associations are established beforehand in the data model, allowing the database system to automatically understand and navigate relationships without requiring explicit JOIN operations in each query. This preliminary definition of relationships enables both consistency checking and semantic expression capabilities.

Inventive Principle:

Principle #10Preliminary action

2Measurement precision

If explicit JOIN declarations are required in SQL queries to navigate between tables, then query execution accuracy is improved, but query formulation complexity deteriorates

Engineering Contradiction:

Improvequery execution accuracyVSAvoidquery formulation complexity

Core Design Contradiction:

Measurement precisionVSDevice complexity

Solution Approach 1:

The patent implements self-service by enabling the database system to automatically perform JOIN operations based on pre-defined associations. When associations are declared in the data model, the query processor can automatically navigate between related tables without requiring the user to explicitly write JOIN clauses. The system serves itself by using the declared associations to automatically join tables, thereby simplifying query formulation while maintaining execution accuracy.

Inventive Principle:

Principle #25Self-service

3Adaptability or versatility

If relationships are expressed only at the application level rather than database level, then application flexibility is improved, but database navigation efficiency deteriorates

Engineering Contradiction:

Improveapplication flexibilityVSAvoiddatabase navigation efficiency

Core Design Contradiction:

Adaptability or versatilityVSProductivity

Solution Approach 1:

The patent applies preliminary action by pre-defining association relationships between tables at the database schema level using forward-declared JOINs. These associations are established beforehand in the data model, allowing the database system to automatically understand and navigate relationships without requiring explicit JOIN operations in each query. This preliminary definition of relationships enables both consistency checking and semantic expression capabilities.

Inventive Principle:

Principle #10Preliminary action

Data Source

PatentUS10599650B2Enhancements for forward joins expressing relationships

Publication Date: 2020.03.24 SAP SE

AI summary

A system, medium, and method to define a database table and view based on a database model using a database language statement, the database language statement including a statement to create the table and a forward-declared JOIN statement that names the table and view, respectively and includes information regarding a relationship between at least two entities of the database.