Procedures Database

October 12, 201015 yr

I am trying to build a Procedures database and I am stuck on how to handle the sequence of ACTIVITYs or sequence of ACTIONs.

Tables:

PROCEDURE, ACTIVITY, ACTION, ?

Each PROCEDURE is completed by performing one or more ACTIVITYs

Each ACTIVITY is completed by performing one or more ACTIONs

Each ACTIVITY in a PROCEDURE must have a unique number representing the sequence (or order) in which the ACTIVITYs are to be performed.

Each ACTION in a ACTIVITY must have a unique number representing the sequence (or order) in which the ACTIONs are to be performed.

Sample Procedure:

How to Bake a Cake (PROCEDURE)

Step 1: Obtain Ingredients (ACTIVITY)

Step 1.1: Collect ingredients (ACTION)

Step 1.2: Verify quantities available (ACTION)

Step 2: Prepare Oven (ACTIVITY)

Step 2.1: Remove Pots and Pans from Oven (ACTION)

Step 2.2: Set temperature to 350 degrees (ACTION)

Step 2.3: Set timer for 15 minutes (ACTION)

Step 3: Mix Ingredients (ACTIVITY)

Step 3.1: Per Recipe (ACTION)

Step 3.2: Lick the bowl (ACTION)

How do I support the need to number the ACTIVITYs, and number the ACTIONs for each ACTIVITY? This needs to be easy and have the results dynamically displayed once the ACTIVITYs or ACTIONs are created. Equally required is the ability to edit the order of any ACTIVITY or ACTION and have the results reflect the new order.

From a perspective of a User who is creating a Procedure using this database - the ability to have the results reflected quickly is important.

Does anyone have any way of doing this?

Thanks,

David

October 12, 201015 yr

Actions and activities are the same, except an Action is the first in the list. This simplifies things a lot.

October 12, 201015 yr

My approach would be to build the structure using join tables.

So, there would be a "library" of Activities, and "library" of Actions and a join table that relates them to each other.

Then, I'd build a join table btw Procedures and Activities.

The SortOrder field would be in the join tables.

October 13, 201015 yr

Author

Thanks for the input Vaughn. I have seen procedures structured in a variety of ways. Users will need the flexibility to create sub-actions or sub-activities. I thought a single table recursive for "activities/actions" might work as well.

An alternative view is to let the "activity" be a summary sentence of what the activity is - and let the actions be the specific instruction that are to be performed as prescribed. I admit the concepts are fuzzy and I have looked at it a few different ways. I am currently viewing it as such:

Activity (Does not describe how to do it - rather what will be accomplished after following actions for this activity)

Activity 1: Assemble engine into frame

Action (Instructs how to accomplish the Activity)

Action 1: Attach engine block to hoist using blah, blah, blah,

Action 2: Apply loctite to bolts using your finger

Action 3: Thread bolt by hand to start

Action 4: Tighten bolts to 200 ft/lb using XYZ torque wrench

Given the above example - do you still see activities and actions as being one in the same? I am pretty sure they can be treated structurally the same - as I think you are suggesting, but I do see each as being different in their meaning.

I am struggling with the interface - a User who is creating a procedure will need the ability to declare an "action or activity" as being Step 1, Step 2, etc. and then will need the ability to rearrange those steps. How to automate the numbers so they are ordered?

October 13, 201015 yr

Author

Hi Barbara (if I remember correctly),

I like the idea - not sure how well it would fit given the Procedures Database in theory should be able to cover a wide swath of business related procedures. I am concerned the library values will be messy - as the Users will be creating them. Maybe it's fine.

I think I will give that structure a go and see how I can get the data to fit.

As for the numbering, you mention placing the SortOrder in the join tables. Sounds easy enough. What I may be having a hard time describing is how can I provide an "interface" for the User who is creating a Procedure to rearrange the "actions" and have the Step #s reflect the change?

What do you build on the layout that allows Step #7 to be moved before Step #5 and have all the affected Steps renumber properly?

I envision a Procedure layout with a portal to show actions. Each action is either typed in - or a drop down pulling from a library table as you suggest. As the list of actions are built, the user will encounter the need to rearrange the sequence. What mechanism is used to allow either a portal row "drag to new location and numbers resort". If not that, perhaps an arrow showing up or down that allows a User to incrementally move a step up or down.

I hope this make sense, thanks.