As a side note what do people consider the best way of arranging tables for storing family relationships?
My journey was
Stage 1: Flat table (with total ignorance of the fact that a table can be related to itself)
Stage 2 : Junction Tables for children only
Stage 3: Key value relationship with tag for type of relationship
Final Stage : Back to Flat table with two fields for mother and father this time with knowledge that tables can be self referential
My eureka moment was that parent to child relationship can be looked at from the parent or from the child. Yes a parent can infinite children but a child can only have (ignoring messy breakthroughs in science) two parents - so we are dealing with a 1 to 2 relationship where the child is actually the parent record
. Put people in an individuals table enforce completion of parents fields and if you have full field completion you can in theory work out all relationships to all individuals with zero data duplication.
Its an interesting thing a one to many relationship is in itself actually a set of all the 1:n relationships and a 1:2 relationship can be considered as just two 1:1 relationships which hey isn't that the definition of a tuple.
SUGAR - need to revise some former systems!