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,

Answer 1

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'

Answer 2

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.