I have a question about best practices regarding how one should approach storing complex workflow states for processing tasks in a database. I've been looking online to no avail, so I figured I'd ask the community what they thought was best.
关于如何在数据库中存储处理任务的复杂工作流状态，我有一个关于最佳实践的问题。我在网上找了半天也没用，所以我想问问社区里的人，他们认为什么是最好的。
This question comes out of the same "BoxItem" example I gave in a prior question. This "BoxItem" is being tracked in my system as various tasks are performed on it. The task may take place over several days and with human interaction, so the state of the BoxItem must be persisted. Who did the task (if applicable), and when the task was done must also be tracked.
这个问题来自于我在前一个问题中给出的“BoxItem”示例。在我的系统中跟踪这个“BoxItem”，因为在它上执行各种任务。该任务可能会持续几天，并且需要人工交互，因此必须持久化BoxItem的状态。谁完成了任务(如果适用)，以及何时完成任务也必须被跟踪。
At first, I approached this by adding three fields to the "BoxItems" table for every human-interactive task that needed to be done:
首先，我在“BoxItems”表中添加了三个字段，用于处理每个需要完成的人机交互任务:
IsTaskNameComplete
IsTaskNameComplete
DateTaskNameComplete
DateTaskNameComplete
UserTaskNameComplete
UserTaskNameComplete
This worked when the workflow was simple... but now that it has grown to a complex process (> 10 possible human interactions in the flow... about half of which are optional, and may or may not be done for the BoxItem, which resulted in me beginning to add "DoTaskName" fields as well for those optional tasks), I've found that what should've been a simple table now has 40 or so field devoted entirely to the retaining of this state information.
当工作流程很简单的时候，这是可行的。但是现在它已经发展到一个复杂的过程(在流动中可能有10个人类的相互作用……)一半是可选的,可能是也可能不是BoxItem完成,这导致我开始添加“DoTaskName”字段为可选任务),我发现,现在应该是一个简单的表有40左右领域完全致力于保留的状态信息。
I find myself asking if there isn't a better way to do it... but I'm at a loss.
我发现自己在问是否有更好的方法去做……但我不知道。
My first thought was to make a generic "BoxItemTasks" table which defined the tasks that may be done on a given box, but I still would need to save the Date and User information individually, so it didn't really help.
我的第一个想法是创建一个通用的“BoxItemTasks”表，它定义了在一个给定的框上可以完成的任务，但是我仍然需要单独保存日期和用户信息，所以它并没有真正的帮助。
My second thought was that perhaps it didn't matter, and I shouldn't worry if this table has 40 or more fields devoted to state retaining... and maybe I'm just being paranoid. But it feels like that's a lot of information to retain.
我的第二个想法是，也许这并不重要，我不应该担心，如果这个表格上有40个或更多的领域致力于国家保留……也许我只是有点偏执。但感觉上有很多信息需要保留。
Anyways, I'm at a loss as far as what a third option might be, or if one of the two options above is actually reasonable. I can see this workflow potentially getting even more complex in the future, and for each new task I'm going to need to add 3-4 fields just to support the tracking of it... it feels like it's spiraling out of control.
无论如何，我不知道第三种选择是什么，或者如果上面的两种选择中有一种是合理的。我可以看到这个工作流在未来可能会变得更加复杂，对于每个新的任务，我需要添加3-4个字段来支持它的跟踪……感觉像是失控了。
What would you do in this situation?
在这种情况下你会怎么做?
I should note that this is maintenance of an existing system, one that was built without an ORM, so I can't just leave it up to the ORM to take care of it.
我应该注意到，这是对一个现有系统的维护，一个没有ORM的系统，所以我不能把它交给ORM来处理。
EDIT:
编辑:
Kev, are you talking about doing something like this:
Kev，你是说做这样的事吗?
BoxItems
(PK) BoxItemID
(PK)BoxItemID
(Other irrelevant stuff)
(其他无关紧要的东西)
BoxItemActions
(PK) BoxItemID
(PK)BoxItemID
(PK) BoxItemTaskID
(PK)BoxItemTaskID
IsCompleted
IsCompleted
DateCompleted
DateCompleted
UserCompleted
UserCompleted
BoxItemTasks
(PK) TaskType
(PK)TaskType
Description (if even necessary)
描述(如果必要)
Hmm... that would work... it would represent a need to change how I currently approach doing SQL Queries to see which items are in what state, but in the long term something like this looks like it would work better (without having to make a fundamental design change like the Serialization idea represents... though if I had the time, I'd like to do it that way I think.).
嗯…这将工作……它表示需要改变我当前处理SQL查询的方式，以查看哪些项处于什么状态，但从长远来看，类似这样的操作看起来会更好(不需要像序列化概念那样进行根本性的设计更改……)虽然如果我有时间，我想这样做。
So is this what you were mentioning Kin, or am I off on it?
这就是你提到的亲属吗，还是我说错了?
EDIT: Ah, I see your idea as well with the "Last Action" to determine the current state... I like it! I think that might work for me... I might have to change it up a little bit (because at some point tasks happen concurrently), but the idea seems like a good one!
编辑:啊，我认为你的想法和“最后的行动”决定了当前的状态……我喜欢它!我想这可能对我有用……我可能需要稍微修改一下(因为有时任务同时发生)，但是这个想法看起来不错!
EDIT FINAL: So in summation, if anyone else is looking this up in the future with the same question... it sounds like the serialization approach would be useful if your system has the information pre-loaded into some interface where it's queryable (i.e. not directly calling the database itself, as the ad-hoc system I'm working on does), but if you don't have that, the additional tables idea seems like it should work well! Thank you all for your responses!
编辑FINAL:所以在总结中，如果其他人在将来用同样的问题来查找这个问题…这听起来像是序列化方法是有用的,如果你的系统信息预装了一些接口的可查询(即不能直接调用数据库本身,作为我工作特别的系统),但如果你没有额外的表的想法似乎应该工作得很好!谢谢大家的回复!
6 个解决方案
#1
4
If I'm understanding correctly, I would add the BoxItemTasks table (just an enumeration table, right?), then a BoxItemActions table with foreign keys to BoxItems and to BoxItemTasks for what type of task it is. If you want to make it so that a particular task can only be performed once on a particular box item, just make the (Items + Tasks) pair of columns be the primary key of BoxItemActions.
如果理解正确的话，我将添加BoxItemTasks表(只是一个枚举表，对吗?)，然后是BoxItemActions表，其中带有外键的BoxItems和BoxItemTasks是什么类型的任务。如果您想使它使特定的任务只能在特定的框项上执行一次，那么只需将(Items + Tasks)对列作为BoxItemActions的主键。
(You laid it out much better than I did, and kudos for correctly interpreting what I was saying. What you wrote is exactly what I was picturing.)
(你说得比我好得多，而且称赞我正确地解释了我说的话。你写的正是我所想象的。
As for determining the current state, you could write a trigger on BoxItemActions that updates a single column BoxItems.LastAction. For concurrent actions, your trigger could just have special cases to decide which action takes recency.
至于确定当前状态，您可以在boxitemaction上编写一个触发器来更新单个列的boxitem。lastaction。对于并发操作，您的触发器可以只有一些特殊情况来决定哪个操作是最近的。
#2
3
As the previous answer suggested, I would break your table into several.
正如前面的答案所示，我将把你的桌子分成几张。
BoxItemActions, containing a list of actions that the work flow needs to go through, created each time a BoxItem is created. In this table, you can track the detailed dates \ times \ users of when each task was completed.
BoxItemActions包含工作流需要执行的操作列表，在每次创建BoxItem时创建。在该表中，您可以跟踪每个任务完成时的详细日期\时间。
With this type of application, knowing where the Box is to go next can get quite tricky, so having a 'Map' of the remaining steps for the Box will prove quite helpful. As well, this table can group like crazy, hundreds of rows per box, and it will still be very easy to query.
对于这种类型的应用程序来说，要知道下一个框的位置是非常困难的，所以为这个框提供一个其余步骤的“映射”将非常有用。同样，这个表可以像crazy一样分组，每个框有数百行，而且查询起来仍然非常容易。
It also makes it possible to have 'different paths' that can easily be changed. A master data table of 'paths' through the work flow is one solution, where as each box is created, the user has to select which 'path' the box will follow. Or you could set up so that when the user creates the box, they select tasks are required for this particular box. Depends on our business problem.
它还可以让“不同的路径”很容易被改变。工作流程中“路径”的主数据表是一种解决方案，在创建每个框时，用户必须选择该框将跟随的“路径”。或者您可以设置，以便当用户创建框时，他们为这个特定的框选择任务。这取决于我们的业务问题。
#3
1
How about a hybrid of the serialization and the database models. Have an XML document that serves as your master workflow document, containing a node for each step with attributes and elements that detail it's name, order in the process, conditions for whether it's optional or not, etc. Most importantly each step node can have a unique step id.
如何混合序列化和数据库模型。拥有一个作为您的主工作流文档的XML文档，其中包含每个步骤的节点，每个步骤都包含一些属性和元素，这些属性和元素详细描述了它的名称、过程中的顺序、它是否可选的条件等等。
Then in your database you have a simple two table structure. The BoxItems table stores your basic BoxItem data. Then a BoxItemActions table much like in the solution you marked as the answer.
然后在数据库中有一个简单的两个表结构。BoxItems表存储基本的BoxItem数据。然后，在您标记为答案的解决方案中，一个boxitemaction表非常类似。
It's essentially similar to the solution accepted as the answer, but instead of a BoxItemTasks table to store the master list of tasks, you use an XML document that allows for some more flexibility for the actual workflow definition.
它本质上类似于作为答案被接受的解决方案，但您使用的XML文档允许实际工作流定义具有更大的灵活性，而不是BoxItemTasks表来存储任务的主列表。
#4
0
For what it's worth, in BizTalk they "dehydrate" long-running message patterns (workflows and the like) by binary serializing them to the database.
值得注意的是，在BizTalk中，他们通过将长期运行的消息模式(工作流等)序列化到数据库来“脱水”。
#5
0
I think I would serialize the Workflow object to XML and store in the database with an ID column. It may be more difficult to report on, but it sounds like it may work in your case.
我认为我应该将工作流对象序列化为XML并使用ID列存储在数据库中。报道起来可能比较困难，但听起来对你来说可能行得通。
#6
0
For this kind of problem, consider the database schema shown in http://www.databaseanswers.org/data_models/workflow/index.htm which models a series of events in a busniess process.
对于此类问题，请考虑http://www.databaseanswers.org/data_models/workflow/index.htm中所示的数据库模式，该模式对busniess过程中的一系列事件进行建模。
#1
4
If I'm understanding correctly, I would add the BoxItemTasks table (just an enumeration table, right?), then a BoxItemActions table with foreign keys to BoxItems and to BoxItemTasks for what type of task it is. If you want to make it so that a particular task can only be performed once on a particular box item, just make the (Items + Tasks) pair of columns be the primary key of BoxItemActions.
如果理解正确的话，我将添加BoxItemTasks表(只是一个枚举表，对吗?)，然后是BoxItemActions表，其中带有外键的BoxItems和BoxItemTasks是什么类型的任务。如果您想使它使特定的任务只能在特定的框项上执行一次，那么只需将(Items + Tasks)对列作为BoxItemActions的主键。
(You laid it out much better than I did, and kudos for correctly interpreting what I was saying. What you wrote is exactly what I was picturing.)
(你说得比我好得多，而且称赞我正确地解释了我说的话。你写的正是我所想象的。
As for determining the current state, you could write a trigger on BoxItemActions that updates a single column BoxItems.LastAction. For concurrent actions, your trigger could just have special cases to decide which action takes recency.
至于确定当前状态，您可以在boxitemaction上编写一个触发器来更新单个列的boxitem。lastaction。对于并发操作，您的触发器可以只有一些特殊情况来决定哪个操作是最近的。
#2
3
As the previous answer suggested, I would break your table into several.
正如前面的答案所示，我将把你的桌子分成几张。
BoxItemActions, containing a list of actions that the work flow needs to go through, created each time a BoxItem is created. In this table, you can track the detailed dates \ times \ users of when each task was completed.
BoxItemActions包含工作流需要执行的操作列表，在每次创建BoxItem时创建。在该表中，您可以跟踪每个任务完成时的详细日期\时间。
With this type of application, knowing where the Box is to go next can get quite tricky, so having a 'Map' of the remaining steps for the Box will prove quite helpful. As well, this table can group like crazy, hundreds of rows per box, and it will still be very easy to query.
对于这种类型的应用程序来说，要知道下一个框的位置是非常困难的，所以为这个框提供一个其余步骤的“映射”将非常有用。同样，这个表可以像crazy一样分组，每个框有数百行，而且查询起来仍然非常容易。
It also makes it possible to have 'different paths' that can easily be changed. A master data table of 'paths' through the work flow is one solution, where as each box is created, the user has to select which 'path' the box will follow. Or you could set up so that when the user creates the box, they select tasks are required for this particular box. Depends on our business problem.
它还可以让“不同的路径”很容易被改变。工作流程中“路径”的主数据表是一种解决方案，在创建每个框时，用户必须选择该框将跟随的“路径”。或者您可以设置，以便当用户创建框时，他们为这个特定的框选择任务。这取决于我们的业务问题。
#3
1
How about a hybrid of the serialization and the database models. Have an XML document that serves as your master workflow document, containing a node for each step with attributes and elements that detail it's name, order in the process, conditions for whether it's optional or not, etc. Most importantly each step node can have a unique step id.
如何混合序列化和数据库模型。拥有一个作为您的主工作流文档的XML文档，其中包含每个步骤的节点，每个步骤都包含一些属性和元素，这些属性和元素详细描述了它的名称、过程中的顺序、它是否可选的条件等等。
Then in your database you have a simple two table structure. The BoxItems table stores your basic BoxItem data. Then a BoxItemActions table much like in the solution you marked as the answer.
然后在数据库中有一个简单的两个表结构。BoxItems表存储基本的BoxItem数据。然后，在您标记为答案的解决方案中，一个boxitemaction表非常类似。
It's essentially similar to the solution accepted as the answer, but instead of a BoxItemTasks table to store the master list of tasks, you use an XML document that allows for some more flexibility for the actual workflow definition.
它本质上类似于作为答案被接受的解决方案，但您使用的XML文档允许实际工作流定义具有更大的灵活性，而不是BoxItemTasks表来存储任务的主列表。
#4
0
For what it's worth, in BizTalk they "dehydrate" long-running message patterns (workflows and the like) by binary serializing them to the database.
值得注意的是，在BizTalk中，他们通过将长期运行的消息模式(工作流等)序列化到数据库来“脱水”。
#5
0
I think I would serialize the Workflow object to XML and store in the database with an ID column. It may be more difficult to report on, but it sounds like it may work in your case.
我认为我应该将工作流对象序列化为XML并使用ID列存储在数据库中。报道起来可能比较困难，但听起来对你来说可能行得通。
#6
0
For this kind of problem, consider the database schema shown in http://www.databaseanswers.org/data_models/workflow/index.htm which models a series of events in a busniess process.
对于此类问题，请考虑http://www.databaseanswers.org/data_models/workflow/index.htm中所示的数据库模式，该模式对busniess过程中的一系列事件进行建模。