When to do DB Insert

[Luis]

Application X has three screens. The user captures information on
screen one and then clicks a navigation button to go onto screen two.
He does the same on screen three. At the bottom of screen three is a
submit button. When this button is clicked the system does some
calculations with the information that is supplied and then uploads
all the info that was captured on the screens to the database.

My question is: Should the data be uploaded to the database all on one
go when the user clicks the submit button or should it be uploaded
after each screen - ie when the user clicks the button on Screen 1 the
data is uploaded to the database and then screen two is displayed.

Which is the better approach?

My problem with doing the upload after each screen is that the user
might click the back button and change the info that was captured on
one of the previous screens. I would have to add code to each screen
to check if any of the data that the user captured has changed and
then update the database appropriately.

Which approach should I take?

Thanks

 

[Alan Howard]

Horses for courses. A database is just another persistence mechanism - like
a form post, querystring, session variables or text file. You might want to
insert into a database to avoid problems with, say, your session timing out
when your user knocks off from morning tea, or to avoid really long URLs
from being presented to the user in the browser's address bar. To manage the
use of a back button, you can do your initial insert and return the ID of a
record that marks a user's submission - whatever you decide that is. Your
insert scripts can then check to see if data already exists for this
submission, and do an update instead of an insert. You pass this
SubmissionID from page to page in your workflow, and at the end of your
workflow, mark the submission as complete so that you and your application
know that row of data is complete and valid.

You'll need to consider how you're going to identify incomplete submissions
(session timeouts for e.g., users that don't complete all the pages, etc.)
and how robust your forms need to be. Be careful to consider what your users
expect to see - if they click 'Next' on page two, and then *your
application's* 'Back' button on page three do they expect to see the data
they just submitted on page two - probably yes - and you can retrieve this
from the database. If they click *the browser's* back button on page three
to return to page two, their expectations may be different so make sure that
your code accommodates this.

Some things to think about.

Alan

 

[Luis]

You might want to
insert into a database to avoid problems with, say, your session timing out
when your user knocks off from morning tea <snip>

Those are the problems I want to prevent.

You'll need to consider how you're going to identify incomplete submissions
(session timeouts for e.g., users that don't complete all the pages, etc.)

This is my biggest concern. I don't want the database to end up with a
whole bunch of incomplete records because the user did not complete
all the screens and click the submit button on the final screen.

How do I handle this?

 

[Alan Howard]

"Alan Howard" <[email protected]> wrote in message

Those are the problems I want to prevent.

etc.)

This is my biggest concern. I don't want the database to end up with a
whole bunch of incomplete records because the user did not complete
all the screens and click the submit button on the final screen.

How do I handle this?

I generally avoid using the Session to cache variables unless it's stuff
that really isn't expected to change for the duration of the session - a
username perhaps. I'm also not a fan of carrying a whole bunch of variables
around in a form post or querystring - variables that were defined on the
previous pages of a workflow.

When I need a multi-page workflow I opt for the save-to-database approach.
When the first form is submitted the insert into the database returns a
ResponseHeaderID - a ResponseHeader record groups all Responses from one
pass through the workflow. You pass the ResponseHeaderID from form to form,
and you code enough logic into your procs to determine whether new data from
each from needs to be inserted, or whether what's already there needs to be
updated (user may have backtracked). On the final submission you mark the
ResponseHeader as 'complete' (IsComplete BIT NOT NULL DEFAULT(0)) - set the
IsComplete column to 1. By filtering on the value of this column when
looking at your results you can ignore all of the incomplete ResponseHeader
records, and associated Responses.

Alan

 

[Luis]

When I need a multi-page workflow I opt for the save-to-database approach.
When the first form is submitted the insert into the database returns a
ResponseHeaderID - a ResponseHeader record groups all Responses from one
pass through the workflow. You pass the ResponseHeaderID from form to form,
and you code enough logic into your procs to determine whether new data from
each from needs to be inserted, or whether what's already there needs to be
updated (user may have backtracked).

That's what I've done.

On the final submission you mark the
ResponseHeader as 'complete' (IsComplete BIT NOT NULL DEFAULT(0)) - set the
IsComplete column to 1. By filtering on the value of this column when
looking at your results you can ignore all of the incomplete ResponseHeader
records, and associated Responses.

So you would fileter out the "incomplete" submissions by doing
something like:
SELECT x,y,z from TableName where IsComplete = "1".

With this approach the user would only ever have access to the
"complete" records on the database - but it would still "leave the
dirty laundry under the bed" (and my mother would love that!). I want
to be able to remove those incomplete records.

I thought of a slightly different approach after reading your post -
maybe it will make sense?

I would create a seperate table on the database where all the data
from the various screens is stored while the user is working. When the
user clicks the Submit button I do the calculations that I need to do
onSubmit. Then I move all the data from the "temporary" table to the
main tables and delete the record in the temporary table. The result
is that the main tables would only contain complete records.
Incomplete records would remain in the "temporary" table (maybe I
could provide a "Resume previous session" function).

Then I just set up a job to truncate the temporary table on a regular
basis. End of problem! (?)

Is this a good approach?

 

[Alan Howard]

"Alan Howard" <[email protected]> wrote in message

I would create a seperate table on the database where all the data
from the various screens is stored while the user is working. When the
user clicks the Submit button I do the calculations that I need to do
onSubmit. Then I move all the data from the "temporary" table to the
main tables and delete the record in the temporary table. The result
is that the main tables would only contain complete records.
Incomplete records would remain in the "temporary" table (maybe I
could provide a "Resume previous session" function).

Then I just set up a job to truncate the temporary table on a regular
basis. End of problem! (?)

Is this a good approach?

It depends how important it is to you to clean out those temporary records.
If you can quantify how long you need to persist a user's incomplete
responses (the resume previous session functionality you mention) then just
add a smalldatetime column to store the creation date of a response, and
then schedule a job to delete those records where IsComplete = 0 that are
older than a given age. You don't need to complicate the design of your
database by adding additional tables - you're still going to have partial
records floating around somewhere.

 

[Luis]

It depends how important it is to you to clean out those temporary records.
If you can quantify how long you need to persist a user's incomplete
responses (the resume previous session functionality you mention) then just
add a smalldatetime column to store the creation date of a response, and
then schedule a job to delete those records where IsComplete = 0 that are
older than a given age.

EXCELLENT idea! Thanks...

You don't need to complicate the design of your
database by adding additional tables

Very true...

I've converted almost all of the asp pages so that they maintain state
on the database using the ideas you suggested (previously using a
whole bunch of session variables - which I really wasn't happy with).

It all seems to be going well so far...

Thanks

 

[Alan Howard]

Good-o. Let us know how it goes.

Alan

EXCELLENT idea! Thanks...


Very true...

I've converted almost all of the asp pages so that they maintain state
on the database using the ideas you suggested (previously using a
whole bunch of session variables - which I really wasn't happy with).

It all seems to be going well so far...

Thanks