Best practices when handling data being stepped along a process

Question

I am currently creating a Windows App for a handheld device (WM 6.0, CF 2.0, sqlce 3.5) that pulls data from the server only when connected to a dock (all the significant data is maintained/altered on the handheld til it comes back and is docked for a sync with server). The data is similiar something UPS would do in that it manages pickups and dropoffs of items from a location to a location and records the time these "transactions" occur.

Initially, the handheld has a couple source tables (pickups and dropoffs) and some validation tables. During a given pickup or dropoff, I am trying to figure out the best way to move the data along this multistep process.

For a pickup I currently...

1. first select a subset of the items from pickup based on a common characteristic, lets say the package is the color "blue". So i select all "blue" packages and insert them into a temppickups table.

2. then the user has to determine of all the blue packages that need to be picked up (items in temppickups) which ones need to be picked up at the location he is at so he selects a "pickup site" and all the corresponding items in temppickups are inserted into tempsitepickups.

3. Then the user chooses all the items that he wants to pickup from a list (from tempsitepickups) and runs a report to confirm completeness.

4. Then he confirms that these are the ones he wants and the items are the n transferred to a separate table (pickedUpItems, with added columns to record delivery time and stuff) to make it easier to update the server (instead of having to create a select statement from the original tables to find the updated records)

Now, i could have the original table have the additional columns filled with NULL until a delivery time is inserted, then use IS NOT NULL as a limiter on syncing only the updated records. However, my trend has been that when the data is altered from its parent table it gets put into a new table (this only happens 3 times= 3 new tables) until it reaches a destination table (when it does all the new tables between the original and the destination are emptied for the next pickup or dropoff.

Is this the best way to handle this kind of data tracking as it gets manipulated in an application? Your thoughts would be of help. Thanks!

*And let me know if you would like clarification on some aspect of this hypothetical.

Answer 1

What immediately comes to mind is having just one table, and have a "state" column, eg. current state of the item (item selected, site selected, item picked up etc). Additional columns that you refer to could be placed in a separate table.

But on the other hand, having separate tables simplifies and speeds up the SQL queries, especially if you have many records.

Just some thoughts...