Lecture
Relational databases remain the foundation of information systems thanks to their rigorous logic, transaction support, and versatility. Within the relational model, however, there are several different approaches to organizing data storage that allow the structure to be adapted to specific tasks.
The classic normalized model ensures integrity and minimizes duplication, but can be complex for high-load systems. In contrast, the denormalized model sacrifices strict structure for faster access. For flexible scenarios where the number of attributes is not known in advance, the EAV model (Entity–Attribute–Value) is used. In cases where semi-structured data needs to be stored, relational DBMSs support JSON/XML/LOB columns, combining the advantages of SQL and NoSQL. There are also approaches for specific tasks: wide tables for a fixed but diverse set of attributes, pivot models for analytics, and hierarchical structures for representing trees and categories.
Within relational databases (SQL), there are several ways to organize data storage besides EAV. They differ in table structure and modeling approach:
Principle: data is broken down into related tables (1NF, 2NF, 3NF and higher).
Features: minimal duplication, strict structure, convenient for transactions.
Example: a Customers table and a separate Orders table, linked by a key.
Principle: part of the data is stored in a single table to speed up queries.
Features: more duplication, but fewer JOIN operations.
Example: an orders table that directly contains the customer's name and address.
Principle: a table is created with a very large number of columns, some of which may be empty.
Features: convenient for a fixed but diverse set of attributes.
Example: a table with hundreds of columns for different product characteristics.
Principle: data is stored in columns of type JSON, XML, or BLOB.
Features: flexibility similar to NoSQL, but within a relational DBMS.
Example: PostgreSQL JSONB, Oracle XMLType.
Principle: similar to EAV, but values are grouped by columns rather than by rows.
Features: used for analytics, when data needs to be dynamically pivoted.
Example: a table where rows are turned into dynamic columns via PIVOT.
Principle: storing trees and hierarchies in a relational table.
Features: convenient for categories, menus, organizational structures.
Example: a Categories table with a Parent_ID column.
Ways of storing hierarchical structures in relational databases:
Materialized Path (also known as Path Enumeration, materialized path pattern) — when a node stores a string with the full path from the root to it (in your case this is path_keys: region:eu|difficulty:normal|...). Russian-speaking teams often simply call it "materialized path" / "storing the path in a field".
Nested Sets — this is lft/rgt.
Adjacency List — this is parent_id.
Closure Table — a separate table of all ancestor-descendant relationships.
Here is a comparison table of the main methods of storing data within relational databases:
| Storage method | Principle | Advantages | Disadvantages | Application |
|---|---|---|---|---|
| Normalized model | Data is broken down into related tables (1NF–3NF+) | Minimal duplication, strict structure, transaction support | Many JOIN operations, query complexity | Classic business applications, accounting, CRM |
| Denormalized model | Data duplication to speed up access | Fast queries, fewer JOINs | Redundancy, risk of inconsistency | Analytics, reporting, high-load systems |
| EAV (Entity–Attribute–Value) | Attributes and values are stored row by row | Flexibility, easy to add new properties | Complex queries, weak typing | Medical systems, product catalogs |
| Wide Tables | A table with a large number of columns | Simple structure, fast access to fixed data | Many empty values, difficulty extending | Systems with a fixed but diverse set of attributes |
| JSON/XML/LOB storage | Data is stored in columns of JSON, XML, BLOB format | NoSQL-like flexibility, storage of complex structures | Limited optimization, indexing complexity | Configurations, storage of semi-structured data |
| Pivot model | Data is pivoted into dynamic columns | Convenient for analytics, flexible aggregation | Implementation complexity, server load | BI systems, reporting |
| Hierarchical storage (Adjacency List, Nested Sets) | Storage of trees and hierarchies | Convenient for categories and structures | Complex updates when the structure changes | Product categories, organizational structures, menus |
Thus, relational databases provide not a single universal storage method, but a whole set of methods, each of which is optimal under certain conditions. Combining them wisely makes it possible to build systems that are simultaneously robust, flexible, and performant. For example, EAV is just one of the approaches, alongside normalization, denormalization, JSON/XML storage, wide tables, and special models for hierarchies.
Comments