我应该使用EAV数据库设计模型还是很多表

I started a new application and now I am looking at two paths and don't know which is good way to continue.
I am building something like eCommerce site. I have a categories and subcategories.
The problem is that there are different type of products on site and each has different properties. And site must be filterable by those product properties.
This is my initial database design:

我开始了一个新的应用程序，现在我正在寻找两条路径，不知道哪种方法可以继续。我正在构建类似电子商务网站的东西。我有一个类别和子类别。问题是现场有不同类型的产品，每个产品都有不同的属性。并且网站必须可以通过这些产品属性进行过滤。这是我最初的数据库设计：

Products{ProductId, Name, ProductCategoryId}
ProductCategories{ProductCategoryId, Name, ParentId}
CategoryProperties{CategoryPropertyId, ProductCategoryId, Name}
ProductPropertyValues{ProductId, CategoryPropertyId, Value}

Now after some analysis I see that this design is actually EAV model and I read that people usually don't recommend this design.
It seems that dynamic sql queries are required for everything.

That's one way and I am looking at it right now.

现在经过一些分析，我发现这个设计实际上是EAV模型，我读到人们通常不推荐这种设计。似乎所有东西都需要动态的SQL查询。这是一种方式，我现在正在看它。

Another way that I see is probably named a LOT WORK WAY but if it's better I want to go there. To make table

我看到的另一种方式可能被称为很多工作方式，但如果它更好，我想去那里。制作桌子

Product{ProductId, CategoryId, Name, ManufacturerId}

and to make table inheritance in database wich means to make tables like

并在数据库中创建表继承，这意味着使表格像

Cpus{ProductId ....}
HardDisks{ProductId ....}
MotherBoards{ProductId ....}
erc. for each product (1 to 1 relation).

I understand that this will be a very large database and very large application domain but is it better, easier and performance better than the option one with EAV design.

我知道这将是一个非常大的数据库和非常大的应用程序域，但它比EAV设计的选项更好，更容易，性能更好。

3 个解决方案

#1

EAV is rarely a win. In your case I can see the appeal of EAV given that different categories will have different attributes and this will be hard to manage otherwise. However, suppose someone wants to search for "all hard drives with more than 3 platters, using a SATA interface, spinning at 10k rpm?" Your query in EAV will be painful. If you ever want to support a query like that, EAV is out.

EAV很少是一场胜利。在你的情况下，我可以看到EAV的吸引力，因为不同的类别将具有不同的属性，否则将很难管理。但是，假设有人想要搜索“所有硬盘超过3个盘片，使用SATA接口，以10k rpm旋转？”您在EAV中的查询将是痛苦的。如果你想支持这样的查询，EAV就会出局。

There are other approaches however. You could consider an XML field with extended data or, if you are on PostgreSQL 9.2, a JSON field (XML is easier to search though). This would give you a significantly larger range of possible searches without the headaches of EAV. The tradeoff would be that schema enforcement would be harder.

然而，还有其他方法。您可以考虑使用扩展数据的XML字段，或者如果您使用PostgreSQL 9.2，则可以考虑使用JSON字段（尽管XML更容易搜索）。这将为您提供更大范围的可能搜索，而不会出现EAV的麻烦。权衡将是模式执行会更难。

#2

This questions seems to discuss the issue in greater detail.

这些问题似乎更详细地讨论了这个问题。

Apart from performance, extensibility and complexity discussed there, also take into account:

除了在那里讨论的性能，可扩展性和复杂性之外，还考虑到：

SQL databases such as SQL Server have full-text search features; so if you have a single field describing the product - full text search will index it and will be able to provide advanced semantic searches

SQL Server等SQL数据库具有全文搜索功能;因此，如果您有一个描述产品的字段 - 全文搜索将对其进行索引，并且能够提供高级语义搜索
take a look at no-sql systems that are all the rage right now; scalability should be quite good with them and they provide support for non-structured data such as the one you have. Hadoop and Casandra are good starting points.

看看现在风靡一时的无sql系统;可扩展性应该与它们相当好，并且它们为非结构化数据提供支持，例如您拥有的数据。 Hadoop和Casandra是很好的起点。

#3

You could very well work with the EAV model. We do something similar with a Logistics application. It is built on .net though. Apart from the tables, your application code has to handle the objects correctly. See if you can add generic table for each object. It works for us.

你可以很好地使用EAV模型。我们使用物流应用程序执行类似操作。它建立在.net上。除了表之外，您的应用程序代码必须正确处理对象。看看是否可以为每个对象添加通用表。它适用于我们。

#1

#2