AnsweredAssumed Answered

Self-Join Relationships

Question asked by RedDot1 on Oct 16, 2013
Latest reply on Oct 16, 2013 by RedDot1


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.