4 Replies Latest reply on Dec 3, 2016 4:55 PM by ajcvet

    relationships

    ajcvet

      Good Day,

       

      I'm a bit new to FM.  I'm starting a company that will gather "clients", then form investor groups from those clients to purchase property.  I've created a table for clients and their subsequent info/fields.  I've also have a table for "properties", and a table for "investor groups".  the investor group property relationship works (investor group "Parent" and property "child" as one property can have only one investor group but one investor group can own more than one property so a good one to many relation.

       

      However, my problem is, an investor group can have one or more clients in the group so it seems like a one to many relation with the investor group being parent and client being child but then "a" client could be part of more than one investor group which to me is a many to many relationship.  I've tried to figure out a way to use a joining table but seem to end up with a one to one relationship table.

       

      can anyone suggest a way to look at this a better way?

       

      thanks

      anton cvet

        • 1. Re: relationships
          keywords

          As you have said, a Group comprises many Clients. If, however, a Client can belong to more than one Group then you have a many-to-many relationship to deal with. The usual method is to create a join table, called client–JOIN–group or some such. This join table needs only two fields for it to function: foreignKeyClientID and foreignKeyGroupID. This method resolves the many-to-many relationship into a pair of one-to-manys. If there is other data particular to the Join record, you might decide to have this fields as part of the Join record (e.g. the date on which the Client joined the Group), but essentially this record exists purely to set up the link between a client and a group.

          1 of 1 people found this helpful
          • 2. Re: relationships
            ajcvet

            OK.  thanks.  that actually makes sense.

            cheers

            • 3. Re: relationships
              philmodjunk

              You might treat "investorGroup" as your join table. You'll have to look at all the needs for each table to see if this work, but maybe:

               

              Clients---<Client_Property>-------Property

               

              The group of Client_Property records with the same PropertyID would form your investor group

              • 4. Re: relationships
                ajcvet

                hey phil,

                 

                thanks for reply.  however, the investor group / client property can possibly own more than one property so i believe going your way would create a many to many.

                 

                thanks

                a