设计帮助/健全检查-工作订单系统

Question

我是一名程序员，负责为我的团队创建一个工作订单系统。MariaDB是我们认为应该使用的数据库引擎。我认为在过去的一个月里，我已经做了足够多的阅读工作，完全有足够的能力让自己陷入麻烦。

每个工作订单都有一些与其相关的基本信息，如名称、日期等。每个工作订单也有一个包含任意数量任务的列表。每项任务都有一些基本信息，如描述、收费代码、日期等。每项任务都有一份清单，列有分配给该任务的任意数量的人员。

我是否正确地认为，这是我应该如何设计我的桌子为这一需要？如果没有，任何向正确方向的轻推都会受到高度赞赏.

persons
  PK
+----+-------------+-------------+--------------------------+
| id | first_name  | last_name   |    email_address         |
+----+-------------+-------------+--------------------------+
|  1 | Jane        | Doe         | Jane.doe@example.com     |
|  2 | John        | Doe         | john.doe@example.com     |
|  3 | Bob         | Tables      | bob.tables@school.edu    |
|  4 | Fox         | McCloud     | fox.mccloud@corneria.net |
+----+-------------+-------------+--------------------------+

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 |
+----+---------+-------------+------------+

如果到目前为止，我并没有犯可怕的错误，那么我的查询是什么来打印我的硬拷贝来获取一个工作订单呢？像这样的工作订单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;

如果人退出，这是否是我的查询，以查找所有受影响的工作订单？

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;

编辑1-一些澄清信息

任务一旦添加，就永远不会从工作订单中删除™，除非在边缘情况下，例如将错误的任务添加到错误的工作顺序。它们将被简单地标记为“活动”、“关闭”、“取消”或其他根据其身份所需的内容。我忘了在上面的示例中包括配置表/字段。类似地，工作订单也不会从系统中删除™，并且将以与任务类似的方式进行标记。

任何一个人都可以同时被分配到任意数量的任务中，他们的名字永远不会被删除(除非他们在实际开始之前就被从该任务中删除)。

收费代码只是一串文本，我们在时间表上使用这些文本来显示我们的时间流向。幸运的是，这是一个完全不同和不相关的系统，我们在这里不需要担心。该系统上下文中的计费代码字段不需要对其施加任何限制，因为它永远不会是唯一的，我们也不能保证格式(只有合理的最大长度)。

Answer 1

从设计的角度来看，您可能希望将"dt_added“表中的"task_assignments”列更改为类型datetime的“瞬间”，并添加一个tinyint (布尔值)列"is_added“(例如，删除了假的方法)，以跟踪那些为某个任务做出贡献但可能稍后被重新分配到另一个任务的人。

除了引用其他表的列中的索引外，还应将索引放在您计划用于在查询中排序或筛选的列中。如果您确实知道您将只使用特定索引列中的值进行直接访问(即将确切的值放置在WHERE = ' value '，而不是排序或范围筛选中)，那么最好使用哈希索引类型。如果有疑问，请使用BTREE类型。

我理解“任务”表上的"chargecode“必须类似于标识每个任务的引用号，因此它应该有一个唯一的索引，以避免不必要的重复。在这种情况下，您可能希望使用哈希索引类型。

根据所使用的数据库引擎的不同，您可能还希望在表上设置Foreign约束，这些表中有引用其他表的列，以确保这些列上的值始终一致。

至于第一个查询，我认为连接应该如下所示(注意a和t是在第二个联接上翻转的)：

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;

至于第二个查询，您应该使用内部联接，还应该翻转a和t：

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;