数据库建模：具有 1 个或多个类别的产品
我正在开发一种数据库设计，允许产品属于 1 个或多个类别。类别可以是父子关系。到目前为止，我的情况如下：
==================
product
==================
product_id
name
==================
category
==================
category_id
parent_category_id
level
name
==================
product_category
==================
product_id
category_id (leaf node only)
问题
- 设计看起来不错吗？
- 我只关心product_category->category_id中的叶节点是否正确？ （我想我可以使用此信息从叶节点一直到根节点）
- 给定一个产品，我如何获得该产品所属的类别树？
- 给定一个类别（类别树中的任何级别），我如何获得该类别下分类的产品数量？
- 我还需要调查其他任何问题吗？
I'm working on a database design that allows for a product to be in 1 or more categories. A category can be in a parent-child relationship. Here's what I have so far:
==================
product
==================
product_id
name
==================
category
==================
category_id
parent_category_id
level
name
==================
product_category
==================
product_id
category_id (leaf node only)
Questions
- Does the design look good?
- Am I right to only be concerned with the leaf node in product_category->category_id? (I suppose I can work my way from the leaf node up to the root node with this information)
- Given a product, how would I get the category tree(s) that product belongs to?
- Given a category (any level in the category tree), how would I get the number of products categorized under it?
- Any other queries I need to look into?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您所呈现的内容看起来像是数据库中树的正确设计。但是，您的查询可能会变得复杂，并且您可能会发现自己需要对某些任务进行多次查询，例如遍历深度时每个级别都有一个查询。
然而，数据库中还存在一种完全不同的树实现方法。这有点违反直觉，但是当您了解查询简单性的好处时，就会发现它有很多优点。
阅读：http://articles.sitepoint.com/article/hierarchical-data-database
此外，如果您使用像 Doctrine 这样的 ORM 来为您实现树以及操作，情况可能会更好。
What you have presented looks like a correct design for a tree in the database. However, your queries may become complicated and you can find yourself requiring many queries for some tasks, like one for every level when traversing the depth.
However, there also exists a totally different approach for tree implementation in databases. It's kind of counterintuitive, but when you look at the benefits of the query simplicity, it becomes obvious that there is a big set of advanages.
Have a read: http://articles.sitepoint.com/article/hierarchical-data-database
Also, you could be better off if you use an ORM like Doctrine to implement the tree for you, along with the operations.
正如其他人已经说过的那样，您的设计没问题。只是一个想法：
根据您正在建模的业务，可能可以以更简单的方式表示类别的层次结构。例如：
如果这在您的领域中可用，我认为它可以使您的设计更容易。
Your design is ok, as the others have said already. Just an idea:
It might be possible, depending on the business you are modelling, to represent the hierarchical structure of the categories in a simpler way. E.g:
If this is usable in your domain, I think it could make your design easier.
我也同意只把叶子放在这里。我预计您主要希望在其特定类别中显示产品，但会有子查询滚动到父项，此时您可能已经在类别表中查找名称等。 - 经验法则，从反规范化开始，并根据性能需要进行规范化。
product_category 表上的内部联接（如果您想查看 SQL，请告诉我）。
给定一个类别（类别树中的任何级别），我如何获得分类在该类别下的产品数量？ （通过连接和计数轻松完成 - 与上面的注释相同）。
随着您的前进，您可能会发现更多的查询，但这就是紧急设计的美妙之处:)
Yes - a many to many intersection table is correct (a product can be in more than one category, a category has more than one product).
I would agree on only having the leaf here as well. I'm expecting that primarilly you want to display products in their specific category, but would have subqueries that roll up to the parent, at which point you're probably already hitting the category table anyway for the name, etc. - Rule of thumb, start with denormalization and normalize as needed for performance.
An inner join on the product_category table (let me know if you want to see the SQL).
Given a category (any level in the category tree), how would I get the number of products categorized under it? (Easily accomplished with a join and a count - same note as above).
Likely you'll uncover more queries as you move forward, but that's the beauty of emergent design :)