OT: Alternatives for persisting work in progress.

[Bob Johnson]

I would appreciate your thoughts, perspectives, and opinions on the two
design alternatives described below for the following requirement (to be
implemented in a distributed system with both a Windows Forms and an ASP.NET
client (Framework 2.0).

I have a requirement to enable users to start a series of steps, complete x
of y of the steps, then come back and finish the series at some later time.

More specifically, the users can submit a work request. The request must
then be approved by a manager. After preliminary approval, users can proceed
to add further details to the request, then submit for final approval. Once
final approval is obtained - the data can be stored in a database. The
customer wants no "incomplete requests in the database."

I am considering the following two solution designs:
DESIGN 1 ------------------
have some sort of "flag" in the database that indicates the current status
of the work request. The client app can then account for the current value
of the flag and respond accordingly (i.e., presenting the user with the
appropriate "next step" given the current flag value).

DESIGN2 ------------------
Have no such "flag" (as described in option 1 above), and instead model the
work request - in the database - as if it is always in a
final/completed/approved state. In fact, the request never makes it into the
normalized database tables until it is in the final/approved state. Before
requests reach that final/approved state, however, all of the client objects
comprising work requests that are "in progress" get serialized (perhaps in a
binary stream written to a BLOB column). So in action it would look like
this:
STEP 1 - end-user creates a new work request and inters
preliminary/required data. Client objects are then serialized when end-user
session ends. Manger is notified of pending work request needing preliminary
approval.
STEP 2 - manager fires up application; client objects comprising the work
request are deserialized. Manager then approves and updates object state.
Objects are then serialized.
STEP 3 - end-user then opens approved work request; objects are
deserialized. Final state changes are made; then objects are serialized.
STEP 4 - Objects are deserialized; manager gives final approval and makes
final state changes to the objects. This final/approved work request is then
saved to the normalized database tables.

So withi Design 2, the first time the normalized database tables see this
work request, it is in a final/approved state, with all required values in
place.


Thanks for your time and consideration.

-Bob

 

[Alberto Poblacion]

I am under the impression that design 1 would be simpler to implement than
design 2. If the customer wants "no incomplete requests in the database",
provide a View in the database that filters on the "complete flag", and have
the customer access the database through that view. This is not much
different than design 2, which would also have the incomplete designs in the
database, even if their storage form is "serialized". In both cases we are
storing in the database the incomplete requests, each one using a different
technique to hide them from the customer. Since they are technically
equivalent, go with the one that is simpler to implement, which IMO is the
first one.

 

[Bob Johnson]

Hi Alberto - thanks for your response.

You are right that in both cases incomplete data gets stored in the
database. But an important difference is that the serialized-to-BLOB-column
option keeps the normalized tables cleaner. Consider that the normalized
tables (not the table storing serialized BLOB data) will need to have some
fields defined as NOT NULL (i.e., "required"). To store incomplete work
requests would require those columns to be nullable. Consequently, to get
data integrity, I'd have to place table-level constraints that
allow/disallow null values based on some "status" flag value. Doing this
complicates the implementation. So one benefit of option 2 (serialize object
state outside of the normalized tables) as compared to option 1 is that we
could keep the database design simpler; incomplete records never make it
into the normalized tables because it only lives in some BLOB column
(perhaps even in an entirely different database).

Again, thanks for your response...

 

[Alberto Poblacion]

You are right that in both cases incomplete data gets stored in the
database. But an important difference is that the
serialized-to-BLOB-column option keeps the normalized tables cleaner.
Consider that the normalized tables (not the table storing serialized BLOB
data) will need to have some fields defined as NOT NULL (i.e.,
"required"). To store incomplete work requests would require those columns
to be nullable. Consequently, to get data integrity, I'd have to place
table-level constraints that allow/disallow null values based on some
"status" flag value. Doing this complicates the implementation. So one
benefit of option 2 (serialize object state outside of the normalized
tables) as compared to option 1 is that we could keep the database design
simpler; incomplete records never make it into the normalized tables
because it only lives in some BLOB column (perhaps even in an entirely
different database).

In this case, I agree with you. If the characteristics of your data are
such that the database constraints would have to be "tweaked" to allow the
incomplete requests, then I would go with the serialization option. I prefer
to have a clean and simple database.

 

[James Irvine]

Hi Alberto - thanks for your response.

You are right that in both cases incomplete data gets stored in the
database. But an important difference is that the serialized-to-BLOB-column
option keeps the normalized tables cleaner. Consider that the normalized
tables (not the table storing serialized BLOB data) will need to have some
fields defined as NOT NULL (i.e., "required"). To store incomplete work
requests would require those columns to be nullable. Consequently, to get
data integrity, I'd have to place table-level constraints that
allow/disallow null values based on some "status" flag value. Doing this
complicates the implementation. So one benefit of option 2 (serialize object
state outside of the normalized tables) as compared to option 1 is that we
could keep the database design simpler; incomplete records never make it
into the normalized tables because it only lives in some BLOB column
(perhaps even in an entirely different database).

Again, thanks for your response...

Here's how one large company I worked at loads a large number of records
daily, and cleanly separates 'production, approved data' from those in
the process of passing all the approval steps:

1. The source text files are loaded as they arrive into 'staging'
database tables. These tables have no constraints -they just load what
was sent us. Aside from that, they are mirror-image layouts of the
'production' db tables. In your case, you could add fields in the stage
tables for whatever flags or notes you need to track the ongoing status.

2. The data in these staging tables are then validated a variety of ways
(similar to your management approval steps), and when everything is
deemed a-ok, the records are moved from the stage tables into the
production tables, which *do* have all the constraints, NOT NULL defs, etc.

It's been working this way 24/7 for years now, so I guess it's safe to
call it a successful solution.

 

[Rad [Visual C# MVP]]

I would appreciate your thoughts, perspectives, and opinions on the two
design alternatives described below for the following requirement (to be
implemented in a distributed system with both a Windows Forms and an ASP.NET
client (Framework 2.0).

I have a requirement to enable users to start a series of steps, complete x
of y of the steps, then come back and finish the series at some later time.

More specifically, the users can submit a work request. The request must
then be approved by a manager. After preliminary approval, users can proceed
to add further details to the request, then submit for final approval. Once
final approval is obtained - the data can be stored in a database. The
customer wants no "incomplete requests in the database."

I am considering the following two solution designs:
DESIGN 1 ------------------
have some sort of "flag" in the database that indicates the current status
of the work request. The client app can then account for the current value
of the flag and respond accordingly (i.e., presenting the user with the
appropriate "next step" given the current flag value).

DESIGN2 ------------------
Have no such "flag" (as described in option 1 above), and instead model the
work request - in the database - as if it is always in a
final/completed/approved state. In fact, the request never makes it into the
normalized database tables until it is in the final/approved state. Before
requests reach that final/approved state, however, all of the client objects
comprising work requests that are "in progress" get serialized (perhaps in a
binary stream written to a BLOB column). So in action it would look like
this:
STEP 1 - end-user creates a new work request and inters
preliminary/required data. Client objects are then serialized when end-user
session ends. Manger is notified of pending work request needing preliminary
approval.
STEP 2 - manager fires up application; client objects comprising the work
request are deserialized. Manager then approves and updates object state.
Objects are then serialized.
STEP 3 - end-user then opens approved work request; objects are
deserialized. Final state changes are made; then objects are serialized.
STEP 4 - Objects are deserialized; manager gives final approval and makes
final state changes to the objects. This final/approved work request is then
saved to the normalized database tables.

So withi Design 2, the first time the normalized database tables see this
work request, it is in a final/approved state, with all required values in
place.


Thanks for your time and consideration.

-Bob

Personally I'd go with design one for a couple of reason:
- You'll write less code
- It would be easier to report on requests and their statues
- It will be easier to write code against, since you can use data
binding
- Clearer, simpler design

 

[Bob Johnson]

RE:
<< It would be easier to report on requests and their statues >>

Excellent point. Thank you very much!