CODEWITHSUNDEEP
mysqlsqlrelationship
Rid Iculous
Rid Iculous

Reputation: 3962

What's the best db structure to represent the relationships within a family if the base-object is a Person

I have a large DB full or people called PERSONS.

I want to store their relationship to one another in a separate table.

I am thinking something like this: 

FAMILY
PERSON_IDS: 123, 345, 678
RELATIONS:  self, brother, daughter

Using a 'self' flag to indicate the person referring the relation-ship from. So, in the example 123 would be a man, 345 is his brother and 678 is a woman, his daughter. I.e, the gender information could be derived from the relationship.

Is this the right way to go about it?

Or would it be more efficient to have only 2 PERSONS per row and one relationship-type

PERSON_IDS: 123, 456
RELATION: brother

-

PERSON_IDS: 123, 678
RELATION: daughter

Or is it better to have a male/female field per person and the relation-ship table only specify eg "sibling".

Or should I have a table with PARENTS only and store the gender with the PERSON?

TABLE PARENTS:
PARENT_ID: 123
CHILD_ID: 678

The aim is to display related people when a Person is displayed. The design should be optimized for performance & speed rather than ease of administration.

Thanks for your help.

Upvotes: 1

Views: 5692

Answers (1)

educampver
educampver

Reputation: 3005

It's a many-to-many relationship: each person can have many relatives and also be a relative of many people. Assuming your Person table has a primary key named Id, you could have a table, which holds the relationship between two people as well as the kind of relationship they have. It would be useful to also have a table with all the possible kinds of relationships, something like this:

Relationship(Id, Kind)
Family(Person_Id, Relative_Id, Relationship_Id)

So if you have the following data for Person and Relationship tables:

Person:             Relationship:
Id | Name           Id | Kind
 1 | John            1 | Father
 2 | Mike            2 | Sister
 3 | Susan

and the following for Family table:

P_Id | Rel_Id | Relation_Id
 1   |   2    |      1
 1   |   3    |      1
 2   |   3    |      2

Family table is saying that John is the father of Mike and Susan, and that Mike's sister is Susan. I think this could be a good approach, but of course, as almost everything in databases design, this solution is arguable.

Upvotes: 8

Related Questions