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.

Improve this question
17
  • 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. Commented Jun 19, 2022 at 14:36
  • yes, it's only a level of 2, i will add that. Also "parent without childs" is correct too. Commented Jun 19, 2022 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? Commented Jun 19, 2022 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. Commented Jun 19, 2022 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 Commented Jun 19, 2022 at 16:28
Related questions
1
Question about Hierarchical data
4
Parent Child Relationships PHP/MYSQL
13
Hierarchical queries in MySQL
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.