具有多个类别和多个子功能的产品的数据库设计 [英] Database design for products with multiple categories and multiple sub-features

问题描述

假设您要为网站存储设计一个可搜索的数据库，并且有以下情况：

---

code>产品将有多个不同的类别。

，考虑一个MARKER作为产品。

例如：同一个MARKER可能有 category 以下所有三种：

• TOY


• SCHOOL -STATIONERY


• ART-ACCESSORY

现在，每个 / code>有一组相应的任何数量的参数（我们称之为 features ）只适用于该类别。

因此，对于其三个产品类别表示中的每一个，上述MARKER将具有多个不同的产品特征名称以及相应的产品特征值。

- 特征名称和产品特定特征值：

$ b b

• PRODUCT-NAME ::: CATEGORY-NAME ::: Feature1名称 = Feature1值等。


• MARKER ::: TOY ::: AgeGroup = 6-12 ，有毒 = =


• MARKER ::: SCHOOL-STATIONERY ::: BulkAvailability = 否


• MARKER ::: ART-ACCESSORY ::: 使用 = < CompatibleSurface = 全部

---

对于此类情况，最佳/最适合的设计是什么？

---

我的想法是使用三个表，但我不知道这是否是最高效的检索数据后（可能这应该在两个表，甚至只是一个？）：

PRODUCT-TABLE

id，product_name

CATEGORY表格

id，fk_product_id，fk_category_name

id，fk_category_id，feature_name，feature_value

  yn = 
 yes，no 
 age_range = 0-4,4-6,6-12，... 
 use = Fine-Drawing，Canoeing，... 
 surface_group = All，PaperOrSkin，PaperOrWall ，... 
 TOY（p，AgeGroup，Toxic，ChristmasSpecial，...）
  -  [b] p]是一个玩具的年龄范围[AgeGroup]和它是否有毒是[有毒]，无论是在圣诞特别是[圣诞节特别]和... 
 SCHOOL-STATIONERY（p，BulkAvailability，...）
  -  [p]是学校文具，其大量可用性是[BulkAvailability]和... 
 ART-ACCESSORY（p，Use，CompatibleSurface，...）
  -  [p]是使用[u]的艺术附件，并且与表面组[CompatibleSurface]中的表面兼容... 
  

SQL查询组合条件和表。

请参阅这个stackoverflow帖子从今天或数百万的其他人在EAV和OTLT。 或此。此外，只是了解数据库设计。也许开始这里。

注意事项更简单，如果是/没有选择分开：

  TOY（p，AgeGroup ...） -  [p] [年龄组]和... 
 TOY-TOXIC（p） - 玩具[p]是有毒的
 TOY-XMAS-特别（p） - 玩具[p]是圣诞节特别
学校文具（p，...） -  [p]是学校文具和... 
学校文具 - 可用（p） - 学校文具$ b  

也许你也希望有时候没有标签的东西组，但只是陈述的事情或第一和范围内的最后一件事：

  age = 0,1,2，... 
 surface = ，墙，... 
 TOY（p，...） -  [p]是一个玩具和... 
 TOY（p，MinAge，MaxAge） -  [p]年龄最小[MinAge]和最大[MaxAge] 
 ART-ACCESSORY（p，Use，...） -  [p]是使用[u] -accESSORY-COMPATIBLE-SURFACE（p，CompatibleSurface） - 附件[p]与表面兼容
  

您可以随时更改当前硬连线的信息（类型集和类型，列和表名）。你总是可以写通用查询，甚至不知道什么是硬连线，因为所有的硬连线名称是组成DBMS元数据的表中的值。

Suppose you are designing a searchable database for a webstore, and have a situation like the following:

---

Each product will have multiple, different categories.

For example, consider a MARKER as a product.

E.g.: The same MARKER might have as its category all three of the following:

• TOY
• SCHOOL-STATIONERY
• ART-ACCESSORY

Now, each category has a corresponding set of any number of parameters (let's call them features) that apply only to that category.

Thus, for each of its three product-category representations, the MARKER above will have multiple, different product-feature-names and respective product-feature-values.

E.g.: We might have these category-based feature-names and product-specific feature-values:

• PRODUCT-NAME::: CATEGORY-NAME::: Feature1-name = Feature1-value, etc.
• MARKER::: TOY::: AgeGroup = 6-12, Toxic = No, ChristmasSpecial = Yes
• MARKER::: SCHOOL-STATIONERY::: BulkAvailability = No
• MARKER::: ART-ACCESSORY::: Use = Fine-drawing, CompatibleSurface = All

---

What would be the best / most-optimal design for this type of situation?

---

My thought is to use three tables, but I don't know if this is the most efficient for retrieving data later (perhaps this should be done in two tables, or even just one?):

PRODUCT-TABLE

id, product_name

CATEGORY-TABLE

id, fk_product_id, fk_category_name

FEATURE-TABLE

id, fk_category_id, feature_name, feature_value

解决方案

Just have the database say what's so.

yn = yes,no
age_range = 0-4,4-6,6-12,...
use = Fine-Drawing,Canoeing,...
surface_group = All,PaperOrSkin,PaperOrWall,...
PRODUCT(p,n) -- product [p] is named [n]
TOY(p,AgeGroup,Toxic,ChristmasSpecial,...)
    -- [p] is a toy for age range [AgeGroup] and whether it's toxic is [Toxic] and whether it's on Christmas special is [ChristmasSpecial] and ...
SCHOOL-STATIONERY(p,BulkAvailability,...)
    -- [p] is school stationery and its bulk availability is [BulkAvailability] and ...
ART-ACCESSORY(p,Use,CompatibleSurface,...)
    -- [p] is an art accessory with use [u] and is compatible with surfaces in surface group [CompatibleSurface] and ...

An SQL query combines conditions and tables. The meaning of the query is combined from the conditions and the table meanings given above.

See this stackoverflow post from today or zillions of others on EAV and OTLT. Or this. Also, just learn about database design. Maybe starting here.

Notice things are simpler if yes/no choices are separated:

TOY(p,AgeGroup,...) -- [p] is a toy for age range [AgeGroup] and ...
TOY-TOXIC(p) -- toy [p] is toxic
TOY-XMAS-SPECIAL(p) -- toy [p] is on Christmas special
SCHOOL-STATIONERY(p,...) -- [p] is school stationery and ...
SCHOOL-STATIONERY-BULK-AVAILABLE(p) -- school stationery [p] is available in bulk

Probably you also want to sometimes not have labels for groups of things but simply state about the things or the first and last things in a range:

age = 0,1,2,...
surface = Paper,Skin,Wall,...
TOY(p,...) -- [p] is a toy and ...
TOY(p,MinAge,MaxAge) -- [p] is a toy with age minimum [MinAge] and maximum [MaxAge]
ART-ACCESSORY(p,Use,...) -- [p] is an art accessory with use [u] and ...
ART-ACCESSORY-COMPATIBLE-SURFACE(p,CompatibleSurface) -- accessory [p] is compatible with surface [CompatibleSurface]

You can always change the currently-hardwired info (type sets and type, column and table names). You can always write generic queries that don't even know what is hardwired because all the hardwired names are values in the tables constituting DBMS metadata.

这篇关于具有多个类别和多个子功能的产品的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！