(If you get really bored with my background, please skip to the ** **
paragraph, second from the bottom to get to my question. Thank you.)

I wrote a Parts List/Bill of Materials program for a company in the
waining months of the last century, and per the customer's
requirement, the entire thing was written in Access. This was good,
since it was really sort of a re-work of the current application they
were using in Dbase. This was a big step forward since only one
person could lock the dbase tables at a time, thus making it a single
user application.

The conversion of the structure from the Dos world into the Access one
was problematic. Currently, each of the users' jobs are each in their
own table. There is one central database that stores all of these
tables. When the user wants to edit their job, they retrieve the
tables that are associated with their jobs, copy those tables to the
workstation - I term the process "Checking out a job" for
simplicity.

The engineer edits those tables until the job contains the parts they
require for the job, they print it, and then they "check it back in",
or copy it back up to the network. This method is somewhat in-
elegent, for one thing, this creates excessive numbers of tables in
the back end. I had to write an "Archive" utility that copies them
out of active storage and puts them in "inactive storage" so at least
it doesn't bog down the "live" data. I was still fairly new to
database theory back then, and I could see that this violated all 5
rules, but exactly how, I couldn't explain to my bosses, so they said
just make it work, and you can worry about how pretty it is on the
next re-write.

12 or 15 years later it's time to revisit this issue. I want the data
moved into SQL. This will be my second conversion of an access back
end to SQL. I've continued to use the front end programs as they are
so I won't have to re-write the front end just yet. At least having
the data in a SQL back end will provide a level of security, and
reliability that we have not had with the current Access backend.

The first thing I want to do is combine all the individual jobs into
two tables, one for the header and one for the detail records. There
are some other data involved, but this is the major structure.

** My theory question is this: When it comes time for the users to
edit their individual jobs, should I have them edit the SQL table as
it resides on the server with their keyed jobs, or should i have local
tables, and have the data copied to their workstation, let them edit
it, and then update the data back to the server side? **

Sorry for the background, but I thought it was necessary. If you see
something else in my scheme that needs to be addressed, please let me
know. Thanks in advance.

-BrianDP
Best Data Processing

BrianDP wrote:

Yes.

BrianDP,

Your application users know about the state you described. But it is only apart of their state, which has parts reflecting long transactions on that first part. Long transactions typically involve a user policy like check-out-check-in to manage waste from possible simultaneous editing. So your application state should probably involve certain extra state about long transactions. These parts have relations and attributes like the first part but contain in-edit values plus transaction attributes plus possibly relaxed constraints. (Of course, it's all in the same database. And any justified implementation architecture should be hidden within the dbms.)

(Think of editing a shared string. If it's an essay then maybe the users would like to know others are composing changes. Maybe editing is exclusive. Maybe editing by paragraph would be a better policy (exclusively or not). The set of characters is constrained like the shared value but maybe nothingelse is until you hit 'enter'. Regardless, if in-edit pre-'enter' state has to be available indefinitely, it should be in the database. And regardless, if you "need" a distributed implementation, which should be hidden from the user, then you need such a dbms.)

See http://thehelsinkideclaration.blogsp...this-blog.html about "the fat database".

philip