1

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 |-+  |...          |  |
+--------------+ |  +-------------+  |
                 +-------------------+
Hugo A
  • 13
  • 1
  • 5
  • 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. – ypercubeᵀᴹ May 02 '14 at 00:19

1 Answers1

2

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.
Mack
  • 146
  • 6
  • If you get rid of the relatives table, you have eliminated the 1-to-many relationship between a member and a relative. – ypercubeᵀᴹ May 02 '14 at 00:16
  • I can't use a single table, as relatives can't be members. – Hugo A May 02 '14 at 02: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 02 '14 at 02:56