c# - 将属性存储为(名称-值对)表？

Question

我要写的应用程序如下:

SQL 表名称:(DepProperties)

+----+-------+----------+------------------------+------------+
| Id | DepId |   Type   |          Name          |   Value    |
+----+-------+----------+------------------------+------------+
|  2 |   123 | String   | DepartmentDescription  | xxxx       |
|  3 |   123 | DateTime | DateCreated            | xx/xx/xxxx |
|  4 |   321 | String   | DepartmentDescription  | xxxx       |
|  5 |   321 | Boolean  | NewerDep               | xx/xx/xxxx |
+----+-------+----------+------------------------+------------+

并通过 [DepId] 获取所有值

select * from DepProperties where DepId = 123

并且，在 VB.Net 中序列化为类

Public Class Department
    Public Property DepartmentDescription As String
    Public Property DateCreated As DateTime
End Class

Public Class ExtendedDepartment
    Inherits Department

    Public Property NewerDep As Boolean
End Class

我尝试使用这种方式。

因为，将来当我向特定部门添加新属性时只是，从父类扩展的新类包含特定属性

而不是在数据库中添加新列。

这个方法好吗，它是怎么做到的？

Answer 1

您提出的建议通常称为 Entity-Attribute-Value设计(或简称 EAV)，正如@DanGuzman 在他的评论中指出的那样，它可能成为性能问题，具体取决于多种因素，包括:

• 表中的行数
• 同时使用该表的并发连接数
• 您的客户端代码是如何编写的；即它是否使用此模式来存储其所有数据，还是仅用于有限数量的属性？

有很多商业系统都是建立在完全像这样的设计之上的，它们确实支持大量的并发、大量的行和精心设计的客户端代码。

摘自关于 EAV¹ 的维基百科文章:

The typical case for using the EAV model is for highly sparse, heterogeneous attributes, such as clinical parameters in the electronic medical record (EMRs), as stated above. Even here, however, it is accurate to state that the EAV modeling principle is applied to a sub-schema of the database rather than for all of its contents. (Patient demographics, for example, are most naturally modeled in one-column-per-attribute, traditional relational structure.)

Consequently, the arguments about EAV vs. "relational" design reflect incomplete understanding of the problem: An EAV design should be employed only for that sub-schema of a database where sparse attributes need to be modeled: even here, they need to be supported by third normal form metadata tables. There are relatively few database-design problems where sparse attributes are encountered: this is why the circumstances where EAV design is applicable are relatively rare. Even where they are encountered, a set of EAV tables is not the only way to address sparse data: an XML-based solution (discussed below) is applicable when the maximum number of attributes per entity is relatively modest, and the total volume of sparse data is also similarly modest. An example of this situation is the problems of capturing variable attributes for different product types.

Sparse attributes may also occur in E-commerce situations where an organization is purchasing or selling a vast and highly diverse set of commodities, with the details of individual categories of commodities being highly variable.

您可能要考虑使用以下表格设计:

CREATE TABLE dbo.DepProperties
(
    DepPropertiesID INT NOT NULL
        CONSTRAINT PK_DepProperties
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , DepId INT NOT NULL
    , PropertyType VARCHAR(50) NOT NULL
    , PropertyName VARCHAR(50) NOT NULL
    , PropertyValueString VARCHAR(247) NULL /* Adjust this number to
                                               allow for the largest
                                               value you want to 
                                               support */
    , PropertyValueInt INT NULL
    , PropertyValueDate DATE NULL
    , PropertyValueDateTime DATETIME NULL
    , PropertyValueDecimal DECIMAL(20, 10) /* Adjust per your needs */
);

请注意，“值”列可以为空，并且每种数据类型都有离散的列。这允许您保持类型安全。

---

^{1 - https://en.wikipedia.org/wiki/Entity-attribute-value_model#Modeling_sparse_attributes}