how to relate many rows to a row from the same table postgresql?

Question

consider Amazon product category architecture (one product may have 7 parent categories another might have 2). I want to build the same thing using Postgres.

A: Is there any scaleable logical way to do this? or I must consider using a graph database. ps: the project will not be AMAZON BIG. this is a monolith project, not a microservice.

B: my thoughts are that I should have a field named parent_categories in my category table which is an array of UUIDs of categories then a field named category_id for the products table that is related to the last category parent would work. something like this:

  CREATE TABLE categories (
    id UUID PRIMARY KEY NOT NULL DEFAULT gen_random_uuid (),
    name VARCHAR NOT NULL,
    parent_categories UUID[]
);
CREATE TABLE products (
    id UUID PRIMARY KEY NOT NULL DEFAULT gen_random_uuid (),
    name VARCHAR NOT NULL,
    category_id UUID[],
    CONSTRAINT fk_category FOREIGN KEY(category_id) REFERENCES categories(id)
);

the problem is with joining the chained categories I'm expecting a result like the below when fetching categories (I'm using node.js) and I don't know how to join every element of that array.

categories: [{
    id: "id",
    name: "name",
    parent_categories: [{
        id: "id",
        name: "name"
    }]
}]

J_H · Accepted Answer · 2022-12-13T18:17:07.103

This question is about relational theory.

You have a pair of tables containing id and name, that's lovely. Discard the array attributes, and then

CREATE TABLE product_category (
    product_id   UUID   REFERENCES products(id),
    category_id  UUID   REFERENCES categories(id),
    PRIMARY KEY (product_id, category_id)
)

Now you are perfectly set up for 3-way JOINs.

Consider adopting the "table names are singular" convention, rather than the current plural-form names.

Add a parent_id column to categories, so the table supports self-joins. Then use WITH RECURSIVE to navigate the hierarchical tree of categories. (Classic example in the Oracle documentation shows how manager can be used for emp self-joins to produce a deeply nested org chart.)

The tables contain more fields, I didn't bring them up for simplicity. I KNOW how to create a many-to-many relation. I want the categories to be nested. eg: electronic devices -> computers -> laptops -> 16 inc laptops -> apple products -> mackbook pro — kouroshtajalliepour, Dec 13 '22 at 17:58

how to relate many rows to a row from the same table postgresql?

1 Answers1