How to create a many to many self-join relationship
I have a simple question and cannot find the answer anywhere!
I have a super type table - Actors (ActorID, category....)
Two sub type tables - People (ActorID, firstname etc...); Organisations (ActorID, name, location etc...)
The entities in the Participants table can relate to each other in numerous ways.
Each relationship will have a relationship type (e.g. works for, married to, family member, business associate, holding company of, etc etc)
Each relationship may have sources (i.e. evidence of this relationship - video, article, anecdotal etc)
I have created a table - Relationship_Actor_Actor (fkActor1ID, fkActor2ID, relationshipType)
How do I express this in visual form and in SQL?
Furthermore, once I introduce 'evidence' into the equation, how would this table fit into the mix?
The Relationship_Actor_Actor table would have a many-to-many relationship with Evidence (i.e. each actor relationship can be evidenced by many sources, each evidence can relate to many actor relationships)
In addition, the Evidence can also relate to other tables in the schema - including Actors (i.e. not just the relationship between actors)