Subscribe to Grim's Projects        RSS Feed
-----

How do Internet Boards Work?

Icon 4 Comments
As I've not been struck with any new ideas for my Mimesis ffdb, I think I'm going to start anew my GrimBB project. However, I'm redoing it from the ground up. Just the bare bones essentials of what a bulletin board (tailored to my particular requirements) needs.

In thinking about this I've come up with a hierarchy organization as follows listed in a top down view:
  • Category
  • Forum
  • Thread
  • Post

I will be storing pertinent data with regards to each one in particular tables. However, storing data in this case may or may not relate to organizing data. In order to organize the board I need to know in what order to list my categories and forums, and to which categories, forums, threads, and posts each belong to respectively.

My solution to this is to apply an id to each. So let's assume I had a bulletin board with one category, one forum, one thread, and one post. The ids would be something like the following:
  • cid=0
  • fid=0
  • tid=0
  • pid=0

This would form a sort of identifying chain (or tree) that would determine who belongs where. I've come up with two methodologies, but honestly, neither seems... good enough.

Firstly, I've thought about storing within the table that stores category data, all the forums that belong to it, within the table that stores forum data, all the threads that belong to it, and so on and so forth until you reach down to the posts. This would imply though that lots of data is being repeated and rewritten, and that if we deleted something at the top of the chain, the underlings would get lost.

Secondly, I've thought about storing only the category/forum/thread/post that the particular entity comes from, rather than which entities belong to it. A bottom up view as opposed to a top down view. This would mean though that I would have to traverse the ALL of the posts/threads/forums/categories when I make a "query" so that I could sort the entirety of the bulletin board. This seems wasteful in terms of time and I would say time is more of a problem when you have a limit to the amount of time your script can execute in.

Therefore, I am banking on my first approach unless someone here at Dream.In.Code can offer any alternative suggestions? Thanks for the help.

4 Comments On This Entry

Page 1 of 1

Martyr2 Icon

12 June 2010 - 04:26 PM
Well traditionally you would have your tables but then have "cross tables" aka "conjunction tables" that would show the relationship between each. Remember, that a forum is a child of a category and a thread is a child of a forum etc. The idea works like this...


We have two of our tables here. Each table describes one of the entities. Below we have two tables, category and forum.
category
---------
id (pk)
name


forum
---------
id (pk)
name



The trick here is that we then have a third table which joins the two tables together where each row is nothing but the primary keys of the others and possibly some extra data about the "relationship".

category_forum
-----------
id (pk)
category_id (fk)
forum_id (fk)
order_of_forum



This table above shows that each row has an id but that we also have the category id and the forum id. This describes which forum belongs to which category. Notice we have a field called "order_of_forum" which would be something like an integer describing what the forums order is in the category. Something you could sort on for instance. Here is some sample data for it...

1, 2, 9, 1  // Id 1, category id 2, forum 9 and is first forum of that category
2, 2, 8, 2  // Id 2, category id 2, forum 8 and is second forum of that category
3, 5, 1, 1  // Id 3, category id 5, forum 1 and is first in that category



From this data above we see that category 2 has two forums in it (9 & 8) which are ordered first and second in that category. We have only 1 forum in category 5 so it is first order. Hope you get it! :)
1

grimpirate Icon

13 June 2010 - 10:56 AM
Absolutely Martyr2. This is how I had achieved the same effect when I created GrimBB originally. If you're curious here are the actual values that would be stored in the category table: cid (pk), title, order. For the forums it would be: fid (pk), description, order. For the threads: tid (pk), title. For the posts: pid (pk), author, uid, timestamp, message, modded.

I see your suggestion in much the same vein as what I'm describing in my first methodology. The difference is you've taken the relationships and thrust them into a separate table. The way I'd thought of it was just throwing in an array into the actual categories table at the end. The table would change to: cid (pk), title, order, forums. Where "forums" would be an array. Each table would modify respectively to locate its children.

I guess what I'm asking is if perhaps there's a way to go about it other than this? As either in the way you or I suggest we're still employing the same "algorithmic" idea, which is to describe the parent/child relationship from a top down view; ableit the implementation is slightly different.
0

Martyr2 Icon

13 June 2010 - 08:59 PM
Well you can certainly add a field to say your forums table called "parent_category" and there list the category id. The threads would then have a field like "parent_forum" for the forum id. This will work but only somewhat. You really do lose a lot of flexibility here in that how do you have one thread be part of two separate forums for example (you never want to stuff multiple values into a single field). You will also find yourself updating the forums table to just change which category they belong to. The idea here is that you should really be only updating the forums table if you are actually modifying a forum attribute, not its relationship.

Again it will appear to work fine for you for awhile but you will quickly see the road blocks when it comes to writing any more complex relationship style queries. You will find yourself having to create complex nested queries just to get information on how records in one table relate to another.

I really do think the approach I outlined above, and what you have already pondered, is the best approach and offers the most flexibility for updating and editing later. :)
1

grimpirate Icon

13 June 2010 - 11:05 PM
Your approach makes sense using a relational database Martyr2, and SQL. However, my ffdb utilizes a heap method of storage and does not perform complex queries. Thus, the problem you describe does not translate.

Forgetting about the nature/implementation of the database, or even the use of a database altogether, the barebones question here is, in a tree structure (something like a B-tree), how do you relate nodes to one another? I've only come up with these two ways. Either the node knows which node it stemmed from, or it knows which nodes stem from it.

EDIT: This is an article that better illustrates the algorithmic approach of interest to me.
0
Page 1 of 1