I am having a problem with foreign keys between two tables. I am trying to model a relationship between members of a club and their relatives. This is a straightforward many-to-one relationship, with many relatives related to one member. However, one and just one of this relatives must be the main contact. Also, many members can have the same relative as main contact (siblings to their father as an example).

EDIT: Members are kids, and relatives must be adults, so relatives can't be members, and members can't be main contact of another member.

A FK on member table refering the PK of relatives would work, but this will leave me with a circular reference and a "chicken-or-egg" problem.

What is the right approach to this kind of problems? Maybe I am just thinking it too much.

Schema is more or less like this:

+--------------+    +-------------+
| member       |    | relatives   |
+--------------+    +-------------+
|id            |<-+ |id           |<-+
|name          |  +-|*member_id   |  |
|address       |    |name         |  |
|phone         |    |relationship |  |
|...           |    |phone        |  |
|*main_contact |-+  |...          |  |
+--------------+ |  +-------------+  |
  • Besides hte question you ask, it looks like the relationship (relatives(member_id) REFERENCES member(id)) should be many-to-many, not one-to-many. May 2, 2014 at 0:19

1 Answer 1


Sounds like you may need a self-join:

  1. Get rid of the relatives table entirely
  2. Define the main_contact as a foreign_key to the id column of the member table.

Or depending on the relationship a many-to-many resolver table in the middle:

  1. Get rid of member_id from relatives
  2. Get rid of main_contact from from member
  3. Add a new table member_relative with the columns member_id, relative_id and a column to indicate relationship_type.
  • If you get rid of the relatives table, you have eliminated the 1-to-many relationship between a member and a relative. May 2, 2014 at 0:16
  • I can't use a single table, as relatives can't be members.
    – Hugo A
    May 2, 2014 at 2:45
  • Thinking a bit more about it, it seems that @ypercube and Mack are right, this should be a many to many relationship, as a member has many relatives, and a relative can be main contact to many members.
    – Hugo A
    May 2, 2014 at 2:56

Not the answer you're looking for? Browse other questions tagged or ask your own question.