Проектировать реляционную базу данных — использовать иерархические модели данных или избегать их?

У вас есть несколько вариантов хранения иерархий:

• Список смежности
• Рекурсивный запрос в списке смежности
• Перечисление пути
• Вложенные наборы
• Таблица закрытия

Если у вас PostgreSQL версии 8.4 или выше, вы можете использовать рекурсивные запросы сделать все очень просто. Это, безусловно, самое простое решение, которое легко запрашивать, легко вставлять новые записи, легко обновлять текущие записи, легко удалять записи, и у вас есть ссылочная целостность. Во всех других решениях есть части, которые трудно решить.

Список смежности:

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'

Если вы используете Postgres, вы можете сохранить иерархию в массиве в виде материализованного пути.

Вы также получаете выгоду от индексации GIN с этим подходом, который в моих экспериментах показал лучшую производительность, чем рекурсивный запрос.

Что вы подразумеваете под «иерархической моделью данных»? Если вы просто имеете в виду моделирование иерархии в реляционной базе данных или базе данных SQL, то это совершенно обычная и разумная вещь. Существует значительное количество литературы по базам данных о том, как реляционно моделировать иерархии. В этом нет ничего «нереляционного».

Однако термин Иерархическая модель данных чаще относится к типу СУБД (а не к РСУБД или СУБД SQL). Иерархические/сетевые/графовые СУБД работают на других принципах, чем РСУБД – они используют навигационные модели или модели на основе указателей, а не реляционную модель. Реляционная модель/модель SQL в значительной степени (но не полностью) заменила этот тип СУБД. Если вы не используете СУБД иерархического типа, вам не о чем беспокоиться.