Forum Tables

Just finished deciding on the initial set of tables for the discussion forum.

I wanted to keep things as simple and as flexible as possible. In that regard, I tried to normalize as much as is practical and there is one table for both topics and replies and a seperate table defining Topic > Reply (Parent > Child) relationships. The tags are also assigned by the use of two tables; one for the tag itself and another specifying the relationship.

Since tags double as forums or categories, the tags also have a description provision. I wanted to make anyone browsing a particular tag feel like they’re on a forum page.

Clockwise : Posts table stores topics and replies, PostRelations define parent > child relationships, PostTags define categories, PostTagRelations specify which tags go with which topic

I also wanted to keep track of which authors created which tag. Authors are a unique table where each entry is per post since we want to enable anonymous posting. There is a MemberId field, however, that allows an author to be identified by a registered member profile. Following the same normalization pattern, there are PostAuthors and TagAuthors tables.

Author relationship tables

And, of course, the Members table. I’m still going to be using the MembershipProvider model, but with a few customizations. This table may change in the future, but it does what I need it to do for now.

Members table

And of course, the object models… You may notice from the namespace, I decided to call this project “Road”. Sufficiently vague to be interesting ;)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Road.Models
{
	public enum TopicStatus : byte
	{
		Open, Closed, Hidden, HiddenClosed, InTagPromoted,
		InTagPromotedClosed, Promoted, PromotedClosed
	};

	public enum TagStatus : byte
	{
		Open, Closed, NoAnon, AnonModerated, Moderated
	};

	public enum ReplyStatus : byte
	{
		Open, Hidden
	}

	public class Entity
	{
		public int Id { get; set; }
		public DateTime CreatedDate { get; set; }
		public DateTime LastModified { get; set; }
	}

	public class NamedEntity : Entity
	{
		public string Slug { get; set; }
		public string Name { get; set; }
		public string DisplayName { get; set; }
	}

	public class Creator : NamedEntity
	{
		public string Email { get; set; }
		public string IP { get; set; }
		public string Web { get; set; }
		public string Bio { get; set; }
		public string Avatar { get; set; }
	}

	public class PageEntity : NamedEntity
	{
		public Creator CreatedBy { get; set; }
		public string Summary { get; set; }
		public string Body { get; set; }
	}

	public class Tag : PageEntity
	{
	}

	public class Topic : PageEntity
	{
		public LazyList<Tag> Tags { get; set; }
		public PagedList<Reply> Replies { get; set; }
		public int ViewCount { get; set; }
		public int ReplyCount { get; set; }

		public float? Threshold { get; set; }
		public TopicStatus Status { get; set; }

		public Topic()
		{
			this.Id = 0;
			this.Status = TopicStatus.Open;
			this.ViewCount = 0;
			this.ReplyCount = 0;
			this.Threshold = 0;
		}
	}

	public class Reply : PageEntity
	{
		public int TopicId { get; set; }
		public float? Threshold { get; set; }
		public ReplyStatus Status { get; set; }

		public Reply()
		{
			this.Id = 0;
			Status = ReplyStatus.Open;
			this.Threshold = 0;
		}
	}
}

Note: The LazyList is a lazy loading helper class. There are several examples on the web if you Google the term so I haven’t decided which to use or if I’ll write my own. PagedList is an oldie, but goodie I’ve been using for quite some time. There are many examples of that on the web too.

“Threshold” on both Topic and Reply classes are quality measurements. I want to implement some sort of spam/quality filter that will set the threshold on each as they are entered into the database and allow the user to toggle which range to see. Anonymous users won’t be able to toggle the threshold so any search bots would also be spared any spam.

These few days, I’ve been running all over the place so once I settle down, next update on this, I’ll start posting some actual code.