0

My database (version: 10.5.15-MariaDB-o+deb11u2) has a table "positions". Every position is either a "parent", "child" or none of both (so i do not need a hierarchical database, since the hierachy of my case has only a level of 2). If it is a parent, then this position has a connection to 1 to n "child" positions. If a position is a "child", it has a connection to exactly one "parent" position. If the position is none of both, it is just on its own. Should i split this table in three tables and create a relation of parent/child? Later i will want to filter a query and every time a position is kept AND a parent (or a child), then i want to keep all its children (or the parent). Basically i want to keep the whole group of parent/children if only one of all of these is kept (kept after filtering e.g. if the position_id is prime or what ever). My problem is, that i can only think about a very bad filter method. Example: I filtered the positions and kept all prime position_id positions. (Problem comes now...) If a position is child (or parent) i need the parent (children). So i would need to query again. Plus i would have to make sure, that i don't get duplicate positions. In the same example i might already kept the parent position (or children positions). How can i get an effective selection? I think my just described procedure is not effective. Also it seems overcomplicated, can i maybe filter all positions with one "clean" filter at once? Such that also the parent/children are kept after filtering?

My last post (about the same problem) was associated with this post: What are the options for storing hierarchical data in a relational database? What i found there, didn't seem to fit.

This is how an example table looks like:

| position_id | example_value  | parent   | children       |
| ----------- | -------------- | -------- | -------------- |
| 1           | 4              | null     | [ ]            |
| 2           | 5              | 3        | [ ]            |
| 3           | 12             | null     | [2, 4]         |
| 4           | 5              | 3        | [ ]            |
| 5           | 12             | 6        | [ ]            |
| 6           | 11             | null     | [5]            |

If i filter with example_value > 10, then i want to return positions with id 2, 3, 4, 5 and 6, while positions with id=2 and id=4 are added, because they are children of position with id = 3.

Timo
  • 23
  • 5
  • *Every position is either a "parent", "child" or none* I.e. your hierarchy may have only 2 levels, is it? *or none of both* This is a parent without childs simply, i.e. not separate type but special subtype of parent type. – Akina Jun 19 '22 at 14:36
  • yes, it's only a level of 2, i will add that. Also "parent without childs" is correct too. – Timo Jun 19 '22 at 14:37
  • Now clarify what is your question, it is not clear now. Looking the text I predict that you need in storing structure which allows to select effectively the whole group (parent + all its childs) when any one row (either parent or child) is given, is it? – Akina Jun 19 '22 at 14:41
  • Edited, thank you! I need an effective way to select the positions. Inserting and updating can be much slower. And yes, if at least one of a children <-> parent group is kept, i want to keep the whole group. – Timo Jun 19 '22 at 14:46
  • Show actual example data and desired query results. If you want a parent's data returned with a child that could be either as additional columns or an additional row. But if you want children returned with a parent, that would need to be as additional rows. Do you want to know if a row is being returned because it was selected or only because it is a parent or child? Do you want to return whether a row is a child or a parent? Or if a parent, whether it has children or how many? Lots of details including questions I haven't thought of that could make a difference in how best to store it – ysth Jun 19 '22 at 16:28
  • What about keep it simple: `create table positions (position_id int primary key, parent_id int, foreign key (parent_id) references positions (position_id));` If parent_id is not null, then that row is a child. If parent_id is null, then it's either a parent itself, or else if no child references it, then it's standalone. – Bill Karwin Jun 19 '22 at 16:53
  • I'd recommend to look at the next structure: (node_id, tree_id, is_root). And the constraint which does not allow more than one root per tree. – Akina Jun 19 '22 at 17:13
  • @ysth Knowing why a position was selected is (probably) not necessary. I do need to know if a position is a parent or a child (or none). I don't need to know, how many children a parent has. I will later group them on my own. – Timo Jun 19 '22 at 17:30
  • Look at this fiddle: https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=7ecdd5500cdf86f0e7281ac5652120b8 – Akina Jun 19 '22 at 17:33
  • @BillKarwin That sounds good, but what condition do i pass a filter, that a position is kept, because and other position is kept? – Timo Jun 19 '22 at 17:34
  • Is the problem that you don't know how to use `JOIN`? – Bill Karwin Jun 19 '22 at 17:37
  • @BillKarwin Yes, i don't know `JOIN`. I just read it can merge tables like two (or even more?) sets. Which tables should i then join together? Since there is only the positions table, do i join two filter queries of the positions table? – Timo Jun 19 '22 at 17:44
  • @Akina Do you think that would always result in two separate filter queries, where i first find the directly selected positions and then (with the id's of these positions) find all trees? – Timo Jun 19 '22 at 17:56
  • o_O Where you have found **TWO** queries??? – Akina Jun 19 '22 at 18:18
  • @Akina How would you do it in one? For example if i select all positions with `example_value > 10` and after that find all corresponding extra positions. – Timo Jun 19 '22 at 18:36
  • Please learn what JOIN is. Now you simply cannot understand the answers, it seems. – Akina Jun 19 '22 at 18:54
  • Thanks a lot, especially for this big example on fiddle :) – Timo Jun 19 '22 at 19:03

0 Answers0