“重复性任务"的设计选项 [英] Design option for 'recurring tasks'

问题描述

我正在编写一个为人们处理任务的小应用程序.很简单，但就表格设计而言，我坚持的领域是重复性任务的情况，可以是一次、每天、每周或每月一次.如果每周一次，则是在特定的一天，每周一次.每月是特定的一天.

I am writing a small application which handles Tasks for people. Very simple, but the area I am stuck on, as far as table design goes, is the case of a recurring task, which can be either once off, daily, weekly or monthly. If weekly, it's on a specific day, weekly. Monthly is a specific day.

我有一个任务表和一个 recurring_type_id，我打算用代码处理重复性任务，但这是理想的方式吗?另一种方法是在创建任务时插入所有任务 - 对于每个事件时间.但这似乎也不对.

I have a tasks table, and a recurring_type_id, and was going to handle the recurring tasks in code, but is the the ideal way? The other way is to insert all the tasks when the task is created - for each event time. But that doesn't seem right either.

谁能就设计以及如何以可维护和有效的方式处理此问题提出建议?

Can anyone advice on a design and how to handle this in a maintainable and efficient way?

我使用的是 SQL Server 2008 R2

I'm using SQL Server 2008 R2

推荐答案

我会创建一个任务表来插入我的任务.

I would create a task table to insert my tasks into.

taskTable
|taskID  |Freq   |runAt       |
-------------------------------
|1       |daily  |1           |
|2       |daily  |0           |
|3       |weekly |5           |
|4       |weekly |1           |
|5       |monthly|15          |
|6       |monthly|1           |
|7       |once   |2013-7-4    |

dailies 的 runAt 从未被考虑过，因此输入什么值并不重要.

runAt for dailies is not ever considered so it doesn't matter what value is entered.

runAt for weekly 项目是一周中的哪一天运行任务.

runAt for weekly items is the day of the week that the task is to run.

runAt for mothly 是任务要运行的月份中的哪一天(我通常首先运行月末任务，因为这样可以省去处理月份结束日期的麻烦，尽管你可以用它来弄清楚

runAt for mothly is the day of the month that the task is to run (month end tasks I usually run on the first since is saves the hassle of dealing with which day the month ends on although you could use this to figure that out

lastDayOfMonth = datePart(d,dateadd(s,-1,dateadd(mm, datediff(m,0,getdate())+1,0)))

runAt for once 是任务运行的实际日期.

runAt for once is the actual day the task is to run.

然后我会创建一个每天运行的任务，看看需要运行什么.

Then I'd create a task to run daily to see what needed to be run.

select  taskID
from    taskTable
where   (freq = 'once' and runAt = convert(varchar(10),getDate(),21))
  or    freq = 'daily'
  or    (freq = 'weekly' and runAt = datePart(dw,getDate()))
  or    (freq = 'monthly' and runAt = datePart(d,getDate())

这个查询为我提供了我需要运行的任何任务的所有 taskID.

This query gives me all the taskID for any tasks that I need to run.

不确定这是否是您要找的东西，但希望您能从中找到有用的东西.

Not sure if this is what you were looking for but hopefully you'll find something useful in it.

这篇关于“重复性任务"的设计选项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！