对于具有无限子类别的网站,我应该使用什么样的数据库结构?

发布于 2024-08-04 19:37:52 字数 185 浏览 6 评论 0原文

例如,“都乐香蕉”是一种产品,它列在“香蕉”类别下,当我打开“水果”类别时,我想看到“都乐香蕉”。

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

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

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

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(8

云归处 2024-08-11 19:37:52

我通常使用左右树,效果非常好适应数据库查询。每个节点都有一个parentId、left 和right 值。每个节点的子节点都有一个左/右值,该值位于父节点的左和右之间,这使得很容易找到例如节点的所有子节点/父节点。它确实会给插入带来轻微的开销,但除非您插入很多,否则不会产生太大的影响。

编辑:只是警告一句,您需要在锁定事务中进行插入/更新操作,否则树可能会变得混乱。

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.

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.

゛时过境迁 2024-08-11 19:37:52

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

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

使用您的示例树:

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

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.

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
久光 2024-08-11 19:37:52

具有 3 个字段的表“类别”。

  1. CategoryId not null(主键)
  2. ParentCategoryId null
  3. CategoryName not null

获取所有根类别

select * from Categories where ParentCategoryId is null

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

select * from Categories where ParentCategoryId = 12

A Table "Categories" with 3 fields.

  1. CategoryId not null (primary key)
  2. ParentCategoryId null
  3. CategoryName not null

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
懵少女 2024-08-11 19:37:52

您可以使用带有parent_category_id的简单表结构,并通过递归检索整个树,或者实现左/右值并使用预先排序的树遍历方法获取整个树。

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.

朮生 2024-08-11 19:37:52

如果您的意思是无限数量的级别,那么可以递归的自引用表。示例:StuffID、StuffName、StuffParentID(FK 到 Stuff ID)

对于有限数量的固定表:父子-孙子

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)

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

巷雨优美回忆 2024-08-11 19:37:52
    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
    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
白色秋天 2024-08-11 19:37:52

对于无限层次结构,请使用修改后的先序树遍历算法

For infinite hierarchy, use the modified preorder tree traversal algorithm

末が日狂欢 2024-08-11 19:37:52

这是一种可能对您有用的不同方法。它的维护成本比 PARENT_ID 或 lft/rght 方法稍高,但检索更容易(也更快)。

都乐香蕉可以在产品表中。您的产品有一个category_id。

我们需要允许一个产品有多个类别。这导致我们有一个categories_products 连接表,其中product 可以有多个连接行。然后,我们必须决定是只在香蕉中加入都乐香蕉,还是在香蕉及其所有亲本中加入都乐香蕉。由于检索速度至关重要,我们将 dole 香蕉放入其类别及其所有父类别中。 dole 香蕉有 3 个类别产品连接。

使用这种结构,只需一次查询即可轻松快速地返回任何类别的所有项目。您不能在 PARENT_ID 方法中执行此操作(除非您对父母、祖父母等进行硬编码)。添加类别很容易。对产品进行分类需要在连接表中插入多行。删除和移动类别有点棘手。

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

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

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.

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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文