0

Hello people of the coding world.

I'm working on a small web development project and have an idea but am unsure whether or not it is applicable in pure MySQL or whether I will need to utilise some PHP code to make it work the way I need it to work. I'm not against using PHP, but feel that there is a better solution to splitting things.

I have three database tables. I'll call them A, B, and C. Tables B and C contain items that can have an item_parent. Any item in C is a child of at least one item in B, and any item in B is a child of at least one item in A. Each item, in all three tables, will have an item_id and item_level assigned to it. The ID is just a unique identifier, and serves no other purpose. The level is used to determine visibility to the viewer, as the viewer must have a level equal to or above the required level in order to view an item.

My query is that I want to be able to get the access level required in one query. This query would need to check all parents of items in table C and B for their access levels. If an item has multiple parents, it must check both and return the lowest of the two. If you can see at least one parent, you can see the child provided its level is not too high, but if you cannot see any parent then you cannot see the child regardless of child's level.

In order to get the multi-parenting system functional I've used the JSON data type for item_parent. It allows me to set a variable number of values without much change to the code used to assign or read those same values. item_id and item_level are of types INT and SMALLINT respectively.

Some sample data:

table_a
item_id | item_level
1       | 1
2       | 4
3       | 6

table_b
item_id | item_level | item_parent
1       | 2          | [1,2]
2       | 3          | [2]
3       | 7          | [1,3]

table_c
item_id | item_level | item_parent
1       | 0          | [3]
2       | 5          | [1]
3       | 8          | [1,2]

The query I want would need to run checks on the parents and return the lowest of those, then the highest of the returned data sets. Each item_parent refers to an item_id in the table directly above it.

The way I imagine this running (on table_c.item_id=1) is as follows:

  1. table_c.item_id=1 parents are checked. The lowest level of all parents of an item is returned. Assuming we are checking the first item, the number returned would be 7, as we are checking table_b.item_id=3 (the only defined parent of table_c.item_id=1).
  2. table_b.item_id=3 parents are checked, and again lowest value returned. We are only checking table_b.item_id=3 as it is a parent of table_c.item_id=1 and we are following the tree up. This item has two parents, so both are checked and the lowest level between them is returned. This would return a 1.
  3. Now that we've done the level checking, we return the highest level of all returned results including the level of table_c.item_id=1. The numbers we are working with are 0, 1, and 7. The returned level would be 7, as it is the highest level of all those returned.

In the above situation, if your viewing level was 4 then you would be unable to see table_c.item_id=1 due to the level of table_b.item_id=3. However, if your level was 7 or above, you would be able to see it.

I've a few questions on how to make this work.

  • Can this be done by pure MySQL queries, or will I need to break things into multiple queries that are run separately with PHP processing the item_parent JSON data to prepare the next query? If so, what would the query be?
  • Is my use of JSON as the item_parent data type appropriate, or is there a data type that better for storing this information?

Apologies if I have not worded this very well. In my head, the idea is solid, but when it comes to explaining it I'm not sure what words to use or if I've conveyed my thoughts properly. Please let me know if edits are needed or if things are unclear.

Thank you in advance to those to take the time to read and work on a potential solution.

Xyrhen
  • 1
  • 1
    Looks like the smartest thing you could do is rethink the database design. Instead of Table B having one row `3, 7, [1,3]` make two rows: `3, 7, 1` and `3, 7, 3`. Then you can use mysql MIN() and MAX() with some joins to retrieve the data you need. – Michel Nov 22 '20 at 11:34
  • I agree with Michel. [Here's a read](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) on why this pattern is bad. – El_Vanja Nov 22 '20 at 12:08
  • Having read over your comments and part of the link you provided El_Vanja, and having had a think, I did eventually arrive at the same conclusion: the database design needed a bit of rethinking. Thanks for the feedback and time :) – Xyrhen Nov 24 '20 at 05:09

1 Answers1

0

Following some reading of some comments and resources I was linked to, I determined that it would be more appropriate to rethink part of my database design as there is no pure MySQL way to do what I was hoping for with the existing structure.

Since I've rethought part of the database, this question becomes moot.

Xyrhen
  • 1