为Yelp或eBay（很多类别和子类别）的网站设计数据库和关系的最佳方式是什么？ [英] What would be the best way to design database and relationships for a website like Yelp or eBay (lots of categories and sub-categories)?

问题描述

他们是否有一个所有类别的表，另一个是所有子类别（另一个是子子类别等），或者什么？所有级别如何相互沟通？

我是一个noob开始一个可能具有这种复杂性的项目，我正在努力 - / />

我正在使用Rails，但我也会欣赏数据库模式中的答案，指向更多阅读的指针等。

解决方案

我假设你在这里处理分层数据。

需要两张桌子。其中一个表格是类别和类别之间的关系。例如：

  CategoryID CategoryName ParentCategoryID 
 ---------------- ------------------------ 
 1计算机NULL 
 2编程1 
 3游戏1 
 4 Python 2 
  

另一个表用于存储与类别相关联的数据。例如：

  CategoryID ItemID描述
 ---------------- ------------------------------ 
 4 1书 -  Python中的编程
 3 1魔兽世界
  

第一个表包含将子类别链接到其父类别的外键列。这被称为邻接列表模型。这个模型的优点是可以很容易地使用客户端代码（如果你不这样做）很容易理解和做各种事情（例如，检索到Python类别的路径 - / Computers / Programming / Python）请记住性能成本，因为您可能需要对数据库进行多个查询）。但是，如果您尝试在完整SQL中执行此操作，则可以启动。例如，检索类别的路径将需要自联接。

构建此表的另一种方法是将类和子类作为集合和子集（称为嵌套集模型）。没有图表就很难解释这个模型，文章管理MySQL中的分层数据似乎更好地解释概念（邻接列表和嵌套集模型））。

Do they have a table for all categories and another for all sub-categories (and another for the sub-sub-categories and so on), or what? How do all the levels go around communicating with each other?

I'm a noob getting started on a project that might have that level of complexity and I am having a hard-time wrapping my head around that.

I'm working with Rails but I'd also appreciate answers in database schemas, pointers to further reading etc.

解决方案

I am assuming that you are dealing with hierarchical data here.

You just need two tables. One of the tables is for the categories and relationships between the categories. For example:

CategoryID CategoryName ParentCategoryID
----------------------------------------
1          Computers    NULL
2          Programming  1
3          Games        1
4          Python       2

The other table is for storing the data associated with the categories. For example:

CategoryID ItemID Description
----------------------------------------------
4          1      Book – Programming in Python
3          1      World of Warcraft

The first table contains a foreign key column that links the subcategories to their parent categories. This is known as the "Adjacency List Model". This model has the advantage of being simple to understand and doing various things (e.g. retrieving the path to the Python category – /Computers/Programming/Python) with this model can be done quite easily with client-side code (if you don’t mind the performance cost because you may need to make multiple queries to the database). However, it can be mindboggling if you try to do it in full SQL. For example, retrieving the path for a category will require self-joins.

Another way of structuring this table is to think of the categories and sub-categories as sets and subsets (known as the "Nested Set Model"). It’s hard to explain this model without diagrams, and the article Managing Hierarchical Data in MySQL seems to do a better job in explaining the concepts (both "Adjacency List" and "Nested Set" models).

这篇关于为Yelp或eBay（很多类别和子类别）的网站设计数据库和关系的最佳方式是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！