Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
donnapep
Starting Member
15 Posts |
 Posted - 2007-10-17 : 11:43:01
|
| Hi,I am trying to design some functionality that emulates the recurrence functionality of MS Outlook calendar. In the calendar, users can choose a recurrence pattern (i.e. daily, weekly, monthly or yearly). Depending on the radio button that is selected, different choices appear. I am unsure as to how to model this in the database.Here's what I have so far:A ScheduleDetails table that will store information applicable to all schedules, regardless of recurrence pattern. One of the columns in this table will store the recurrence pattern type (0 for daily, 1 for weekly, etc.). The valid values for this column will be stored in a look-up table.Now, if daily is selected, the user can select a radio button called 'Every <#> days' OR a radio button called 'Every weekday'. My plan is to have a new ScheduleDailyRecurrences table that has a Days field. If 'Every <#> days' is selected, the value of Days will be the value entered by the user. Otherwise, if the user has selected 'Every weekday', the value will be -1. Is this a good way to do it? I feel as though I may be attaching too much meaning to the particular value.If monthly is selected, it gets even more complicated. The user can either select 'Day <#> of every <#> month(s)' OR 'The <count> <day> of every <#> month(s)'. I'm not sure how to model this. In my ScheduleMonthlyRecurrences table, should I have a field called 'Pattern'? Depending on the value of this field, the details would be then be stored in yet another table? Or should I just have columns in ScheduleMonthlyRecurrences for each user-entered value? This would mean that some of the fields would not be applicable depending on the radio button that the user has selected.It's a little difficult to explain, but if you go to the Recurrence button in Outlook calendar, you'll see what I mean.Thanks for any advice. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2007-10-17 : 14:51:08
|
| Hm, how many jobs are you expecting to schedule...? I know it's a lame answer but if there's a "manageble" amount (< 50-100 maybe?) you might be better off creating them all as sql server jobs using sp_add_job, sp_add_jobstep and sp_add_schedule. Either way I'd probably look up sp_add_schedule in books online...maybe you'll get some ideas. I'd think that creating this and actually make it work would be a royal pain in the a$$.--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
 |
|
|
donnapep
Starting Member
15 Posts |
Posted - 2007-10-18 : 09:54:20
|
| I am not going to be running any jobs. I am using the scheduling functionality in a web app to schedule items in a playlist. Hence, I need to store this information in tables. I need suggestions for table design. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-10-18 : 10:29:09
|
You might start by looking at the msdb.dbo.sysjobschedules table that the SQL Server Agent uses to schedule jobs.I can't say it's a great design, but it's a good starting point, and it does work.use msdbexec sp_help 'sysjobschedules'select *from information_schema.columnswhere TABLE_NAME = 'sysjobschedules'order by ORDINAL_POSITION CODO ERGO SUM |
|
|
|
donnapep
Starting Member
15 Posts |
Posted - 2007-10-18 : 11:28:55
|
| Yes, I'll look at this. It looks like it may have most of what I need. Thanks everyone. |
|
|
|
|
|
|
|
|
Table Design for Recurrence Patterns