Design Relational Database - Use hierarchical datamodels or avoid them?

Question

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,

See this question for the list of options, further reading, and notes costs of actions such as ancestor retrieval and node insertion times: http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database — orangepips, Feb 07 '11 at 11:57

score 18 · Accepted Answer · edited Jun 20 '20 at 09:12

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'

+1 for pointing out recursive queries (which are supported by a wide range of DBMS nowadays) — , Feb 05 '11 at 18:47
Very good example of how to retrieve a hierarchy and rows' depth. The depth can then be used to do a topological sort (ORDER BY depth). — Thibaut D., Aug 06 '12 at 15:35

score 1 · Answer 2 · answered May 15 '13 at 04:50

1

If you're using Postgres, you can store the hierarchy in an array as a materialized path.

You also benefit from GIN indexing with this approach, which in my experiments has had better performance than a recursive query.

answered May 15 '13 at 04:50

Adam Sanderson

470
5
7

how would you create a GIN index for such a column? – Carlos Melo Jul 07 '16 at 17:56
1

The same way you would create any old index: `CREATE INDEX name ON table USING gin(column);` (https://www.postgresql.org/docs/9.5/static/textsearch-indexes.html) – Adam Sanderson Jul 22 '16 at 04:38

score -1 · Answer 3 · answered Feb 05 '11 at 15:42

What do you mean by "hierarchical data model"? If you just mean modelling a hierarchy in a relational or SQL database then that's a perfectly common and reasonable thing to do. There is a significant quantity of database literature on the subject of how to model hierarchies relationally. There is nothing "non relational" about doing that.

However, the term Hierarchical Data Model more usually refers to a type of DBMS (not an RDBMS or SQL DBMS). Hierarchical / Network / Graph DBMSs operate on different principles to RDBMSs - they use navigational or pointer-based models rather than the relational model. The relational / SQL model has largely (but not entirely) superseded that type of DBMS. Unless you happen to be using a hierarchical type of DBMS then you don't need to worry about it.

Design Relational Database - Use hierarchical datamodels or avoid them?

3 Answers3

Linked