I’ve been doing more work on a system which needs to model Relationships (see the first post in this Series).
The Relationship table is set and cannot be changed by the User. I realise you could handcode the Relationships themselves, maybe as Equates, but I prefer to create a Table.
Values in the Relationship table would be, for example, "Parent/Child" or "Husband/Wife" or "Master/Slave".
The Person table is pretty straight forward for this example. All that matters is that it’s a listing of the people who are going to be a part of the Relationship records.
The RelationshipInstance table is as the name says, an Instance for each Relationship that is created in the system.
The RelationshipInstancePerson table is the end-game. In the context (which is the determined by the system, knowing what to do with the "Name" field in the Relationship record) of the Relationship, there is an "Order". So in the example of "Parent/Child", Order=1 would be the Parent, and Order=2 would be the Child.
How am I doing? Can you think of examples where this structure will break?