Adopting the Entity-Attribute-Value Model

In this lesson, you will learn how to implement a flexible, schema-less data structure within your relational database using the Entity-Attribute-Value (EAV) model.

Breaking the Rigid Schema

Relational databases typically thrive on structured tables where every row shares the same columns. However, when working with embedded or lightweight databases in mobile applications, you often encounter "sparse" data—scenarios where an object (like a user profile or a product) might have a unique set of attributes that aren't shared by others. Instead of adding dozens of nullable columns to your main table, which leads to database bloat, you can adopt the EAV model.

How EAV Works

The EAV model breaks down data into three distinct parts:

1. Entity: The object being described (e.g., a product_id).
2. Attribute: The name of the property (e.g., "color", "weight", "warranty").
3. Value: The actual data stored for that attribute.

By using this approach, you create a "vertical" table structure instead of a horizontal one.

Implementation Example

Consider a product_attributes table designed to hold arbitrary data for any product:

CREATE TABLE product_attributes (
    entity_id INTEGER,
    attribute_name TEXT,
    value TEXT
);

-- Storing dynamic data without altering the schema
INSERT INTO product_attributes (entity_id, attribute_name, value) 
VALUES (101, 'material', 'aluminum'), 
       (101, 'water_resistant', 'true');

Why Use It?

The power of EAV lies in its agility. You can add new properties to your entities without running ALTER TABLE commands, which can be expensive or risky in a constrained mobile environment. This is particularly useful for user-defined fields or evolving configuration settings that don't need to be strictly typed by the database engine.

However, keep in mind that querying EAV data requires more complex joins or pivoting logic to reconstruct a full object. Because of this, it is best reserved for data that is either highly dynamic or too sparse to justify dedicated columns. By keeping your schema lightweight, you maintain better performance and avoid the overhead associated with frequent schema migrations on local devices.

Key Takeaway: The EAV model allows you to store flexible, schema-less data in relational tables by representing properties as rows rather than columns, providing agility at the cost of query complexity.