I want to model an assembly line in SQL Server. This object would progress through a linear set of steps. Each step would have a linear set of statuses: Waiting, In Process and Completed. Which is the best approach for capturing data about a change in step and/or state? Insert one record for the object and update a step field and a status field when these properties change? Or, should I insert a new record every time the object progresses to a new step or changes a status in this step? I have tried the latter and I found the SQL queries required to be complicated.
A:
If one object has only one status, I suggest the status field approach: easier to manage, faster to query. I used this solution in some applications and I haven't seen any problems.
siposa
2009-07-20 19:30:50
You lose almost all your history information, which is typically something that you want to report on in assembly-line type applications.
Evan Anderson
2009-07-20 19:32:11
+2
A:
This belongs on Stackoverflow and I've voted to close it as such. having said that, I will say that I've designed and implemented systems like this (an inventory system for a motorcycle parts manufacturer being the most recent) and the flexibility that a "transactive" model (i.e. the "insert a new record" choice) is wonderful and its utility far outweighs any "complexity" in the queries. Using the "MAX" and "MIN" aggregate functions against the datetime fields that identify the time an operation happend along with the "TOP..." qualifiers on SELECT statements can make the complicated queries much less complicated.
Evan Anderson
2009-07-20 19:31:42
A:
Workflows in general are best modeled with queues. See Building the MSDN Aggregation System, and I know of some real factory assembly lines that also use queues.
Remus Rusanu
2009-07-20 21:37:06
A:
You could do both: maintain a "current" state for each object and a history of progression in case you need to see what path the object took through the queue and how long it took in each one. If each state requires more state information (that is different for each state) then you will need to create separate tables for each grouping of state information or, if you can represent it as a set of key-value pairs, create a simple property table.
Here's a rough example of a table layout:
Object Table:
Id, Name, Description, State
ObjectHistory Table:
Id, ObjectId, Timestamp, State
Properties Table:
Id, ObjectId, Name, Value
--or--
State1Info Table
Id, ObjectId, ...
State2Info Table
Id, ObjectId, ...
Garo Yeriazarian
2009-07-21 13:37:04
Any time you maintain "totals" (by duplicating data, which is, essentially, what you're talking about doing) you run the risk of the "total" coming out of sync with reality. You should normalize first, and then violate normalization rules when you have really, really good reasons to do so. If you wanted the "current" state of object 1 in your hypothetical schema above, one would only need to do "SELECT TOP 1 State FROM ObjectHistory WHERE ObjectId = 1 ORDER BY Timestamp DESC". There's no reason to maintain a duplicate of the state elsewhere.
Evan Anderson
2009-07-22 03:16:33