Discussion Forum Update (tables and classes)

A bit of a long post today to show the updated table schema for the discussion forum and a few of the classes. There are quite a few improvements from the last version of the table schema in that there is no longer a PostRelations table. This was only adding more complexity to the retrival and storage methods so I opted to use a simple ParentId field to take care of the relationships. Now there is also a LastActivity field in the Posts table so that when a new post is added, only this field needs to be modified. The ContentHash field stores a rolling hash of the plain text content so measuring the quality of the post for the threshold will be more consistent.

Forum database schema

The object models also got an overhaul.

Onward to those…

Advertisements

2 thoughts on “Discussion Forum Update (tables and classes)

    • Hey Sky,
      Here you go…
      (Please keep in mind that this schema may change quite a bit since it’s still a work in progress.)

      USE [ContentManagement]
      GO

      /****** Object: Table [dbo].[Posts] Script Date: 03/08/2012 23:48:23 ******/
      SET ANSI_NULLS ON
      GO

      SET QUOTED_IDENTIFIER ON
      GO

      CREATE TABLE [dbo].[Posts](
      [PostId] [int] IDENTITY(1,1) NOT NULL,
      [ParentId] [int] NOT NULL,
      [Title] [nvarchar](100) NOT NULL,
      [Slug] [nvarchar](100) NOT NULL,
      [CreatedDate] [datetime] NOT NULL,
      [LastModified] [datetime] NOT NULL,
      [LastActivity] [datetime] NOT NULL,
      [BodyText] [nvarchar](max) NOT NULL,
      [BodyHtml] [nvarchar](max) NOT NULL,
      [ContentHash] [nvarchar](max) NOT NULL,
      [ViewCount] [int] NOT NULL,
      [ReplyCount] [int] NOT NULL,
      [IsApproved] [bit] NOT NULL,
      [Status] [int] NOT NULL,
      [Threshold] [float] NOT NULL,
      CONSTRAINT [PK_Posts] PRIMARY KEY CLUSTERED
      (
      [PostId] ASC
      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
      ) ON [PRIMARY]

      GO

      ALTER TABLE [dbo].[Posts] ADD CONSTRAINT [DF_Posts_ParentId] DEFAULT ((0)) FOR [ParentId]
      GO

      /****** Object: Table [dbo].[PostTags] ******/

      CREATE TABLE [dbo].[PostTags](
      [TagId] [int] IDENTITY(1,1) NOT NULL,
      [TagName] [nvarchar](80) NOT NULL,
      [DisplayName] [nvarchar](100) NOT NULL,
      [Slug] [nvarchar](80) NOT NULL,
      [CreatedDate] [datetime] NOT NULL,
      [LastModified] [datetime] NOT NULL,
      [BodyText] [nvarchar](max) NULL,
      [BodyHtml] [nvarchar](max) NULL,
      [PostCount] [int] NOT NULL,
      [IsApproved] [bit] NOT NULL,
      [Status] [int] NOT NULL,
      CONSTRAINT [PK_PostTags] PRIMARY KEY CLUSTERED
      (
      [TagId] ASC
      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
      ) ON [PRIMARY]

      GO

      /****** Object: Table [dbo].[PostTagRelationships] ******/

      CREATE TABLE [dbo].[PostTagRelationships](
      [PostTagId] [int] IDENTITY(1,1) NOT NULL,
      [TagId] [int] NOT NULL,
      [PostId] [int] NOT NULL,
      CONSTRAINT [PK_PostTagRelationships] PRIMARY KEY CLUSTERED
      (
      [PostTagId] ASC
      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
      ) ON [PRIMARY]

      GO

      /****** Object: Table [dbo].[PostTagAuthorRelationships] ******/

      CREATE TABLE [dbo].[PostTagAuthorRelationships](
      [TagAuthorId] [int] IDENTITY(1,1) NOT NULL,
      [TagId] [int] NOT NULL,
      [AuthorId] [int] NOT NULL,
      CONSTRAINT [PK_PostTagAuthorRelationships] PRIMARY KEY CLUSTERED
      (
      [TagAuthorId] ASC
      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
      ) ON [PRIMARY]

      GO

      /****** Object: Table [dbo].[Authors] ******/

      CREATE TABLE [dbo].[Authors](
      [AuthorId] [int] IDENTITY(1,1) NOT NULL,
      [AuthorName] [nvarchar](80) NOT NULL,
      [AuthorIP] [nvarchar](100) NOT NULL,
      [AuthorEmail] [nvarchar](500) NULL,
      [AuthorWeb] [nvarchar](255) NULL,
      [SessionHash] [nvarchar](500) NOT NULL,
      [MemberId] [int] NOT NULL,
      CONSTRAINT [PK_Authors] PRIMARY KEY CLUSTERED
      (
      [AuthorId] ASC
      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
      ) ON [PRIMARY]

      GO

      /****** Object: Table [dbo].[PostAuthorRelationships] ******/

      CREATE TABLE [dbo].[PostAuthorRelationships](
      [PostAuthorId] [int] IDENTITY(1,1) NOT NULL,
      [PostId] [int] NOT NULL,
      [AuthorId] [int] NOT NULL,
      CONSTRAINT [PK_PostAuthorRelationships] PRIMARY KEY CLUSTERED
      (
      [PostAuthorId] ASC
      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
      ) ON [PRIMARY]

      GO

      /****** Object: Table [dbo].[Members] ******/

      CREATE TABLE [dbo].[Members](
      [MemberId] [int] IDENTITY(1,1) NOT NULL,
      [Username] [nvarchar](80) NOT NULL,
      [DisplayName] [nvarchar](100) NULL,
      [Password] [nvarchar](500) NOT NULL,
      [PasswordSalt] [nvarchar](100) NOT NULL,
      [Email] [nvarchar](500) NOT NULL,
      [Slug] [nvarchar](80) NOT NULL,
      [CreatedDate] [datetime] NOT NULL,
      [LastModified] [datetime] NOT NULL,
      [LastActivity] [datetime] NOT NULL,
      [Avatar] [nvarchar](255) NULL,
      [Web] [nvarchar](255) NULL,
      [Bio] [nvarchar](max) NULL,
      [IsApproved] [bit] NOT NULL,
      [IsLockedOut] [bit] NOT NULL,
      CONSTRAINT [PK_Members] PRIMARY KEY CLUSTERED
      (
      [MemberId] ASC
      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
      ) ON [PRIMARY]

      GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s