1

I want to build a small app for myself to organize my tasks.

I created a task table as follows:

CREATE TABLE 
TASK(
TASK_ID INT NOT NULL AUTO_INCREMENT,
TASK_NAME VARCHAR(100),
TASK_DESCRIPTION VARCHAR(300),
TASK_ASSIGNEE_ID_PRIMARY VARCHAR(32),
PERCENTAGE_COMPLETED INT3,
PLANNED_DATE_START DATE,
PLANNED_START_TIME TIME,
PLANNED_DATE_END DATE,
PLANNED_END_TIME TIME,
ESTIMATED_DURATION INT,
PRIORITY INT,
DIFFICULTY INT,
TASK_STATUS_ID INT,
USER_ID_CREATED VARCHAR(32),
DATETIME_CREATED DATETIME,
USER_ID_UPDATED VARCHAR(32),
DATETIME_UPDATED DATETIME,
PRIMARY KEY (TASK_ID));

I would like to create a trigger or an update query (run from Python's Flask) to update dates (PLANNED_DATE_START, PLANNED_START_TIME, PLANNED_DATE_END, PLANNED_END_TIME) based on the priority.

Here is an example:

1) I have following tasks: 'Learning MySQL(duration 13h, priority = 1 , highest), 'Learning web development' (duration 6h, priority = 2, second-highest), 'Cleaning my room' (duration 6h, priority = 3, lowest).

2) I run the update or trigger the trigger and based on current date and time task with the highest priority get assigned time with assumption I am working 8 hours a day.

3) Assuming today is 2018-01-29 9:00, still 8 hours of work:

  • 'Learning MySQl would get PLANNED_DATE_START = '2018-01-29', PLANNED_START_TIME = '09:00', PLANNED_DATE_END = '2018-01-30', PLANNED_END_TIME = '14:00' since I normally start working at 9.

  • 'Learning web development' would get PLANNED_DATE_START = '2018-01-30', PLANNED_START_TIME = '14:00', PLANNED_DATE_END = '2018-01-31', PLANNED_END_TIME = '12:00'

  • 'Cleaning my room' would get PLANNED_DATE_START = '2018-01-31', PLANNED_START_TIME = '12:00', PLANNED_DATE_END = '2018-02-01', PLANNED_END_TIME = '10:00'

I really appreciate any suggestions. Best regards, Wojciech

Wojciech Orzechowski
  • 11
  • 3

0 Answers0