mysql - 如何为以下条件创建架构？

Question

我有两个表，一个是category，另一个是sub-category。对于许多表，两个表被分配给 FK。在某些情况下，我们会将子类别的一条记录移动到主类别。所以这次发生约束错误，因为 key 与其他表相关联。所以我不会创建这个架构。

所以现在我计划在同一个表中创建类别和子类别，并创建关系表以建立它们之间的关系。

分类表:

id(PK,AUTO Increment),
item===>((1,phone),(2.computer),(3,ios),(4,android),(5,software),(6,hardware)).

关系表:

id,cate_id(FK),
parentid(refer from category table)===>((1,1,0),(2,2,0),(3,3,1),
                                        (4,4,1),(5,5,2),(5,5,3)).

在我这边，层次结构不会超过三层。

如果我们轻松地将子类别移动到主类别 ex:(4,4,1) 到 (4,4,0) 而不影响任何其他表。这是好的程序吗？

如果我们保持数百万的记录， future 会不会面临其他问题？

有任何其他想法让我知道吗？

Answer 1

如果您在树中有多个级别，并且想要查找任何类别的所有子类别，则可能会出现问题。这将需要多个查询或一个递归查询。

您可以改为查看 "Nested Sets"数据结构。它支持对任何子树的有效查询。它确实有一个昂贵的更新，但更新可能不会经常发生。如果需要，您可以批量更新并在夜间运行它们。

create table Category (
    Id int not null primary key auto_increment,
    LeftExtent int not null,
    RightExtent int not null,
    Name varchar(100) not null
);

create table PendingCategoryUpdate (
    Id int not null primary key auto_increment,
    ParentCategoryId int null references Category ( Id ),
    ParentPendingId int null references PendingCategoryUpdate ( Id ),
    Name varchar(100) not null
);

---

如果您的类别数量较少，一个普通的父引用就足够了。您甚至可以将类别读入内存进行处理。

create table Category (
    Id int not null primary key auto_increment,
    ParentId int null references Category ( Id ),
    Name varchar(100) not null
);

-- Just an example
create table Record (
    Id int not null primary key auto_increment,
    CategoryId int not null references Category ( Id )
);

select *
from Record
where CategoryId in (1, 2, 3); -- All the categories in the chosen sub-tree