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

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"
    }]
}]

CodePudding user response：

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.)