drop table revisionchecks
go
drop table revisionpdfs
go
drop table revisions
go
drop table revisionstages
go
drop table revisiontypes
go

/****** Object:  Table [dbo].[RevisionTypes]    Script Date: 08/30/2011 14:30:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RevisionTypes](
	[TypeID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](200) NULL,
 CONSTRAINT [PK_RevisionTypes] PRIMARY KEY CLUSTERED 
(
	[TypeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

insert into revisiontypes (name) values ('Revision')
insert into revisiontypes (name) values ('Temp Mod')
go

/****** Object:  Table [dbo].[RevisionStages]    Script Date: 08/30/2011 14:29:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RevisionStages](
	[StageID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](200) NULL,
 CONSTRAINT [PK_RevisionStages] PRIMARY KEY CLUSTERED 
(
	[StageID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

insert into revisionstages (name) values ('Input to review')
insert into revisionstages (name) values ('Initial review')
insert into revisionstages (name) values ('Final review')
insert into revisionstages (name) values ('Verification')
insert into revisionstages (name) values ('Validation')
insert into revisionstages (name) values ('Issued')
insert into revisionstages (name) values ('Published')
insert into revisionstages (name) values ('Approved')
go

/****** Object:  Table [dbo].[Revisions]    Script Date: 08/30/2011 14:43:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Revisions](
	[RevisionID] [int] IDENTITY(1,1) NOT NULL,
	[ItemID] [int] NOT NULL,
	[TypeID] [int] NOT NULL,
	[RevisionNumber] [nvarchar](50) NULL,
	[RevisionDate] [datetime] NULL,
	[Notes] [nvarchar](max) NULL,
	[Config] [xml] NULL,
 CONSTRAINT [PK_Revisions] PRIMARY KEY CLUSTERED 
(
	[RevisionID] 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].[Revisions]  WITH CHECK ADD  CONSTRAINT [FK_Revisions_RevisionTypes] FOREIGN KEY([TypeID])
REFERENCES [dbo].[RevisionTypes] ([TypeID])
GO
ALTER TABLE [dbo].[Revisions] CHECK CONSTRAINT [FK_Revisions_RevisionTypes]
GO
ALTER TABLE [dbo].[Revisions]  WITH CHECK ADD  CONSTRAINT [FK_Revisions_tblItems] FOREIGN KEY([ItemID])
REFERENCES [dbo].[tblItems] ([ItemID])
GO
ALTER TABLE [dbo].[Revisions] CHECK CONSTRAINT [FK_Revisions_tblItems]

GO

/****** Object:  Table [dbo].[RevisionPDFs]    Script Date: 08/30/2011 14:46:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RevisionPDFs](
	[RevisionID] [int] NOT NULL,
	[StageID] [int] NOT NULL,
	[DTS] [datetime] NOT NULL,
	[UserID] [nvarchar](200) NOT NULL,
	[PDF] [varbinary](max) NULL,
 CONSTRAINT [PK_RevisionPDFs] PRIMARY KEY CLUSTERED 
(
	[RevisionID] ASC,
	[StageID] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[RevisionPDFs]  WITH CHECK ADD  CONSTRAINT [FK_RevisionPDFs_Revisions] FOREIGN KEY([RevisionID])
REFERENCES [dbo].[Revisions] ([RevisionID])
GO
ALTER TABLE [dbo].[RevisionPDFs] CHECK CONSTRAINT [FK_RevisionPDFs_Revisions]
GO
ALTER TABLE [dbo].[RevisionPDFs]  WITH CHECK ADD  CONSTRAINT [FK_RevisionPDFs_RevisionStages] FOREIGN KEY([StageID])
REFERENCES [dbo].[RevisionStages] ([StageID])
GO
ALTER TABLE [dbo].[RevisionPDFs] CHECK CONSTRAINT [FK_RevisionPDFs_RevisionStages]

GO
/****** Object:  Table [dbo].[RevisionChecks]    Script Date: 08/30/2011 14:48:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RevisionChecks](
	[RevisionID] [int] NOT NULL,
	[StageID] [int] NOT NULL,
	[ConsistencyChecks] [xml] NULL,
 CONSTRAINT [PK_RevisionChecks] PRIMARY KEY CLUSTERED 
(
	[RevisionID] ASC,
	[StageID] 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].[RevisionChecks]  WITH CHECK ADD  CONSTRAINT [FK_RevisionChecks_RevisionPDFs] FOREIGN KEY([RevisionID], [StageID])
REFERENCES [dbo].[RevisionPDFs] ([RevisionID], [StageID])
GO
ALTER TABLE [dbo].[RevisionChecks] CHECK CONSTRAINT [FK_RevisionChecks_RevisionPDFs]

GO