postgresql - Design Relational Database - Use hierarchical datamodels or avoid them? -

i'm designing database , have doubts on using hierarchical datamodels in relational databases.

if want deal categories, subcategories , parent categories possible not use hierarchical datamodels in relational database? words, possible deal categories, subcategories , parent categories using relational way of doing things?

by way, i'm using postgresql.

sorry bad english.

best regards,

you have couple of options store hierachies:

• adjacency list
• recursive query on adjancy list
• path enumeration
• nested sets
• closure table

if have postgresql version 8.4 or later, can use recusive queries make things easy. far easiest solution, easy query, easy insert new records, easy update current records, easy delete records , have referential integrity. other solutions have parts hard 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 categories(parent_id, category) values(null, 'vehicles'); insert categories(parent_id, category) values(1, 'cars'); insert categories(parent_id, category) values(1, 'motorcycles'); insert categories(parent_id, category) values(2, 'suv'); insert categories(parent_id, category) values(2, 'sport'); insert categories(parent_id, category) values(3, 'cruising');  insert categories(parent_id, category) values(3, 'sport');    recursive tree (id, parent_id, category, category_tree, depth)  (      select          id,         parent_id,         category,         category category_tree,         0 depth      categories      parent_id null  union      select          c.id,         c.parent_id,         c.category,         tree.category_tree || '/' || c.category category_tree,         depth+1 depth      tree          join categories c on (tree.id = c.parent_id)  )  select * tree order 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'