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.
Can you forward your script to create that schema? Thanks!
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