Okay, this is my first try after reading for hours and hours. I got a hierarchical data-structure and found this topic HAS BEEN ASKED before and also many people found solutions for their problems.
The problem i have does not seem to be answered yet or i just cant find it. As already said i spent quite a while asking my glass-google and especially SO.
To start off, i have a defined set of Tables (3 in my case) to represent a forum, which are called [forums], [threads], [posts].
The forums and threads are mostly just theoretical constructs to maintain the structure. From the point of normalization (thanks, school...) this seems to make sense.
The posts on the other side contain the actual information, which i want to retrieve.
For simplicity I will skip fields that are not relevant for the data-structure.
forums then would have the fields [id:PK], [parent:FK -> forum.id] and [name].
threads would have [id:PK], [parent:FK -> forum.id] and [topic].
posts at last would have [id:PK], [parent:FK -> thread.id], [content], [date].
What im trying to achieve is
- Find the post with the latest date (I store them as timestamps) inside any given forum (by id).
- Find the number of posts below any given forum, including those in forums below the given and all threads inside them (recursively).
I tackled this problem before but ended up using the underlying programming language to define the recursive logic which leads to high amount of queries and thus hurts performance easily.
I read about tree traversals in single tables but am not sure if there is a way to extend this to multiple tables.
I think the way to go are stored procedures and i started reading in this, but it seems like i can barely wrap my head around this topic. If this IS the solution, i would be grateful for some hints, how to start this. A working solution would be nice as well.
Please note: IF you post a working solution for this, please, i beg you, dont just throw some SQL at me, try to explain how this works. Nothing hurts more than using code, you dont understand. Also i would need to come back every time i need something like that.
So far, thanks in advance and if you need any more information, feel free to tell me!
EDIT: A graphical representation of what i have.
Also I prepared some sample data for explanation:
forums:
+----+--------+---------+
| id | parent | name |
+----+--------+---------+
| 1 | NULL | Forum 1 |
| 2 | 1 | Forum 2 |
| 3 | 1 | Forum 3 |
| 4 | 2 | Forum 4 |
+----+--------+---------+
threads:
+----+--------+----------+
| id | parent | topic |
+----+--------+----------+
| 1 | 1 | Thread 1 |
| 2 | 2 | Thread 2 |
| 3 | 2 | Thread 3 |
| 4 | 3 | Thread 4 |
| 5 | 3 | Thread 5 |
| 6 | 4 | Thread 6 |
+----+--------+----------+
posts:
+----+--------+----------------+------+
| id | parent | content | date |
+----+--------+----------------+------+
| 1 | 1 | Some Content 1 | 1 |
| 2 | 1 | Some Content 2 | 2 |
| 3 | 2 | Some Content 3 | 3 |
| 4 | 3 | Some Content 4 | 4 |
| 5 | 4 | Some Content 5 | 5 |
| 6 | 4 | Some Content 6 | 6 |
| 7 | 6 | Some Content 7 | 7 |
+----+--------+----------------+------+
Getting the latest post below the Forum 1 would be rather trivial, but getting the latest post for example from Forum 3 is one thing i struggle with (in this case the last post would be the post with id = 6).
Also the number of posts below forum 1 would be the total number of posts, but getting the number of posts below forum 2 for example would result in 6. This, to my understanding, needs recursive traversal of the data, though im unsure how to do this. One of the comments mentioned something similar regarding a single table, but i would be grateful for some kick in the right direction how to adapt this to multiple tables.
