Database Recommendations - Status

I am creating an application where objects have a search status. To give some context, let's use the following example.

A help desk application where jobs are created and navigated through the following workflow:

New - Job created but unassigned In
Progress - Job assigned to the worker and in progress

Completed - Job ready for billing

Closed - Invoiced job

So, I create a status table with the following data:

int ID
String Name

and lookup column in jobs table

int ID
string Name
int CustomerID
int StatusID -> view status

So, in the real world, let's say we have the following requirements.

  • Users need to get a report to show all incomplete jobs (jobs that are either new or InProgress).
  • Down the line, someone will want to add a new status, which is, for example, in the middle of completed and closed.

So my initial thoughts are to create a new column in the state table named SortOrder or similar and assign numbers to it like

New - 10 In
Progress - 20
Completed - 30
Closed - 40

This would mean that for case # 1 above, I could simply query the database for all jobs whose status is greater than or equal to 30. That would also be great for case # 2, because it means that if I enter a new status between completed and closed, it will not violate this report.

I can see this has cropped up a lot in different applications. Has anyone implemented such a solution or encountered this problem before?


source to share

1 answer

What we are doing is one state table and a table of associated state groups.

create table status_group (
    id integer primary key not null,
    alias varchar(20) not null,
    descr varchar(128)

create table status (
    id integer primary key not null,
    status_group_id integer,
    alias varchar(20) not null,
    descr varchar(128)


Then all statuses live in one place, but are grouped together with the presence of millions of separate ones.



All Articles