Database Natural to Surrogate Key Translation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
The challenge lies in efficiently mapping natural keys to surrogate keys in a database system, particularly when multiple natural keys need to map to a single surrogate key, while maintaining data integrity and transparency for external clients accessing the database.
Innovation Solution
The database employs a natural-to-surrogate key mapping technique, using mapping rules and a compiler to translate natural keys to surrogate keys, allowing external entities to access data using natural keys while the database processes requests using surrogate keys, thereby preserving data relationships and historical values.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If natural keys are used directly in the database, then data accessibility and understanding for external clients is improved, but data integrity and relationship maintenance deteriorate
Solution Approach 1:
The patent introduces surrogate keys as intermediary elements between natural keys and internal data storage. External clients continue to use meaningful natural keys for data access, while the database internally uses surrogate keys to maintain relationships and ensure integrity. This intermediary layer resolves the contradiction by allowing both natural key accessibility and surrogate key reliability to coexist.
Solution Approach 2:
The patent segments the key management function into two distinct parts: natural keys for external client interaction and surrogate keys for internal database operations. This segmentation allows each key type to serve its specific purpose optimally - natural keys provide meaning and accessibility, while surrogate keys provide stability and relationship maintenance.
2Adaptability or versatility
If multiple natural keys map to a single surrogate key, then data consolidation and historical value preservation are improved, but mapping complexity and processing overhead increase
Solution Approach 1:
The patent implements preliminary action by pre-establishing mapping relationships between natural keys and surrogate keys in mapping tables before data operations occur. The compiler transforms natural key references to surrogate key references in advance, eliminating the need for complex runtime resolution and reducing processing overhead during actual data operations.
Solution Approach 2:
The patent creates copy structures in the form of mapping tables that store the relationship between natural keys and surrogate keys. Instead of computing complex mappings during data operations, the system uses these pre-created copies of the mapping relationship to quickly resolve key translations, reducing processing complexity.
3Ease of operation
If natural key to surrogate key translation is implemented, then transparency for external clients is maintained, but processing time and computational resources increase
Solution Approach 1:
The compiler performs preliminary translation of natural key references to surrogate key references during the query planning phase, before actual data retrieval occurs. This advance translation eliminates the need for runtime key resolution, maintaining client transparency while minimizing processing time during actual data operations.
Solution Approach 2:
The system creates and uses copy structures (mapping tables) that store natural key to surrogate key relationships. These copies allow for rapid key translation without complex computational overhead during data operations, maintaining both transparency and performance.
Data Source
AI summary
A technique includes, in response to an access to a database involving access to a table and specifying a natural key, using the database to translate the natural key to a surrogate key based at least in part on a mapping.