Design of Scheduled Task data schema

I am designing a table structure for scheduled tasks. Tasks must be
scheduled either on a fixed interval or at specific times. Those specific
times can be either every day or specific days of every w.
I have come up with the plan I'll paste in here, but the NULL's and one
field telling how to interpret data from another table leave me suspecting
there's a better way. I'm designing this from scratch so am free to change
as needed.
Suggestions?
Use Pubs
Create Table SchedTask(
TaskCode nvarchar (6) not null Primary Key,
TaskDescription nvarchar (30) ,
ScheduleType char(1) not null Check ( ScheduleType in ('I', 'D',
'W')), --for Interval, Daily, Wly
ScheduleInterval SmallInt NULL
)
go
Create Table SchedDay(
TaskCode nvarchar (6) Foreign Key References SchedTask (TaskCode),
DayOfW TinyInt not null Check ( DayOfW Between 0 and 7), --0
indicates it doesn't matter, 1-7 map to Sunday-Saturday
TimeOfDay SmallDateTime not null
)
go
--Add a task to repeat every 10 minutes
Insert Into SchedTask (TaskCode, TaskDescription, ScheduleType,
ScheduleInterval)
Values ('10M', 'Repeat every 10 minutes', 'I', 10)
--Add a task to occur at 9:00 AM every day
Insert Into SchedTask (TaskCode, TaskDescription, ScheduleType,
ScheduleInterval)
Values ('9D', 'occur at 9:00 AM every day', 'D', NULL)
Insert Into SchedDay (TaskCode, DayOfW, TimeOfDay)
Values ('9D', 0, '9:00')
--Add a task to occur at 9:00 AM every Monday and 11:45 every Friday
Insert Into SchedTask (TaskCode, TaskDescription, ScheduleType,
ScheduleInterval)
Values ('2W', 'Do this twice a w', 'W', NULL)
Insert Into SchedDay (TaskCode, DayOfW, TimeOfDay)
Values ('2W', 2, '9:00')
Insert Into SchedDay (TaskCode, DayOfW, TimeOfDay)
Values ('2W', 6, '11:45')
--Show the data
Select * From SchedTask
Select * From SchedDay
--Now clean up the DB
drop table SchedDay
go
drop table SchedTask
go
Thanks.
Daniel Wilson
http://Embtrak.com Development TeamSee if you can get some inspiration from the sysjobschedules database in the
msdb database. It's the table that SQL Server Agent uses to schedule jobs
and it is documented in SQL Server Books Online.
Jacco Schalkwijk
SQL Server MVP
"Daniel Wilson" <d.wilson@.Embtrak.com> wrote in message
news:edmiKCcyFHA.1252@.TK2MSFTNGP09.phx.gbl...
>I am designing a table structure for scheduled tasks. Tasks must be
> scheduled either on a fixed interval or at specific times. Those specific
> times can be either every day or specific days of every w.
> I have come up with the plan I'll paste in here, but the NULL's and one
> field telling how to interpret data from another table leave me suspecting
> there's a better way. I'm designing this from scratch so am free to change
> as needed.
> Suggestions?
> Use Pubs
> Create Table SchedTask(
> TaskCode nvarchar (6) not null Primary Key,
> TaskDescription nvarchar (30) ,
> ScheduleType char(1) not null Check ( ScheduleType in ('I', 'D',
> 'W')), --for Interval, Daily, Wly
> ScheduleInterval SmallInt NULL
> )
> go
> Create Table SchedDay(
> TaskCode nvarchar (6) Foreign Key References SchedTask (TaskCode),
> DayOfW TinyInt not null Check ( DayOfW Between 0 and 7), --0
> indicates it doesn't matter, 1-7 map to Sunday-Saturday
> TimeOfDay SmallDateTime not null
> )
> go
> --Add a task to repeat every 10 minutes
> Insert Into SchedTask (TaskCode, TaskDescription, ScheduleType,
> ScheduleInterval)
> Values ('10M', 'Repeat every 10 minutes', 'I', 10)
> --Add a task to occur at 9:00 AM every day
> Insert Into SchedTask (TaskCode, TaskDescription, ScheduleType,
> ScheduleInterval)
> Values ('9D', 'occur at 9:00 AM every day', 'D', NULL)
> Insert Into SchedDay (TaskCode, DayOfW, TimeOfDay)
> Values ('9D', 0, '9:00')
> --Add a task to occur at 9:00 AM every Monday and 11:45 every Friday
> Insert Into SchedTask (TaskCode, TaskDescription, ScheduleType,
> ScheduleInterval)
> Values ('2W', 'Do this twice a w', 'W', NULL)
> Insert Into SchedDay (TaskCode, DayOfW, TimeOfDay)
> Values ('2W', 2, '9:00')
> Insert Into SchedDay (TaskCode, DayOfW, TimeOfDay)
> Values ('2W', 6, '11:45')
> --Show the data
> Select * From SchedTask
> Select * From SchedDay
> --Now clean up the DB
> drop table SchedDay
> go
> drop table SchedTask
> go
> Thanks.
> Daniel Wilson
> http://Embtrak.com Development Team
>
>|||But whatever you do, don't use ints for date and time values. :-)
A
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23gJJ2VcyFHA.2932@.TK2MSFTNGP10.phx.gbl...
> See if you can get some inspiration from the sysjobschedules database in
> the msdb database. It's the table that SQL Server Agent uses to schedule
> jobs and it is documented in SQL Server Books Online.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Daniel Wilson" <d.wilson@.Embtrak.com> wrote in message
> news:edmiKCcyFHA.1252@.TK2MSFTNGP09.phx.gbl...
>|||Thanks. I'll think that over.
And I'll definitely NOT use integers for times!
dwilson
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23gJJ2VcyFHA.2932@.TK2MSFTNGP10.phx.gbl...
> See if you can get some inspiration from the sysjobschedules database in
the
> msdb database. It's the table that SQL Server Agent uses to schedule jobs
> and it is documented in SQL Server Books Online.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Daniel Wilson" <d.wilson@.Embtrak.com> wrote in message
> news:edmiKCcyFHA.1252@.TK2MSFTNGP09.phx.gbl...
specific
suspecting
change
>