对于单层层次结构(节点深度=1),使用
supertype-subtype
.
如果你确实需要一个可变节点深度的树,那么可以考虑使用
闭合表
,而不是拥有
parent_id
在同一张桌子上。
闭包表将所有路径存储在树中,因此每个祖先-后代链接都是一个单独的行。这样,给定节点的所有祖先/后代都会被暴露出来。闭包表易于查询,但维护起来有点困难,所以这是一种权衡。
-- Category CAT exists.
--
category {CAT}
PK {CAT}
-- Data Sample
(CAT)
------------------------
('Dogs')
, ('Big Dogs')
, ('Small Dogs')
, ('Chihuahua')
, ('Pug')
, ('Pit Bull')
, ('Birds')
, ('Macaw')
, ('Finch')
-- Ancestor ANC has descendant DCS
--
category_tree {ANC, DCS}
PK {ANC, DCS}
FK1 {ANC} REFERENCES category {CAT}
FK2 {DCS} REFERENCES category {CAT}
-- Data Sample, includes ANC=DCS
(ANC, DCS)
------------------------
('Dogs' , 'Dogs')
, ('Birds' , 'Birds')
, ('Dogs' , 'Big Dogs')
, ('Dogs' , 'Small Dogs')
, ('Big Dogs' , 'Big Dogs')
, ('Small Dogs' , 'Small Dogs')
, ('Dogs' , 'Chihuahua')
, ('Small Dogs' , 'Chihuahua')
, ('Chihuahua' , 'Chihuahua')
, ('Dogs' , 'Pug')
, ('Small Dogs' , 'Pug')
, ('Pug' , 'Pug')
, ('Dogs' , 'Pit Bull')
, ('Big Dogs' , 'Pit Bull')
, ('Pit Bull' , 'Pit Bull')
, ('Birds' , 'Macaw')
, ('Macaw' , 'Macaw')
, ('Birds' , 'Finch')
, ('Finch' , 'Finch')
-- Trainer TRA trains all descendants of ancestor ANC.
--
trainer {TRA, ANC}
PK {TRA, ANC}
FK {ANC, ANC} REFERENCES category_tree {ANC, DCS}
-- Data Sample
(TRA, ANC)
------------------------
('Joe' , 'Dogs')
, ('Jane' , 'Small Dogs')
, ('Jane' , 'Finch')
, ('Jill' , 'Big Dogs')
, ('Jack' , 'Birds')
, ('John' , 'Pug')
WITH
q_00 AS (
select ANC, count(1) as cnt
from category_tree
group by ANC
having count(1) = 1
)
SELECT t.TRA, x.DCS, t.ANC
FROM trainer AS t
JOIN category_tree AS x ON x.ANC = t.ANC
JOIN q_00 as q ON q.ANC = x.DCS
ORDER BY TRA, t.ANC;
;
退货:
TRA DCS ANC
----------------------------------
Jack' 'Finch' 'Birds'
Jack' 'Macaw' 'Birds'
Jane' 'Finch' 'Finch'
Jane' 'Pug' 'Small Dogs'
Jane' 'Chihuahua' 'Small Dogs'
Jill' 'Pit Bull' 'Big Dogs'
Joe' 'Pit Bull' 'Dogs'
Joe' 'Pug' 'Dogs'
Joe' 'Chihuahua' 'Dogs'
John' 'Pug' 'Pug'
注:
All attributes (columns) NOT NULL
PK = Primary Key
FK = Foreign Key
SQL测试
CREATE TABLE category (
CAT VARCHAR(32) NOT NULL
, CONSTRAINT pk_cat PRIMARY KEY (CAT)
);
CREATE TABLE category_tree (
ANC VARCHAR(32) NOT NULL
, DCS VARCHAR(32) NOT NULL
, CONSTRAINT pk_ctre PRIMARY KEY (ANC, DCS)
, CONSTRAINT fk1_ctre FOREIGN KEY (ANC)
REFERENCES category (CAT)
, CONSTRAINT fk2_ctre FOREIGN KEY (DCS)
REFERENCES category (CAT)
);
CREATE TABLE trainer (
TRA VARCHAR(32) NOT NULL
, ANC VARCHAR(32) NOT NULL
, CONSTRAINT pk_tra PRIMARY KEY (TRA, ANC)
, CONSTRAINT fk1_tra FOREIGN KEY (ANC, ANC)
REFERENCES category_tree (ANC, DCS)
);
INSERT INTO category (CAT)
VALUES
('Dogs')
, ('Big Dogs')
, ('Small Dogs')
, ('Chihuahua')
, ('Pug')
, ('Pit Bull')
, ('Birds')
, ('Macaw')
, ('Finch')
;
INSERT INTO category_tree (ANC, DCS)
VALUES
('Dogs' , 'Dogs')
, ('Birds' , 'Birds')
, ('Dogs' , 'Big Dogs')
, ('Dogs' , 'Small Dogs')
, ('Big Dogs' , 'Big Dogs')
, ('Small Dogs' , 'Small Dogs')
, ('Dogs' , 'Chihuahua')
, ('Small Dogs' , 'Chihuahua')
, ('Chihuahua' , 'Chihuahua')
, ('Dogs' , 'Pug')
, ('Small Dogs' , 'Pug')
, ('Pug' , 'Pug')
, ('Dogs' , 'Pit Bull')
, ('Big Dogs' , 'Pit Bull')
, ('Pit Bull' , 'Pit Bull')
, ('Birds' , 'Macaw')
, ('Macaw' , 'Macaw')
, ('Birds' , 'Finch')
, ('Finch' , 'Finch')
;
INSERT INTO trainer (TRA, ANC)
VALUES
('Joe' , 'Dogs')
, ('Jane' , 'Small Dogs')
, ('Jane' , 'Finch')
, ('Jill' , 'Big Dogs')
, ('Jack' , 'Birds')
, ('John' , 'Pug')
;
编辑
如果整个表应仅限于一个祖先,那么您可以:
-- Trainer TRA trains dog DCS; (ANC = 'Dogs').
--
dog_trainer {TRA, DSC, ANC}
PK {TRA, DSC}
FK {ANC, DSC} REFERENCES category_tree {ANC, DCS}
CHECK (ANC = 'Dogs')