1 Reply Latest reply on Nov 24, 2009 8:28 AM by philmodjunk

    Ideas on how to set up an inventory...please.

    cheathamtech

      Title

      Ideas on how to set up an inventory...please.

      Post

      I'm inventorying the tech equipment at several schools by room.

      I'll have unique info about monitors, projectors, computers, etc.

       

      Each object has different fields... like a computer has hard drive space and RAM; a projector has bulb wattage, etc.

       

      Should I set up a table with school info, a table with room info, a table with teacher info, then a separate table for each piece of equipment with unique fields...

       

      Or is there a way to set up an "equipment" field which immediately change to reflect the needed fields when I identify the equipment as "computer," "projector," etc.  ?

       

      I know any of these ways may work but I also know experienced DB folks will have wider insight than my own.

       

      Thanks!

       

      Dale 

        • 1. Re: Ideas on how to set up an inventory...please.
          philmodjunk
            

          Sometimes only the user and developer can answer the question of "which way is best" as you are the ones that know how and why each piece of data needs to be recorded in your database.

           

          Here's a description of some options and some of the trade-offs for each:

           

          Separate tables for each type of equipment.

           

          If you can categorize your equipment so that they all fall into just a few categories, this might work. You would have a table were one record = 1 item and is used to list all items of all types. An "Item Type" field in this table would identify which "equipment type" table stores the detail fields specfic to that equipment type. This makes it easy to set up tables and layouts to record such info, but reports listing detail info for multiple types of equipment become difficult to set up and the more "detail" tables you need, the more design work you have to do creating/maintaining tables and the layouts that refer to them.

           

          One Table for all equipment.

           

          In this approach, as you find you need a field for a specific equipment detail, you simply add another field to the table definition. For any one type of equipment a significant number of fields will simply not be used and will not be visible on any layouts you create for displaying the detail fields for a specific equipment type. To view such info for all computers, for example, you perform a find for all records in this table of type "computer" and then switch to the "computer" layout to see just the fields applicable for this one type of equipment. This approach makes combined equipment reports easier, but still will require separate "detail" layouts for different equipment types.

           

          Use a "feature" table of related details.

          In this approach, you have your unified table for all equipment, but then define a details table with the following fields:

          EquipmentID

          FeatureName (text)

          Feature (text)

           

          In this approach, you would display the Feature records in a portal on a layout based on your equipment table. Completely different equipment can be documented on the same layout as each type of equipment will differ only in the specific list of "feature" records displayed in the portal. Adding new types of equipment now becomes a data-entry operation instead of having to design new layouts and/or tables. Unified reports of all equipment can be designed on a layout that references the Features table but with other data drawn from fields in the Equipment table via the relationship linking the two tables. The drawback here is that any data displayed as the result of a calculation performed on a "feature" field is tricky to set up and your generic "all equipment" layout may not be as clear and easy to use as a layout customized for one equipment type. You'd also probably want to create a "template" table that lists all the FeatureNames you'd want for a specific type of equipment so as to standardize the records for each type. You'd then need a script to generate a set of such Feature records for each new equipment record you'd create.