The most popular stabilized structure of database for any forum would be to store all posts inside a table in which a column define the thread number. To show a thread, we have to perform
WHERE clause for that thread number (sorted by date). Within this situation, we cope with a really lengthy table for each query.
Could it be really the easiest method to store posts and threads for any forum?
It's more difficult for any Q&A like stockexchange projects, as with accessory for solutions (i.e. just like posts in forum), each answer might have comments.
What's the most effective database structure for any large website like stackoverflow?
It's not even close to being as huge as SO, however in my application, the model I chos ended up being to have each publish have
- a FK to the parent publish (root posts have null within this column)
- a FK towards the root publish (root posts get their own ID within this column, that is a little awkward to place new root posts, but assists in queries)
- a FK for their forum
This enables getting easily
- the main posts of the forum (trying to find posts having a null parent)
- all of the messages inside a thread at the same time (searching all of the posts getting confirmed root)
- showing the thread in flat mode (per date) or perhaps in tree mode (since each publish knows its parent)
- the final posts inside a given forum (searching the most recent publish arranged by forum)
A quicker method to totally a combination from a trie along with a nested set i.e. celko-tree. For instance you should use the thread id to construct a ternary trie with a lot more right and left id for that nested set query. The complicated factor would be to place or remove out of this tree. Or have a kart-trie having a hash-key to find right and left. However I think a nested set alone might be sufficient because then your id is inorder.
should you accept forums are another type of tree structure - I'd suggest nested set or closure table to produce the associations between posts. nested set closure table - could not find the best link for closure - sorry!
Simply storing thread and parent id's could make things just a little tricky...