4 Replies Latest reply on Nov 26, 2015 11:16 PM by Stigge

    Creating part fixed relation between 2 tables

    Stigge

      Hi

       

      Small issue that someone might know a good answer to..

       

      I made an own asset management database with several tables in it..

      the relevant ones are:

      - PCs (stores all our PC´s and who is using them etc.)

      - Software licenses (stores all software licenses that are purchased and info on them)

       

      The PC ID is the relation between them.. So for example PC 1234 have several software installed, then the ID is set to the field ID on the software table. that way I can easy see per PC what software each PC are using.

      Now we have decided that ALL PC´s should have one of the software automatically assigned. all active PC´s (not the PCs set to "in store" or the ones set for to be scrapped).. we have today about 1000 active PC´s.. and of that software we own about 1200 software licenses. so the records in the software table will cover it for sure.

      Is there a way to create that ? Or do anyone have any good ideas on how I can solve that ?

        • 1. Re: Creating part fixed relation between 2 tables
          Extensitech

          Not exactly sure what you're trying to solve. Are you trying to get the software assigned to the 1000 existing pc's, or are you needing a method to automatically add the software to any new PC's that get created?

           

          I would imagine, in this scenario, three tables, PC's, Software, and Installations (an Installation being a join table, a record of a particular software installed on a particular pc... this is where you could put individual license keys, as well). In that case, you could create installations for the existing pc's by importing all the pc primary keys into the foreign key in installations, and then use replace to set the software key in those installation records. Likewise, you could flag software to "automatically install", and on creation run a script to import those softwares into installations and set the new pc key.

           

          Does any of this help? Do you have a different structure for some reason?

           

          Chris Cain

          Extensitech

          • 2. Re: Creating part fixed relation between 2 tables
            Stigge

            Thanks for the fast reply .. =)

             

            Well the PC table is the main view table.. And yes am trying to have an function for the "Automatic installed records"..

             

            To break it down, so it might get a bit more clear.

            PC table is the main view table..

            Software table stores all owned Software. some of them are assigned and some are "in store" (free to be assigned to a PC)

             

            Lets play with the idea that we have in the software table 3 different software's..

             

            - Wordfinder 1200 licenses, 1 per record

            - Adobe Acrobat 100 licenses, 1 per record

            - Microsoft Office 1200 Licenses, 1 per record

             

            Without the need of creating a new table. I need to somehow set for example "Wordfinder" and "Microsoft office" licenses to each PC by default. with for example a key, checked if "standard on all PC`s" or something similar.

            My PC´s have the ID scope from 0001 -> 9999.. since we have been using this database for this now for 10-15 years. some PC`s have broken down, been scrapped etc.. so the scope of PC ID`s isn't straight 0001 -> 1001. its a big mix from 0001 to 9999. with a lot of free ID`s in middle. In the whole table its about 3500 PC´s with different status (deployed, scrapped, broken, etc)... now I need to be able to somehow assigned Wordfinder and Microsoft Office to all "Deployed" PC`s. without needing to manually add 1 by 1 to each PC to each License. also automatically being added on new PC purchases that are imported.

             

            Did that make it more clear ? haha.. or just more confusing ??

            Sorry, English isn't my native language. =) am trying my best

            • 3. Re: Creating part fixed relation between 2 tables
              siplus

              Stigge wrote:

               

              Without the need of creating a new table.

               

               

              1) PCs to Software is a N to M relationship.

              2) N to M relationships are mostly useless and bad.

              3) to solve 2) you need a join table, like Extensitech said.

              4) There is no 4)

              • 4. Re: Creating part fixed relation between 2 tables
                Stigge

                well I have a join table.. its just irrelevant in this question, I think =)

                but since you say that is the solution. let me paint up the whole picture then. =)

                 

                Now am not the one who built this from start.. so don't judge me from its look =D The one who did have retired and am just updating it because its built not accordingly how I would have done it.