6 Replies Latest reply on Apr 15, 2016 6:25 AM by DanielShanahan

    Rebuilding Asset Management DB

    Stigge

      Hi

       

      I have taken over our filemaker asset management databases from the original creator (he retired).

      There is TONS of layouts / scripts / variables / all what should be tables are own databases etc. its a complete mess to be honest.

       

      Until now we have purchased all our PC´s, but now we are just going to start to lease them. And by doing this i need to rebuild our asset management databases. And am thinking about how to do it, becouse there is some requests that i need to fill with functionality, like Logging, reports with equipment that are closing in on lease end date etc.

       

      Today the DB structure look something like this (simplyfied ALOT, total there is 30 databases):

      Each of the boxes are own databases, and users are working directly on the PC/Printer/network database.

      Am guessing this is not a "best practice" to have it build like this.. ? And i cant figure out how to add "Logging" to this build ether.. but that might just me my lack of knowlage that stops it.

       

      I know i need to rebuild this. But am thinking about 2 options then..

       

      1: Build it the same way, but use fewer databases and use tables instead. But still dont know how to add logging.

       

      2: Build it as the picture below, and from the "View/edit" use a "Save" button before changes are made. that way i can always log changes and the users arent working direct on the raw data.

       

      As i mentioned before, my skills are still a bit newbie =). So i figured i send the question to you experts for some input. =)

        • 1. Re: Rebuilding Asset Management DB
          Mike_Mitchell

          Stigge wrote:

           

          I have taken over our filemaker asset management databases from the original creator (he retired).

          There is TONS of layouts / scripts / variables / all what should be tables are own databases etc. its a complete mess to be honest.

           

          Welcome to the jungle. 

           

          Most of us have, at some point, been in this position. Kinda goes with the territory.

           

          Each of the boxes are own databases, and users are working directly on the PC/Printer/network database.

          Am guessing this is not a "best practice" to have it build like this.. ?

           

          You are guessing correctly. Sharing a database over a network via file sharing is absolutely the worst, riskiest way you could do it. You need FileMaker Server.

           

          And i cant figure out how to add "Logging" to this build ether.. but that might just me my lack of knowlage that stops it.

           

          I know i need to rebuild this. But am thinking about 2 options then..

           

          1: Build it the same way, but use fewer databases and use tables instead. But still dont know how to add logging.

           

          2: Build it as the picture below, and from the "View/edit" use a "Save" button before changes are made. that way i can always log changes and the users arent working direct on the raw data.

           

           

          A lot depends on what change you're trying to log. Are you trying to log just changes to ownership, location, etc.? If so, then you would do it basically as you have it laid out (use scripting to control the movement of assets and make the logging part of that). On the other hand, if you're trying to log any change to any of the records, you might be better served by using Script Triggers to fire the change log creation. Just write a script that creates a new log record and pass it the correct parameters from wherever you need.

           

          I'm attaching a document from the FileMaker 8 days about how to refactor an old solution that came from multiple separate databases. It's old, naturally, but most of the advice is still sound.

           

          You might also want to go back and start your Entity-Relationship Diagram from scratch. I'm not sure what entity "ID" represents, but it may not be necessary to have separate tables for computers, printers, and network hardware. You might be able to do an "asset" table and differentiate by type. That will largely depend on how different the tables are. If you have a lot of fields different between them, then separating them is okay. But if you only have a few fields different, it's often better to put them all into a single table, which simplifies reporting and searching.

           

          HTH

           

          Mike

          • 2. Re: Rebuilding Asset Management DB
            Stigge

            Hi Mike..

             

            Thanks for the reply..  Will start to read your attachment straight away.. =)

             

            For there record, we have a filemaker server =) version 14. same with our clients. had 8 before.

             

            What to Log, changes made by users.

            Example if a user change the owner of a PC, and it turnes out it was made wrong. then we have no way to see what the old information was and the information is lost :/

             

            ID:

            to name our assets we use a naming standard so the ID is part of the naming.  CN (computer notesbook) SE (the country that owns the asset) and then a single 1 (can be used up to 9) then comes the ID. 4 digits from 0001 -> 9999. so the name of the asset in one case will be:

            CNSE14444

            The ID is then used to for example assign Software for example in the database. So everything(most of it) is today based and connected by this ID.

            • 3. Re: Rebuilding Asset Management DB
              Mike_Mitchell

              Stigge wrote:

               

              For there record, we have a filemaker server =) version 14. same with our clients. had 8 before.

               

              Good!  

               

              What to Log, changes made by users.

              Example if a user change the owner of a PC, and it turnes out it was made wrong. then we have no way to see what the old information was and the information is lost :/

               

              Then you'll probably want to use a Script Trigger. Or you could look at something like Ray Cologon's UltraLog.

               

              ID:

              to name our assets we use a naming standard so the ID is part of the naming.  CN (computer notesbook) SE (the country that owns the asset) and then a single 1 (can be used up to 9) then comes the ID. 4 digits from 0001 -> 9999. so the name of the asset in one case will be:

              CNSE14444

              The ID is then used to for example assign Software for example in the database. So everything(most of it) is today based and connected by this ID.

               

              If that's the case, you might want to consider seriously combining the different types of assets into a single table. (But I would use a second key for relational joins - a UUID or serial number.)

               

              Good luck!

              • 4. Re: Rebuilding Asset Management DB
                coherentkris

                If this is a mission critical system then you may want to hire a consultant to work with you or take over the project entirely.

                All depends on your level of comfort with tackling the task and the risk tolerance of the business.

                Refactoring a system is never a simple project.

                • 5. Re: Rebuilding Asset Management DB
                  Mike_Mitchell

                  +1

                   

                  And, be absolutely certain to test the new version thoroughly with actual users. We developers don't make the best testers. You need someone who, as my wife says, "acts like a user".  

                  • 6. Re: Rebuilding Asset Management DB
                    DanielShanahan

                    One way to proceed is to group many of your current databases into one table.  Software, printers, PC/Servers, Network HW could all be types of assets (and so in an Asset table).

                     

                    If you have a lot of the exact same kind of asset (e.g. an iPhone 6s) then you may want to give each one a unique serial number.

                     

                    ASSET ---< SERIALNO

                     

                    Your asset probably has one location but your location may have many assets (think of an office with a chair, desk, computer, printer, etc.).

                     

                    ASSET >---LOCATION

                     

                    Your asset may be connected to a person instead of a location.  For example, a sales team may have iPhones.  The phone travel with the team member and are not associated with that team member's office location.

                     

                    ASSET -----< ASSETPERSON >----- PERSON

                     

                    An asset database is a type of an inventory database.  The change log is sometimes referred to a transaction table.  No matter what you call it, it should be able to track your assets from the time of purchase, through any changes, until the time it is discarded (e.g. sold, donated, recycled, etc.).