CODEWITHSUNDEEP
phpsqlrelational-databaseentity-relationship
SPC_75
SPC_75

Reputation: 41

SQL Members table to show family relationships between members

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

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 :(

Upvotes: 4

Views: 5285

Answers (4)

Philip Kelley
Philip Kelley

Reputation: 40319

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).

Upvotes: 2

Minras
Minras

Reputation: 4346

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;

Upvotes: 5

Maciek
Maciek

Reputation: 3234

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;

Upvotes: 0

Tudor Constantin
Tudor Constantin

Reputation: 26861

Try adding a pivot table:

Relationships:

MemberId1
MemberId2
RelationshipType

Upvotes: 0

Related Questions