Programming Forums > Web Development and Design > Other Web Development Languages

jonyzz · Aug 31st, 2005, 3:53 AM

Hi,
I was wondering how for example is the structure of this forum represented in a relational database ? (new threads, replies, etc)
If you can offer some links, I will be thankful.
greetings

Arevos · Aug 31st, 2005, 5:06 AM

Probably something like:

(Toggle Plain Text)

CREATE TABLE threads (
  id int(11) NOT NULL auto_increment,
  parent_id int(11) NULL,
  author_id int(11) NOT NULL,
  contents TEXT,
  PRIMARY KEY (id),
  KEY author_id
);

The parent_id field stores the id parent node, allowing for a tree structure.

However, if one wanted to display a tree structure of all the nodes at once (this forum just displays the titles of the threads), one would need one SQL query per node - not a very efficient way of doing things. I came up against this problem when designing a database-driven finance package for myself. In the end, I decided to live with the inefficiency as I had only a small number of nodes in my tree structure.

There are more complicated and more efficient ways to store tree data in a relational database, but the above method I outlined is the simplest I know of.

Edit: These forums may take a slightly different approach, since threads here are one-directional. They don't branch off into sub-threads. So the database structure might look more like:

(Toggle Plain Text)

CREATE TABLE threads (
  id int(11) NOT NULL auto_increment,
  author_id int(11) NOT NULL,
  contents TEXT,
  PRIMARY KEY (id),
  KEY author_id
);

CREATE TABLE followups (
  id int(11) NOT NULL auto_increment,
  parent_id int(11) NULL,
  author_id int(11) NOT NULL,
  contents TEXT,
  PRIMARY KEY (id),
  KEY author_id,
  KEY parent_id
);

When a person starts a thread, an entry is added to the 'threads' table. When a person replies to a thread, an entry is added to the 'followups' table.

jonyzz · Aug 31st, 2005, 5:18 AM

Thank you Arevos. I couldn't find anything specific on that topic in the Internet. And that's what I needed most: sample code. Seems like the Internet can't replace a human's shared experience.

Aug 31st, 2005, 3:53 AM	#1
jonyzz Programmer Join Date: Aug 2005 Location: null Posts: 40 Rep Power: 0	Do you know an algorithm that presents tree structure within a database ? Hi, I was wondering how for example is the structure of this forum represented in a relational database ? (new threads, replies, etc) If you can offer some links, I will be thankful. greetings

Aug 31st, 2005, 5:06 AM	#2
Arevos Programming Guru Join Date: Aug 2005 Location: England Posts: 1,499 Rep Power: 5	Probably something like: (Toggle Plain Text) CREATE TABLE threads ( id int(11) NOT NULL auto_increment, parent_id int(11) NULL, author_id int(11) NOT NULL, contents TEXT, PRIMARY KEY (id), KEY author_id ); CREATE TABLE threads ( id int(11) NOT NULL auto_increment, parent_id int(11) NULL, author_id int(11) NOT NULL, contents TEXT, PRIMARY KEY (id), KEY author_id ); The parent_id field stores the id parent node, allowing for a tree structure. However, if one wanted to display a tree structure of all the nodes at once (this forum just displays the titles of the threads), one would need one SQL query per node - not a very efficient way of doing things. I came up against this problem when designing a database-driven finance package for myself. In the end, I decided to live with the inefficiency as I had only a small number of nodes in my tree structure. There are more complicated and more efficient ways to store tree data in a relational database, but the above method I outlined is the simplest I know of. Edit: These forums may take a slightly different approach, since threads here are one-directional. They don't branch off into sub-threads. So the database structure might look more like: (Toggle Plain Text) CREATE TABLE threads ( id int(11) NOT NULL auto_increment, author_id int(11) NOT NULL, contents TEXT, PRIMARY KEY (id), KEY author_id ); CREATE TABLE followups ( id int(11) NOT NULL auto_increment, parent_id int(11) NULL, author_id int(11) NOT NULL, contents TEXT, PRIMARY KEY (id), KEY author_id, KEY parent_id ); CREATE TABLE threads ( id int(11) NOT NULL auto_increment, author_id int(11) NOT NULL, contents TEXT, PRIMARY KEY (id), KEY author_id ); CREATE TABLE followups ( id int(11) NOT NULL auto_increment, parent_id int(11) NULL, author_id int(11) NOT NULL, contents TEXT, PRIMARY KEY (id), KEY author_id, KEY parent_id ); When a person starts a thread, an entry is added to the 'threads' table. When a person replies to a thread, an entry is added to the 'followups' table.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Aug 31st, 2005, 5:18 AM	#3
jonyzz Programmer Join Date: Aug 2005 Location: null Posts: 40 Rep Power: 0	Thank you Arevos. I couldn't find anything specific on that topic in the Internet. And that's what I needed most: sample code. Seems like the Internet can't replace a human's shared experience.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Programming Forums > Web Development and Design > Other Web Development Languages

Do you know an algorithm that presents tree structure within a database ?