对于一个站点，我应该使用什么样的数据库结构?

For example, "Dole Banana" is a kind of product, it's listed under the "Bananas" category, when I open the "Fruits" category, I want to see "Dole Banana".

例如，“Dole Banana”是一种产品，它被列入“香蕉”类别，当我打开“水果”类别时，我想看到“Dole Banana”。

+ Food
|--+ Fruits
|------+ Bananas   
|------+ Apples
|--+ Vegetables
|------+ Onion
|------+ Spinach

8 个解决方案

#1

I've usually used left-right trees which are very well adapted to database querys. You have a parentId,left and right value for each node. Every nodes children has a left/right value that is between the parent nodes left and right which makes it very easy to find for example all children/parents of a node. It does give a slight overhead on insertions, but it shouldn't be too much of an impact unless you insert alot.

我通常使用左右树，它们很好地适应了数据库查询。每个节点都有一个parentId、left和right值。每个节点的子节点都有一个左/右值，该值位于左右父节点之间，这使得查找一个节点的所有子节点和父节点非常容易。它确实会给插入增加一些开销，但是如果不插入太多，就不会产生太大的影响。

Edit: Just a word of warning though, you need to make the insert/update operations in a locked transaction or the tree can get messed up.

编辑:只是一个警告，您需要在一个被锁定的事务中进行插入/更新操作，否则树会被搞砸。

#2

If you're looking for online resources that address this problem, "Storing a Tree in a Database" would be a good search phrase.

如果您正在寻找解决这个问题的在线资源，“在数据库中存储树”将是一个很好的搜索短语。

As for the solution, note that each subcategory can have either one or zero parent categories. Therefore, the entire tree can be stored in a single self-refferental table with a "parent" field.

对于解决方案，请注意，每个子类别可以有一个或零父类别。因此，可以将整个树存储在一个具有“父”字段的单个自fferental表中。

Using your example tree:

用例树:

 ID  | PARENT | NAME
-----+--------+-------------
  1  |  null  | Food
  2  |   1    | Fruits
  3  |   2    | Bananas
  4  |   2    | Apples
  5  |   1    | Vegetables
  6  |   5    | Onion
  7  |   5    | Spinach

#3

A Table "Categories" with 3 fields.

有三个字段的表“类别”。

CategoryId not null (primary key)
类别id不为空(主键)
ParentCategoryId null
ParentCategoryId零
CategoryName not null
CategoryName非空

To get all root categories

获取所有根类别

select * from Categories where ParentCategoryId is null

To get all sub categories of some specific category:

获取特定类别的所有子类别:

select * from Categories where ParentCategoryId = 12

#4

You could use simple table structure with parent_category_id and retrieve whole tree with recursion or implement left/right values and fetch whole tree using preordered tree traversal method.

可以使用parent_category_id使用简单的表结构，使用递归检索整棵树，或者实现左/右值，使用有序树遍历方法获取整棵树。

#5

If you mean an infinite number of levels, then a self referencing table that can be recursed. Example: StuffID, StuffName, StuffParentID (FK to Stuff ID)

如果您指的是无限多个级别，那么可以递归的自引用表。示例:StuffID, StuffName, StuffParentID (FK到StuffID)

For a finite number, fixed tables: parent-child-grandchild

对于有限的数字，固定的表:父子-孙子。

#6

    CREATE TABLE [dbo].[Category](
    [CategoryId] [int] NOT NULL,
    [ParentCategoryId] [int] NULL,
    [CategoryName] [nvarchar](50) NOT NULL,
     CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
    (
        [CategoryId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON 

[PRIMARY]

GO

ALTER TABLE [dbo].[Category]  WITH CHECK ADD  CONSTRAINT [FK_Category_Category] FOREIGN KEY([ParentCategoryId])
REFERENCES [dbo].[Category] ([CategoryId])
GO

ALTER TABLE [dbo].[Category] CHECK CONSTRAINT [FK_Category_Category]
GO

#7

For infinite hierarchy, use the modified preorder tree traversal algorithm

对于无限层次结构，使用修改后的preorder树遍历算法。

#8

Here's a different approach that might be useful to you. It has slightly more maintenance costs than the PARENT_ID or lft/rght approach, but retrieval is much easier (and faster).

这里有一个可能对你有用的不同方法。它的维护成本略高于PARENT_ID或lft/rght方法，但检索要容易得多(而且速度也更快)。

Dole bananas can be in products table. You have a single category_id for a product.

多尔香蕉可以在产品表。产品只有一个类别id。

We had a requirement to allow multiple categories for a product. This lead us to having a categories_products join table, where product could have multiple joined rows. Then, we had to decide whether to have Dole bananas in just bananas, or in bananas and all its parents as well. As speed of retrieval was critical, we put dole bananas in its categories and all of their parent categories. There are three category-product joins for dole bananas.

我们有一个要求，允许一个产品有多个类别。这导致我们有一个categories_products联接表，其中product可以有多个联接行。然后，我们必须决定是在仅仅香蕉中，还是在香蕉中，还是在所有的双亲中。由于检索速度非常重要，我们将dole banana归入其类别和所有的父类别。多尔香蕉有三种分类产品。

Using this structure, returning all the items from any category is easy and quick, only one query. You can't do this in the PARENT_ID approach (unless you hard-code parents, grand-parents, etc.) Adding a category is easy. Categorizing a product requires inserting multiple rows in the join table. Deleting and moving categories are a bit trickier.

使用这种结构，从任何类别返回所有项都很容易且快速，只有一个查询。在PARENT_ID方法中不能这样做(除非您对双亲、祖父母等进行硬编码)，添加类别很容易。对产品进行分类需要在连接表中插入多个行。删除和移动类别有点麻烦。

#1