GO
/****** Object: Table [dbo].[Employees] Script Date: 05/20/2014 00:03:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employees](
[EmpID] [int] IDENTITY(1,1) NOT NULL,
[EmpNumber] [varchar](50) NOT NULL,
[UserName] [nvarchar](50) NOT NULL,
[Password] [nvarchar](100) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
[Email] [varchar](100) NOT NULL,
[Address1] [varchar](50) NOT NULL,
[Address2] [varchar](50) NULL,
[City] [varchar](50) NULL,
[State] [varchar](50) NULL,
[PostCode] [varchar](15) NULL,
[MobileNumber] [varchar](50) NULL,
[DOB] [datetime] NULL,
[BillingRate] [decimal](18, 2) NULL,
[DOJ] [datetime] NULL,
[Department] [varchar](50) NULL,
[PhoneNumber] [varchar](15) NULL,
[Extension] [varchar](10) NULL,
[Photo] [varbinary](max) NULL,
[Salary] [decimal](18, 2) NULL,
[SpouseName] [varchar](50) NULL,
[SupervisorID] [int] NULL,
[Notes] [varchar](max) NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[EmpID] 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
/****** Object: Table [dbo].[Products] Script Date: 05/20/2014 00:03:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Products](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [varchar](50) NOT NULL,
[ProductDescription] [varchar](100) NULL,
[UnitPrice] [decimal](18, 2) NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[ProductID] 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
/****** Object: Table [dbo].[OrderHeader] Script Date: 05/20/2014 00:03:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OrderHeader](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderRefNo] [varchar](50) NOT NULL,
[OrderDate] [datetime] NOT NULL,
[CustomerID] [int] NOT NULL,
[EmployeeID] [int] NULL,
[RequiredByDate] [datetime] NULL,
[ShipAddress1] [varchar](50) NULL,
[ShipAddress2] [varchar](50) NULL,
[ShipCity] [varchar](50) NULL,
[ShipState] [varchar](50) NULL,
[ShipCountry] [varchar](50) NULL,
[ShipDate] [datetime] NULL,
[ShipName] [varchar](50) NULL,
[ShipPhoneNo] [varchar](15) NULL,
[ShippingMethod] [varchar](25) NULL,
[ShipPostCode] [varchar](20) NULL,
[OrderStatus] [varchar](50) NULL,
[PaymentMethod] [varchar](50) NULL,
[Quotation] [varchar](50) NULL,
CONSTRAINT [PK_OrderHeader] PRIMARY KEY CLUSTERED
(
[OrderID] 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
/****** Object: Table [dbo].[OrderDetail] Script Date: 05/20/2014 00:03:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OrderDetail](
[OrderDetailID] [int] IDENTITY(1,1) NOT NULL,
[OrderID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[Quantity] [int] NULL,
[UnitPrice] [decimal](18, 2) NULL,
[SalesPrice] [decimal](18, 2) NULL,
[DiscountPercent] [float] NULL,
[DiscountAmount] [decimal](18, 2) NULL,
[ColorNeeds] [varchar](25) NULL,
[PreferredVectorImage] [varchar](10) NULL,
[BGColor] [varchar](25) NULL,
[TurnaroundTime] [varchar](10) NULL,
[Instruction] [varchar](500) NULL,
[ReturnFile] [varchar](10) NULL,
CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED
(
[OrderDetailID] 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
/****** Object: Table [dbo].[Jobs] Script Date: 05/20/2014 00:03:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Jobs](
[JobID] [int] IDENTITY(1,1) NOT NULL,
[OrderDetailID] [int] NOT NULL,
[FileName] [varchar](50) NULL,
[FilePath] [varchar](100) NULL,
[SourceFileExt] [varchar](10) NULL,
[JobStatus] [varchar](25) NULL,
CONSTRAINT [PK_Jobs] PRIMARY KEY CLUSTERED
(
[JobID] 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
/****** Object: ForeignKey [FK_Jobs_OrderDetail] Script Date: 05/20/2014 00:03:30 ******/
ALTER TABLE [dbo].[Jobs] WITH CHECK ADD CONSTRAINT [FK_Jobs_OrderDetail] FOREIGN KEY([OrderDetailID])
REFERENCES [dbo].[OrderDetail] ([OrderDetailID])
GO
ALTER TABLE [dbo].[Jobs] CHECK CONSTRAINT [FK_Jobs_OrderDetail]
GO
/****** Object: ForeignKey [FK_OrderDetail_OrderHeader] Script Date: 05/20/2014 00:03:30 ******/
ALTER TABLE [dbo].[OrderDetail] WITH CHECK ADD CONSTRAINT [FK_OrderDetail_OrderHeader] FOREIGN KEY([OrderID])
REFERENCES [dbo].[OrderHeader] ([OrderID])
GO
ALTER TABLE [dbo].[OrderDetail] CHECK CONSTRAINT [FK_OrderDetail_OrderHeader]
GO
/****** Object: ForeignKey [FK_OrderDetail_Products] Script Date: 05/20/2014 00:03:30 ******/
ALTER TABLE [dbo].[OrderDetail] WITH CHECK ADD CONSTRAINT [FK_OrderDetail_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([ProductID])
GO
ALTER TABLE [dbo].[OrderDetail] CHECK CONSTRAINT [FK_OrderDetail_Products]
GO
/****** Object: ForeignKey [FK_OrderHeader_Employees] Script Date: 05/20/2014 00:03:30 ******/
ALTER TABLE [dbo].[OrderHeader] WITH CHECK ADD CONSTRAINT [FK_OrderHeader_Employees] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employees] ([EmpID])
GO
ALTER TABLE [dbo].[OrderHeader] CHECK CONSTRAINT [FK_OrderHeader_Employees]
GO
/****** Object: ForeignKey [FK_OrderHeader_UserProfile] Script Date: 05/20/2014 00:03:30 ******/
ALTER TABLE [dbo].[OrderHeader] WITH CHECK ADD CONSTRAINT [FK_OrderHeader_UserProfile] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[UserProfile] ([UserId])
GO
ALTER TABLE [dbo].[OrderHeader] CHECK CONSTRAINT [FK_OrderHeader_UserProfile]
GO
