Inside Technique : Adding Discussion Forums : Discussion Forum Schema

The discussion forum consists of two tables (three if you include the user membership table):

t_user Table

This is the user membership table from the first article. This table stores information about each registered user. The most important field in the t_user table is u_id which represents an individual user. This field is used to associate a user with a particular message.

t_forums Table

This table contains the list of message forums. Adding new forums is as simple as adding new records to this table.

Create Table dbo.t_forums
  (
  forum_id int Not Null Identity (1, 1),
  forum_name varchar(50) Not Null,
  forum_desc varchar(255) Not Null,
  forum_update datetime Null
  )
Go
Alter Table dbo.t_forums Add Constraint
  PK_t_forums Primary Key Nonclustered
  (
  forum_id
  )
Go

t_topics Table

This table contains the list of messages. Each message is associated with a specific forum through the forum_id field. We designed this table so that it can be extended to support threaded messages. However, for this example, we limit all discussions to a single thread (just like the SiteExperts.com discussion forums).

This table also contains two triggers for handling message deletions, message counts and last updates. We will explain the triggers in more detail when we walk through the stored procedures.

Column Name	Data Type (length)	Special Notes
forum_id	Integer	The forum the message is posted in.
topic_id	Integer	Auto-incrementing primary key representing the individual message.
u_id	Integer	The ID of the user who posted the message.
topic_name	String (200)	The name of the discussion thread.
topic_create	DateTime	The time and date the message was posted.
topic_update	DateTime	The time and date the thread was responded to. Only updated for the original message of the thread.
topic_count	Integer	The number of replies to the message.
topic_parent	Integer	The topic_id of the parent message. Defaults to -1 for new threads.
topic_message	Text	The actual message.

Create Table dbo.t_topics
  (
  forum_id int Not Null,
  topic_id int Not Null Identity (1, 1),
  u_id int Not Null,
  topic_name varchar(200) Null,
  topic_create datetime Not Null Constraint DF_t_topics_topic_date Default (getdate()),
  topic_update datetime Not Null Constraint DF_t_topics_topic_update Default (getdate()),
  topic_count int Not Null Constraint DF_t_topics_topic_count Default (0),
  topic_parent int Not Null Constraint DF_t_topics_topic_parent Default ((-1)),
  topic_message text Null
  )
Go
Alter Table dbo.t_topics Add Constraint
  FK_t_topics_t_forums Foreign Key
  (
  forum_id
  ) References dbo.t_forums
  (
  forum_id
  )
Go
Alter Table dbo.t_topics Add Constraint
  FK_t_topics_t_user Foreign Key
  (
  u_id
  ) References dbo.t_user
  (
  u_id
  )
Go

Create Trigger t_topics_Delete
On dbo.t_topics
For Delete
As
  Declare @topic_id int
  select @topic_id = (select topic_parent from deleted)
  if @topic_id=-1 
    begin
      DELETE t_topics FROM t_topics a , deleted b WHERE (a.topic_parent = b.topic_id)		
    end
  else
    update t_topics 
      set a.topic_count = a.topic_count -1 
      from t_topics a, deleted b 
      where a.topic_id = b.topic_parent
Go

Create Trigger t_topics_Insert
On dbo.t_topics
For Insert
As
  if (select topic_parent from inserted)<>-1
  update t_topics 
    set a.topic_count = a.topic_count + 1, a.topic_update = getDate() 
    from t_topics a, inserted b where a.topic_id = b.topic_parent
  update t_forums set forum_update = getDate() 
    from t_forums a, inserted b where a.forum_id = b.forum_id
Go

Table Relationships

The relationships between each table is fairly straightforward. We reuse the same field name in all tables to represent the relationship. All our relationships are one-to-many relationships between the primary and foreign key. This means that each primary key can be related with any number of records in the related table. For example, looking at the first relationship below, an individual user can post any number of messages.

Primary Key		Foreign Key
t_user->u_id		t_topics->u_id
t_forums->forum_id		t_topics->forum_id
t_topics->topic_id		t_topics->topic_parent

Our approach is to always build and test the database before writing any of the ASP pages. Therefore, next we walk you through the stored procedures used to access and manipulate the discussion forums. These stored procedures expose all the operations for the discussion forum.

© 1997-2000 InsideDHTML.com, LLC. All rights reserved.