Performance Data Storage Schema Normalization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
The existing schema for storing performance data in network devices results in large amounts of data, leading to performance degradation, increased storage costs, and compromised data integrity due to the generation of numerous rows and columns, which affects query response times and overall system performance.
Innovation Solution
A second schema is created that defines columns corresponding to common primitive types and includes rows for identified object descriptors, reducing the number of rows and columns, and normalization techniques are applied to further reduce the data footprint, along with the creation of an index to enhance query performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If a detailed schema with multiple columns (time stamp, device key, device address, interface, counter type, counter value) is used to store performance data, then data completeness and query flexibility are improved, but data volume increases exponentially leading to performance degradation and increased storage costs
Solution Approach 1:
The patent segments the flat table schema into multiple relational tables including device tables, interface tables, counter type tables, and performance data tables. This segmentation allows each table to store only essential information, eliminating redundant data across rows and columns while maintaining comprehensive query capabilities through relational joins.
Solution Approach 2:
The patent transforms the two-dimensional flat table structure into a multi-dimensional relational database structure with multiple tables connected through foreign keys. This dimensional transformation organizes data hierarchically, reducing the exponential growth of rows and columns while preserving data accessibility from multiple query perspectives.
2Measurement precision
If performance data is collected and stored at high frequency (e.g., every minute for multiple devices, interfaces, and counter types), then data granularity and monitoring precision are improved, but query response times increase and system performance degrades
Solution Approach 1:
By segmenting data into specialized tables (devices, interfaces, counter types, performance readings), the patent enables selective querying of specific data subsets. Users can query only relevant device types, interface categories, or time ranges without scanning the entire dataset, significantly reducing query response time while preserving fine-grained data precision.
Solution Approach 2:
The patent applies local quality by creating specialized indexes on frequently queried columns in each table (device keys, interface identifiers, time stamps). This optimization allows rapid access to specific data regions without processing the entire database, maintaining measurement precision while reducing query time.
3Quantity of substance
If large amounts of performance data are stored in a flat table schema, then data retention capacity is improved, but storage costs increase and data integrity may be compromised
Solution Approach 1:
The patent segments data into normalized tables with defined primary and foreign key relationships, enforcing referential integrity constraints. This segmentation reduces data redundancy and eliminates anomalies that can compromise integrity in flat tables, while maintaining the ability to retain large volumes of historical performance data across multiple devices and time periods.
Solution Approach 2:
The patent introduces intermediary tables (device tables, interface tables, counter type tables) that act as mediators between raw performance data and storage systems. These intermediary tables enforce data validation rules, maintain consistent data formats, and ensure referential integrity, thereby protecting data integrity while enabling scalable data retention.
Data Source
AI summary
Performance data generated according to a first schema is read. From the first schema, object descriptors having common primitive types are identified. A second schema is then created. The second schema defines a plurality of rows and at least one column. The rows include a record corresponding to an identified object descriptor. The at least one column corresponds to a primitive type in common with the identified object descriptors.