Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2010
    Posts
    3

    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.

    Am I on the right path? Do you see any red flags here?
    Attached Thumbnails Attached Thumbnails ScreenShot090.png  
    Attached Files Attached Files
    Last edited by joesm; 01-12-10 at 02:05. Reason: clarification

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I don't think I fully understand your business requirements.

    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2010
    Posts
    3
    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... )

    Does that help clear it up?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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).
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jan 2010
    Posts
    3
    Your guidance is much appreciated Teddy, thank you.

    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?

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Yes, that creates problems. If you have no unique way of identifying folders, then you have no way of tracking them. At this point you might actually get the most bang for your buck out of excel.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •