1 Reply Latest reply on Jan 11, 2014 6:55 PM by philmodjunk

    Newbie needs help with relationship logic

    krislynch

      Title

      Newbie needs help with relationship logic

      Post

           I'm somewhat of a FileMaker Pro newbie. I've been using it for a while but not to the level I would like. I've created a database but am starting over and need help with a relationship logic question.

           I understand one-to-many and many-to-many relationships, and why all relationships need to be resolved to be one-to-many.

           Here's what I'm trying to do...

           I have about 700 users with various pieces of data (email address, password, etc.) Their email address is a unique field to this table and would likely be the table's primary key.

           I also have about 800 electronic devices that are assigned to these users (most assigned, some just in inventory). This table includes the serial number and other information about each device. The serial number would be the primary key.

           My device table actually also includes user assignments, but this information changes frequently so I need to import new data every so often.

           My problem is how to set up the relationship between these tables. Right now this is a many-to-many relationship because users can have had multiple devices assigned to them over a period of time; devices can be assigned to multiple users over a period of time as well. What's most important is that I can keep track of current assignments and history of past assignments/activity.

           Can anyone give me some advice on how to set this up?

           Thank you in advance! 

        • 1. Re: Newbie needs help with relationship logic
          philmodjunk
               

                    Their email address is a unique field to this table and would likely be the table's primary key.

               An email address is not a good choice for a primary key.

               

                    The serial number would be the primary key.

               A better choice than an email, but still not the optimum to use for a primary key. The ideal primary key is: Internally generated by the database, unique, devoid of any additional "encoded meaning" and never ever changed. Email addresses fail this test primarily due to the last requirement, equipment serial numbers don't meet these requirements as they are not internally generated.

               In FileMaker Pro, your primary keys should be either number fields with an auto-entered serial number or text fields that auto-enter the Get ( UUID ) (new feature in FileMaker 12) calculation.

               

                    I understand one-to-many and many-to-many relationships, and why all relationships need to be resolved to be one-to-many.

               

                    Right now this is a many-to-many relationship

               If you understand these two key details, where exactly do you need help?

               Start with these relationships:

               Personnel-----<Personnel_Equipment>-----Equipment

               Personnel::__pkPersonnelID = Personnel_Equipment::_fkPersonnelID
               Equipment::__pkEquipmentID = Personnel_Equipment::_fkEquipmentID

               You can place a portal to Personnel_Equipment on the Personnel layout to list and select  Equipment records for each given Personnel record. Fields from Equipment can be included in the Portal to show additional info about each selected Equipment record and the _fkEquipmentID field can be set up with a value list for selecting Equipment records by their ID field.