Question 330357 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Part List Layout. How to implement a realtionship with a table itself?

so I want to build a intventory Management database.

My schema looks like this:

Database Schema

An Itemclass (screwdriver, hammer, measuring device,…) can have many items (screwdriver1, screwdriver2,…)

Everything is fine and working as expected. But now i want to create a feature for part lists. So basically I want to create a Itemclass (e.g Tools for XY) which include other Itemclasses (screwdriver, hammer,…)

I might overthink this, but i dont find a working solution. Anybody got some tips?

This is the result I actually expect:

My Thoughts

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    While studying the answer from Bahattin Ungormus, and looking for self-erences if found my solution.

    Model:

    ItemClass.belongsToMany(ItemClass, {as: "Children", through: "ItemClassChildren", timestamps: false })

    And the query for this:

        const results = await ItemClass.findAll({
    include: {
        model: ItemClass,
        as: "Children",
    },

  2. 0

    If you add a parent_id field to your item_class table that is a foreign key to the id field of item_class table, you can easily achieve this. When you want to add an item_class under another just set the child item_class’s parent_id to its parent’s id.

    You can then use ltree in PostgreSQL or CONNECT BY in Oracle to create hierarchical queries. Here is a source for ltree: POSTGRESQL: SPEEDING UP RECURSIVE QUERIES AND HIERARCHICAL DATA

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search