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?
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_
[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_
[OnDateTime] [datetime] NULL ,
[Status] [int] NOT NULL ,
[Comment] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_
[Instruction] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_
) 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.