If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Simple Database Design Questions.

Hello dbforums. I’m new here, and as you’ll see, a novice when it comes to access and databases in general, but am interesting in learning more. I’m looking for some help designing a simple database with access 2007 to help me with a project. Below I’ve outlined the scenario along with my initial design. Any help would be much appreciated.

Scenario:

> I have a bunch of file folders located in multiple boxes that need to be scanned to PDF. I will manually go through each box, folder by folder one at a time

> I have a list of folders that need to be scanned in an excel spreadsheet by “folder number” which will be imported to the “ScanList:Table” (no duplicates)

> As I’m going through the boxes I’d like to input the folder number to an access form and have it tell me whether the folder should be scanned or not.

|-> If the folder is to be scanned then I will physically move the folder to the new box and log the box number to the database via the same form.

|-> If the folder is NOT to be scanned, then I will move that folder to a different box and log the box number via the form.

> Repeat process…

Here is my Initial Design

ScanListTable:Table
*FolderNumber:Field

MasterFolder:Table
*ID:Field
FolderNumber:Field
BoxNumber:Field

Box:Table
*BoxNumber:Table
Scan:Field (yes / no)

Below are the relationships I have setup. I'm no doubt struggling with this... I've also zipped my initial database.

It sounds to me like you have three major things you're working with here: papers, folders and boxes. I understand that folders are physical folders that are placed in physical boxes. I do not understand the relationship between folders and papers. Of course I understand that folders are likely to consist of papers, but I don't understand how or if you'd like to track that relationship.

All that said, I think you need one table for folders, one table for boxes and one table that stores which folders are in which boxes. If papers are to be stored as well, then I would also create a table for just papers, then a second table for storing which papers are in which folders.

thanks for the quick response and sorry for confusion , I will try to clarify.

I'm not concerned with the papers within the folders at this point; I will once I get down to scanning them, but for all intents and purposes, this database will not cover the papers.

For now, I need a way of distinguishing if a folder within the original boxes need to be scanned or not.

I will be going through the original boxes pulling one folder at a time and inputting each folder number individually via access form. Once inputted, access will tell me if the folder needs to be scanned or not. This would be done via a lookup as I already have a list of folder numbers that need to be scanned. If the match is positive it will be treated as a "ToBeScanned" folder and placed into a new box which needs to be logged into the database.

If the folder number does not match my imported list then I can assume that the folder does not need to be scanned and will be placed into a separate box and logged into access.

So in the end I will have 3 box groups

Original Boxes
To Be Scanned Boxes
Do Not Scan Boxes

The original boxes will be empty as all the folders would have been pulled from there and placed into either the ToBeScan boxes or the or DoNotScan boxes.

I then, would like to run a report on all folders in either box group as well as all the folders contained with in the given box (e.g., box A000001 contains x folders etc... )

Ok... so you currently have a bunch of boxes with folders in them, some of those folders need to be scanned and some don't. If they do need to be scanned, you will place them in a new "to be scanned" box. If they do not need to be scanned, you will place them in a "not to be scanned" box. When you are finished with your task, the boxes which have the folders in them right now will be empty.

Is that all correct?

If so, then you are only dealing with two entities: folders and boxes.

tblFolders
-----------
folder_id
description

tblBoxes
---------
box_id
description

You also have a list of folders which are to be scanned. This ends up being a "yes/no" field which could be attached to tblFolders, assuming this is a one-time shot and you won't be doing this on a routine basis. If it's something that will happen often, then you'll probably want to break out the "lists" of folders in to batches.

Here's the former:

tblFolders
----------
folder_id
description
scan

And the latter:

tblScanBatch
------------
scan_batch_id
scan_date

tblScanBatchFolders
------------
scan_batch_id
folder_id

So now you have a way of defining folders and boxes, as well as a way of determining which folders should be scanned. Now you need a way to associate folders with their new boxes. This introduces a new question of whether you'd like to track every box a folder has been in, or if you'd like to simply assign a folder to a single box and call it a day.

Former:

tblFolder
----------
folder_id
box_id
description
scan

Latter:

tblBoxFolder
-----------
box_id
folder_id
boxed_date

You can also apply a similar set of options for determining whether a box should be scanned or not. You could either attach a yes/no field to the box table, or you could work up a set of tables that track which boxes were scannable for a given batch.

As you can see there are several options to choose from depending on your plans for the overall life of your application. As always, I urge you to err on the side of flexibility and scalability vs. rigid but fast and easy to build, unless you are ABSOLUTELY SURE you will not ever have additional needs from your application (which would be ridiculously rare).

The structure for the folder and box tables makes sense to me, but am having a hard time understanding how to attach a yes/no value from the xls list I will import.

If there are ~5000 folders and ~2500 of them need to be scanned, does that mean that my folder_id table will start with the ~2500 records that would be imported?

The list itself will be a one time import that will take place at the start of the project. I might have left this information out; but there will most likely be folders that have the same number that are located in multiple boxes (original boxes). When I move these folders to the destination boxes (be it "to scan" or "not to scan") I imagine they will also most likely end up in different boxes. Once a box is filled and cannot fit additional folders it will go straight to processing (scanning) or storage depending on if it needs to be scanned or not to be scanned. Does this pose any major problems?