数据库最佳做法 - 状态 [英] Database best practices - Status
问题描述
我正在创建一个应用程序,其中对象具有状态查找。要给出一些上下文,请使用以下示例。
I'm creating an application in which objects have a status lookup. To give some context, let's use the following example.
创建作业并在以下工作流中移动的帮助台应用程序:
A helpdesk application where jobs are created and move through the following workflow:
新建 - 创建但未分配的作业
正在进行 - 分配给工作人员和进行中
完成 - 工作已完成准备开具发票
已关闭 - 工作发票
New - Job created but unassigned
In Progress - Job assigned to a worker and in progress
Complete - Job complete ready for invoicing
Closed - Job invoiced
所以,我创建一个状态表,其中包含以下详细信息:
So, I create a status table with the following details:
int ID
字符串名称
和作业表上的查找列
int ID
字符串名称
int CustomerID
int StatusID - >查找状态
int ID
string Name
int CustomerID
int StatusID -> looks up the status
所以在现实世界中,假设我们有以下要求
So in the real world, let's say we have the following requirements.
- 用户需要获取报告才能显示所有不完整的作业(新的或InProgress的作业)
- 下线,有人会想要添加一个新的状态,例如完成和关闭的状态。
所以考虑到这一点,我的初步想法是在名为SortOrder或类似的状态表中创建一个新列,并为其分配数字,如
So with this in mind, my initial thoughts are to create a new column on the Status table called SortOrder or similar and assign numbers to it such as
新 - 10
正在进行 - 20
已完成 - 30
关闭 - 40
New - 10
In Progress - 20
Completed - 30
Closed - 40
这将意味着对于上述案例1,我可以简单地查询数据库中所有状态大于或等于对于案例2,这也是非常好的,因为这意味着如果我在完成和关闭之间引入了一个新的状态,它就不会破坏这个报告。
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. This would also be great for case #2 because it means that if I introduced a new status in between completed and closed it would not break this report.
我可以看到它会经常出现在不同的应用程序中。有没有人实施过这样的解决方案或者以前遇到这个问题?
I can see that it would come up often in different applications. Has anyone implemented a solution like this or come across this problem before?
推荐答案
我们做的是单一的Status表,一个配套状态组表。
What we do is have a single Status table, and a companion Status Group table.
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 of the statuses live in a single spot, but are grouped together vs having a zillion individual ones.
这篇关于数据库最佳做法 - 状态的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!