Design help / Sanity check - Work order system

Question

I, a programmer, have been tasked with creating a work order system for my group. MariaDB is the database engine we think we should use. I think I've done enough reading over the last month to be perfectly qualified enough to get myself into trouble.

Each work order has some basic information associated with it such as its name, dates, etc. Each work order also has a list with any number of Tasks. Each task has some basic information such as description, charge codes, dates, etc. Each task also has a list with any number of persons assigned to that task.

Am I correct in thinking this is how I should design my tables for this need? If not, any nudges in the right direction would be highly appreciated...

persons
  PK
+----+-------------+-------------+--------------------------+
| id | first_name  | last_name   |    email_address         |
+----+-------------+-------------+--------------------------+
|  1 | Jane        | Doe         | [email protected]     |
|  2 | John        | Doe         | [email protected]     |
|  3 | Bob         | Tables      | [email protected]    |
|  4 | Fox         | McCloud     | [email protected] |
+----+-------------+-------------+--------------------------+

workorders
  PK                        Index?        Index?
+----+--------------------+-------------+------------+--------------------+
| id | name               | dt_created  | dt_due     | description        |
+----+--------------------+-------------+------------+--------------------+
|  1 | Do a barrel roll   | 2019/12/05  | 2020/06/15 | Something or other |
|  2 | Invent time travel | 2019/12/05  | 2019/12/04 | Something relevant |
+----+--------------------+-------------+------------+--------------------+

tasks
  PK   Index                 Index?       Index?       Index?
+----+-------+-------------+------------+------------+------------+----------------+
| id | wo_id | series_num  | chargecode | dt_created | dt_due     | description    |
+----+-------+-------------+------------+------------+------------+----------------+
|  1 | 1     | 1           | xyz123abc  | 2019/12/05 | 2020/06/15 | Blah blah blah |
|  2 | 2     | 1           | xyz321aba  | 2019/12/05 | 2019/12/04 | Blah blah blah |
|  3 | 2     | 2           | xyz321abb  | 2019/12/05 | 2019/12/04 | Blah blah blah |
|  4 | 2     | 3           | xyz321abc  | 2019/12/05 | 2019/12/04 | Profit         |
+----+-------+-------------+------------+------------+------------+----------------+

task_assignments
  PK   Index     Index
+----+---------+-------------+------------+
| id | task_id | person_id   | dt_added   |
+----+---------+-------------+------------+
|  1 | 1       | 4           | 2019/12/05 |
|  2 | 2       | 2           | 2019/12/05 |
|  3 | 2       | 3           | 2019/12/05 |
|  4 | 3       | 1           | 2019/12/05 |
|  5 | 3       | 3           | 2019/12/05 |
|  6 | 4       | 4           | 2019/12/05 |
+----+---------+-------------+------------+

If I'm not horribly wrong so far, what would my query be to print my hardcopy for a workorder? Something like this for workorder id 2?

SELECT w.name, w.dt_created, w.dt_due, w.description,
t.series_num, t.chargecode AS 'task_code', t.dt_created AS 'task_created',
t.dt_due AS 'task_due', t.description AS 'task_desc', a.dt_added AS 'task_added'
FROM `workorders` w
LEFT JOIN tasks t ON w.id = t.wo_id
LEFT JOIN task_assignments a ON t.task_id = a.id
WHERE w.id = 2;

If person n quits, would this be my query to find all affected work orders?

SELECT w.name, w.dt_created, w.dt_due, w.description,
t.series_num, t.chargecode AS 'task_code', t.dt_created AS 'task_created',
t.dt_due AS 'task_due', t.description AS 'task_desc', a.dt_added AS 'task_added'
FROM `workorders` w
LEFT JOIN tasks t ON w.id = t.wo_id
LEFT JOIN task_assignments a ON t.task_id = a.id
WHERE a.person_id = n;

Edit 1 - Some clarifying info
Tasks will never™ be removed from work orders once they are added, except in edge cases such as adding the wrong task to the wrong work order. They will simply be marked as "active", "closed", "cancelled" or whatever else is necessary based on their status. I forgot to include the disposition table/fields in the above example. Similarly, work orders will never™ be deleted from the system either and will be marked in similar fashion to tasks.

Any one person can be assigned to any number of tasks at the same time and their name will never be removed (Unless they're removed from that task before ever actually having started on it).

Charge codes are simply a string of text that we use for our accounting on timesheets to show where our time went. Luckily that's a completely different and unrelated system that we don't need to worry about here. The charge code field in the context of this system doesn't need to have any constraints placed on it as it will never be unique nor can we guarantee a format (only a reasonable max length).

Answer 1

From the design perspective maybe you would like to change the "dt_added" column on the "task_assignments" table to simply "moment" of type datetime and add a tinyint (boolean) column "is_added" (False means removed, for instance) to also keep track of those who have contributed to a task but may have been reassigned to another one at a later time.

The indexes, aside from those in the columns that reference other tables, should be put in the columns you plan to use for sorting or filtering in your queries. If you know for sure you are only going to use the values in a particular indexed column for direct access (i.e. putting the exact value in WHERE column = 'value', no sorting or range filtering) then it's better to use a HASH index type. When in doubt, use a BTREE type.

I understand "chargecode" on the "tasks" table must be like a reference number that identifies each task, so it should should have a Unique index to avoid unwanted duplicates. This is probably a case where you would like to use a HASH index type.

Depending on the database engine you're using you probably also want to put Foreign Key constraints on the tables that have columns referencing other tables to make sure the values on those columns are always consistent.

As for the first query I believe the joins should be as follows instead (note the a. and t. are flipped on the second JOIN):

SELECT w.name, w.dt_created, w.dt_due, w.description,
t.series_num, t.chargecode AS 'task_code', t.dt_created AS 'task_created',
t.dt_due AS 'task_due', t.description AS 'task_desc', a.dt_added AS 'task_added'
FROM `workorders` w
LEFT JOIN tasks t ON w.id = t.wo_id
LEFT JOIN task_assignments a ON a.task_id = t.id
WHERE w.id = 2;

As for the second query, you should use INNER JOINs instead and the a. and t. should be flipped too:

SELECT w.name, w.dt_created, w.dt_due, w.description,
t.series_num, t.chargecode AS 'task_code', t.dt_created AS 'task_created',
t.dt_due AS 'task_due', t.description AS 'task_desc', a.dt_added AS 'task_added'
FROM `workorders` w
INNER JOIN tasks t ON w.id = t.wo_id
INNER JOIN task_assignments a ON a.task_id = t.id
WHERE a.person_id = n;