CODEWITHSUNDEEP
mysqldatabasedatabase-designrelational-database
kenshin9
kenshin9

Reputation: 2365

Database Design - Different Statuses for Different Types

I have a system that handles tasks, and there can be different types of tasks. So for example, perhaps there's a "fix computer task" and a "fix phone task". And I want to update the current status for each, but since they're different types of tasks, they may or may not be sharing a similar status. I was wondering what the best way to approach something like this would be.

So for computer tasks, some statuses might be:

  1. Diagnosing
  2. Troubleshooting
  3. Fixing
  4. Complete

And then for phone tasks, we'd have:

  1. Diagnosing
  2. Ordering Parts
  3. Fixing
  4. Testing Calls
  5. Complete

As you can see, some statuses overlap, while some are unique to that type. Would you create a new table of statuses per task type, or is there a better way to handle something like this?

Upvotes: 0

Views: 611

Answers (1)

Adi Levin
Adi Levin

Reputation: 5233

There is more than one valid answer. Here are two competing approaches with their pros and cons.

Approach 1: Different status table for each task type (with columns status_id and status_name). This is suitable if you're also tracking the tasks in different tables per task type (e.g. computer_tasks_tbl and phone_tasks_tbl), because then you can define foreign key constraints between each tasks table and its corresponding status table. This advantage is that you ensure data integrity at the database level, and the schema is a useful documentation of the design. The disadvantage is that if there are increasingly many types of tasks, you will end up with lots of tables, and will need to update the schema more often.

Approach 2: A single status table for all possible statuses. The advantage is that this simplifies the schema, especially if you have many different kinds of tasks, which means you will have to make less schema changes over time. The disadvantage is that you are not enjoying the data integrity guarantees of foreign-key constraints, and you rely completely on the application to insert good data.

As all design decisions, this involves a trade-off. In this case it is a trade-off between data integrity guarantees, and the simplicity and stability of the schema.

Upvotes: 3

Related Questions