database-design - 建模稀疏属性的最佳模式

Question

我从事金融服务产品的工作，该产品存储了有关最终客户的大量信息。我们的客户不断希望添加新属性，这些属性通常不会用于驱动我们产品中的任何流程。它们被捕获和显示，但没有别的。由于我们的客户运作方式不同，他们通常想要存储非常不同的值。我们尝试了两种解决方案来适应他们:

具有数百列的稀疏填充表。

客户可以根据需要定义新列的实体属性值表。

我们已经经历了这两种解决方案的大部分缺点。许多列为我们提供了安慰，因为我们知道将哪些数据添加到我们的数据库中，但是当客户“只是”想要存储新值(例如最喜爱的高尔夫俱乐部)时，会使我们显得不灵活和昂贵。 EAV 已经显示出它所有的常见问题:查询性能不佳、失去对数据的控制、缺乏验证和可维护性问题。

那么有没有更好的模式呢？

Answer 1

我在 Percona Live MySQL Conference & Expo 2013 做了一个关于这个主题的演讲。 .我的演讲被称为 Extensible Data Modeling .

对于您的情况，由于您的 SQL 查询中未使用用户定义的属性(仅按您所说的方式捕获和显示)，因此我建议使用 Serialized LOB图案。

这是我的演讲摘要。幻灯片是免费提供的:

Designing an extensible, flexible schema that supports user customization is a common requirement, but it's easy to paint yourself into a corner.

Examples of extensible database requirements:

• A database that allows users to declare new fields on demand.
• Or an e-commerce catalog with many products, each with distinct attributes.
• Or a content management platform that supports extensions for custom data.

The solutions we use to meet these requirements is overly complex and the performance is terrible. How should we find the right balance between schema and schemaless database design?

I'll briefly cover the disadvantages of Entity-Attribute-Value (EAV), a problematic design that's an example of the antipattern called the Inner-Platform Effect, That is, modeling an attribute-management system on top of the RDBMS architecture, which already provides attributes through columns, data types, and constraints.

Then we'll discuss the pros and cons of alternative data modeling patterns, with respect to developer productivity, data integrity, storage efficiency and query performance, and ease of extensibility.

• Class Table Inheritance
• Serialized BLOB
• Inverted Indexing

Finally we'll show tools like pt-online-schema-change and new features of MySQL 5.6 that take the pain out of schema modifications.