How Can We Design an EAV Database for Efficient Historical Data Management with Attribute Differentiation?

An EAV database designed to handle historical data with attribute differences

Introduction

The Entity-Attribute-Value (EAV) database has been criticized for its limitations, particularly inefficient design and reporting challenges. By separating entity attributes based on type, these disadvantages can be overcome while maintaining the advantages of EAV tracking historical data.

RDBMS pattern design

The proposed schema introduces a master attribute table that categorizes the attributes of each entity type. This allows processing of a variety of attribute types, including options, integers, dates, strings, text, and decimals.

<code>entity_type {
    id,
    type,       // 例如，“博客”、“用户”、“产品”等
    created_at
}

entity {
    id,
    entity_type_id, 
    created_at
}

    attr {
        id,
        entity_id,
        type,
        name,
        created_at
    }

        option {
            id,
            attr_id,
            entity_id,
            multiple, // 允许多个值？
            name,
            created_at
        }

        attr_option {
            id,
            attr_id,
            entity_id,
            option_id,
            option,
            created_at
        }

        attr_int {
            attr_id,
            entity_id,
            int,
            created_at
        }

        attr_relation {
            attr_id,
            entity_id,
            entity_fk_id,
            created_at
        }

        attr_datetime {
            attr_id,
            entity_id,
            datetime,
            created_at
        }

        attr_string {
            attr_id,
            entity_id,
            var_char,
            created_at
        }

        attr_text {
            attr_id,
            entity_id,
            text,
            created_at
        }

        attr_decimal {
            attr_id,
            entity_id,
            decimal,
            created_at
        }</code>

Example query

• Retrieve entity type:

  <code>  SELECT * FROM entity_type et LEFT JOIN entity e ON e.entity_type_id = et.id WHERE e.id = ?</code>

• Get entity attributes:

  <code>  SELECT * FROM attr WHERE entity_id = ?</code>

• Retrieve attribute value:

  <code>  SELECT * FROM attr_option, attr_int, attr_relation, attr_text, ... WHERE entity_id = ?</code>

• Find relationships between entities:

  <code>  SELECT * FROM entity AS e
    LEFT JOIN attr_relation AS ar ON ar.entity_id = e.id
    WHERE ar.entity_id = 34 AND e.entity_type = 2;</code>

Potential issues

While an improvement over traditional EAV design capabilities, there are still some potential issues to consider:

• Multiple queries are still required to retrieve the complete data.
• Maintaining the integrity of relationships and metadata can be challenging.
• Performance optimization requires careful indexing and data organization.

The above is the detailed content of How Can We Design an EAV Database for Efficient Historical Data Management with Attribute Differentiation?. For more information, please follow other related articles on the PHP Chinese website!