What is best way to store repeatable task(daily,weekly) in a database?

Answer 1

Are you looking for table structure ideas? this be what I have in mind;

[Tasks]
Task_Id (primary)
Task_class (daily, weekly ...)
Task_description

[Tasks_details]
Task_Id (refer to Tasks.Task_Id)
Task_start (date/time of task initialization)
Task_complete (date/time of task completion)
Task_status (task status: success, failure, pending ...)
Task_next (next date/time to execute task)

Sorry if you meant something else.

Answer 2

The value of <Schedule_Data> for daily and weekly schedules type are sort of bitwise flags, you can add or remove day flag from it or compound some days flag together. For example, if a schedule was to be executed on Monday, Wednesday and Friday the value would be 1 + 4 + 16.

But if you prefer it in another way, you can look into SET or ENUM field types.

https://dev.mysql.com/doc/refman/5.7/en/set.html
https://dev.mysql.com/doc/refman/5.7/en/enum.html

Answer 3

thanks for writing...
yeah I am looking for table structure...
but how can I search for all my tasks given a time range?

Answer 4

Do you mean a single task can be delegated to multiple assignee? in that case I guess we need to have a table for assignee data, and use the Assignee table primary key as foreign key in Tasks_details table, not too sure though, I can't figure what relation is built between [Tasks] and [Asignee].

Answer 5

[Schedules]
Schedule_Id,
Schedule_Description,
Schedule_Type (1:Daily, 2:Weekly,3:Monthly)
Schedule_Data (explanation follows)

<Schedule_Data> defines when a task/event is to be executed. The meaning of this value depends on <Schedule_Type>, with the following scheme:

* Schedule_Type = 1 (Daily)
<Schedule_Data> value defines on which day(s) the task is executed. Possible values are:
1. Monday
2. Tuesday
4. Wednesday
8. Thursday
16. Friday
32. Saturday
64. Sunday

* Schedule_Type = 2 (Weekly)
<Schedule_Data> value defines on which day(s) of the week the task is executed. Possible values are:
1. Monday
2. Tuesday
4. Wednesday
8. Thursday
16. Friday
32. Saturday
64. Sunday

*,Schedule_Type = 3 (Monthly)
<Schedule_Data> value defines date on the month the task is to be executed. Possible values are from 1 to 31, depends on how many days in a month. If month is only 28 days and value is larger than 28, the task should be skipped.

* Task A is weekly scheduled
* Event B is monthly scheduled

Answer 6

there is table storing tasks
for example one entry could be repeat task A on Wednesday on every week..or repeat event b on 2nd of every month.. how should I store these details?

Answer 7

Thanks for the replies guyz..