Denormalized Object Permissions Database with Streaming Queries
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing relational database systems struggle to efficiently manage large numbers of user-object permissions due to the many-to-many relationship, leading to impractical or impossible data outputs when querying permissions from users or user groups, especially in systems with billions of objects and hundreds of thousands of users.
Innovation Solution
Implementing a denormalized database structure that combines user and group tables with a large object table, utilizing streaming technology to efficiently query and visualize permissions by caching small tables in memory and streaming results, thereby avoiding the generation of large data sets.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If a relational database structure is used to store permissions, then efficient querying by object is achieved, but querying by user becomes computationally expensive and impractical
Solution Approach 1:
The patent segments the permission data model into multiple denormalized tables (user_permissions, object_permissions, user_object_pairs) that can be queried from different entry points. This segmentation allows efficient querying both by object and by user without requiring complex joins across the entire permission dataset.
Solution Approach 2:
The patent adds a new dimension to the data structure by creating user_object_pairs table that directly links users and objects without requiring traversal through the traditional object-centric permission hierarchy. This dimensional change enables efficient user-based queries by providing a direct access path.
2Reliability
If permission data is stored in a normalized relational database, then data integrity is maintained, but the data set size becomes unmanageably large when querying user-object pairs
Solution Approach 1:
The patent extracts the user-object permission relationships into a separate denormalized table (user_object_pairs) that contains only the essential pairing information. This extraction reduces the data set size for user-based queries while maintaining referential integrity through foreign key constraints.
Solution Approach 2:
The patent creates denormalized copies of permission data in multiple tables (user_permissions, object_permissions, user_object_pairs) that can be queried efficiently from different perspectives. These copies maintain data integrity through consistent foreign key relationships while enabling fast querying without traversing the entire normalized dataset.
3Use of energy by moving object
If streaming technology is implemented to reduce memory usage, then system resource efficiency improves, but implementation complexity increases
Solution Approach 1:
The patent implements streaming technology that processes and yields permission data in small batches as they are generated, rather than loading entire data sets into memory. This preliminary action of streaming results incrementally reduces memory requirements while maintaining implementation feasibility through standard database streaming capabilities.
Data Source
AI summary
A denormalised database structure and access methodology to allow efficient querying of permissions data and visualisation of permissions data.