April 13, 2006 at 10:06 am
I have a database that manages 'work orders'. I add 'work order items' to the 'work orders' that represent tasks that must be completed. Until now, the 'work order item' descriptions have been typed in. I have created a list of standard 'work order items' to select from to populate the 'work order'. It is my desire to create dependencies between standard 'work order items' so that when an item is selected to be added, the required dependents and precedents are also added as 'work order items'. I am curious if there are any ideas on how to set up the data structure in SQL Server to accomodate this.
This is the table for the standard 'work order items':
| WorkOrderItemsList | |||
| WorkOrderItemID | int | 4 | 0 |
| Description | nvarchar | 75 | 1 |
| WorkOrderItemClassID | int | 4 | 1 |
| ProcedureID | int | 4 | 1 |
| DeptID | int | 4 | 1 |
| DeptUnitID | int | 4 | 1 |
| WorkStationID | int | 4 | 1 |
| MinutesToComplete | int | 4 | 1 |
I would like to relate them in such a way that they could be viewed somewhat like the following table. 'F' represents a Forward relationship, 'B' represents a Backward relationship, and a 'C' represents a Concurrent relationship. For example, Item2 would need to be completed prior to Item1.
| Item1 | Item2 | Item3 | Item4 | Item5 | Item6 |
Item1 | X | B | C | F | F | C |
Item2 | F | X | C | C | F | F |
Item3 | C | C | X | F | F | C |
Item4 | B | C | B | X | F | B |
Item5 | B | B | B | B | X | B |
Item6 | C | B | C | F | F | X |
It would be great not to have to create relationships multiple times, i.e. the relationship could be created once and read by either precedent or dependent and handled appropriately. It would already be implied. In this case, the values in red would not necessarily need to be created individually.
Any help is greatly appreciated.
Thanks,
Corey
April 13, 2006 at 10:42 am
This is, as I'm sure you've figured out, a non-trivial problem.
First, are you going to allow tasks to be added to work orders that are NOT in your list of standard tasks? Personally, I recommend it. Which means there isn't really a "relationship" in RI terms between the WorkOrderItems table and the Tasks table.
So, what you would do is set up quite a bit of logic in your front-end code to handle this. First, create a table of Tasks, and a table of TaskDepends.
Tasks should contain:
tskID -- primary key, preferably some abbreviation as a natural key, but probably an identity column
tskDesc -- description of the task, which is what will actually be stored in the WorkOrderItems table
TaskDepends should contain:
tdID -- primary key, probably identity column
tdPrimaryTask -- the tskID of the task you are adding to WorkOrderItems
tdDependTask -- the tskID of another task that should be automatically added
tdRelation -- B, C, or F, as described in your post
Then, create a drop-down list in your application that uses Tasks as the datasource. Add AfterUpdate code to check the TaskDepends table, and add each referenced task in the correct order.
Now, if you really want to limit WorkOrderItems to only pre-existing Tasks, you can set up a relationship. Then, just put a trigger on the WorkOrderItems table to also add tasks from the TaskDepends table for each update or insert (watch for recursive triggers, as the automatically added tasks could generate more tasks of their own!!)
April 13, 2006 at 11:58 am
-- First, are you going to allow tasks to be added to work orders that are NOT in your list of standard tasks? --
Yes, only because I believe there will be much abuse of the system if I allow it. There is a possibility, though, that I could allow the department admin to edit these entries and apply the correct existing 'item' or create new 'items' as needed.
-- TaskDepends should contain:
tdID -- primary key, probably identity column
tdPrimaryTask -- the tskID of the task you are adding to WorkOrderItems
tdDependTask -- the tskID of another task that should be automatically added
tdRelation -- B, C, or F, as described in your post --
Wow... I had modeled as one of my possible solutions. I guess I was a little put off by labeling them 'primary/dependent', or 'item_1/item_2' in my case, because I was afraid of implying any kind of priority. It really is necessary, though, to establish the direction of the relation. I do like this because it requires that the relationship be created only once. I can maintain the global order of execution in the 'Tasks' table.
I wish to use two datagrid controls on my 'Item Manager' form. They would both be populated with the list of standard 'work order items'. When I select an 'item' in the first datagrid, I would like the second datagrid to display an icon showing the type of the relation between the two (no icon if no relationship exists).
Given this design, my first thought is to add the extra column for the icon in the second grid. Then, on my FormattingRow event, I would query the 'Dependencies' table for the ID's of the two 'items' (primary grid item and dependent grid item) and display the appropriate icon for each row. This seems a little busy. Do you know of a better way? I guess I could also set up two SELECT queries and first gather all of the 'items' related to the selected 'item' and then use a NOT IN keyword to get the rest of the unrelated 'items'.
BTW, thanks for the help.
Corey
April 13, 2006 at 12:15 pm
Oh, one thing I thought of. Because you can't have more than one relationship between any two tasks (e.g., both concurrent and forward), you could probably actually make tdPrimaryTask + tdDependTask a multi-column primary key. That would both remove the necessity for the identity column, and ensure the uniqueness of the relationship.
You'd want to create a cross-tab query to display the datagrid you want. Poke around here and in the T-SQL forum, and you'll see several ideas for doing that. If you create it with a fixed number of columns (fixed, obviously, to the number of tasks you have), it will display a NULL where there is no relationship. You'll have to tweak it to also show an X at the intersection of a task with itself.
April 14, 2006 at 11:32 am
Good tip.
Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply