数据库架构，用于从父类别中检索存储在子类别中的项目 [英] DB architecture for retrieving items stored in child categories from parent categories

问题描述

未能找到解决方法这个问题，我想知道是否有更好的存储方法

Failing to find a solution to this question I was wondering if there was a better way of storing data for this problem.

该数据库结构允许将项目存储在多个类别中，但不允许轻松访问父类别层次结构。

That db structure allows items to be stored in multiple categories, but doesn't allow easy access to the parent category hierarchy.

我想要建立一个类别关系，例如：

What I would like is to have a category relationship such as:

Books 
  > Novels
    > Paperbacks
    > Hardbacks

例如，平装本还可以存储在小说和书籍中。因此，类别实际上比实际类别更像过滤器。

And have an item stored against Paperbacks for instance that would also show up in Novels and Books. So the 'categories' actually work more like filters than actual categories.

推荐答案

首先，您需要设计类别表嵌套集体系结构的用法。通过使用嵌套集，您可以轻松地选择类别的整个分支，然后就可以为这些类别选择产品。

First of all you need to design your category table with usage of Nested Set architecture. With usage of Nested Sets you will easily select whole branch of categories, and then you will be able to select products for these categories.

因此，第一个表为：

CREATE TABLE categories (
  id int unsigned NOT NULL auto_increment,
  name varchar(255) NOT NULL,
  left int unsigned NOT NULL,
  right int unsigned NOT NULL,
  PRIMARY KEY (id)
);

第二张表将是：

CREATE TABLE products (
  id int unsigned NOT NULL auto_increment,
  name varchar(255) NOT NULL,
  PRIMARY KEY (id)
);

第三张表将是：

CREATE TABLE product_categories (
  category_id int unsigned NOT NULL,
  product_id int unsigned NOT NULL,
  PRIMARY KEY (category_id, product_id)
);

现在要选择整个类别的所有产品，您需要使用如下查询：

Now to select all products for whole branch of categories, you need to use query like this:

SELECT p.*
  FROM categories AS c1
  LEFT JOIN categories AS c2 ON c1.left <= c2.left AND c2.right <= c1.right
  LEFT JOIN product_categories AS pc ON pc.category_id = c2.id
  LEFT JOIN products AS p ON pc.product_id = p.id
 WHERE c1.id = @id

嵌套集合操作

添加新节点

第一步：更新已存在的类别

1st step: update already existed categories

UPDATE categories 
   SET right = right + 2, left = IF(left > @right, left + 2, left) 
 WHERE right >= @right

第二步：插入新类别

INSERT INTO categories SET left = @right, right = @right + 1, name = @name

删除现有节点

第一步：删除节点

DELETE FROM categories WHERE left >= @left AND right <= @right

第二步：更新其他节点

UPDATE categories 
   SET left = IF(left > @left, left – (@right - @left + 1), left), 
       right = right – (@right - @left + 1) 
 WHERE right > @right

这篇关于数据库架构，用于从父类别中检索存储在子类别中的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！