我应该使用EAV数据库设计模型还是使用很多表格 [英] Should I use EAV database design model or a lot of tables

问题描述

我启动了一个新应用程序，现在我正在研究两条路径，但不知道哪一种是继续的好方法.
我正在建立类似电子商务网站的网站.我有一个类别和子类别.
问题在于站点上的产品类型不同，每种都有不同的属性.并且网站必须可以通过这些产品属性可过滤.
这是我最初的数据库设计:

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}

现在，经过一些分析，我发现该设计实际上是 EAV 模型，并且我读到人们通常不推荐这种设计.
似乎所有内容都需要动态sql查询.

那是一种方式，我现在正在研究它.

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.

我看到的另一种方式可能是很多工作方式，但如果更好，我想去那里.制作表格

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).

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

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很少会赢.在您的情况下，鉴于不同类别具有不同的属性，否则我将很难理解EAV的魅力.但是，假设有人要搜索使用SATA接口以10k rpm旋转的所有具有3个以上磁盘的所有硬盘驱动器?"您在EAV中的查询将很痛苦.如果您想支持这样的查询，那么EAV就不可用了.

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.

但是，还有其他方法.您可以考虑使用具有扩展数据的XML字段，或者，如果您使用的是PostgreSQL 9.2，则可以考虑使用JSON字段(尽管XML更易于搜索).这将为您提供更大范围的可能的搜索，而不会出现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.

这篇关于我应该使用EAV数据库设计模型还是使用很多表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！