产品数据库设计，包括产品线、类别、制造商、相关软件、产品属性等
我正在重新开发中型产品数据库的前端和数据库，以便它可以支持类别/子类别、产品线、制造商、支持的软件和产品属性。现在只有一个产品表。将有按产品线、类别/子类别、制造商、支持的软件（可选）列出的产品页面。每个页面都会根据其他分类进行额外的过滤。
类别/子类别（多级） 产品和产品线可以分配给多个类别树。应支持最多 5 层深度。
产品线（单级） 产品组。产品只能属于单一产品线。
制造商（单级） 产品和产品线可以分配给单个制造商。
支持的软件（单级） 某些产品仅适用于一种或多种软件，因此产品/系列可以不分配给任何软件，也可以分配给一个或多个软件。
属性（类型/选项 - 可以进行处理，以便每种类型都是一个类别，而项目是子项） 产品和产品线可以被分配属性(例如-颜色>红色/蓝色/绿色)。属性应该能够分配给一个或多个类别。
由于所有这些项目基本上都是子类别的类型，因此我是将它们全部放在一个主表中还是将它们分成每个单独的表？
主表思想：
ClassificationTypes（产品线、类别/子、制造商、软件、属性都是类型）
-类型ID
-名称
分类
-ClassID
-类型ID
-ParentClassID
-名称
分类产品协会
-产品ID
-ClassID
我仍然需要至少一个表来将类型链接在一起（例如 - 将属性链接到类别）以及一种将产品线链接到各种类型的方法。
如果我为每种类型使用一个表格，它很快就会变得混乱，而且我仍然需要一种将所有内容链接在一起的方法。
多表设置：
类别
-类别ID
-姓名
-ParentCategoryID
类别关联
-类别ID
-产品ID
-产品线ID？
属性
-属性ID
-姓名
-ParentAttributeID（使用此属性，因为父级将是“颜色”，子级将是“红色”）
AttributesAssociations
-属性ID
-产品ID
-CategoryID（我还需要将类别链接到父属性吗？）
CompatibleSoftware
-软件ID
-名称
兼容软件协会
-软件ID
-产品ID
-产品线ID？
制造商
-制造商ID
-名称
产品线
-产品线ID
-制造商ID
-名称
产品
-产品ID
-产品线ID
-制造商ID
-名称
关联的其他选项是使用单个关联表来链接上面的表：
主关联
-产品ID
-产品线ID
-制造商ID
-类别ID
-软件ID
-AttributeID
最好的解决方案是什么？
I am redeveloping the front end and database for a medium size products database so that it can support categories/subcategories, product lines, manufacturers, supported software and product attributes. Right now there is only a products table. There will be pages for products by line, by category/subcategory, by manufacturer, by supported software (optional). Each page will have additional filtering based on the other classifications.
Categories/Subcategories (multi level)
Products and product lines can be assigned to multiple category trees. Up to 5 levels deep should be supported.
Product lines (single level)
Groups of products. Product can only be in single product line.
Manufacturers (single level)
Products and product lines can be assigned to single manufacturer.
Supported software (single level)
Certain products only work with one or more softwares, so a product/line can be assigned to none, one or more softwares.
Attribues (type / options - could be treated so each type is a category and items are children)
Products and product lines can be assigned attributes (eg - color > red / blue / green). Attributes should be able to be assigned to one or more categories.
Since all these items are basically types of subcategories, do I put them all together in a master table OR split them into separate tables for each one?
Master table idea:
ClassificationTypes (product line, category/sub, manufacturer, software, attribute would all be types)
-TypeID
-Name
Classifications
-ClassID
-TypeID
-ParentClassID
-Name
ClassificationsProductsAssociations
-ProductID
-ClassID
I would still need at least one more table to link types together (eg - to link attributes to a category) and a way to link product lines to various types.
If I go with a table for each type it can get messy quick and I will still need a way to link everything together.
Multiple table setup:
Categories
-CategoryID
-Name
-ParentCategoryID
CategoriesAssociations
-CategoryID
-ProductID
-ProductLineID ?
Attributes
-AttributeID
-Name
-ParentAttributeID (use this as the parent would be "color" and child would be "red")
AttributesAssociations
-AttributeID
-ProductID
-CategoryID (do I also need to link the category to the parent attribute?)
CompatibleSoftware
-SoftwareID
-Name
CompatibleSoftwareAssociations
-SoftwareID
-ProductID
-ProductLineID ?
Manufacturers
-ManufacturerID
-Name
ProductLines
-ProductLineID
-ManufacturerID
-Name
Products
-ProductID
-ProductLineID
-ManufacturerID
-Name
Other option for associations is to have a single associations table to link the tables above:
Master Associations
-ProductID
-ProductLineID
-ManufacturerID
-CategoryID
-SoftwareID
-AttributeID
What is the best solution?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
在我看来，选择多个表，它使设计更加明显且更具可扩展性。虽然它现在可能适合您的解决方案，但进一步的更改可能会更加困难。
Go for multiple tables, it makes the design more obvious and more extensible, in my opinion. While it may fit your solution now, further changes may be more difficult.
我同意帕迪的观点。它会让你未来的生活更轻松，你也更加灵活。你可能想要进行库存控制和其他东西。要将所有内容链接在一起，请使用表的 id（整数）父/子。
I agree to Paddy. It makes your life easier in the future and you are much more flexible. You might want to put in stock control and other stuff. To link everything together use the id's (integer) parent/child of the tables.
我认为多个表是可行的方法，但要真正了解，请执行以下操作：充实这两种方式的设计，然后抽取 5-10 个产品的样本。
在两种设计中填充 5-10 个产品的表格。
现在开始为这两种方式编写查询。您将开始看到哪个更容易编写（我打赌的单表），并且您可能会发现仅在一种设计中有效的情况（我打赌的多表）。
当您完成时，您并没有失去工作 -您可以使用表架构继续前进，并且您的一些查询已经被写入。
如果您遇到一个没有意义、似乎很复杂的查询，您可以将其发布在这里并获得反馈——拥有真实的代码总是会得到更好的评论。
I think multiple tables is the way to go, but to really know, do this: Flesh out the design for both ways and then take a sample of 5-10 products.
Populate the tables in both designs for the 5-10 products.
Now start writing the queries for both ways. You will start to see which is easier to write (the single table I bet), and you might find cases that only work in one design (the multi-table I bet.)
When you are done you have not lost the work -- you can use the table schema to move forward and some of your queries will already be written.
If you get to a query that does not make sense, seems to complicated, or such you can post it here and get feed back -- having real code always gets better comments.
只是想发布我的决定，由于我对所提供的任何答案都不满意，所以我选择回答我自己的问题。
我最终设置了一组表：
分类类型（例如 - 产品线、类别、制造商等）
分类（同时支持父/子邻接列表、嵌套集和物化路径，以便充分利用优势我有一个 SQL CTE，可以在数据更改时一次性填充所有字段）
分类关系（能够将产品与分类相关联、将分类与其他分类相关联以及将分类与其他类型相关联）
我承认这一点该解决方案不是 100% 规范化，但此设置为我提供了通过创建新类型进行扩展的终极灵活性，并且非常强大且易于查询。
Just wanted to post my decision and since I was not satisfied with any of the answers provided, I have elected to answer my own question.
I ended up setting up a a single set of tables:
Classification Types (eg - product lines, categories, manufacturers, etc)
Classifications (supporting parent/child adjacency list, nested sets, and materialized path all at once in order to take advantage of strengths of each. I have a SQL CTE that can populate all the fields in one go when the data changes)
Classifications Relations (with ability to relate products to classifications, relate classifications to other classifications and also relate classifications to other types)
I will admit that the solution is not 100% normalized, but this setup gives me ultimate flexibility to expand by creating new types and is very powerful and easy to query.