我正在设计一个数据库,我对在关系数据库中使用Hierarchical数据模型有一些疑问.
如果我想处理类别,子类别和父类别,可能不在关系数据库中使用分层数据模型？换句话说,可以使用关系做事方式处理类别,子类别和父类别？
顺便说一下,我正在使用Postgresql.
对不起,我的英语不好.
最好的祝福,
解决方法
您有两种存储层次结构的选项：
>邻接清单
>在adjancy列表上的递归查询
>路径枚举
>嵌套集
>关闭表
如果你有Postgresql版本8.4或更高版本,你可以使用recusive queries使事情变得非常简单.这是迄今为止最简单的解决方案,易于查询,易于插入新记录,易于更新当前记录,易于删除记录,并且您具有参照完整性.所有其他解决方案都有难以解决的问题.
附加清单：
CREATE TABLE categories (
id SERIAL PRIMARY KEY,parent_id BIGINT,category TEXT NOT NULL,FOREIGN KEY (parent_id) REFERENCES categories(id)
);
INSERT INTO categories(parent_id,category) VALUES(NULL,'vehicles');
INSERT INTO categories(parent_id,category) VALUES(1,'cars');
INSERT INTO categories(parent_id,'motorcycles');
INSERT INTO categories(parent_id,category) VALUES(2,'SUV');
INSERT INTO categories(parent_id,'sport');
INSERT INTO categories(parent_id,category) VALUES(3,'cruising');
INSERT INTO categories(parent_id,'sport');
WITH RECURSIVE tree (id,parent_id,category,category_tree,depth)
AS (
SELECT
id,category AS category_tree,0 AS depth
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT
c.id,c.parent_id,c.category,tree.category_tree || '/' || c.category AS category_tree,depth+1 AS depth
FROM tree
JOIN categories c ON (tree.id = c.parent_id)
)
SELECT * FROM tree ORDER BY category_tree;
结果：
‘1’,”,’vehicle’,’0′
‘2’,’1′,’cars’,’vehicle/cars’,’1′
‘4’,’2′,’SUV’,’vehicle/cars/SUV’,’2′
‘5’,’sport’,’vehicle/cars/sport’,’2′
‘3’,’motorcycles’,’vehicle/motorcycles’,’1′
‘6’,’3′,’cruising’,’vehicle/motorcycles/cruising’,’2′
‘7’,’vehicle/motorcycles/sport’,’2′
