0

Assuming there is a gigantic organization with a crazy way to manage. Each employee has one or multiple managers, managers are employees themselves who have one or multiple managers on top.

employee table

| id       | name           |managers_id|
| -------- | -------------- |-----------|
| 1        | Smith          |   5,6     |
| 2        | Matt           |    1      |
| 3        | Bob            |    1,2    |
| 4        | Adam           |    1,3    |
| 5        | Suzi           |    6      |
| 6        | Emily          |    23,25  |
| ...      | ...            |    ...    |

It is a one-way management chain, no loops, meaning it goes A-B-C-D, A-a-b-C-D etc, no such case as A-B-C-D-A

The query is to get the management chains, say C has two management chains on top:

A-B-C
A-a-b-C

C also has one chain below:

C-D

The level of C along the chains is not a matter.

In theory, there is no limitation on the number of levels, the chain can keep going indefinitely. I was thinking about 'inheritance' but probably it is not the solution.

Any tips on how to design this postgres dababase, please? Thank you.

rock-paper
  • 93
  • 1
  • 9
  • 1
    you should take a look at Recursive Queries https://www.postgresqltutorial.com/postgresql-recursive-query/ – Tobias S. Oct 27 '21 at 08:39
  • 1
    First normalize your schema. See ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) (Spoiler: Yes, it is.). – sticky bit Oct 27 '21 at 08:43
  • If "each employee has one or multiple managers, managers are employees themselves who have one or multiple managers on top." as you said, you will have loops on chains. – nachospiu Oct 27 '21 at 15:43

0 Answers0