I have seen this question asked many times and most of the times poorly answered.
I think it could be done like this. The left one has two tables – person and pairing. Person table is for entities and the other table for relationships between persons. The green one has both in one table and it is basically recursion hierarchy between Persons.
I don’t know if this solution is correct or not. Neither know I which one is better 🙂
Lines on the right ERD explained:
- Each mother_id can be mother to zero-to-many person_id’s
- Each father_id can be father to zero-to-many person_id’s
- Each person_id can have 0-to-1 father id (zero if I don’t know the father) and 0-to-1 mother id. Person can not have more than one biological father and mother.
NB! You can not add relationships between Persons before you have created the Person you need. Therefore I suggest to leave everything nullable where it makes sense.
Please come back later. If I have time I will share SQL query for setting up MySQL database with sample data and maybe some simple php interface.