if exists (select * from dbo.sysobjects where id = object_id(N'[Roles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Roles]
GO

CREATE TABLE [Roles] (
	[pkId] [int] IDENTITY (1, 1) NOT NULL ,
	[RoleName] [nchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Description] [nchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	CONSTRAINT [PK_Roles] PRIMARY KEY  CLUSTERED 
	(
		[pkId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[UsersInRoles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [UsersInRoles]
GO

CREATE TABLE [UsersInRoles] (
	[pkId] [int] IDENTITY (1, 1) NOT NULL ,
	[UserID] [int] NOT NULL ,
	[RoleID] [int] NOT NULL ,
	CONSTRAINT [PK_UsersInRoles] PRIMARY KEY  CLUSTERED 
	(
		[UserID],
		[RoleID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[BigClassAssociation]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [BigClassAssociation]
GO

CREATE TABLE [BigClassAssociation] (
	[pkId] [int] IDENTITY (1, 1) NOT NULL ,
	[menuId] [int] NULL ,
	[bigClassId] [int] NULL ,
	CONSTRAINT [PK_BigClassAssociation] PRIMARY KEY  CLUSTERED 
	(
		[pkId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[discussProperty]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [discussProperty]
GO

CREATE TABLE [discussProperty] (
	[pkId] [int] IDENTITY (1, 1) NOT NULL ,
	[discussId] [int] NULL ,
	[mustLogin] [int] NULL ,
	[mustCheckPass] [int] NULL ,
	CONSTRAINT [PK_discussProperty] PRIMARY KEY  CLUSTERED 
	(
		[pkId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[Tasks]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Tasks]
GO

CREATE TABLE [Tasks] (
	[pkId] [int] IDENTITY (1, 1) NOT NULL ,
	[Title] [nvarchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
	[DocFile] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
	[Status] [int] NULL ,
	[Comment] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
	[JoinTime] [datetime] NULL ,
	[PassTime] [datetime] NULL ,
	[OverTime] [datetime] NULL ,
	[UserID] [int] NULL ,
	[PassUserID] [int] NULL ,
	[OverUserID] [int] NULL ,
	[BigClassID] [int] NULL ,
	CONSTRAINT [PK_Tasks] PRIMARY KEY  CLUSTERED 
	(
		[pkId]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[BigResear]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [BigResear]
GO

CREATE TABLE [BigResear] (
	[pkId] [int] IDENTITY (1, 1) NOT NULL ,
	[Area] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Address] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[ZipCode] [nvarchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
	[Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Department] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Duty] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Phone] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
	[DateY] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[DateM] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[DateD] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[Answer] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
	CONSTRAINT [PK_BigResear] PRIMARY KEY  CLUSTERED 
	(
		[pkId]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[BigResearDate]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [BigResearDate]
GO

CREATE TABLE [BigResearDate] (
	[pkId] [int] IDENTITY (1, 1) NOT NULL ,
	[Date] [datetime] NULL ,
	CONSTRAINT [PK_BigResearDate] PRIMARY KEY  CLUSTERED 
	(
		[pkId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


