The discussion forum consists of two tables (three if you include the user membership 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.
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
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.
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
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.
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.
Page 1:Adding Discussion Forums
© 1997-2000 InsideDHTML.com, LLC. All rights reserved.