设计关系数据库 - 使用分层数据模型或避免它们？ [英] Design Relational Database - Use hierarchical datamodels or avoid them?

问题描述

我正在设计一个数据库，并且在关系数据库中使用分层数据模型有一些疑问。

如果我想处理类别，子类别和父类别是否可以在关系数据库中使用分层数据模型？换句话说，可以使用关系方式处理类别，子类别和父类别？

顺便说一下，我使用PostgreSQL。 / p>

对不起我的不好的英语。

最好的问候，

解决方案

您有几个选择来存储分类：

• 邻接列表


• 辅助列表中的递归查询


• 路径枚举


• 嵌套集


• 关闭表

如果您有PostgreSQL 8.4或更高版本，可以使用令人难以置信的查询，使事情变得非常简单。这是迄今为止最简单的解决方案，易于查询，易插入新记录，便于更新当前记录，易于删除记录，并具有参照完整性。所有其他解决方案都有难以解决的部分。

Adjency列表：

  CREATE TABLE类别（
 id SERIAL PRIMARY KEY，
 parent_id BIGINT，
类别TEXT NOT NULL，
 FOREIGN KEY（parent_id）参考类别（id）
 ）; 
 
 INSERT INTO类别（parent_id，category）VALUES（NULL，'vehicles'）; 
 INSERT INTO类别（parent_id，category）VALUES（1，'cars'）; 
 INSERT INTO类别（parent_id，category）VALUES（1，'motorcycles'）; 
 INSERT INTO类别（parent_id，category）VALUES（2，'SUV'）; 
 INSERT INTO类别（parent_id，category）VALUES（2，'sport'）; 
 INSERT INTO类别（parent_id，category）VALUES（3，'cruising'）; 
 INSERT INTO类别（parent_id，category）VALUES（3，'sport'）; 
 
 
 WITH RECURSIVE树（id，parent_id，category，category_tree，depth）
 AS（
 SELECT 
 id，
 parent_id，
类别，
类别AS category_tree，
 0 AS深度
 FROM类别
 WHERE parent_id IS NULL 
 UNION全部
 SELECT 
 c .id，
 c.parent_id，
 c.category，
 tree.category_tree ||'/'|| c.category AS category_tree，
 depth + 1 AS depth 
 FROM tree 
 JOIN类别c ON（tree.id = c.parent_id）
）
 SELECT * FROM tree ORDER BY category_tree; 
  

结果：

'1'，''，'车辆'，'车辆'，'0'

'2'，'1'，'cars' / cars'，'1'

'4'，'2'，'SUV'，'vehicle / cars / SUV'，'2'

'5'，'2'，'sport'，'vehicle / cars / sport'，'2'

'3 '，'1'，'摩托车'，'车辆/摩托车'，'1'

'6'，'3'，'巡航' /巡航'，'2'

'7'，'3'，'运动'，'车辆/摩托车/运动'，'2' b $ b

I'm designing a Database and I have some doubts on using Hierarchical datamodels in relational databases.

If I want to deal with categories, subcategories and parent categories it is possible not to use a Hierarchical datamodels in a relational database? By another words, it is possible to deal with categories, subcategories and parent categories using the relational way of doing things?

By the way, I'm using PostgreSQL.

Sorry for my bad english.

Best Regards,

解决方案

You have a couple of options to store hierachies:

• Adjacency List
• Recursive Query on a adjancy list
• Path Enumeration
• Nested Sets
• Closure Table

If you have PostgreSQL version 8.4 or later, you can use recusive queries to make things very easy. This is by far the easiest solution, easy to query, easy to insert new records, easy to update current records, easy to delete records and you have referential integrity. All other solutions have parts that are hard to solve.

Adjency list:

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, category) VALUES(1, 'motorcycles');
INSERT INTO categories(parent_id, category) VALUES(2, 'SUV');
INSERT INTO categories(parent_id, category) VALUES(2, 'sport');
INSERT INTO categories(parent_id, category) VALUES(3, 'cruising'); 
INSERT INTO categories(parent_id, category) VALUES(3, 'sport'); 


WITH RECURSIVE tree (id, parent_id, category, category_tree, depth) 
AS ( 
    SELECT 
        id,
        parent_id,
        category,
        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;

Result:

'1','','vehicle','vehicle','0'

'2','1','cars','vehicle/cars','1'

'4','2','SUV','vehicle/cars/SUV','2'

'5','2','sport','vehicle/cars/sport','2'

'3','1','motorcycles','vehicle/motorcycles','1'

'6','3','cruising','vehicle/motorcycles/cruising','2'

'7','3','sport','vehicle/motorcycles/sport','2'

这篇关于设计关系数据库 - 使用分层数据模型或避免它们？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！