I'm developing a simple change management system for internal use. It's based on ASP.Net and SQL Server. I'm currently suffering developers block and can't think of an easy/simple/elegant way to achieve the following.The users should be able to review the history of a change request through the various stages of it's lifecycle. This should be sorted in chronological order. There is a master table which contains the data for initial change request and then there are tables for each stage of the process. These are related to the master table by a RequestID.Each of these "sub-tables" have different fields and so a UNION isn't straighforward. Here's some table structures to give you a better idea of what I'm talking about.The "master" table:-CREATE TABLE [dbo].[ChangeRequest] ( [RequestId] [int] IDENTITY (1, 1) NOT NULL , [RelativeId] [int] NULL , [RequestorName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL , [RequestDate] [datetime] NOT NULL , [RequestorDepartment] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL , [RequestorTelExt] [smallint] NULL , [RequestType] [tinyint] NULL , [RequestDetails] [nvarchar] (1000) COLLATE Latin1_General_CI_AS NULL , [RequestPriority] [tinyint] NOT NULL , [DateRequired] [datetime] NULL , [Reason] [nvarchar] (4000) COLLATE Latin1_General_CI_AS NULL ) ON [PRIMARY]
The "related" tables:-CREATE TABLE [dbo].[ChangeRequest_ChangeController] ( [ChgCtrlID] [int] IDENTITY (1, 1) NOT NULL , [RequestId] [int] NOT NULL , [StepID] [int] NOT NULL , [ActionedBy] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL , [ActionedDate] [datetime] NOT NULL , [Status] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ) ON [PRIMARY]GOCREATE TABLE [dbo].[ChangeRequest_ChangePlanning] ( [ChgPlanID] [int] IDENTITY (1, 1) NOT NULL , [RequestId] [int] NOT NULL , [StepID] [int] NOT NULL , [ActionedBy] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL , [ActionedDate] [datetime] NOT NULL , [TargetSys] [nvarchar] (1000) COLLATE Latin1_General_CI_AS NOT NULL , [GroupContact] [nvarchar] (150) COLLATE Latin1_General_CI_AS NOT NULL , [LabTested] [bit] NULL , [ChgTestDesc] [nvarchar] (3000) COLLATE Latin1_General_CI_AS NOT NULL , [TestPlan] [nvarchar] (3000) COLLATE Latin1_General_CI_AS NOT NULL , [BackoutPlan] [nvarchar] (3000) COLLATE Latin1_General_CI_AS NOT NULL , [WillChgMigrate] [bit] NULL , [Prereqs] [nvarchar] (3000) COLLATE Latin1_General_CI_AS NOT NULL ) ON [PRIMARY]GOCREATE TABLE [dbo].[ChangeRequest_RiskAssessments] ( [RiskAssID] [int] IDENTITY (1, 1) NOT NULL , [RequestId] [int] NOT NULL , [StepID] [int] NOT NULL , [ActionedBy] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL , [ActionedDate] [datetime] NOT NULL , [RiskLevel] [int] NULL , [ImpactOfChange] [nvarchar] (2000) COLLATE Latin1_General_CI_AS NULL , [ImpactOfNotActioningChange] [nvarchar] (2000) COLLATE Latin1_General_CI_AS NULL , [Authorize] [nvarchar] (10) COLLATE Latin1_General_CI_AS NOT NULL , [AssignTo] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL , [Recommendations] [nvarchar] (3000) COLLATE Latin1_General_CI_AS NULL ) ON [PRIMARY]GOCREATE TABLE [dbo].[ChangeRequest_TestAndValidations] ( [TestValID] [int] IDENTITY (1, 1) NOT NULL , [RequestId] [int] NOT NULL , [StepID] [int] NOT NULL , [ActionedBy] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL , [ActionedDate] [datetime] NOT NULL , [TestValProcedure] [nvarchar] (3000) COLLATE Latin1_General_CI_AS NOT NULL ) ON [PRIMARY]GO
If you need some sample data let me know.Thanking everybody in advance :)
Design Challenge
Posted - 2005-02-16 : 15:28:11
