什么是这种情况下最佳的数据库结构？

Question

我有一个保存着房地产MLS（多重上市服务）数据的数据库。目前，我有一张单表存储所有房源属性信息（价格、地址、面积等）。有几种不同的物业类型（住宅、商业、租赁、收益、土地等），每种物业类型共享大部分属性，但也有一些是特定于该物业类型的。 我的问题是，共享的属性超过了250个字段，这似乎在单个表中太多了。我的想法是，我可以将它们拆分成EAV（实体-属性-值）格式，但我读到了许多关于EAV的负面评价，而且任何一个250个字段中的字段都可能被搜索，这将使查询变得非常麻烦。如果我要选择这条路，我必须从EAV表中提取出所有数据，按列表ID进行分组，在应用程序端合并，在内存对象集合上运行我的查询。这也不太有效率。 我正在寻找一些建议或推荐，以便决定如何继续。也许使用250+字段表是唯一的方法。 作为一个说明，我正在使用SQL Server 2012，.NET 4.5 w/ Entity Framework 5，C#，数据通过WCF服务传递给asp.net Web应用程序。 谢谢。

- Ricketts

---

抱歉，我是指EAV（实体-属性-值），我会更新我的问题。 - Ricketts

重新标记 - 问题与C#无关 - .NET语言通用。重要的考虑因素是SQL Server优化和Entity Framework优化。此外，ASP.NET WCF并不那么相关，因为您不必在EF和WCF中使用相同的实体（如果需要，可以使用映射）。 - Danny Varod

Answer 1

让我们来考虑一下备选方案的优缺点： 一个表格包含所有列表和属性： 1.非常宽的表格-难以查看模型和架构定义以及表格数据。 2.只需一个查询而不需要连接即可检索有关列表的所有数据。 3.每个新属性都需要进行模式+模型更改。 4.如果您始终加载所有属性并且大多数项目具有大多数属性的值，则效率很高。 5.根据属性的LINQ查询示例：

context.Listings.Where(l => l.PricePerMonthInUsd < 10e3 && l.SquareMeters >= 200)
    .ToList();

所有列表共用一个表，属性类型另有一张表，值（包括清单ID、属性ID）存储在另一张表中（EAV结构）：

1. 清单表格狭窄
2. 数据非常稀疏的情况下效率很高（大多数项目没有大部分属性的值）
3. 需要从值中检索所有数据——一个附加查询（或一个联接），但会浪费带宽——每个属性值行都会获取基本清单表格数据
4. 不需要为新属性更改模式+模型
5. 如果你想通过代码以类型安全的方式访问属性，你需要根据属性类型表进行自定义代码生成
6. 按照属性进行的示例LINQ查询：

var listingIds = context.AttributeValues.Where(v =>
                    v.AttributeTypeId == PricePerMonthInUsdId && v < 10e3)
                .Select(v => v.ListingId)
                .Intersection(context.AttributeVales.Where(v =>
                    v.AttributeTypeId == SquareMetersId && v.Value >= 200)
                .Select(v => v.ListingId)).ToList();

或：（在实际数据库上比较性能）

var listingIds = context.AttributeValues.Where(v =>
                    v.AttributeTypeId == PricePerMonthInUsdId && v < 10e3)
                .Select(v => v.ListingId).ToList();

listingIds = context.AttributeVales.Where(v =>
                listingIds.Contains(v.LisingId)
                && v.AttributeTypeId == SquareMetersId
                && v.Value >= 200)
            .Select(v => v.ListingId).ToList();

然后：

var listings = context.Listings.Where(l => listingIds.Contains(l.ListingId)).ToList();

妥协方案 - 一个包含所有列表和一个每个属性组（假设您可以将属性分为组）的值的表：

1. 多个中等宽度的表
2. 如果每组数据稀疏，则效率高（例如，与花园无关的属性对于没有花园的列表都为空，因此您不需要为它们在花园相关表中添加行）
3. 需要一个查询和多个连接（连接不浪费带宽，因为组表与列表表是1：0..1而不是1：many）
4. 需要模式+模型更改以适应新属性
5. 查看模式/模型更简单 - 如果您可以将属性分为10组，则会获得25个具有11列而不是另外250个列的表
6. LINQ查询介于上述两个示例之间。

根据您的特定统计数据（有关稀疏性），需求/可维护计划（例如，属性类型添加/更改频率如何？），请考虑利弊并作出决定。

- Danny Varod

---

我可以告诉你，许多字段将为空，因为输入列表的代理商将它们留空。并非所有查询都会提取所有字段。用户可以控制提取哪些字段。一个用户提取的字段将与另一个用户提取的字段不同。此外，由于数据直接来自MLS，他们经常更改、添加和删除字段（我无法控制）。话虽如此，如果是你，你会选择上述哪种方法？我认为EAV方法是可行的，但我读到的负面信息让我有所顾虑。 - Ricketts

@Ricketts EAV方法非常灵活，我已经看到它在工作中的应用。由于大多数情况下都是空白的（除非你强制代理填写它们），我认为EAV方法更加适合。 - Danny Varod

我建议每种数据类型使用一个值列，而不是所有的都使用 nvarchar 或者变体列（EF 不支持）。如果您需要了解如何在 EF 中实现这一点，请提出相关问题，我会在明天发布答案（将链接留在评论中）。 - Danny Varod

Answer 2

可能的操作如下： 首先，我会为这250个字段创建一个表格，在其中包含ID和FieldName，例如：

price   -> 1
address -> 2
sqft    -> 3

这个表格也将作为枚举类型硬编码到我的代码中，并用于查询。

然后在主表中，我有两个字段一起使用，一个是从上面的表格中获取的字段ID的类型，另一个是它的值，例如

Line1: 122(map id), 1 (for price), 100 (the actually price)
Line2: 122(map id), 2 (for address), "where is it" 
Line3: 122(map id), 3 (for sqft), 10 (sqft)

这里的问题是你可能需要至少两个字段，一个用于数字，一个用于字符串。

当然，这只是一个建议。

- Aristos

---

这是问题中提到的EAV模式。 - Danny Varod

因此，您将采用EAV样式的路线。假设用户需要价格在100k至200k之间，3个以上卧室，2000平方英尺以上以及在邮编12345中的列表。在数据库端运行该查询将非常困难。因此，我需要提取所有记录，在内存对象中按列表ID进行分组，然后在该对象集合上运行查询。这对性能会产生什么影响，或者甚至是否可以处理数千甚至数十万条记录？ - Ricketts

问题不在于编写这样的查询。没有现代DBMS会在正确索引时运行多百万行表上的查询出现问题。EAV（如果像这样完成）的困难之处在于对“Value”列进行索引。该列将具有来自非常不同数据类型的数据，如char、数字、日期等。因此，它可能必须是VARCHAR数据类型。这意味着，您不能轻松地为仅限于（数字）价格或日期等制作索引。 - ypercubeᵀᴹ

@Ricketts，您对查询构建的假设是错误的。我将编辑我的问题以解释如何根据属性进行查询。 - Danny Varod

@yercube - 只有在每种数据类型只有一个值列的情况下才是正确的。 - Danny Varod

@Danny：好的，我对Aristos提出的设计进行了评论。 - ypercubeᵀᴹ

Answer 3

我会创建一个只包含共享属性的“listing”表。该表将以“listingId”作为主键。 它将有一列用于存储列表类型，这样您就知道它是住宅列表，着陆列表等等。 然后，对于每个子类型，创建一个额外的表。 因此，您将拥有“residential_listing”，“land_listing”等表。 所有这些表的主键也将是“listingId”。 此列还是指向“listing”的外键。 当您希望操作共享数据时，可以完全使用“listing”表来完成此操作。 当您对特定数据感兴趣时，将加入到特定表中。 如果所有数据都在那里，则某些查询可能能够完全在特定表上运行。

- WW.

---

使用组合而非继承（相同的架构，到实体的不同映射）可能更好，因为列表可以同时具有几种类型的属性。无论如何，这与我呈现的第三个选项相同。 - Danny Varod

@DannyVarod 是的 - 取决于哪些事物具有哪些属性。 - WW.