Schedule Task Table Design

CREATE TABLE [dbo].[Schedule](

 [ScheduleID] [int] NOT NULL,
 [ScheduleTaskID] [int] NULL,
 [ScheduleFrequency] [varchar](20) NOT NULL, /*Once, Daily, Weekly, Biweekly, Monthly, Yearly*/
 [ScheduleStatus] [varchar](50) NULL,                 /*A-Enabled, X-Disabled*/
 [ScheduleStart] [smalldatetime] NULL,               /*Full vaule used by Once and Biweekly, Time part only used by Others*/
 [ScheduleEnd] [smalldatetime] NULL,                /**/
 [MonthWeekDay] [smallint] NULL,                    /*This will override Start and End column*/
 [LastModifyUser] [varchar](50) NULL,
 [LastModifyDate] [smalldatetime] NULL, 
CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED
( [ScheduleID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[ScheduleException](

 [ScheduleID] [int] NOT NULL,
 [ExceptionDateStart] [smalldatetime] NOT NULL,
 [ExceptionDateEnd] [smalldatetime] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[ScheduleExecution](

 [ScheduleID] [int] NOT NULL,
 [ScheduleTaskID] [varchar](50) NOT NULL,
 [ExecuteStart] [datetime] NOT NULL,
 [ExecuteEnd] [datetime] NULL,
 [ExecuteStatus] [varchar](50) NOT NULL,
 [RequestUser] [varchar](50) NULL,
 [RequestTime] [datetime] NULL
) ON [PRIMARY]



1). Read "Developing Time-Oriented Database Applications in SQL" by Richard Snodgrass (http://www.cs.arizona.edu/people/rts/tdbbook.pdf). This is a free PDF book which is perhaps the best resource on developing temporal databases.

2). Review some existing data models that relate to scheduling, like:
http://www.databaseanswers.org/data_models/services_job_scheduling/index.htm
http://www.databaseanswers.org/data_models/hairdressers/index.htm
Look at this resources to see if it provides any ideas.
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=FirstAvailableTimeslot&referringTitle=Home

Tibor's article on working with date, time, and datetime values is excellent.
http://www.karaszi.com/SQLServer/info_datetime.asp

Eralper's discussion on Calendar tables is worth reading:
http://www.kodyaz.com/articles/sql-server-dates-table-using-tsql-cte-calendar-table.aspx

Another discussion of using a Calendar table:
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

---

CREATE TABLE [dbo].[ScheduleException](

 [ScheduleID] [int] NOT NULL,
 [ExceptionDateStart] [smalldatetime] NOT NULL,
 [ExceptionDateEnd] [smalldatetime] NOT NULL
) ON [PRIMARY]