4 Replies Latest reply on Oct 16, 2013 8:00 PM by RedDot1

    Self-Join Relationships



      Self-Join Relationships



           I would like to create a database for people with a way to specify the relationships between them.  I assume that I will need to create three tables, Table A with the names, Table B which would be a second occurrence of Table A, and Table C which will contain the specified relationships between members of Table A.

      Table A would have at least two fields:  

           A1 ID Primary Key (Unique Number)

           A2 Name

      Table B have the same fields as A by definition

           A1 ID Primary Key (Unique Number)

           A2 Name

      Table C would have three fields:

           C1 ID Primary Key (Unique Number)

           C2 Relationship

           C3 A1 ID Foreign Key

           Field C3 in Table C would be linked to field A1 in Table B

           Ultimately I would like e.g. Person 4 in Table A to be defined as "first cousin" of person 5 in Table A and to be able to change that to e.g."second cousin" from a pre-defined pull down value list in C2.

           So when I am in a layout which displays records from Table A and I am on e.g. Person 4's record it will display the relationship between Person 4 and Person 5, if there is one.  The layout would have field C3 displayed in which one manually inputs the ID for Person 5 after which fields A2 (Name of Person 5) would be populated as would C2 the defined relationship from the pull down list if there is a record in C for the relationship otherwise records in Table C would be able to be created via Table A or C.

           Am I on the right track here in the overall design and Table linkages?

           Thanks for any advice.

        • 1. Re: Self-Join Relationships

               What you have is both a many to many relationship and a self join.

               You need two foreign key fields in Table C, not just one.

               Start with these relationships:


               A::__pkA1 = C::_fkC3
               B::__pkA1 = C::_fkC4

               You can place a portal to C on the A layout to list and select a B record for each given A record. Fields from B can be included in the Portal to show additional info about each selected B record and the _fkC4 field can be set up with a value list for selecting B records by their ID field. And a field in Table C can identify whether the related person is "Father, Mother, cousin, etc."

          • 2. Re: Self-Join Relationships

                 I see my mistake.  Works well.   Thanks again very much PhilModjunk.

            • 3. Re: Self-Join Relationships

                   Is this a simple familiar relationship database or a genealogical database?

                   I ask because I started a Family Contact database that would relate individuals to families.

                   The depth of the family relationships was fairly shallow, but the number of tables, table occurances, and relationships grew and grew.

                   I found a fellow with a genealogical database that was looking for beta testers, the beta's design table was scary (I am easily scared)

              • 4. Re: Self-Join Relationships

                     It is intended to be a simple family database with maybe 2 generations, not a full bore genealogical database.   Do you offer yours for license?