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

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 inelegent,  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.