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. One for entities and the other for relationships. The green one has both in one table. 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 that one father and mother.
NB! You can not add relationships between entities before you have entities available.
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.