SQL member table to show family relationships between members

Question

I have been trying to develop this database for several days. I develop it in the access and then implement it on the MYSQL server as a PHP web application.

I have a table:

Users

• MemberID (autoNumber, PK)
• Membername
• MemberDetails

Members can have many relationships with other members, it can be a child, parent, friend, spouse, etc. Looking at one member, I would like to be able to create new relationships with existing members, and then have these relationships also visible from a related member without further input. Members should also be able to list all of their relationships.

Could you advise how I should do this? I tried several options, but none of them work properly. I like SQL, I just have problems with unary relations design.

-edit- Also, I forgot to add that it will not be possible to use INNODB due to server limitations. Most likely there will be MYISAM, although I still need referential integrity :(

Answer 1

Let the members table contain member data, and the relations table contain member relationship data. relations.member_id will be a reference to a member, relations.related_member_id be a related member. relations.type is an enumerated type of relationship.

 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: version of MyISAM (remote foreign keys, all possible functions of foreign keys should be processed by scripts on the server side):

 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 from @Minras (+1) design, Id have

 MEMBERS MemberId Name Details RELATIONS FromMemberId ToMemberId RelationType 

But instead of a check constraint, Id adds a third table:

 RELATIONTYPE RelationType Description FromLabel ToLabel 

where RelationType is an integer and Labels are character data. A “directional” relationship, in which you will need to pay close attention to the “From” and “To” members (but not so important for “non-directional” relationships, for example, “went to high school together”). This project will allow you to:

• Define relationships between people with multiple tags, for example. A is father of B and B is son of A
• Add new relationships if, when and when needed

Obviously, you have relational integrity at all through foreign keys or whatever you have, or you will have a train crash awaiting its arrival.

This does not concern the question of how to uniquely and clearly identify members with duplicate names. To do this, you either need to take into account the identification attribute used by people in the real world to solve such situations (student ID number? Social security number?), Or enter an artifact specific to your application (for example, username + password).

Answer 3

Try adding a pivot table:

 Relationships: MemberId1 MemberId2 RelationshipType 

Answer 4

Create a table with columns:

 Member_1_id Member_2_id Relation_type 

Then you can use it like this: If Alice is Bobs's daughter, you will have this relationship:

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

Then you can add some additional data to this relationship, for example, when the ratio started (i.e. when someone started).

You can also create indexes on both columns with member identifiers.

EDIT: To avoid duplicating data in this table, you can create a view and then save each relationship in only one row. However, this decision will not allow you to name relationships such as “father” and “son”.

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