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
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.
source to share