EAV database design for historical data

introduction

The design philosophy of the EAV (Entity-Attribute-Value) database is to store data as a set of attribute-value pairs associated with an entity. This model has flexibility in data storage and querying, especially when processing historical data. However, traditional EAV designs often face challenges in data integrity, report generation, and performance.

Solve common EAV defects

To overcome these shortcomings, it is possible to consider separating different entity attributes according to the attribute type. This method has the following advantages:

• Efficient indexing: You can index values ​​with specific attribute types (for example, decimal, string).
• Entity Relationship: You can maintain BelongsTo, Has, HasMany, and HasManyThrough relationships in the pattern.
• Data Integrity: Strong data types and foreign keys enforce data integrity and consistency.

Example Relational Database Management System (RDBMS) Schema Design

The following RDBMS pattern design demonstrates how to implement this approach:

entity_type: Storage entity type (for example, "Product", "User") entity: represents the main entity table attr: Associate attributes with entities option: Options for storing attribute-value pairs attr_option: Link options to attribute-value pairs attr_int: Stores integer attribute values attr_relation: Stores the relationship with other entities attr_datetime: Store date and time attribute values attr_string: Store string attribute values attr_text: Stores text attribute values attr_decimal: Stores decimal attribute values ​​

This mode allows the storage of historical data by inserting a new attribute-value pair every time the attribute value changes.

Advantages of the proposed method

• Historical Data Tracking: Easy to store and retrieve historical data through timestamp attribute-value pairs.
• Data Integrity: Enforce data integrity through strong types and relationships.
• Query efficiency: Indexed attributes and well-defined data types improve query performance.

Potential Issues

• Multiple queries: A separate query is required to access the attribute-value pair, especially for multi-value attributes.
• Complexity: This pattern can be complicated by the numerous tables representing different attribute types and their relationships.
• Data Migration: Migrating from an existing EAV database to this suggested schema may require a large amount of data conversion.