CODEWITHSUNDEEP
mysqlsqldatabase-designrelational-database
Dennis
Dennis

Reputation: 8111

How to design a relational database to track different types of job & job statuses, on top of an existing job table design?

Existing Situation

I have an existing table that is called job, which tracks a job as it goes through the shop. It works fine but now I need a different class of job, that has different status cycle and may have different fields.

Also, I can't afford to throw away or modify job table drastically as lots of existing code + another system is using that table extensively. But I can add to it, or amend it slightly, and I can create other tables, and I can connect them as I please.

What I have is:

enter image description here

What I need is to track 2 (at the moment) different types of jobs that have different sets of statuses that they go through.

How?

When thinking in OOP I get:

A Job is something that has a (number, status, orderedDate, shipDate); A ShopJob is something that in addition to Job has (manufacturedDate); An OfficeJob is something that in addition to Job has (readyDate);

In my case the Job is an abstract class, which does not exist by itself. ShopJob and OfficeJob extend it.

And here is the thing: the status field must contain a different cycle of job statuses.

How do I translate this into Relational SQL language, in my case MySQL? I can fill in more details if need be.

Solution thoughts:

I am thinking that the simplest yet maybe not quite the best solution is to add a job.type field = {shop, office}, add job.readyDate to accommodate for office job, and use that. I am trying to picture a better design but struggling so far.

Also I need to show all jobs in a job tracker. So if for example I design 5 different tables, and later I have 5 types of jobs, I will need to join all those tables and modify my SQL to account for those tables, which is inconvenient. I want something where SQL accounts for new job types without modification.

My interpretation of HLGEM Answer:

enter image description here

job_type.type = {"shop", "office"}

status.status = {"ordered", "reviewed", "manufactured", "ready", "shipped"}

job_type_status bins statuses into job types

job.job_type_id signifies what type of job job.id belongs to

Q: How do I deal with job having fields that are disjointed, i.e. manufactured_date and ready_date are for different type of jobs, and yet are in the same table.

Upvotes: 0

Views: 2280

Answers (1)

HLGEM
HLGEM

Reputation: 96590

What you need is a JobStatus table with statustype and date. Then you can have a Status lookup table (statusId, statusName) with available statuses and a lookup table for JobTypeStatus with Jobtypeid and status ID as the fields (and perhaps start and end dates if they change frequently)that limits the statuses you can choose to only those available for that jobtype. You would use this to populate any drop downs on your application used to change status.

So then you have the job table (see below for possible renaming) and you join to the jobstatus to get the various statuses for that job. You can pivot to get them all on one row in the query or choose to do that in the application which is likely a better choice.

To keep old working code and add new structure, the most common thing to do is rename the Job table, then create a view named Job that is based on a query that gives the exact fields the old Job table had. There is a great book on refactoring databases that everyone shou dread who works with data, https://www.amazon.com/Refactoring-Databases-Evolutionary-paperback-Addison-Wesley/dp/0321774515

Upvotes: 1

Related Questions