Results 1 to 5 of 5
  1. #1
    BEubanks is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    5

    Tracking different types of items in 1 table

    My boss has asked me to make an Access database to track items in our tool crib. He wants to track different fields for different types of items. For example, for light bulbs, he wants to track brand, wattage, and kelvins; for tools, he wants to track brand and serial number, and for refrigerant, he wants to track tank # and refrigerant type. Is there any good way to have these combined into one basic "inventory items" table or does it need to be split into a table for lights, a table for tools, a table for refrigerant, etc. If I make a table for everything, wouldn't it necessarily have way too many fields with most of them being blank for most items?



    Which makes for a cleaner database?

    (All the fields for all types of items are: part type, brand, component category, voltage, MFD, poles, amps, part number, bulb type, wattage, kelvins, refrigerant type, tank number, and serial number. Each type of item will only have 2 or 3 of these.)

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    A third option is one table with unique record for each item (part, brand, category) and a related child table with fields for SpecificationType and SpecificationValue. Each part will have multiple records for its specifications. SpecificationValue will have to be a text type field.

    It is a balancing act between normalization and ease of data entry/output. "Normalize until it hurts, denormalize until it works".
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    BEubanks is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    5
    Thank you June7, I'll give that a try.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If I make a table for everything, wouldn't it necessarily have way too many fields with most of them being blank for most items?
    No, because individual tables should not be exact duplicates of each other. The entity (what the table is for) has attributes (fields for characteristics) only for that entity, so you wouldn't include fields from other tables that don't apply.

    My advice would be a table for each tool type where the characteristic data you need is common. So if all you will ever want for lifting devices is manufacturer, model, serial and capacity, then one table for these would suffice. However, if you want to distinguish between chainfalls and pullers, then the decision is to have another table or an LDType (Lifting Device Type) field in tblLiftDevs. Just using an example of how the thought process might go. However, lamps, refrigerant, measuring devices have no relationship to drills so why have a fields in a table for refrigerant gas type and chuck size? Not good design. Seeing as how we don't edit records directly in tables (right?) but only in forms, it matters not how many tables you have as long as you don't exceed the limit (which I think is 255).

    Speaking of forms, your design might benefit from the use of a tab control (or more than one) where each page is for a tool category, or maybe type. I have see this sort of thing done before where each table is a category listing and the ability to drill down was done through cascading combos. Hopefully I can provide you with at least a bit of guidance as I'm quite familiar with industrial tools and tool crib equipment.
    Last edited by Micron; 05-18-2018 at 09:46 PM. Reason: correction
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Micron, poster was considering one table for ALL types of items and a field for each specification (spreadsheetitis), which would result in a lot of empty fields. The other option OP already considered is a table for each item type.

    I offered a 3rd option for consideration. It was actually one I considered in my db and rejected as too difficult to work with for my situation.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Normalization for Table With Types
    By kd2017 in forum Database Design
    Replies: 6
    Last Post: 07-23-2017, 08:34 AM
  2. Open Items Tracking Database Design Questions
    By visphoto in forum Database Design
    Replies: 5
    Last Post: 03-08-2014, 07:37 AM
  3. tracking multiple wage types
    By Helystra in forum Database Design
    Replies: 1
    Last Post: 10-31-2013, 11:23 AM
  4. Table Relationship Types (What determines?)
    By dccjr in forum Database Design
    Replies: 2
    Last Post: 05-03-2013, 07:04 AM
  5. Replies: 7
    Last Post: 10-25-2011, 08:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums