sql - 为历史数据正确设计 EAV 数据库

Question

简介

我一直在阅读 EAV database大多数缺点似乎是与非常非常糟糕的 EAV 设计或困难相关 generating reports from the data .

通常当您看到人们提示 EAV 时，他们使用少于三个表来尝试复制 RDBMS 中单独表 + 列的功能。有时这意味着将所有内容从小数到字符串存储在单个 TEXT 值列中。 EAV 还会破坏数据完整性的安全防护，如果您不小心，这可能会非常糟糕。

但是，EAV 确实提供了一种简单的方法来跟踪历史数据，并允许我们在 SQL 和键值存储系统之间来回移动系统的各个部分。

如果我们根据类型分离不同的实体属性会怎么样。这将使我们仍然可以处理belongsTo、Has、HasMany 和 HasManyThrough 关系，以及与特定属性和实体关联的正确索引值。

考虑以下两个基本实体

products (price -> decimal, title -> string, desc -> text, etc...)
    attributes
        options
            [...]
        int
        datetime
        string
        text
        decimal
        relation
            [id,foreign_key]

users (gender -> options, age -> int, username -> string, etc...)
    attributes
        options
            [...]
        int
        datetime
        string
        text
        decimal
        relation
            [id,foreign_key]

RDBMS 模式设计

众所周知，用户资料和产品是世界上最多样化的项目之一。每个公司以不同的方式处理它们，并根据他们的需要使用不同的“列”或“属性”。

以下是如何处理多个(嵌套和/或关系)实体的 View 。

想法是每个实体都有这个主属性表，然后指定如何查找和解释这些值。这使我们能够处理特殊情况，例如其他实体的外键以及“选项”或十进制数字等。

实体类型{ ID， 类型，//即“博客”、“用户”、“产品”等。 创建时间

entity {
    id,
    entity_type_id, 
    created_at
}

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

        option {
            id,
            attr_id,
            entity_id,
            multiple, // multiple values allowed?
            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
        }

这样的表将允许我们永远不必 UPDATE ... 因为我们可以为每个更改值并添加的新属性 INSERT INTO ... created_at 以了解最新值是什么。这非常适合保存历史数据记录(当然仍然可以异常(exception))。

示例查询

首先，它是什么“类型”的实体？ (用户、帖子、评论等。)

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

接下来，这个实体的属性是什么？ (表属性)

SELECT * FROM attr WHERE entity_id = ?

接下来，该实体的属性中存在哪些值？ (attr_### 表)

SELECT * FROM attr_option, attr_int, attr_relation, attr_text, ... WHERE entity_id = ?
vs
SELECT * FROM attr_option WHERE entity_id = ? if( ! multiple) ORDER BY created_at DESC LIMIT 1
SELECT * FROM attr_int WHERE entity_id = ? ORDER BY created_at DESC LIMIT 1
SELECT * FROM attr_relation WHERE entity_id = ? ORDER BY created_at DESC LIMIT 1
SELECT * FROM attr_text WHERE entity_id = ? ORDER BY created_at DESC LIMIT 1
...

这个实体存在什么关系？

假设我们有一个 ID 为 34 的“帖子”实体，并且我们想要它的“评论”(entity_type = 2)，这可以让我们获取产品实体的评论实体 ID:

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;

除了多个查询(键值存储无论如何都需要)，这种方法会存在什么问题？

Answer 1

EAV“数据库”[原文如此]是字面上 数学上 直接数据库及其元数据三元组的无证描述，没有列出关系、查询关系、查询元数据、类型检查、维护完整性、优化、原子处理或控制并发的功能。

软件工程原则规定，合理的 EAV 数据库 [sic] 使用完全包括定义适当的抽象(类型、运算符、过程、解释器、模块)重构 DBMS 的功能。

从一个人的 EAV 三元组及其含义到(碎片化的)数据库描述的映射的机械性质使得这一点很容易显示。

释义Greenspun ，任何足够复杂的 EAV 项目都包含一个临时的、非正式指定的、错误缠身的、缓慢的 DBMS 一半的实现。

我再说一遍:EAV 是对数据库及其元数据的三元组的未记录描述，没有 DBMS。仅对您已证明 DDL 解决方案无法满足性能要求而 EAV 解决方案可以而且值得的数据库部分使用 EAV。