0

I am trying to create a table or sets of tables which will allow me to easily navigate through recorded entities hierarchically. I should also be able to easily migrate a certain parts of the chain from one parent to another when necessary.

organisation parent length other org data
1 NULL 0 note1
2 NULL 0 note2
3 2 1 NULL
4 2 1 NULL
5 3 2 some stuff
6 4 2 etc
7 NULL 0 etc
8 2 1 etc

I was considering the closure table / bridge table method due to its overall easiness of implementation and maintenance, but according to this answer, recursive CTE is better: Closure table equivalent for graph structures in SQL

Can you advise on how to do build the table and how to migrate/update data easily? Is the length column necessary? It can be deduced, but it might save processing time when stored at the time of creation/updating. The levels of the hierarchy should theoretically be unlimited.

I would like to use this method for organisations hierarchy and also separately for tasks and subtasks, inventory assets and parts, as well as for comments and subcomments left on issues raised. Is there anything else I should add? Can you also provide some insight into other types of graphs and where I can read more about them? Thank you.

Samuel Hyde
  • 13
  • 4
  • What do you need a `length`column for? Does it serve any purpose? – The Impaler Jun 15 '22 at 17:31
  • From the point of view of modeling an organization entity is different from a task, subtask, asset, part, comment, and subcomment. I would consider them different, separate entities; for example the `other org data` is clearly an attribute of organization, but it's not an attribute for the other entities. – The Impaler Jun 15 '22 at 17:33
  • The length column was just advised in the closure_table method, but it might be redundant here. I have updated my question, there will obviously be separate tables for organisations, tasks, assets, comments, but I think they follow the same hierarchical logic. – Samuel Hyde Jun 15 '22 at 17:49
  • In terms of flexibility, this is the best structure; moving a whole branch under a diferent parent is trivial. Now if you need to search inside sub-branches, then a computed/redundant path column can speed up searches. – The Impaler Jun 15 '22 at 17:52
  • Would you be able to drop a full answer on how to design the hierarchy please? I am reading a lot of theory about these things, but there are hardly any queries indicating exactly how the tables look like and how to make changes. The SQL Antipatterns book advises for closure table, but I am reading that CTEs are better, yet I cannot tell the right way since I am not seeing the queries or the tables drawn to figure out how to use them – Samuel Hyde Jun 15 '22 at 18:02

0 Answers0