Designing the Database Tables

To help you understand the database design of this module, we'll compare it to the design that was implemented in the first edition of this book, examining the changes (but it's OK if you didn't read the first edition — you will still see some value in this comparison). Figure 8-1 represents the first edition's UML diagram of all the tables.

With six tables for a single module, this made the forums system the most complex module of the book. In this edition, we'll simplify the design significantly. We'll also add some new features, but without removing anything important. In Figure 8-1 you can see that we had a two-level organization for message threads: categories and then subforums. A simpler design with multiple subforums would be entirely adequate in most situations. We also had two separate tables for storing the first message of threads (the Forums_Threads table) and the replies (the Forums_Replies table): These two tables do not differ for many fields, as they both have a primary key field and the Message, MemberID, MemberIP, and AddedDate fields. The first message of a thread and its replies are all forum posts, and dividing them in two separate tables only complicates things, i.e., it leads to longer queries and is more difficult to read and maintain. In the new design there is just a single table for all messages; the few fields that only make sense for one type of message will simply have a default value for the other type. Additionally, we had threads and replies linked to a record of the Forums_Members table, which contained a profile of the user, with her signature, avatar, and other information, and this was linked to a record in the Accounts_Users table, which contained the membership information (username, password, etc., of all user accounts). Yes, we had two redundant places to hold user data, and it was confusing and caused problems when they became out of sync. In the new version we'll consolidate all membership and profile data, and it will be stored and managed automatically by the ASP.NET provider classes!

To recap, the new forums system will just have one table that stores subforum information, and another one that stores all messages. Figure 8-2 represents the tables as shown by the Database Diagram Editor window of VS 2005's Server Explorer tool (which is similar to what Enterprise Manager would show in SQL Server 2000 with VS 2003 — now you can stay inside VS most of the time).

The tbh_Forums table is similar to the tbh_Categories table used in Chapter 5 for the articles module, with the addition of the Moderated column, which indicates whether the messages posted by normal users must be approved before they become visible in the forums' pages. The tbh_Posts table contains the following columns (the usual AddedDate and AddedBy fields aren't shown here):

• PostID: The primary key

• AddedByIP: The IP of the user who authored the message — used for auditing purposes. Remember that you may become partially responsible for what users write (this also depends on the laws of your country). You should try to log information about the user who posted a message (such as the date/time and IP address) so you can provide this to legal authorities in the unlikely event that it might be needed.

• ForumID: The foreign key to a parent forum

• ParentPostID: An integer referencing another record in the same table, which is the first message of a thread. When this field contains 0, it means that the post has no parent post; therefore, this is a thread post. Otherwise, this is a reply to an existent thread.

• Title: The title of the post. Reply posts also have a title; it will usually be "Re: {thread title here}", but it's not absolutely necessary and the user will be free to change it while posting a new reply.

• Body: The body of the post, in HTML format (only limited HTML tags are allowed)

• Approved: A Boolean value indicating whether the post has been approved by a power user (administrators, editors, and moderators), and visible on the end-user pages. If the parent forum is not moderated, this field is automatically set to 1 when the post is created.

• Closed: This field is only used for thread posts, and is a Boolean value indicating whether the thread is closed and no more replies can be added. The user will be able to specify this option only while creating the thread. Once a thread has been created, only power users can close the thread.

• ViewCount: An integer indicating the number of times a thread has been read. If the record represents a reply, this field will contain 0.

• ReplyCount: The number of replies for the thread post. If the record represents a reply, this field will contain 0.

• LastPostBy: The name of the member who submitted the last post to this thread. As long as there are no replies, the field contains the name of the member who created the thread, which is also the name stored in the record's AddedBy field.

• LastPostDate: The date and time of the last post to this thread. As long as there are no replies, the field contains the date and time when the thread was created, which is also the value stored in the record's AddedDate field.

In the case of ParentPostID, the replies will always link to the first post of the thread, and not to another reply. Therefore, the proposed structure does not support threaded discussions, such as those in Internet newsgroups. Instead, posts of non-threaded discussions will be shown to the reader, sorted by creation date, from the oldest to the newest, so that they are read in chronological order. Both of these two types of forum systems, threaded or not, have their pros and cons. Threaded discussions make it easier to follow replies to previous posts, but non-threaded discussions make it easier to follow the discussion with the correct temporal order (time-sequenced). To make it easier for the reader to follow the discussion, non-threaded discussions usually allow users to quote a previous reply, even if the referenced reply is a number of posts prior to that one. In my research, non-threaded discussions are more widely used, and easier to develop, so we'll use them for our sample site. If you want to modify the forum system to support threaded discussions, you'll be able to do that without modifying the DB; you just need to set the post's ParentPostID to the appropriate value.

Our first edition used SQL aggregate functions COUNT and MAX to dynamically compute the thread's message count values instead of storing them in the database. This was a serious problem in terms of performance because it took a long time to execute this SQL, and it ran often. It will save a lot of time if you store the values directly in the thread post's record, and update them when a new reply is added, as we'll do in this book.

The Stored Procedures

The following table contains the list of stored procedures for the typical CRUD operations on the two database tables, plus some special updates to handle approving posts, moving threads, incrementing the view count, and so on.

There are two notable queries missing from the preceding list. One is tbh_Forums_GetThreads, which returns the details of a page of threads. The other is tbh_Forums_GetThreadsByForum, which should return the details of a page of threads for a specific forum. These queries are not defined as stored procedures because the list of threads must support different ordering (LastPostDate, ReplyCount, and ViewCount), and unfortunately we can't parameterize the ORDER BY clause used in the stored procedure together with the ROW_NUMBER()... OVER statement. The simplest solution to this problem is to dynamically build and execute these queries from the data access layer, according to the input parameters. As explained in Chapter 3, if you use parameters in the SQL query, SQL Server will be able to cache the execution plan and you'll be shielded against SQL injection attacks, even if you're not using a stored procedure.

Note

Retrieving the list of threads present in all forums is only useful for sitewide RSS feeds that list the last "n" threads created, or the "n" most active threads (threads with the most replies).

Designing the Configuration Module

The configuration settings of the forums module are defined in a <forums> element within the <theBeerHouse> section of the web.config file. The class that maps the settings and exposes them is ForumsElement, which defines the following properties:

Designing the Data Access Layer

As usual, the DAL consists of a number of entity classes that wrap data from the database tables (the ForumDetails and PostDetails classes), an abstract class defining a number of virtual methods to manipulate the data and a few helper methods (ForumsProvider), and finally a concrete class that inherits from the abstract class and implements its virtual methods with the code to call the respective stored procedures. Figure 8-3 shows the UML diagram of all these classes and their relationships.

Besides GetThreads, all other DAL methods simply wrap a stored procedure, so they don't require any further explanation. As explained earlier, GetThreads executes the dynamically constructed SQL queries in order to support different sorting options according to the input parameters.

Designing the Business Layer

Like the DAL, the BLL for this module is very similar to the other BLLs used in this book. There's a BaseForum class that contains the usual ID, AddedDate, and AddedBy properties common to both the tbh_Forums and the tbh_Posts tables, a Settings property, which returns an instance of the ForumsElement class, which wraps all forums' settings read from web.config, and finally a CacheData method that caches the input data according to the forums' settings. The derived classes are Forum and Post. Forum is almost identical to the Category class designed and implemented in Chapter 5, with the addition of the Moderated property. Post represents both threads (the first post of a thread) and replies, and it has properties that wrap all data coming from the PostDetails DAL class, plus instance methods such as Update, Delete, Approve, MoveThread, and CloseThread that manipulate the data contained in the current object. These instance methods simply forward the call to static methods that in turn call the respective DAL methods and add some logic to store and purge data from the cache, add data validation, and so on. The methods for retrieving a single post, the list of posts for a given thread, or the list of threads for a given forum are also implemented as static methods. Figure 8-4 shows the UML diagram of the BLL.

Designing the User Interface Services

The last thing we need to define are the UI pages and user controls that enable the user to browse forums and threads, post new messages, and administer the forum's content. Following is a list of the user interface pieces that we'll develop shortly in the "Solution" section:

• ~/Admin/ManageForums.aspx: Adds, updates, and deletes forums

• ~/Admin/ManageUnapprovedPosts.aspx: Lists all unapproved posts (first thread posts, and then replies, all sorted from the oldest to the newest), shows the entire content of a selected post, and approves or deletes it

• ~/Admin/MoveThread.aspx: Moves a thread (i.e., the thread post and all its replies) to another forum

• ~/ShowForums.aspx: Shows the list of all subforums, with their title, description, and image. Clicking on the forum's title will bring the user to another page showing the list of threads for that forum. For each forum, this also provides a link to its RSS feed, which returns the last "n" threads of that forum (where "n" is specified in web.config).

• ~/BrowseThreads.aspx: Browses a forum's threads, page by page. The grid that lists the threads shows the thread's title, the number of times it was read, the number of replies, the author of the last post, and when the last post was created. Power users also see special links to delete, close, or move the thread. The results can be sorted by date, reply count, or view count.

• ~/ShowThread.aspx: Shows all posts of a thread, in a paginated grid. For each post, it shows the title, body, author's signature, submission date and time, author's name, avatar image, and status description. Power users also see links to delete or edit any post, and a link to close the thread to stop replies. Normal members only see links to edit their own posts.

• ~/AddEditPost.aspx: Creates a new thread, posts a new reply, or edits an existing message, according to the parameters on the querystring

• ~/GetThreadsRss.aspx: Returns an RSS feed of the forum's content. According to the querystring parameters, it can return the feed for a specific subforum or include threads from any subforum, and supports various sorting options. This can retrieve a feed for the sitewide threads (if sorting by date) or for the most active threads (if sorting by reply count).

• ~/Controls/UserProfile.ascx: This control already exists, as it was developed in Chapter 4 while implementing the membership and profiling system. However, you must extend it here to support the Avatar image and Signature profile properties.