Design help / Sanity check – Work order system

database-design

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

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).

Best Answer

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;

Related Solutions

Importing order information

I think the best way to approach this would be to have tables like so:

table Customer
    ID
    Name
    ...

table CustomerFields
    CustomerID
    FieldID
    FieldName
    ... (could have things like type, required/optional, size, etc.)

table CustomerOrders
    CustomerID
    OrderID
    OrderNumber
    DeliveryDate
    ... (all your generics and mappable fields)

table CustomerOrderFields
    CustomerID
    OrderID
    FieldID
    FieldValue

By having the table CustomerOrders, you satisfy the ability to map common customer order data to common fields (simplifying reporting at the expense of making the import a little more painful since the fields must be mapped), and the CustomerOrderFields gives you the ability to have the custom fields per customer necessary for the un-mappable data.

The custom fields are still reportable, but not as easily as your generics as they'll come to you in multiple rows (instead of multiple columns). There are some ways around all that depending on your report creator (e.g., pivoting the results).

The only other option would be to do something like this (which, personally, I would avoid):

table CustomerFields
    CustomerID
    Field1Name
    Field2Name
    Field3Name
    ...
    Field99Name

Table CustomerOrders
    (all your generics)
    Field1Value
    Field2Value
    ...
    Field99Value

This has the advantage that all your data is in one row, but also has the disadvantage that it isn't immediately apparent from the row what each value means. The first method can always be joined to the field list to give a good definition of the data in each custom field. In addition, what happens if some company requires 100 custom fields? In the above example, you'd be making changes to your data structures and code whereas in the first example, you'd never have the issue -- customers could have infinite custom fields.

I've seen it done both ways, and both ways work. Both ways have their downsides and upsides. The first is far more scalable, but harder to get in to columns (instead of rows). Everything's a trade-off.

Hope that helps some!

Mysql – Efficient way to move rows across the tables

I think its faster (feel free to test) to keep the record in the same table while processing, but be sure to set an index on the column(s) that track which processor is using them.

Also use MyISAM tables if you don't require InnoDB's features. The ARCHIVE table can best be set to the archive type, so it does not make space for updates (if you don't need to write to it)

Best Answer

Related Solutions

Importing order information

Mysql – Efficient way to move rows across the tables

Related Question