Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    6

    Unanswered: Complex Inventory database design

    I'm trying to design an access database to keep track of several things for out IT group. Those are:

    1. Hardware
    2. Software
    3. Repairs
    4. Network Location (Computer -> Wall Jack -> Switchport )

    We are planning to do a physical inventory and attach barcode labels to everything (Computers, Monitors, Printers, etc), and we needed to start a fresh outside of out cluttered and poorly designed Filemaker database. Here's the problem, hopefully someone can shed some light on it or offer an alternative solution, I've been banging my head over it for a few days on how to best fit the solution.

    TblBarcode is the 'master' table. Everything will have a unique barcode attached

    TblPeripheral - associated with a specific barcode, tracks brand, model, type, serial, repair record, etc.

    TblComputer - assocuated with a specific barcode, tracks same as above, but also MAC Address, RAM, Software Installed, etc.

    .....

    As you can see, there are several slave catagories that all share the same base of barcode numbers. I've attached the 'real-rough' work in progress database with tables (that are not totally linked), 'admin' forms (read: wizard forms to not give access to tables for little-changed vendor / funds tables). When I instanciate a new computer, the computer form should be a blank AddNew, and should be linked so that we can just type in a barcode number [scan, technically], then tab down, set ram size, processor, etc.

    Of course, all this is tied into a set of linked location tables, as well as each having their own repair list, which that would be a subform under barcode on it's table.

    If anyone has any pointers on the 'best' way to setup this odd set of tables, or how to link them correctly and efficiently, I'd greatly appreciate it.
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Why do you have a tblBarcode? as items get replaced or sold, the replacement should get its own bar code. Old equipment that you no longer have does not have to be tracked any longer. The bar code numbers will eventually not be in sequential order. How about just making do with the 'slave' tables.

    btw, my cable modem is a peripheral but it has a MAC address...

  3. #3
    Join Date
    Feb 2004
    Posts
    6
    Originally posted by jmrSudbury
    Why do you have a tblBarcode? as items get replaced or sold, the replacement should get its own bar code. Old equipment that you no longer have does not have to be tracked any longer. The bar code numbers will eventually not be in sequential order. How about just making do with the 'slave' tables.

    btw, my cable modem is a peripheral but it has a MAC address...
    I've been working considerably on this project, and if the TblPeripheral doesnt have a mac address in the one I uploaded, it does now. (I believe it did, I caught that early on, mainly for print servers).

    The reason for the master table being barcode is that barcodes are going to be the lifetime / audit tracking. When a computer or peripheral is surplused (in a school district, that takes many years. ) we will will have repair records associated with it that will need to be in the database (or that years table, at least) and backed up for audit storage for at least 7 years. For this reason we determined that each barcode be the 'primary key' to the inventory system, and when a machine / peripheral / switch is surplused, that barcode will still exist and will not be reused. Since I'm referencing everything via relationships, I should be able to change the 'type' of barcode later if we run out of six digit barcodes (or just go Alphanumeric, tbd).

    Basically, we have 'one roll' of barcodes, 000001-999999 [right now], and queries to look up barcode 040439 would be a lot easier if barcode was the master table and the query didnt have to search through multiple tables to try to find that barcode (not to mention data entry error where a barcode may be reused between tables...)

    Thanks for looking at it though, I've done a bit of commercial small / unique database work, but mainly in MySQL or MDB with a VBA frontend... This is the most head tripping table set that I've had to do yet, and it's getting hard to remember all those relationships!

    Diamond

  4. #4
    Join Date
    Dec 2002
    Posts
    117

    Some things I notice

    The barcode textbox is erroring out because of reference to a form that does not exist.

    What exactly are you trying to accomplish? Please sit down a take a couple of minutes to explain as much as you can and maybe we can help.

    Thanks.

  5. #5
    Join Date
    Feb 2004
    Posts
    6

    Database

    I'm trying to do a 'basic' service and repair database for our school, which will accomplish the following tasks:

    1. Provide a central place to store barcode-numbered computers and peripherals and their associated inventory information.

    2. Provide a central place (linked) to track any repairs necessary to said equipment or software.

    3. Provide a central place (linked) to track where that asset is in our campus.

    4. Provide a central place (linked) to track how/where that asset is connected to our internal network. This is done logically by saying "The NIC is plugged into Jack 132-3, which is 132-3 on the panel in closet 2A. This is patched into switch named "CLOSET2A-MAIN" in port 34." Being able to trace the computer back to the switchport will help in problem resolution and usage tracking.

    5. Provide a central place to keep track of software licenses (bought sometimes in bulk, sometimes individually) and keep track of what software is loaded on what computer. IE: Barcode 100300 is a Dell Optiplex GX260 [insert other info, processor, ram, etc], which is running Windows 2000 Professional SP4 which the license was purchased in bulk on 9/12/04. There were 34 licenses purchased that day, out of which 8 are still available. The other 26 are on machines, and the product key for that computer is AAAAA-AAAAA-AAAAA-AAAAA-AAAAA.

    Basically, it's a simple yet complex set of relationships. Each barcode is unique, and computers will have their own barcode, as will printers, scanners, monitors, etc. The computers will be linked to the peripherals attached, as well as the software installed and repairs done to them. Their location and switchport will also be available.


    I've come a long way with this database since the first post; however, I'm still having problems with updating multiple tables from a single form. It seems that I am on the easiest track with using subforms; however, even though relationships are there, the foreign keys are not being saved when a new record is added. I've attached the latest copy of the database to this message, if someone would like to take a look and give me some pointers I would greatly appreciate it.

    Please let me know there you have any questions or don't follow my logic.... Of course it makes sense in my head.
    Attached Files Attached Files

Posting Permissions

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