数据库建模：具有1个或多个类别的产品 [英] Database Modeling: Product with 1 or more categories

问题描述

我正在开发一个数据库设计，允许一个产品在一个或多个类别。类别可以处于父子关系中。这是我到目前为止：

  ================== b $ b product 
 ================== 
 product_id 
 name 
 
 ====== ============ 
 category 
 ================== 
 category_id 
 parent_category_id 
 level 
 name 
 
 ================ 
 product_category 
 ====== ============ 
 product_id 
 category_id（仅限叶节点）
  

问题

1. 设计看起来不错吗？


2. 我是否只关心product_category-> category_id中的叶节点？ （我想我可以用这个信息从叶节点到根节点工作）


3. 给定一个产品，我如何获得产品所属的类别树


4. 给定一个类别（类别树中的任何级别），我将如何获得按其分类的产品数量？


5. 我需要研究一下吗？

解决方案

树在数据库中。但是，您的查询可能会变得复杂，您可能会发现自己需要许多查询来完成某些任务，例如在遍历深度时对每个级别进行查询。

但是，完全不同的数据库树实现方法。这是一种反直觉，但是当你看看查询简单性的好处，很明显，有一个很大的优点集。

有一个阅读： http://articles.sitepoint.com/article/hierarchical-data-database

---

此外，如果您使用 ORM strong>为您实现树，以及操作。

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

1. Does the design look good?
2. 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)
3. Given a product, how would I get the category tree(s) that product belongs to?
4. Given a category (any level in the category tree), how would I get the number of products categorized under it?
5. Any other queries I need to look into?

解决方案

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.

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