Entity–Attribute–Value Model (EAV)

The Entity–Attribute–Value (EAV) model is a flexible database design pattern used to store complex, dynamic, or sparse data. Instead of using a fixed set of columns, EAV structures data into three parts:

• Entity: The subject or object being described (e.g., a product, user, or medical record).

• Attribute: A characteristic or property of the entity (e.g., "color," "age," or "temperature").

• Value: The specific data assigned to the attribute (e.g., "red," "30," or "98.6°F").

This model is commonly used in applications where attributes vary significantly across records, such as electronic health records (EHRs), product catalogs, and configuration management systems.

Also known as: Open schema, Name-Value-Pair Model

Comparisons

• EAV vs. Relational Database Schema: Traditional relational models use fixed columns for attributes, while EAV stores attributes as rows, making it more flexible but often more complex to query.

• EAV vs. NoSQL: NoSQL databases like MongoDB also handle dynamic attributes but often in a document-based format rather than a key-value structure.

Pros

• Handles dynamic and sparse data efficiently.

• Allows schema flexibility without altering the database structure.

• Useful for applications where attributes are unpredictable or frequently change.

Cons

• More complex queries due to data being stored in rows instead of columns.

• Performance can be slower than traditional relational designs for large datasets.

• Can make data validation and indexing more challenging.

Example

Imagine an e-commerce platform that sells various types of products, such as laptops, books, and furniture. Each product category has unique attributes, making a fixed-column database structure inefficient. Instead of having a separate column for "RAM" (which only applies to laptops) or "Author" (which only applies to books), the EAV model stores data like this:

• A laptop may have attributes like RAM: 16GB, Processor: Intel i7, and Storage: 512GB SSD.

• A book might have attributes such as Author: Jane Doe, Genre: Science Fiction, and Pages: 350.

• A table could have attributes like Material: Oak Wood, Dimensions: 60x40 inches, and Weight: 15kg.

By using an EAV model, the platform can dynamically handle various product attributes without requiring a separate column for each possible feature.