数据库建模：如何使产品像亚马逊这样的产品 [英] Database Modeling: How to catogorize products like Amazon?

问题描述

假设我有一些产品（从几千到数十万）需要分层次分类。

这样一个简单的父子表如下：

  product_category 
  -  id 
  -  parent_id 
  -  category_name 
  pre> 
 
 
然后在我的产品表中，我会这样做：
  product 
  -  id 
  -  product_category_id 
  -  name 
  -  description 
  -  price 
  
我担心的是这不会扩展。顺便说一下，我现在正在使用MySQL。
解决方案
课程将缩放。那会很好，这是一个常用的结构。 
 
 
 
包含一个 level_no 。这将有助于代码，但更重要的是，需要排除重复。 
 
 
 
如果你想要一个非常紧凑的结构，你需要像Unix的unix概念。
 
 
 
你可以难以让您的头脑围绕生成层次结构所需的代码，从产品说，但这是一个单独的问题。
 
 
 
请更改
 
 
 
 
 
（ product_category ）） id  to  product_category_id  
 
 
（ product   id  to  product_id  
 
 
  parent_id  to  parent_product_category_id  
 
 
 
 
 
 回应评论 
 
 
 
  level_no 。看看这个数据模型，它是针对目录树结构（例如FlieManager Explorer窗口）：
 
 
 
  目录数据模型  
 
 
 
看看是否可以理解它，这就是Unix inode概念。 FileNames在Node中必须是唯一的，因此是第二个Index。这实际上是完整的，但是现在有些开发人员会很谦虚地编写所需的代码来导航层次结构。那些开发人员需要一个 level_no 来确定他们处理的层次结构中的级别。
 
 
 
 
推荐的更改。是的，它被称为好的命名约定。我很僵硬，我发布它，所以它是一个命名标准。有理由，当您使用3或4级连接编写一些SQL时，这将变得清楚;特别是当你去同一个父母两种不同的方式。如果你搜索SO，你会发现很多问题;总是一样的答案。 $ / $> 
 
 
Assume I had a number of products (from a few thousands to hundred of thousands) that needed to be categorized in a hierarchical manner. How would I model such a solution in a database?

Would a simple parent-child table like this work:
product_category
- id
- parent_id
- category_name
Then in my products table, I would just do this:
product
- id
- product_category_id
- name
- description
- price
My concern is that this won't scale. By the way, I'm using MySQL for now.
解决方案
Course it will scale.  That will work just fine, it is a commonly used structure. 

Include a level_no.  That will assist in the code, but  more important, it is required to exclude duplicates.  

If you want a really tight structure, you need something like the Unix concept of inodes.

You may have difficulty getting your head around the code required to produce the hierarchy, say from a product, but that is a separate issue.

And please change  


(product_category)) id  to  product_category_id
(product id to product_id
parent_id to parent_product_category_id


Responses to Comments

level_no. Have a look at this Data Model, it is for a Directory Tree structure (eg. the FlieManager Explorer window):

Directory Data Model

See if you can make sense of it, that's the Unix inode concept. The FileNames have to be unique within the Node, hence the second Index.  That is actually complete, but some developers these days will have a hissy fit writing the code required to navigate the hierarchy, the levels.  Those developers need a level_no to identify what level in the hierarchy they are dealing with.
Recommended changes.  Yes, it is called Good Naming Conventions.  I am rigid about it, and I publish it, so it is a Naming Standard.  There are reasons for it, which will become clear to you when you write some SQL with 3 or 4 levels of joins; especially when you go to same one parent two different ways.  If you search SO, you will find many questions for this; always the same answer.  It will also be highlit in the next model I write for you.


                        
这篇关于数据库建模：如何使产品像亚马逊这样的产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！