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

问题描述

他们有所有类别的所有子类（用于子子类别等等，另外），还是什么表，另一个？如何做的所有层面四处相互沟通？

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.

我与Rails的工作，但我也AP preciate答案数据库架构，指向进一步阅读等。

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

第一个表中包含的子类别链接到它们的父类的外键列。这被称为邻接表模型。这种模式具有简单理解和做各种事情的好处（如检索路径的Python类 - /计算机/编程/ Python）的这个模型可以很方便地与客户端code（做，如果你不介意的性能损失，因为你可能需要进行多次数据库查询）。但是，它可以，如果你尝试做全面的SQL来mindboggling。例如，检索路径类别将需要自连接。

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.

构建此表的另一种方法是想到类别和子类别，集合和子集（称为嵌套集合模型）的。这是很难解释这种模式不图和文章管理MySQL的分层数据，似乎在解释概念（既邻接表和嵌套集合模式），以做得更好。

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).

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