产品属性的数据库架构 [英] database schema for products attributes

问题描述

我想按类别实施产品过滤，并且我对正确的数据库架构有疑问. 现在，我有以下表格:

I want to implement products filtering in category and I have questions about the right DB schema. For now I have the following tables:

类别:

1. id
2. category
3. description

产品:

1. id
2. category_id
3. product
4. image
5. price

属性:

1. id
2. attribute

类别属性:

1. category_id
2. attribute_id

我的问题是我应该创建哪些表，哪些列要求它们存储不同类型的值，属性值，产品属性值等.

And the question I have is what tables should I create and what columns shout they have to store different kinds of values, attribute values, products attribute values etc.

再创建3个表是否正常?

Would it be normal to create 3 more tables:

值:

1. id
2. value

Attributes_Values :

1. attribute_id
2. value_id

Products_Attributes_Values :

1. product_id
2. attribute_id
3. value_id

我已经把最后一张桌子搞砸了.什么会更好地存储和过滤?

I've messed up in last tables. What would be better to store and filter?

推荐答案

您要实现的是实体属性值(EAV)或可能的行建模解决方案.请注意，这种结构由于种种种种很好的理由而被广泛反对.

What you are trying to achieve is an Entity-Attribute-Value (EAV) or possibly a row modeling solution. Note that this type of structure is largely frowned upon for a wide variety of pretty good reasons.

但是，我争论过(例如 ，此处和

However, I have argued (e.g. here, here, here, and here) that EAV is EVIL, except when it isn't. One of those rare exceptions is in the case of a product catalog where you are tracking the properties of products and where those properties aren't that interesting (to your system!) except insofar as you need to retrieve these and print them out on a product web page or a comparison grid, etc.

考虑这样的设计:

在这样的模型中，您正在做的事情是描述给定类别的产品应具有的属性，这些属性可能具有的值以及每个特定产品对每个属性具有的值.

What you are doing in a model like this is describing which attributes products in a given category ought to have, what values those attributes might have, and then which values each specific product does have for each attribute.

此设计确实具有EAV施加的所有常规限制.但是，如果您要问类似的问题:哪个珠子的直径为8毫米?"这很简单.

This design does have all of the usual limitations that EAV imposes. However, if you want to ask questions like: "Which beads have a diameter of 8mm?" that is pretty straight-forward.

这篇关于产品属性的数据库架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！