Re: Storing and Retrieving Parts Lists / BOM Lists (Individual Jobs) where to put active storage

BrianDP wrote:

> (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? **

Yes.

> 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
>
Received on Fri Jan 13 2012 - 12:18:39 CET