SQL Members table to show family relationships between members

Question

I've been trying for days to design this database. I'm designing it in access then implementing on MYSQL Server as a PHP Web Application.

I have a table:

Members

• MemberID (autoNumber, PK)
• MemberName
• MemberDetails

Members can have many relationships with other members, this may be Child, Parent, Friend, Spouse etc. Looking at one Member, I'd like to be able to create new relationships with existing members and then have that relationship also be visible from the related member without further input. Members should also be able to list all their relationships.

Can you please advise how I should do this? I've tried a few options but none seem to work as intended. I'm comfortable with SQL, I'm just having trouble with the Unary relationship design.

-edit- Also, I forgot to add, this is not going to be able to use INNODB due to server restrictions. Most likely will be MYISAM, though i still want referential integrity :(

Answer 1

Let the table members contain members data and the table relations contain member relations data. relations.member_id will be a reference to the member, relations.related_member_id - to the related member. relations.type is enumerable relation type.

CREATE TABLE `members` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` TEXT NOT NULL ,
`details` TEXT NOT NULL
) ENGINE = INNODB;

CREATE TABLE `relations` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`member_id` INT NOT NULL ,
`related_member_id` INT NOT NULL ,
`type` ENUM(  'Child',  'Parent',  'Friend',  'Spouse' ) NOT NULL,
FOREIGN KEY (member_id) REFERENCES members(id),
FOREIGN KEY (related_member_id) REFERENCES members(id)
) ENGINE = INNODB;

UPD: MyISAM version (removed foreign keys, all possible foreign keys functionality should be handled by server side scripts):

CREATE TABLE `members` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` TEXT NOT NULL ,
`details` TEXT NOT NULL
) ENGINE = MyISAM;

CREATE TABLE `relations` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`member_id` INT NOT NULL ,
`related_member_id` INT NOT NULL ,
`type` ENUM(  'Child',  'Parent',  'Friend',  'Spouse' ) NOT NULL
) ENGINE = MyISAM;

Answer 2

Starting with @Minras design (+1), I’d have

MEMBERS
  MemberId
  Name
  Details

RELATIONS
  FromMemberId
  ToMemberId
  RelationType

But instead of a check constraint, I’d add a third table:

RELATIONTYPE
  RelationType
  Description
  FromLabel
  ToLabel

with RelationType being an integer and the “Labels” being character data. The relation is "directional", in that you'll have to pay close attention to which member is "From" and which is "to" (but not so important for "non-directional" relations, such as "went to high school together"). This design will allow you to:

• Define a relationship between people with multiple labels, e.g. A is father of B and B is son of A.
• Add new relationships if, when, and as necessary

Obviously you’ll either have relational integrity on everything via foreign keys or whatever you have available, or you’ll have a train wreck waiting to happen.

This does not address the issue of how to uniquely and clearly identify members with duplicate names. To do that, you either need to factor in the identifying attribute” used by people in the real world to deal with such situations (student ID? social security number?), or introduce an artifact specific to your application (say, login + password).

Answer 3

Try adding a pivot table:

Relationships:

MemberId1
MemberId2
RelationshipType

Answer 4

Create a table with collumns:

Member_1_id
Member_2_id
Relation_type

Then you can use it like that: If Alice is Bobs daughter you'll have those relations:

<Bob id> <Alice id> 'Father'
<Alice id> <Bob id> 'Daughter'

You can then throw some additional data into those relationships like when did the relation begin (ie. when someone got engaged).

You may also want to create indexes on both columns with member ids.

EDIT: To avoid duplicating data in this table you can create a view and then store every relation as only one row. However this solution won't allow you to name relations like 'father' and 'son'.

SELECT member1, member2, relation FROM rel_table UNION ALL SELECT member2, member1, relation FROM rel_table;