3 Replies Latest reply on Mar 14, 2017 12:29 PM by siplus

    Design question


      I'm having a tough time deciding how to handle data entry and was hoping someone could shake me from my current brain freeze.


      It's an inventory control program. People are assigned items for a job.

      There' s an invoice table (tbleInv) that has the job number, person assigned to the job and date of the job.

      There's a main equipment table (tblEquip) that has details of the equipment, plus a link to the invoice table and a unique serial number

      A Line Item table (tblLineItem) has a link to the invoice table, a unique serial number and a link to the main equipment table. This is where all the data for the specific job will be stored.


      Pretty simple and things are running good.


      The trouble is, there is also an accessories table. Some of the equipment (tblEquip) will have accessories but not all.  Every accessory will be linked to a particular piece of equipment, however not ALL accessories will be needed with the main equipment on every job (for instance, a camera will have to go to the job site, but perhaps only 3 of the 5 accessory lens for that camera will need to go.)


      My first thought was to put the accessories in a separate table from tblLineItem and combine them in a report simply because I didn't want to have to scroll though a huge drop-down box searching for accessories. This way, when a person is entering the main equipment, they can do so and if the equipment needs accessories, they can simply click on a second drop-down box and enter all needed accessories into it's own table (tblAcc).


      Now I'm not so sure and am wondering if it's better just to combine everything, main items and accessories in one table for ease of retrieval.


      I have no idea why I'm going cross-eyed on this

        • 1. Re: Design question

          tolson wrote:


          It's an inventory control program. People are assigned items for a job.


          I lost you here.

          • 2. Re: Design question

            People are assigned items for a particular job.


            Maybe rather than "inventory control" I should say it's a an assignment program. An administrator will use this to assign specific equipment to crews as they go out to a jobs. Once the equipment piece is assigned, it won't appear as a selection until after the job is done and the equipment checked back in.


            My main problem is conceptually how to handle the storage of some of the smaller items that may or may not have to go out to a job site.

            • 3. Re: Design question

              I once had this problem.


              I solved it by defining 3 categories of equipment:


              - single items (each has an ID)

              - suitcases (which were associations of items into a collection of specific ID's)

              - sets (which were associations defined at the single item level, but extracting the available ID's from the current unused "inventory").


              Maybe this can ring a bell for you.