9 Replies Latest reply on Apr 19, 2016 12:32 PM by DanielShanahan

    Combined Computer Software and Hardware Inventory Database?

    BC2015

      I'm trying to create a database that will make it easier to keep track of what software/hardware is installed on each of our Graphic Artist computers at my facility.

       

      I want to be able to print a report detailing, for each computer:

      • Location of each Computer
      • Hardware specs for each Computer (manufacturer, RAM, Hard Drive, Video Card, etc.)
      • P.O. # for Computer
      • Installed Software List
      • License code for each piece of software
      • P.O.# for each piece of software
      • List of peripherals attached computer
      • Specs for those peripherals
      • P.O.# for each peripheral

       

      Once this information is in the database, I'm assuming I should be able to generate reports based on:

       

      • a particular Software Manufacturer
      • what versions of their software we have installed in the building
      • where those installations are located (which computers/rooms)
      • the serial number associated with each installation
      • The P.O.# associated with each installation

       

      Here's what I have so far... Am I on the right track or completely off-base?

       

      Screen Shot 2016-04-13 at 3.34.06 PM.png

       

      Thanks very much.

        • 1. Re: Combined Computer Software and Hardware Inventory Database?
          Vaughan

          BC2015 wrote:

           

          what software/hardware is installed on each of our Graphic Artist computers at my facility.

           

          You need a table for "Computers". Things like software and hardware are "Assets" which can have such attributes as Serial No, license code, etc. Also need a table for the "Owner", maybe another for "Location". It will be simple to create reports for "what we have" and "where it is" and "Who is responsible for it" from the Assets table.

           

          This will create a very simple data structure, but more complexity with data entry and attribute-value pairs. (Conservation of Complexity theorem: complexity is never destroyed it just moves to a different layer of abstraction.)


          Need to think about whether the "computer" is an empty box and everything else like RAM, CPU etc are asset records, or whether RAM and CPU are attributes of the computer itself. Maybe the computer itself is another asset? One way would be to use a recursive structure that allows an asset to contain other assets, but that's starting to become complex.

           

          Much depends on whether details like "hardware specs for computer" need only be recorded somewhere (in which case a description field is sufficient) or whether there is a requirement to be able list out all the computers with quad core i7 CPUs, in which case a more complex structure is necessary.

           

          IMHO you don't want "computers" to be in a different table from "printers" and "drawing tablets" otherwise creating a list of assets becomes difficult. Computers, printers and tablets need to be in the same table.

          • 2. Re: Combined Computer Software and Hardware Inventory Database?
            DanielShanahan

            It looks good to me.  A couple of things:

             

            1. You have a locationID but no Location table.  I presume that you are planning on creating it but wanted to see if you were on track first, correct?

             

            2. In the Software table I would add fields like Software Name, Version, etc.  Again, you may have planned that but I thought I'd mention it.

             

            3. The Relationship Graph has crows feet at both ends of the relationships.  That indicates that your primary keys are not marked as unique.

             

            HTH.

            • 3. Re: Combined Computer Software and Hardware Inventory Database?
              BC2015

              Thanks very much for the advice.

               

              I completely see your point about simplifying.

               

              But I think you're also correct in that I do need to have something where assets have assets.

               

              I will need to identify all the computers that have certain assets - like the amount of RAM, or specific video cards or CPUs - for identifying which computers need upgrades, or which computers we can use for different tasks, etc.

               

              And I would need to be able to identify which computers have specific versions of specific software installed, and the license codes associated with those specific installations.

               

              A computer could have an asset of a piece of software, and the piece of software would have assets of many different license codes... and, depending on the software, you might only have one license code associated with a particular computer installation, or you might have one license code associated with many computer installations (like a floating license is).

               

              So that's kind of why I had the license codes as a separate table, because the license codes need to have fields to identify what type of license code it is (whether it's individual, floating, or license server), and they need the ability to be associated with individual computers.

               

              Or is there another way I can get these same results?

              • 4. Re: Combined Computer Software and Hardware Inventory Database?
                BC2015

                Thanks Daniel!

                 

                Yes, I was planning on adding those fields to those tables. I just wanted to start simple for now and make sure I was on the right track.

                 

                I will lookup primary keys and how to change them from non-unique to unique.

                • 5. Re: Combined Computer Software and Hardware Inventory Database?
                  BC2015

                  Ok, so, is this crazy, or does it look like it will work?

                   

                  Screen Shot 2016-04-15 at 8.58.52 AM.png

                  • 6. Re: Combined Computer Software and Hardware Inventory Database?
                    BC2015

                    Ok, I'm still stuck. The most important thing in this is keeping track of the software licenses. Let's simplify it to just that for now:

                     

                    I want to have a layout to look at Computer records that shows the details for that computer, including a portal list that lists all the software that's been installed on that computer, the license code of that installation, and the PO# for that license code.

                     

                    I also want to have a layout to look at Software records that lists the details for that piece of software, what computers that software is installed on, and the license codes for those installations, along with the PO# for each license code.

                     

                    How would I set that up?

                    • 7. Re: Combined Computer Software and Hardware Inventory Database?
                      DanielShanahan

                      COMPUTER -----< LICENSE >----- SOFTWARE

                       

                      and

                       

                      LICENSE >----- PO

                      • 8. Re: Combined Computer Software and Hardware Inventory Database?
                        BC2015

                        Thanks for your help, I really appreciate it. I wonder if I'm going about this the right way to begin with.

                         

                        The more I'm thinking about this, the more confused I get.

                         

                        The problem that I have now with my excel sheet is that it's hard to read and impossible to search efficiently.

                         

                        There is also a LOT of redundant data. That's why I thought Filemaker would be better.

                         

                        Should I revert to my original structure from Apr 15, 2016 9:02 AM ?

                        • 9. Re: Combined Computer Software and Hardware Inventory Database?
                          DanielShanahan

                          You might benefit from hiring a professional FM developer/consultant.  You're welcome to email me here or look for someone in your area.