Design Relational Database - Use or avoid hierarchical data?

Question

I am developing a database, and I have some doubts about using hierarchical data in relational databases.

If I want to deal with categories, subcategories, and parent categories, can I not use hierarchical datamodels in a relational database? In other words, can you deal with categories, subcategories, and parent categories using the relational way of doing something?

By the way, I'm using PostgreSQL.

Sorry for my bad english.

Regards,

Answer 1

You have several options for storing hierarchies:

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, 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;

:

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

'2', '1', '', '/', '1'

'4', '2', '', ' //', '2'

'5', '2', '', '//', '2'

'3', '1', '', ' /', '1'

'6', '3', '', ' //', '2'

'7', '3', '', ' //', '2'

Answer 2

Postgres, .

GIN , , .

Answer 3

" "? SQL- , . , . "" .

" " ( SQL). // - , . /SQL- ( ) . , .