PostgreSQL - Multiple Parent Corregido

create table category (

  id serial primary key,

  name text not null,

  parent_id int null references category(id)

);

create table brand (

  id serial primary key,

  name text not null unique

);

create table brand_category (

  brand_id int not null references brand(id),

  category_id int not null references category(id),

  primary key (brand_id, category_id)

);

/* the product specification, not the actual product asset instance: */

create table product (

  id serial primary key,

  name text not null,

  brand_id int not null references brand(id)

);

create table product_category (

  product_id int not null references product(id),

  category_id int not null references category(id),

  primary key (product_id, category_id)

);

/* null parent_id means top-level category */

insert into category values

(1, 'Electronics', null),

(2, 'Cameras', null),

(3, 'Smart Phones', 1),

(4, 'Tablets', 1),

(5, 'Laptops', 1),

(6, 'Headphones', 1);

insert into brand values

(1, 'Samsung'),

(2, 'Nokia'),

(3, 'Apple');

/* samsung brand in both smartphones and cameras: */

insert into brand_category values

(1, 3),

(1, 2),

(2, 3),

(3, 3);

insert into product values

(1, 'Samsung Model X', 1);

/* add samsung model x as a smart phone: */

insert into product_category values

(1, 3);

-- Suppose we want to traverse upwards from product to see the full classification. The query would look like:

WITH RECURSIVE product_chain (id, path, parent_id, category_name) AS (

   SELECT c.id, c.name, parent_id, c.name

     FROM category c

    WHERE id in (SELECT category_id FROM product_category

                  WHERE product_id = 1)

UNION ALL

   SELECT c.id, c.name || '/' || pc.path, c.parent_id, c.name

     FROM category c

     JOIN product_chain pc ON pc.parent_id = c.id

 )

 SELECT p.*, pc.path

   FROM product p

   JOIN product_chain pc ON pc.parent_id IS NULL

  WHERE p.id = 1;

--Similarly we can generate the entire category tree with something like:

WITH RECURSIVE cat_tree (id, level, path) AS (

   SELECT c.id, 1, c.name

     FROM category c WHERE parent_id IS NULL

UNION ALL

   SELECT c.id, ct.level + 1, ct.path || '/' || c.name

     FROM category c

     JOIN cat_tree ct ON c.parent_id = ct.id

 )

 select c.name, ct.path, ct.level

   FROM category c

   JOIN cat_tree ct ON c.id = ct.id;