Link to home
Start Free TrialLog in
Avatar of asp123
asp123

asked on

Table Design Question MS SQL Server

I am working on a Message Board kind of Application where I have the Master table Comments and Detail/History Table called CommentsHistory.

I need advice on how to deal with Comment Text, Status, ModifiedBy and ModifiedOn, If I keep all of them in CommentsHistory then I will need a view with FirstCommentHistoryID and LastCommentHistoryID for each comment to find the Original Comment and it was by who and what is the latest Comment,Status, By Who and Date/Time.

My current table structue is like
CREATE TABLE [dbo].[Comments] (
      [CommentID] [int] IDENTITY (1, 1) NOT NULL ,
      [SurveyItemID] [int] NOT NULL ,
      [Status] [smallint] NULL ,
      [Priority] [smallint] NULL ,
      [AssignedTo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [InternalDiscussion] [smallint] NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[CommentHistory] (
      [CommentHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
      [CommentID] [int] NOT NULL ,
      [ByWho] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [OnDateTime] [datetime] NULL ,
      [Status] [int] NOT NULL ,
      [Comment] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Instruction] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Is having a view and joining back to History table to get the Original and Latest info about the comments a good approach?




ASKER CERTIFIED SOLUTION
Avatar of dqmq
dqmq
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial